DISTINCT COUNT in TOTALS and SUBTOTALS

Ingo Klose shared this idea 7 years ago
Idea Logged

Hi we have a report counting coustomer per product group:


product _group distinct_customer_count

textiles 100

hardware 150


Now I want a total in the report, that does the distinct count over both categories, let's say the 100 customers in the textiles all also bought from the hardware product group, therefore the total needs to be 150.


If I add a normal total in the report it will return 250, but not the 150.


Do you guys have any workaround or solution for this kind of problem, since I don't think it can be achieved with "normal" means.


Many thanks and best regards,

Ingo

Replies (9)

photo
1

Hi Ingo,

Thanks for the question. This was a tough one to work through. I was able to solve this, but it required the use of some free hand SQL, virtual tables, and sub queries. There might be some other ways to solve this problem, but this is the solution I came to.

I created a virtual table using the following syntax using our tutorial iVBORw0KGgoAAAANSUhEUgAAA+IAAAFUCAIAAAAvdBFdAAAgAElEQVR4nO3dfWwbd57nef3XiwZ2MFj0P8nczmxjBruD2T5gc/DcTu9ez97t3d7t3sxNcL0DTA5I7wJjQDN2HGedh47HcLc7Tmwn7agdr5M46VhKYjt+iB8ix7Et23qybNmyLFHigyiRokTqiZIokio9Wk9U3R/Fh6piFVlF8aEovV/4wrDIquKPJZL68MdvFSueAgAAALCYilIPAAAAAIAaMR0AAACwHGI6AAAAYDnEdAAAAMByiOkAAACA5RDTAQAAAMshpgMAAACWQ0wHAAAALIeYDgAAAFgOMR0AAACwHGI6AAAAYDnEdAAAAMByiOkAAACA5RDTAQAAAMshpgMAAACWQ0wHAAAALIeYDgAAAFgOMR0AAACwHGI6AAAAYDnEdAAAAMByiOkAAACA5RDTAQAAAMshpgMAAACWQ0wHAAAALIeYDgAAAFgOMR0AAACwHGI6AAAAYDnEdAAAAMByiOkAAACA5RDTAQAAAMshpgMAAACWQ0wHAAAALIeYDgAAAFgOMR0AAACwHGI6AAAAYDnEdAAAAMByiOkAAACA5VRU1IQpiqIoiqIoirJWlX4EFEVRFEVRFEWpqvQjoCiKoiiKoihKVTEAxoyMjJR6CBosOyrLDqzUQ9DAqAAA6YjpgFHWTC2WHZVlB1bqIWhgVACAdMR0wChrphbLjsqyAyv1EDQwKgBAOmI6YJQ1U4tlR2XZgZV6CBoYFQAgHTEdMMqaqcWyo7LswEo9BA2MCgCQjpgOGGXN1GLZUVl2YKUeggZGBQBIR0wHjLJmarHsqCw7sFIPQQOjAgCkI6YDRlkztVh2VJYdWKmHoIFRAQDSEdMBo6yZWiw7KssOrNRD0MCoAADpiOmAUdZMLZYdlWUHVuohaGBUAIB0xHTAKGumFsuOyrIDK/UQNDAqAEA6YjpglDVTS75Gtby8vLS0tLq6GovF1tbWnj59ury8vJFRbe7dlV+MCgCQjpgOGGXN1GJ2VJOTkw6H40ma+/fv37t3r7W19cmTJ21tbc3NzS0tLe3t7dK1Ho9HEARTozI1sMXFxWg0GjZpZmbG7HuJzfFLjMVic3NzZneXIAgrKysFHRUAII+I6YBROaSWxcVFKR6trq7Ozc0NDw87nc7uhM7OTofDMTQ0tLCwULRReb3exsbGtra2LqX6+vqbN2+2tLR0dXU9fvz4+vXrt2/f7uzs7Orqam5ufvjw4djYmKlRmRrY8PDww4cP79+//1Dp3r17d+/evX379p07d+7cuXP79u3m5ubW1lbpKpvNNjU1Zerum91d09PTMzMzKysr09PTExMT0qcNeZfDQ8vlcjU1NT007N69e0+ePDH7XsvsqAAAeURMB4zKIbWMjo56PJ5AIDA4OOh2u3t7e/v6+jwJbre7p6fH4XC4XK5QKJRbBDQ7Krfb3dLSMjQ0NKPkcDja2tp8Pt/MzEwwGHzw4EFHR0ckEkleZeqGzMZ0t9t99+7dzs5Oj1Jra+uFCxd++9vffvbZZ7/97W+rq6vr6+vdbrfH42lpabl//77Zu292+aGhIafT6fF4pH+tE9NbWlrq6up6e3s9xjx48ODOnTuhUKigowIA5BExHTAqh9TS19fX2tra1dXldrt9Pl84HFZ1HSwtLY2PjzudTofDkdtkbQ4xvb6+vqurq1/pwYMH9fX1HR0d/f39TqdTmrf2eDzSVWYDcQ4xva6u7tGjR91Kzc3N586d+yTh1KlTt27dstls3d3dDQ0N9+7dK3RMD4VCDx48uH79+oMHD0ZHRxcXF8fHx8cyCofDS0tLBR1VLBZra2traWkx+GGCIAhPnjxpbm5mNh0AyggxHTDKbGpZWlrq7u6+ceNGe3t7NBrNsOTCwkJfX19PT8/MzEyhRzU6OtrR0fHkyRObktRz0tbWZrPZnjx50tLS8uDBg87OTpvN1tbW5nQ6w+GwqVGZGtjQ0FBTU9MNLd/JqK5qb283NT0cM7+7IpHIo0ePrl+//vDhw4mJCUEQnE5nZ0b9/f3z8/MFHVUsFvP7/U1NTTabbW5uLvOSCwsLDofjzp07breb3nQAKCPEdMAos6llfn7e6XQ+evTI5XINDw9nnsicm5vzer0+n89UkMptVKFQaDyN3+/3+XzDw8Pj4+Ojo6M+n29wcDAYDErXRiKRp0+fmhqVqYGFQiG32+10OnuUHA5HV1dXMgHbbDapR0i6amBgwNT0cMz87hoYGOju7u7t7XU4HH19fQsLCxMTE6MZTU1NFWE2fWVlpbe3t6GhoaenJ8PNLS8v9/f3NzQ0dHV1mfoN5jYqAEAeEdMBo3JLLQsLC36/X2omybykz+fr7Ow0O6GeQ+5saGi4c+dOg1Jtbe3ly5dv3LjR0NBQV1f39ddfX7169e7duw0NDbdu3Xr06NHExISpUZltevnuu+/u3r3bonTjxo3Tp08nm14+++yza9eu3bt3r6Wl5bvvvmtsbCx000s0GpUOIQ2Hw2NjY9bpTY/FYgsLC93d3Xfv3vX7/ZoDW11dHRsba2hoePz4sdkJ/pxHBQDIF2I6YFShU8vk5KQ0725qrRx605ubm71er2o2vaOj4/79+z09PePj44ODg01NTY8ePRodHZWuevjwYaF70x8+fJjead3f33/16tVkTP/888+dTqd0Ekav19ve3l7omC4IwvT0tHTGHkud6UUyPT3d1tZWV1c3NTW1trYmv2ptbS0cDjc0NNy/f99Uw9LGRwUAyAtiOmBUoVPLzMyMdMimqbVyiOmagdjr9dpstmAwKI3k8ePHLpcr50C8aWJ6d3d3a2urdK6brq6u5eXllZWV5YxWVlZUiTnvo5KbnJxsbm6+e/euar58fn6+ubm5sbFR+p3mgJgOAKVFTAeMIqabGtXmiOkrKytPnjy5fPny48eP5+bmBEGw2+3tGXk8nqyHdW5wVCrj4+PfffddW1ub/ELpyNehoaGcN0tMB4DSIqYDRhU6tUxNTfX29hY6dxLTTS0/Pj7e0NBw6dKlhoaGQCAwOzsrnUM9A7/fb/b7qjb40FpeXg4EAufOnfviiy+++OKL2tpa6QuqfD6f2W9pzeOoAAAbREwHjCp0apG+SWd2dtbUWsR047cSy+mIW+mE9x6Px263Ly0tLS0tPc1oeXm5mE0vkuXl5YmJiVu3bl24cCEYDHZ1dT18+DC3lvQ8jgoAsBHEdMCogqaWpaUln8+Xw/dcEtON30rM/O5aWFh4+vTp2tra7OxsOBy22iGkSdKE+jfffDM2NraystLe3t7R0TE9PV3aUQEANoKYDhhV0NQSCATsdrupkx5KiOnGbyWW6y9xfn6+r6/P5XKZPfW4QXmJ6UNDQ2fPnpXeSDQ3N/f09ORwEsb8jgoAsBHEdMCowqWWaDTa29vr9/tzSIHEdOO3EjO5u2ZmZqampiKRyMDAQGdn5+DgYDQadTqdXRn5fL4ifAupytra2tTU1JkzZxYWFtbW1m7fvt3b22u2RT7vowIAbAQxHTCqQKklGo329PTkkO0kxHTjtxIzubsikYjL5WppaXn8+PHw8PDi4uLc3JzH4+nJKBAIFPkQUsnMzMzVq1eDweDq6uo333wTCATMfqNtIUYFAMgZMR0wKu+pZWVlZXx83OFwuN3uaDRanFG53e6mpqbe3t4xpSdPnrS0tDidzrGxMZ/P19TU9PDhw+Hh4bGxsfb29tbW1i0Y0xcWFgYHBzs6OgYGBqQPOlZXV5eWlhYzWlpaKv4hpLFYbH5+vqmpyW63r6ysXLhwYXx8fIMbJKYDQGkR0wGjChHTh4eHPR7PRg71MzuqwcHBlpaW1tZW1dm+792719jY+ODBg/b29kePHtXX1zc1NT1+/Li9vb25ubm9vd1U33wOMf3OnTvt7e0upebm5q+++ioZ06urq+/evetwOFwuV1NTU0tLS6F706VTuxToyNGkvDy0nj596nK5rl+/Pjw8fOHChRyOcyjEqAAAOSOmA0ZZM7WYHZUgCIFAoN+ksbExUz05ZmP6yMjIgwcP7qSpq6u7detWXV3dnTt3bickr7XZbOkT8FlvyNTyxZGXUa2urk5NTV29evXGjRuXLl0KhUJWGBUAIGfEdMAoa6YWy44qLwOTOkykLpe1tbWVlZUNTmxbdnflZTurq6tDQ0NXrlz59ttvI5GIRUYFAMgNMR0wypqpxbKjsuzASj0EDfkdVSAQePTokSAIG9yONfcVAGwdxHTAKGumFsuOyrIDK/UQNOR9VDkcxprOmvsKALYOYjpglDVTi2VHZdmBlXoIGhgVACAdMR0wypqpxbKjsuzASj0EDYwKAJCOmA4YZc3UYtlRWXZgpR6CBkYFAEhHTAeMsmZqseyoLDuwUg9BA6MCAKQjpgNGWTO1WHZUlh1YqYeggVEBANIR0wGjrJlaLDsqyw6s1EPQwKgAAOmI6YBR1kwtlh2VZQdW6iFoYFQAgHTEdMAoa6YWy47KsgMr9RA0MCoAQLqKEQAAAAAWUyEkdHR0CAAAAAAsgJgOAAAAWI5GTO8AkJH8WQMAAFAI2jG9KO8QgLLUoYzpJRzJJtbR0SECALC1EdMBc4jpRUBMBwCAmA6YQ0wvAmI6AADEdMAcYnoRENMBACCmA+YQ04uAmA4AADEdMIeYXgTEdAAAiOmAOcT0IiCmAwBATAfMIaYXATEdAABiOmAOMb0IDMX09RVxuV1cPCcunMpei+fE5XZxfaXwL6ooOh4JSBNeWG0dXajumdv7aPavbkaTtffR7Ne+xZH5tVIPEDCEmA6YQ0wvAiMxPbbQuj73sTj3tjh7IHvNvS0ufCoutxfhVRVFxiMBcquxdfvE4pHWib+6OvJvLk38y/OhZ7+YTNa/PB/6X2vDf98s3BleWo6tl3qwQBaWjelj/c7+sVIPAkhXyJjeVr13b3VbXjdZnozE9PXoWVE4aCiZJfPZ4rkivKqiyHgkQO7J2PzPG8a2nfb/D6dGnv18XJ7Rk/VHZ0M/vRW9MvCUpA6L20BMD3VePfGrN3btqKysrNyx641D1Xd6Qnn7K+06d3Dn2xf68rY9IF8MxXRv/elf79+9s7KysnLn7v2/Pl3vNbRtYnqcoZgeqV6fNpzMpFo4VYRXVatbW17eXB/4yx8Jy8KhkdBnjrHLsdm3opFjbUO3ngpHpKt8E18OT57aDI+E2MrySqyAy5ez0PzKwfsT/9OX/mdPjfzzs6Gf3pp+rXX2909P/tur4Xdt839ybkqK6f+lfvpvbk//9Fb0zvBSqYe8IXMTE3Nmll8KT4TL+x5vOTnH9O6zB3a+UXX5vjTlPdZ//3LVG7sOXzIWRrQQUFAessd0//Wq3bsPVt+y+UOCIIzZG88c3r276ro/+7Z5FsQVP6bPBJwdCZ1dDrcvOFveWXYm4PSMa10xF3DZByLFHk4hyR8J05HfPAjcvexxrMy84w5e+MgxJUR/I111d/BB69BtYzE9tjg56HZ0dXZ0dNi6XP1FfzBkfjhOeG29oyayltnlTVkcdnf0DM1nWmTc4wzMaF/Rof0gzd3XPdH/40Lgn54aefbziR9fCX9gX2gcXf6js6FdLTORp7E/uxyWYvqXfYvvd83/0dnQ3zcLWfrU5zqv/ubAKy9VVlbufPnn7525P1TklFt3rDLppVcO/OaifATuc/tf/eyRia3dPf7S4W9GCzDEd2vDGtf0XzxQWXmsLnXB0tD9M+/9/OWdiTvTGlRuRveebl25xnTX2QN7TjYqL+s+/YsNRAwCCspD1ph+79PXXz/ZqPhoKdR48vVfnO7Oum2eBXElienJKBFbmZnw9XS5AkIZT0HqxvTNR/5IGJ86WT/YUj94/6lw5EHgzmn3wGz0fbMxfWncY3d4gzMrMVEU1xZCAbfdpZ0zN0j3l7Thh6NuMs7bEOPmh9xutztt9yhuv2gxfWEltv27oT88FXi2JvjsF5P/6bvo572LX/Qu/sn5qQ/sC47wyrZLU/KY/uwXk3/+Tfi8d1F3i0vdZ/a/euhM88CcKIpLYft3x9989fhdrUC6Uc4z++RxNqXuWCrnzg08uvj+q68eqwtqLWlyw6ZlGOLLL7984GJ/2hVPava+8cZrqZWCdcdeffP4d/bwkiiKcwPNZw69uv9M91JyMxu7p5tUrjE9VHt056GLqrlzeT+5t7760Bu7dsT7YVIf+Yd6rn/yq9ekZoDXfvXJ9Z6QIAi1R1PvoeIppfZo5dHa5EqdV08kVtq9/9jVzkQEqj1aefRq59Vj+3btqKys3Pma/JbG7p//tXS5aiVgI7LF9NZTb2pE7caTew6cdcV/8Nafjj82VU8PWUxXPAOkqxI/1x7dUXUx8QSr3PnaobOtodTDfefu/SdueuUbrK+vPvTazsrKyh279smeCLLnomoYpVfamC6KoijGBL/TMTidj5fZkthyMX06+pux0Ke2kdorHsedgdaBiS8vexzn+/p8E6eXhUMmYnos1N/tHlZMD6+Me+z9ofy/aTMU06VBmXw4FiumzwRcfWNLoX6Xamyliem9U4t/cXHwn1aP/Phy+K9uRvc/nm0YXT7rWfxZ/XTj6HLbxMp/rRf++VcheUz/43OhV+7r7qn+iwde+fieYkrXeWafViDdMEMxXRRFUQzefP+V964bfqtQlJi+71TNe3trnigvXmr68I0PL36ZXGmp6cNX3q1V5O6ltlNvJHfnRu/pJpV7b7q3tuq113554kzd/fRDPUN1x/fs++/fPvKHBCHkf3T+8J59n7cJQrwb4PD51BW7dx+vk1KDah5RFlK8lw7veqPqcmIleXdN7dHK3Xt+Gb9uzH756O7XP70nrVV3fPe+k7fsY0LilpLXABuRLaar87VaqPHTn+85WC09Nsfst6oP7vn5p41pz4KMMb1yx85fSI/u+NNoz2t7k0+DWx/t3fXBzeQGd+3e+3Z1fNmmU/t2Hr4kdd+4zh5IPBeFMfutk6lrLMACMV0UV0Z7U90hS+FAr6Ors6Ojs8vZH5yPJVbyjIYDvXZbR0eHrcs9GF5amx7xOOPL+UKpv+/J9Tts9t7A1GIy8sWE4cT6Ll9oNBloxj1Of1C6yjMuirH5YL+ryyb1QPQGku2l8cUSQ/NNJrY8E3B6gvPB/rTBKO7pUmL08rtVbqRHwoPAnc9cox85Qse6hU+c4586g+93zZ10THzmGp2Y+thETI8M2HtHVedrjE309wQE6f96v0pVGE1F0HGPMxBOPkzsvcNCTLo+KS2rZn44ytJt8hfYYbO7B0NLorJhRtpIanndR4X0CHMm71Z4KdsQRVEUxelBh2c8JsbkOV3r9rV2gJj/mH6jX/jxmcFnTo0e654PzK5FnsaW1taDC7HR+bV1UZxcjA3Nrv1f1yPymP7sF5N/dTOqs71w7buvnWpTXei88PaJuvjvJtia7OB4XdYPo8qzzjP79p1xJq642Xfzw/0v76ys3Pny/k+agqKo7PZIC8Jp4VUU2069Fu8xSW1aGszrL1VWVla+9PrbNU1BzQ2ntlZ3bN+Z1sQqL73+3qXkrHZqSztf3v/hzb6l7EPcd6a76cM3PmySv6MJX39vb80T2b649/Erx+rUbSzOM/sOXgpkv6db14bO9BLyP/q2+tiv3ti1c9e+Q59cbk/EddfZA8oP+ENX391zslFqlfmoXjarHbp+/Jc1rYIgZIjp9z5Vtwt0n/5FPHPXHq1UXNd66s3ElOX1qt3vXk4d1MqZY5AnG4zp/kuHdydCtCR084Pd8YRsOKYfPC+b+m48uUfxJvTJ5/sSW2mrTkV2QRCE0NV3dx2vSy716f3kkyLkd7v9lvnEyRIxXRT8jmS66XH6JuZXYqK4thAacDkGIjFppU5bz8DUYkwUY4tTPme33e70TSysiWJsJep32frGVuLru+zuQEi6Yma0r7vbNxUTRVFcmfDaXQOpK+ydqZjeaYtvSxTFKZ+9d0RYXhNFMbYyM9KbnNsd93TaehIbiA65uxPrzwSc3fae5GAGnV2+KfU9nQk4u/tGZ5J3yx4fVZlJPhLWZw/YRmvP9/VNhj+MRj74oFvwT3y+OvO2uaaXcY/DL+heq/urzBTTu7qdvUNRaT+Pebrcw4uJjRmdTRflD8fkplfG+uzxX6D08HGPLCZuXTGbLYvp2o+KlXFPt+KBmPg8IeNsemTAGX8gTvns8S1p3r7ODsh3TL/givzpmcAz1WPPfjH5e19O7mubbZtY+Q/fRv6Xq+Gna+v/353pH54JqZpeMsZ055l979/Ub492nks2xCyF7VffeyUx854ppr/y6q8+vi2t8vjzfS9/fC+1kMHZdFFcuvl+aoPx/4Wvv/fqe1ft4aX4YF49dCWgsWF5TH/5lX2HLz4OL4ni3MC1919OpGXnmX2vxLc0N9B8av+ric8TMs2mn3GKT2r2yqe+w9ffO3ixX7x7fGdiJWmx9JX/4cvu7Pd068rPCRlDftut0+++sefwpW5BEITrVTsq0xytFYS647t0A4xeTHedPfDmqda0ZQ9d9AuaUSaxkVDP9RP7du3ctefNX733yZm6djI68mODMb3u+K53r6ricO3R+JEehmO63lWJBWQxXdmBI1t37P7pQ6/t3Ln71f2HTlRfac7jiZo2zhoxPZlQpnzKz/QjA07flLSA/OC5mYBTfnBmLJiIKlO+7kRgl64J+xx9YyuqCXvFKuK4p0u/zWJctpgiU84P9cQ3qLpDcwFX/MfUFVM+ZSfHdKBnIKRzi1aWfCTMRKuaB5tqvV1LwuG+4Pnj3dEl4XDyt288pmfoF9H9VWaO6Z7x1DoT3s5ENjUV01MLJze9OOx2+MLJAyDXlp+uJOep9WK65qNicditOAx0JejtHZnPOERRmc1TiV3r9nV2QL5j+hn71LZETP/TS+FPXAu3hpb++NzU39+bmV9d/xfnUidQNxzTM/SL3PtY2ZGx1PrbN6QLMsV0eep0n9uf/NFMTE8F3tQGA5cOvvHb1uRZX+YmhsNa4VoR04/VpYafjNP3PlbOij8586vf3ss2xH1nnKLYf/FAqh+o/+LB966HFcPXi+mpEWW4p1tXjjFdc+bNe/6gNFGnm1NyjOlpnb5t1XulSfMMMT050Pt156t/vXfn7qO1Fmq9RfnKrTf9XvX+977uE7SfBLVH41PcRY3pkrH+9sZvz5z45Z6d+6pbLZPUrRHTE9OX8k/xFT0Aqgij3kYiqswEnOouivhVaRlInr8V24rNB33xU4/IugnSM2VyylW96eSCyVHOBVzJad2ylmx6+dQZPGGPHO+e/sw19rFj8v2uuc9cY6abXjLNpuv/KrM0vcwotpFTTNeYTY+3TNm6nG7P4FBIdjYY/Ziu+aiY8HYrJsM1R6sSC/U7Ze8x4/0v2revswPyHdOvuqP/+nS86WV0fm1meX1hdX1obm3qaWx9XRyaWwuYa3rJOJvuPLNP3Y+RyJQZm17qNFbQuEq2jKHZdHGp+9J7r7/00iv/8HZVzcV7PcnArh/TFQE4sZj73P7EtLrGPdYb4s7jd8VEm4soimJqat1ATE9cymy6phxjuv/ioV3KD+4FQQhdfXdH1XVBEFxnD6RfKwgaTS/Cva8+utIuCEKGppfGk3syNb3oxHRVk0uo/qM9GRuGAYM2eKaX9OeOdtPL9aodiln3xpN78hrT1W+1vecPWugkM5aI6cmp7pmA0xPUmtjeSEx3+AXjMX1lwmt3eINC4oTnuml+C8f06ehvHg7VXexzNw429wXPXfE4ar1dw6FTY6FPzR1Cmqk3Xf9XWfCYrt2bLoqiKK4tz0aCAY/TlmzAKUJMj417OlXvXTtTHzSUJqYPRpf+43nf750a+fGV8NGu+Qfjy7/tWfi7ZqFhdLlueOk/10X/6mbUzCGkGXvTNWO61L9R6Jiu3ZsuiqIozk30PLpx5jf7XnpFu1WlQDE9vtGlpg/31zyJHzwqTcnLhk9vek5yPtNL3Yk9ew5++m38+NGQ33ar+mDy3NChuuO7dycOkgv5bbeqj9c0jgjqQ0htt07u25M4hNR19sCuquvJZJ3xENLkSWb0Y3pb9d7dh89Lwwv5bdc+eJ1DSJEXWWN68rzp9sSXCpw/vGfPifgDXeMQ0t3JVC9L1f5Lh3fuO9UUf6pcO/7Gjsq8xnR/7dHd+04mzu3e3/DFgd0cQqo4tUZkwBE/tUZs3NMt/9BevpKRmC5O+eyK9Y01vci2NeXrkt+QYjH9ppcsMT2t6WV+fHBUvynbupKPhO7Rb2q9XX3Bc1PhE5c9zrbhm2szbyV/+5PhDyOR4zmd6SUW6o/vKt1fpSiOe1QtT/mM6bKHYyrdxlaePpV9b1Es6On0TiQWMRXT1U0vsciwf3Ip0xBXxvrUyX560JHM6aWJ6Uursf96LfDDz/zP1AT3PpqtG1r6+3sz/1ttpGl0+Ujn/B+cTjW9/IdvI3/+TTjrCRk1zvSS6uy49/Eb8q4RZdOLPFx2f/kP+Yzpwdp3X0p02yRj+lJ4eFj2vUVLN9+XJrhNx/S0ppf+ui++6TQ8xCc1+z9sCqZm1eXXmT/Ti+yebl0b6E0fa7984ldvSl+0KH3TovxrSJMnZFSd6y11Ergdu95InJBRWqP22Gs7KyulCfm0EzIeS32lo+qEjMmlBGUoSY2AEzIij7LHdCHbt5DqPTuUqdp784TsrI3nP8p304vsacUJGRW5aG1ZCPY7ZSeqjgkBV1fPYGhhTbp21OcPr4rGY7rxQ0i7tbtZFofd3X2js2ti/DTe8sU6upNbjg6lTvBtIKbHBL/8EFK/2+Gd0HgzYnnSI2Ft9q2HQ3XfeLvHQp+OT52scQ15x8/EZlMx3R0875v4MntMz3zedP1DSOeHero9wfgVQa+j01BM7xma0/igJuPDMZVuw77Eo0eMrcwHPd3Jj0fGPV3eyTX18vqPiqWg/BDSoMchexxpDXFltDf9/JDTg47UwaGK288e05PflJr8j6zT3qh1UaGact8AACAASURBVLzYE/335/3/rHr4qG3u9vDS/3Mj+n9/F+0X1v62Ufj9LyeTMX3nvZmf3Y3+sy+Cf3s3nOnrjTKeN133ENKlpg9f2f9l/ODM5jOHXqk0FNMPnOvV6LCR59ywt/nMYfnZxFOz6Q8+eXX/qcQ4vd8de0V2RKhsw9lj+lL3l/JDSL88+GbiDhsZoth/8eD+/bJzViqyt8Z50/fWtGmcNz39nqa+PzX5n1T//aaWn0NIga3DUEzHxhiK6dFifgup/Cx87sH4aQ8Nx3SjJ2ScHNHpZomlTqLncA9Ojnps8WnTcY/TN5o6V2TqBHsGYrq4WU7IGK1enz4Qm30rMFnTGzy/MP3ebPT9JyPfzUSr1mW/fd/El8OTp4zE9PihAPETHaq/hTT7r7Kzy9EbGPU5s8Z0cWaop6uzo8M9okobmR+Ost705Hk6pcdPcjArE/12W0dHty8sGorpmidk1B/i/FCP1lnc54d64pPyqtvPGtNXg32drsCc7D9zAVfyswETphZW33s48W++CvzNjckdzcL/eGFq26Wpt57M/eml8O/JYvp/uTv9n69P/NtzQ5/Zs52MPth68Tf7X96p+S2kOidkFMVg0yf7E1+1+eHN8+9nj+mi88LBl3dWVh66ovqO0IzfzSnvTe+7+aH0bamVL71y4JP65FKKDWeP6aLmCRmzDTF1t8LX35NPgaumyHP+FtLpG+/v3H/OLfuP+9z+xCcGmxoxHTCHmF4EhmL6zFfrwkETGX3ubXHxXBFeVTdgJuA02S6et6+xKWOb8ZGA3PVOPf1ly8S20/5nfzv0TE0wGc0TNfFMTfD3Phvedtr/q5bx/sgWmI9FOSOmA+YQ04vAUExffCjOfSzOvL0uHFifzljCgXXh4PrcSXG5vQivqmashgb6BkPz0omvo0NudVN0VsT0TfJIQD71hJ6+2zr+f14Y/OOawO9XjzxzavSZ6rFnqseeOTX6+9Ujf/x54D9eDLz3cNIVelrqkQJZENMBc4jpRWAkpovrK+JS2/rsufXp6vVoxpquXp/5an3xobhuvdZr2debyr9d1ChiurhZHgnIq7nltXr/7H+7O/r8Jf9PvvL/2dnA/3zG/5Oz/v/3sv+/3R2rG5gVlvRb0gHLIKYD5hDTi8BQTAeAjJbX1kdnlluHZ7/1TF90Re4PzQ4LS0ur5XkQBrYkYjpgDjG9CIjpAAAQ0wFziOlFQEwHAICYDphDTC8CYjoAAMR0wBxiehEQ0wEAIKYD5hDTi4CYDgAAMR0wh5heBMR0AAC0YzqADOTPGgAAgELQjumlfvMAWFcHM+gAAKDwiOmAOcR0AABQBMR0wJwOml4AAEDhEdMBczo4hLTweBUCAICYDphDTC8CXoUAACCmA+YQ04uAVyEAAIjpgDnE9CLgVQgAAGI6YA4xvQh4FQIAgJgOmENMLwJehQAAIKYD5hDTi4BXIQAAiOmAOcT0IuBVCAAAYjpgDjG9CHgVAgCAmA6YQ0wvAl6FAAAgpgPmENOLgFchAACI6YA5WWN6W/XeypQdu/bsP3a5fazQwXZz4VUIAABiOmCOkZh+tDb5U8j/6HLVGzv3fd5WqEi7GfEqBAAAMR0wx2RMFwRBELpP/2LPycYCxNnNilchAACI6YA5ucR0ofZoZeoyb/3pX+/btaOysnLHrjcOVdd7k+sdOOtKLOS/eKjy0EV/4kfX2QOvf3pPEARBGLt/Pr565c7d+49d7QzlGoati1chAACI6YA5Oc6mJ0J2qPHTn+85WH3LPiYIwpj9VvXBPT//tDEkCILQeHJPMpj7Lx7au3ev/MfEdHzd8d37Tkqrh/yPzh/endjyZsKrEAAAxHTAnJx603cdviTNmfsvHd79wU35/Hfo5ge7D1/yC4IgNJ58/WhtSBAEwX/x0IGzjad/Ec/podqjryeaZq5X7X73ck9yC2P9zv7Nd3wqr0IAABDTAXNMnumlsnL3/hOpxpS647vevarqUqk9mpopf/2Dm6F4SncJ3YmcXnf89eN18aVDPddP7Nu1c9eeN3/13idn6jbnOWR4FQIAgJgOmGNuNt1/vWqP/Cwvdcd3aTSu70qEcCmPx1O6kMjpjSf3KmfgBSHkd9+vO1/96707dx+t9eaShC2NVyEAAIjpgDlme9NDdcf37Ktujads/8VDu3SbXuLdLbcuHT4YP5bUdfbAoYuN1fvivTBCWpNLqP6jPWm5v/zxKgQAADEdMMf8IaShxpM/Twb19ENId79+sjGZ20O1R/ft23cwecYX19mDBw4cSKV0oa167+7D5+/3S4eQ2q598DqHkAIAsBkR0wFzcjnTS6jx5Ot7TyVm1L311YfeSD8ho8R/6fAO2XkZBdfZAzuSk+2q1TkhIwAAmxcxvVTWlheX1/K4vZgw3Gu3dXR0dHY53P7watYVxj0dnnHTV2mZCTidgRnjy5e5rDEdG8erEAAA5RXTxz0dSZ1d3a7+0emlUo8pR4sjblvvaP5GH/Z1O32hhTVRFNeWhdmF7GsQ03NETC8CC78KAQBQJOUW01NxMLYyHwr02u2e8TJJ6iazrzkzAacrMGdqFWJ6jojpRWDhVyEAAIqkfGO6ZCno6XYPz5doPKYUOqab3TgxPUfE9CKw8KsQAABFUu4xXRSnBx3u4cX41R2e0ZDP2dXZ0eEZF8XYfNDndnR1Sg3bvYGwbN59KRxItnL3DkXHNLYsiuLa9IjH2dXZ0dHRYety9QfnY4lrYvPBfleXLf2K1CqdXU7PyPSaKIozAWeqWycegBVReCk8lFxJMdBxT4cnOB/sl6602ZV3IrmMauuKbeuNVL5QLHUTXS7f5Ejqqtji5KDbbuvQ2ocJMwGnMxBO7VCnfH+IS+FAr6OrU72++peVYZdaDDG9CCz8KgQAQJGUf0yXxc1xT0envXcouiIFPKlde34lJopibGVmtM/uHpHyvLg02mtzDcRbuRdCA67ODq2YPuXrTiwVW5kZ7et2DE6LoiiK88Nue99QJLW+3TuxIoqiOBNwKTdsS871q6aoUz/GIoMOuzuQXCngtjsGI7HEUt32Hm9wZiUmimsLQU9yCErK2XTZTemOVDGAAUd33+jMSixxP5NvJhaH3Ylr4venb2xF48a7up290j2IrUT9qaViUz67yzeR2rTdFd/Pql9WhoFaDTG9CCz8KgQAQJFstpjuznBUZmrBuYDLPhCRXSX4HVoxfcLb7QkupibKEydnmfLZ+0Mx5fq9oyuiOOXrUo5gZsjtHV9V3bzyx5Wxvm7l1mKh/u54zh33dPQMyXp6BH/yswMFvZiuO1L5QhPezvhl8dsPyveUc3A6eUaa2MrTpytps9wzAWeX/FZiQU+Xb0oURXFx2K0YvhgLeuJ7XvXLyjBQqyGmF4GFX4UAACiSzRbTFS3VscWpIY/URpGh4UQUdburY4uTPmeXravb6fYMDoVm43F1LuDqSOMMzIiLw279Jm29mD7l6/IEY+plU2FWMVK9NnCdmK47UvlC6duU3era9FCv3Wbrsrt6fYFgdFGrEyVtA8n1J7yd6bcvXaW8Z5kGajXE9CKw8KsQAABFUv4xPTJgl/emp5JfbMrX3d03FJlPTP/q5/nMB0HGVp4KodGAx2nrlk4ro7t0zjFdfbzmeGI6eoMxPdP9MhbTJWvLs5GJIV+P3eYKCFqz6Tox3fBhquV0FCoxvQgs/CoEAECRlHtMV5zpJS35KWapla0cyhZv7ZSo+gaiWNhnTyZrZS9HQlrTy+LE4FA4S9PLaG9XpqaXjcR0/ZEqm15UHefJq9RNLkujWu9D9GP64rBbdd80bj7bQK2GmF4EFn4VAgCgSMo3pq8tCxOD7u5u98hM6mp58lsZ67O5/FHp4MX5UMCdOjBSfQhpwN2t1WExE3B2940Ky9IhpPMT/XbZIaQ2R/zATnFtWQj6fGPz0iqyQ0iXhdE+e/JNxJSvq2doLhlZMx1C2u0YSB1CuqGYrj9S/UNIg15HZ+ItxLin2zUQPwx3bTky5O7WPoRUJ6ZLH2kk7ltsZT4U6A9EVrXumf5ArYaYXgQWfhUCAKBIyi2mpxqXO7scbp/yW0jVyW8p5JM60zu7HO7ByQmfPXVQ4tr0kOw0gaM+7fSbPJlghhMyqq5JnZDR1uWKn5BRWkMI9HR1dnTEv4ZIdULGxO1onJBxQzFdf6SKEzIuTiZ3ldMzMpL42ECxduYTMmrHdP37lt4Po7tLLUYV0wEAAAqhvGJ6wcga3IHMOphBBwAAhbdFY/rq5KAnIPVyxFbmdU4HDmghpgMAgCLYojFdjC1OJVoxbF2u/uDsWvZ1AFEkpgMAgKLYqjEdyFUHvekAAKDwiOmAOR3KmF7q4QAAgM2JmA6YQ0wHAABFQEwHzNGM6RU1YYqiKIqiyqVKmyUMIqYD5hDTKYqiKKrcq7RZwiBiOmAOMZ2iKIqiyr1KmyUMIqYD5hDTKYqiKKrcq7RZwiBiOmAOMZ2iKIqiyr1KmyUMIqYD5hDTKYqiKKrcq7RZwiBiOmAOMZ2iKIqiyr1KmyUMIqYD5hDTKYqiKKrcq7RZwiBiOmAOMZ2iKIqiyr1KmyUMIqYD5hDTKYqiKKrcq7RZwiBiOmAOMZ3ayvX8UEwURZtdebl9VRRXj2RfffGIXfuqI/G/mEY2ojsqtfBijnfTvirfjPrO6q5iZvD1y0Exdq0+/arZawticGi25L9oitr0VdiskCfEdMAcYjq1lSseiBeWn5dfbiikLtr0Iq9uZjUzKtWQahZtYi5J/fmhmOK+1C8HRQO5OW8xnaKoIlUJg4RxxHTAHGI6tZXr+aGYuBBTJ9cNxnSzGVd7VKqYnttmNSaztTe+wdsiplNUqau0WcIgYjpgDjGd2soVz6z2VVGeMlUhVdY0koi8i7bkRcoZbnm/Snzh+uWgxsKLNjF2bUjasjoQayZp9by4xqjSa/baQuY5eNkdEWWfKij3wJFUANDJ4gabXuS7Qn4HNe+LtE17ahX5myKN/Zy8v3EbeqdEUWVXhv7klxoxHTCHmE5t5UoG4iNh7ZD6/FAslU0VHSPGZtPtq2JqMXnjyqJNTGu2SRuV7MJFmyyPKiO74iqtwUjSY7RGho7/mLoLyqCvej+TLCMxXbH3UpvVvS9Spk/sB/li6b8Umz3zLqKozV+lSBCmEdMBc4jp1FYuWSCWhTxlSJUnYNXyWWP6kbByMjt1VaZsrXkIqWrOWHHTWXpU5HPMov6Ssjub3GD9clC5vPoeSWUgpus02+jfl1T+Vt2EzmGp9lXl9jl6ldpaVYzEsGHEdMAcYjq1lUuRHZNTxcowrQiRqdhqJKanJ8VFWzxr6q+uTrTpB48qO1XijM0cS5Pryrgs62lJi+nKs8TE5RTTtfN9hvui2mbqR+1dl+29DUVt8jL7178kiOmAOcR0aiuXaoo33vqSIaZnuEpjmbSYni1rao4q3v6RzLiqaWZZyQO37okXU40riVn2+G1pzKYbbR3JOabr3xezMV3RtkRRW6+Knx9yQEwHzCGmU1u5NANxcCFWnKYXozFdfX73jM3o6pGkpedk3lVna622H71m9Bqdbaqvytr0on9fdGO6djcLzejUFq+SRgmjiOmAOcR0aiuXXiA2cAipfuuz4UNIjcf0+My34njKVITVn0iW5st1mssVM9nxmXXtQ0iVHTga99rwIaTJu5y8j7r3RTemqxJ58iaU3UEZ5ukpajNWSSKEWcR0wBxiOrWVSzcQGzj1ofZXI9WkHdCpe0JGUzE9MYzEFhSt2BmbPVRN22lniE9dnorIirugOAJVf+ZbLTg0a/CEjNr3RT+mq1aRDUnR6U5Gp7ZUZfpLbxnEdMAcYjpFURRFlXuVNksYREwHzCGmUxRFUVS5V2mzhEHEdMAcYjpFURRFlXuVNksYREwHzCGmUxRFUVS5V2mzhEHEdMAcYjpFURRFlXuVNksYREwHzCGmUxRFUVS5V2mzhEHEdMAcYjpFURRFlXuVNksYpB3TAWRATKcoiqKosq7S5m+DNGI6AIOI6RRFURRVjlXa/G0QMR3IHTGdoiiKosqxSpu/DaLpBTCNmE5RFEVRZV2lzd8Gacf0okxEAmVJM6YDAADkFzEdMEczpgdQCux8FI7Ip2QUtamrZNHbDGI6YA4x3TrY+SgckZhOUZu6Sha9zSCmA+YQ062DnY/CEYnpFLWpq2TR2wxiOmAOMd062PkoHJGYTlGbukoWvc0gpgPmENOtg52PwhGJ6RS1qatk0dsMYjpgDjHdOtj5KByRmE5Rm7pKFr3NIKYD5hDTrYOdj8IRiekUtamrZNHbDGI6YA4x3TrY+SgckZhOUZu6Sha9zSCmA+YQ062DnY/CEYnpFLWpq2TR24yCxPSQ3+32hwRBEIS26r17q9s2uD3AQojp1sHOR+GIxHSK2tRVsuhtRu4xvfZoZbqjtYIgCHdO7N7zcaMgCMR0bD7EdOvY2M53ttyqTWl4nM+RKT1+rLXxxw21aspBPG7IPizn48dO6T8tt2pvtTjzO0TlUDU2n7x5/cEaHFhqS1YhEtPzWPZVWfCIXasv2A3VLx7R2viR9EgWXpQtMHttQQwOzWbe+PP2xeel/wzFxIXl53Mc5OwRe5YboopTBY/Y+bCRmG4kf8tjOpEdmwEx3Tpy3/mPG5Sx09lySzOG5oFufk27wvQgNhjNsw9Rdv2tW7fUCylufkMxPV93JK9EYnp+avbagiiKq0eSl9hXRVG02QtwW/XLQZ33AEfCGrncVNTeWDRX3G7W9wNUcapEwdscYjpgDjHdOnLd+Y8bNOKw5oV5YDymmx5EkWK6dLWzRZ3TielU9np+KKbI6PoX5qFMxPRwRc2iTTSRmInpm69KFr3NKEhMl+XxxH/lLTLJ9bz11Yfe2LWjsnLHrjcOVdd7k1uuPHr+5ol9u3bEu2hCPdc/+dVrOysr1UsCxUdMt44cd76RZhJ5S0oqQKripOxHaZuplWQX63XVaA5DfgvyBeQdMtJlsp6dWy1OA2PR2VCmISZvp+Gx7D+pi2Xbf9xQW9vQkrpQsaByD6oWUW9Jd+8XmUhMz0MZS6WylpjUwvbVtDl46Udpm4vXFhLrSPm7fjmYvpFEacV0efJWjFPeISPN+j8/FEtcELtWL1tRemNgT920/FMC2VrSxmdTY85D4qc2WnmJ0YVWrJiu/r8ghOqO79n337995A8JQsj/6PzhPfs+b4tvuXLHnrdPN8WPQhVcZw/sPnz+kT8kCMKY/dbJfTsPX/IbClRA/hHTrSO3nZ996lbRfSL/IXNMV4ZqrbCtoH2F7NLkf/WmreWXGx6LxhWZ3rfIw7lqubRhad6oamDJFeQbVmxJ+XagUB9yZCcS0/NQi7Zs/S3PD8VS3er1y8FkyM4Y01WrxG/C3Gx6evSfrVDNmsvGIL9cGdNFxeXy5TUGyWy6hapEwducfB5CqpXHdWO66+yBX5zulm0vdPXdPScb41s+eF42Yf7k8317P70/llwwdR4ZoPiI6daR2853ttzKPJmuzvGpnzPHdNk1WmFbzXhM14uqGWK65lj0hpIhpivvsXLLaTFd80ZTSz1uUN4L7f2a9usx9OFHAYjE9DzUoi3LAaPq2JpKwJljeipzy7aQj5h+JKw91Z0hpqfeh6QGoBfHiekWqpJFbzNKNpt+vWqH3oliao/G/5Mwdv/0odd27tz96v5DJ6qvNPeQ0VFCxHTryG3nZ51N1zy2M31aW6PpRWsTeYjp8r4Q+RoZYrrWWHTvuH4QVq+SNh+u15uuEdOVZ9ZRtLSoW33SENPLtbLOpqctUL8clKJzlqaXZNLNc0yXN8+oO1i0Y7rsFlM/6t1xYrqFqgSh27ySxfS0LC7fsuZVY/3tjd+eOfHLPTv3VbeS1FEqxHTryHHn66TSZBhPu14v6BYgpsvnktMXSEZY9UR1gWK6ZmTWukkjMT1D+4psS+lHqpaKSEzPQ+ml0uQse1qcTUbnwsf054diiQs1xpnsUE+1pxPTN1eVInWbVrKY7jp7YNcHNzXTtiqmq5tcvOcPcsIYlA4x3Tpy3fmaeTWVIg03vciCZ55iuuKyrD0qJmO66aYXrWAta2/PZTY9+6R96ZrRVURiel5KlbZrwhWKHm6jTS/6q+Qc0+VJWjc9J1c0GdNpeimDKln0NqOYMd119sCuquuJHvNQ3fHduw9W37KPCYIQ8ttuVR+vaRyRtqyI6f7ao7v3nbxlkw4h7W/44sBuDiFF6RiP6Rof8lsifmyIfsdICVqIM+z8LDKfN133EFLFMZjS7zdrTDc+h60+b7pOb7r2WwDDM/saG9IZon5Xjkbszx7TVUew6h6cqtoNJYvtIjE9P5XlvOm6h5DK/1+zaBPF7DFdv8cm23nT9XrTZRvUPJxUN6arTjqp3H56+w1ViipJ7DarmDFd8NYee21nZeWOquvxnxMnZFSeZzGt6SXUefXY/t2ckBGWYC6mK/OFMqaUJUud4TrDzjdA2dGhNa+t9eYq9ebrVstjI9E4sYbm9H3GFmxVCNcaT3wbDY+NxXTVrarPrph+R7UerLIrUjdvKKar77RqefU7oBK/txWJ6fkr+akJNb6FVPOEjIrLV48YaXpJdqqk5WAz30IqO22iYjzSWwXRZjcU0ys0Tsgov1MFOG08ZbI2kp6LJveYDmxNG4npVuq8zdEmiulAJiIxnaI2dZUseptBTAfM2VhM15x1TJs1lNZsaZDPOj5u0JiD1Pq2GPV2pQ2lZjYT30GT6rbWmN9Uj0B1dg+NK4xM2uZbhp0PbJBITKeoTV0li95mENMBczYa03VPaqH6RhjVD4p2AFlDr6I3WGshxerSD8owrYrWijMApm9PMTS99mKdb9Ex7tl/9zeapVosw84H9Bh/dJU8RlAUlUNpPsHTFytZ9DaDmA6Yk7+Yrv89Lulfy641aa0+wk9xxKHsCnXI1p/cVp2oW7UNrdN1aHZEb/xg0rO1t7b99Q75K+y2v95xtvaWarEMOx/QY/zRVfK0QVFUDvWP3mpSZfR/9FZT+mIli95mENMBc/IX0/W/x0W5pk5M1zxC9VaLM/0KvcP55ENSdajobsPAmcPz077+wecXfvzCLukV9scv7Prg8wvpy2TY+UAGBh9dJU8bFEXlVv949+lkRv/Hu09rLlOy6G0GMR0wJw+96bKucCPf/Wg0picuMBHTE+8UNL/2srQxPRAIvHWiZttf79j21zveOlGjuUCGnQ9kZuTRVfKoQVFUzvVPXnzn2X/3N//kxXf0FihZ9DaDmA6Ys/EzvWT/HhdDMT3XphetE3NrLqjb9JLtezjzeAb1PYdO7Dl0Qu/aDDsfyCrro6vkOYOiqI2UZkt6skoWvc0gpgPm5PG86brf42Ispud4CKn2t2iqzlyd8RDSbDFd/+t48izDzgc2SCSmU9SmrpJFbzOI6YA5+f0WUu3vcTEY01XrGzwho86X9dTWNjxOxXGtU0JqDEb/S3U4IWOhaXyDUsaHGkwSyyum21c1vjaoJpz2dZvJhVOUX9up+HIfierL7VNf7qP+3qKE+LXy7wSNfzeQzpLZbzRR0na0v0JIafVIjeKrQ1Ml/xIizQV0hpTcrIEb1aC8R8rta3wvqXIB1W9Q9bVKsp0T3+H1y0GNzSYX0Pl16O72zVk6+8BaiOmAOcZjumUk2+GNs9S3GOkqh51fMOqYvum+8LbUxDKM6cEFdeJ8figWXIjJQ96RsKgM9Is2UZ7nVN/xmb5AWkxXJUjlivKYrnw/IK/sN6q8m5q5VusmNhbTDWRWwzeqXkD9RaqK3ZgWsp8fion6X3eqMRhpC+plNEab8Te4yavokTsXxHTAnDKI6ek9J6YTNzHd8jLH9E3whbelJpZfTF+9NhRLmxpfvSbPYdoJctGWSo1a8bR+OSiLd0WJ6eoblepIWBTDi88PxfTuRTnEdPneNvwriCf1xGYNxfRYUP1BCjFdUSWL3mZox3QAGVg9puv10pjcADHd0rLF9EIeFLAliGUY04/ULwflkcu+Gg+1st4SzeiZdRkpH6ctXMiYrrzRihpZcNdK8GUT0w3NtWsuoE7hWWP6tXrVWwJiuqJKFr3N0I7ppR4VYF1lEdO3iC2987PH9PJ4r2VZYjnG9JrZawup9HYkLNrs8hymn5VTsU9/Klcrmhc0pqs2Lp9OVid4vZuwXkzPGoszLJC610ZjuuqgBWK6okoWI8wgpgPmENOtY0vvfGJ6gYllGdPDzw/FEuFy0ZY83jEV0zUPM91YTFdLJtTsh5AmrjUS05XLaBwyq5eYNeV4CKlms4qxG43fyvNDMa3GeuW91lkgl5guraX/Po2YbnHEdMAcYrp1bOmdT0wvMLE8Y3pFsu/FvipFuk0ym64Opukd3sym63XFJPcVMV1RJYsRZhDTAXOI6daxpXe+od50UnruxDKN6Ym+F6njpcJA37mRZY6EUxcWszc9eeERzUyV7fhIC8Z0vWtTk+hGetO1PxiRXajK8fEPH4jpiipSaNgYYjpgDjHdOrb0zjdyphdS+gaI5RrTpb6XZZu8yyIPZ3pRnqq8SGd6SZ8PVt1l+TbLJKZrb9nkmV5qZq8tZLxd9XS7dM7HWPqht8R0iyOmA+YQ061jS+98zpteYGLZxvR4b7TWiVkqyvS86frvLmQDK5eYno/zpmtconwnk94VEz+TOjE9VUWNDrkipgPmENOtY0vvfL6FtMDE8o3pyuyokcMSiU2y0W8hNRzTtSQbdTLc6Ow17e8zUk0w5xzT1Wx27SGJorih41YVd0G5Q7TunaLPR3MnK29I8WvSal6XjvclpidL+9dkMcR0wBxiunWw81E4YnnFdIqiTFbJYoQZxHTAHGK6dbDzUTgiMZ2iNnWVLEaYQUwHzCGmWwc7H4UjEtMpalNXyWKEGRuL6esr4nK7uHhOXDiVvRbPicvt4vpKIe8OUHDEdOtg56NwRGI6DcPu5AAAE81JREFURW3qKlmMMGNDMT220Lo+97E497Y4eyB7zb0tLnwqLrcX9P4AhUZMtw52PgpHJKZT1KauksUIMzYU09ejZ0XhoKGMnkzqi+cKen/KQ2xleSX9C5ZRHojp1sHOR+GIxHSK2tRVshhhxsZieqR6fdpwRpdq4ZTe1mYCzo6Ezi6H2xecXcvTvSyRcY8zMKN1xYTX1ju6VOzhID+I6dbBzkfhiMR0itrUVbIYYYa1Ynoy1cZWZiZ8PV2ugFDOk866MR3ljJhuHex8FI5ITKeoTV0lixFmWDSmi6IoijHB73QMTm/sDpYSMX1TIqZbBzsfhSMS0ylqU1fJYoQZVo7porgy2msfiMR/WAoHeh1dnR0dnV3O/uB8LLGSZzQc6LXbOjo6bF3uwfDS2vSIxxlfzhdKtZbE5oP9ri5bR3wDso6apZAvvoKjdzjod3rGU9uWrnIGZmQD6LB1uZIjSCwmbdpm7x2aTmx53OMMhJODs/cOJz4bGPd0xG9D2qo9Oar5cv70YIsgplsHOx+FIxLTKWpTV8lihBnWjumi4HckM3OP0zcxvxITxbWF0IDLMRCJSSt12noGphZjohhbnPI5u+12p29iYU0UYytRv8vWNyadAXJlwmt3eIMzyQ3Y3MPz8S277H2jySt6bJ2pmN5p7x2KSkd7Lg67nb7Q/EpMFMW1hZDP2TM0n1osuYEJn6PbMy7d5rinq9sZ38Dawpinyz28GL+iI3Ub3bIbd9l9UwR1iyOmWwc7H4UjEtMpalNXyWKEGRaP6TOB+NT2lM+laH+JDDh9U9ICibgcXz41/S6KsWCi7WRx2K3sn1kajV8QGbD3jspO5i5/Z6DYts7IxJmA0xNMZetYqL8rvsFxT1cisYuiKE54E28AkjF9ymfvD8ly+XSgZyCkc4uwCM2YDgAAkF8Wj+mJzCw/C0xC4opk+4jGNpLd4eMeeXwXZWuqG8iV+Vu2bXEpPOSRWltkA0hfLPXeQLXp1IKJmD4XcCUm2FE2iOkAAKAIrB3Tk73pqhlrxUobiOnBmPGYPhNw2d2BeNdLpjRPTN/kiOkAAKAIrBzTY5EBR7xTJTbu6Zb3j8hXMhTTF4fdyo0ba3pJbVu9AeViek0vWWJ6WtPL/PjgqJB+L2ElxHQAAFAEFo3pa8tCsN8pO296TAi4unoGQwtr0rWjPn94VTQe040fQurq1O5mmfJ1u+KHqq7MBL0O+WKdqS1P+Bx278SK6tZV20vG9Jjglx9C6nc7EuvCsojpAACgCKwV0zN+C6nsfIh29+DkYiy+krGYbvSEjEPBQZ1ulqXUeR9dnpGg3+UKzCUWG0psWn7aRQMxXeSEjOWHmA4AAIpgYzE9ms+Ybg2xcU+3b8rMGuredGxyxHQAAFAEG4vpM1+tCwdNZPS5t8XFcwW9PzmYH/F4RoXlNVEU1xYm+u3dJk9dTkzfYojpAACgCDYW0xcfinMfizNvrwsH1qczlnBgXTi4PndSXG4v6P3JRaobRvHtokYR07cYYjoAACiCDcV0cX1FXGpbnz23Pl29Hs1Y09XrM1+tLz4U1zlCEuXNREy/sb2iYluVN8PVGa4FkBNv1bYKSdrzy1u1rWL7jeSPN7ZX6C0JwLI0n7mpJ778SZ68Sn1ZudhYTAe2HsMx3Vu1rWL79u36Lw4bjunl/NIDFMaN7Yk/0t6qbcq/19Lf9sQl3qptySdgaiUA1qb9zJU9h9MmyG5s14ju5YKYDphjNKZLGTpTkiamA/mmeFKknmLSRNu27dt1njJ8tAWUo8QzV/EMVv5pvLG9Ytu28v1bSUwHzDEY05OvE+q//8lP67Zv316xreqG/PVEvpLysztv1baK7VWJz/QSL0uyn+SvS8n/e6u2bdu+fVuFfL5B8yNBYLOQPRNkzz3vjRteUfedrXx6DkC50Hnmyj8d81ZtyzxfZnXEdMAcYzFdFhAUOV31ify2Kq/s9cNbtW1blVcWJVKvQYmlFdvQjOaiMqZXaCxBKMGmluhRTX+Qp/+1ji/L8wEoK/rPXPkfuLQ/quWHmA6YYyim60TztMsVLyBaLyiJFVQvPCZietpaaeMANpFUX2pab7p+nxi96UA5SnvmKp/1N7anXgvK9QlOTAfMMRDTZQecJ6XFZkUEj8fzZCKXy9jTkj2myy9M2yywyWR+O6r715o3rkA5SutJV/6JTX1STUwHtojsMV37g/X0w1xSP9zYvq3qRjyla7+g5Ceml+vrFGAUMR3YQlSHiaef2Sl9uqzMENMBc7LGdM3Pz2WHo6t605PraLWnZA7cisl5rfPQKUKJ1maBzUZ1QId+04vqMDNSOlAWNJ+5mY+4Kuc/ecR0wJxsMV1nWk5x3KfUdFK1XRGyNacBtDrLlceCVlQof4hvOT2ma20W2HwMf71Rhq9DAWBZ6c/c9JnztI71cn2OE9MBc0x8CykAAECuiOmAOcR0AABQBMR0wBxiOgAAKAJiOmAOMR0AABQBMR0wh5gOAACKgJgOmENMBwAARUBMB8whpgMAgCIgpgPmENMBAEARENMBc4jpAACgCIjpgDmaMX0wGKGKX+x8qnDFQ4uiNneVLEaYQUwHzGE2HQAAFAExHTCHmA4AAIqAmA6YQ0wHAABFQEwHzCGmAwCAIiCmA+YQ0wEAQBEQ0wFziOkAAKAIiOmAOcR0AABQBMR0wBxiOgAAKAJiOmAOMR0AABQBMR0wx1hM91Ztq0jZfqOoQwTgrdqWfOLd2F6htK3Km1iG5yhQblLP6PhTWRQzPp3lrwblhpgOmGMkpt/YLn+d8FZt21gKKOeXGKAkbmzXyd6pJ6fsaXpju+LvPQDL8lZtSz5bjT2ddV8NygExHTDHQEy/sV31J3+DOZuYDphyY3vFtm1azxrl3/LU05TnGFCOEk/jDE9n3VeD8kBMB8wxENMzTJ97q7Ztq6rarv5kLvVpXeKVxlu1bdv27fLOGWb7AEO8Vdu239AK3vJpOCXlB2AAyoHOM1r+dNZ9NSgXxHTAHGO96bJmWMWLg5THUx/SJeYB5J/Wbb8hKqN+Ob/EAEXmrdq2rcqr9azRfSLpx3cAlhSf29J43sqfzvqvBmWDmA6YY/JML4m8rpW44z8oLkt8dKd6pSnblxiguG5sl5436c8anefRho8dAVAiaR+DKZ/O+q8G5YOYDpiTywkZU5k7vWeuyqvZVCdfsJxfYoBiurFd9zMozaeRt2ob7WRA2Ur78yn7McOrQRkhpgPmZI/pGm2uyRcJU7PpxHTAlLQzL1akP7VSmEcHylzqaZ32dM70alBGiOmAOUYPIVWdzlX+ShL/fzLO6/Wmy2M6E36AGeo3t5qz6zytgHKjOkA09UG1/tO5nKe6iOmAOQabXhRv5FU9Ldu3a12cdqaXtBPBlOvLDFB86j/M6sn09Kk2nl9AWUj/IqMsT2diOrB15NKbnlLOrxYAAKCIiOmAOcR0AABQBMR0wBxiOgAAKAJiOmDOxmI6AACAIcR0wBxiOgAAKALtmC4A0EFMBwAARUBMB8whpgMAgCIgpgPmENMBAEARENMBc4jpAACgCIjpgDmaMX0wGKGKX+x8qnDFQ4uiNneVLHqbQUwHzGE2HQAAFAExHTBHM6ZHAQBA+Sht/jaImI7NJjA0PVnI7RPTAQAod6XN3wYR08tZW+R3v4x8M5VxkdvhF+4XazxG3I/86Ha0kDcQ+dkX4ZODBbwBYjoAAOWutPnbIGJ6Gfvm2/D/filLCt+cMd0R+dE3kTxv0zBiOgAA5a60+dsgYnrZmoq88FWkuS/ykyuRgP5SxPS8I6YDWTx557mKiufeeaK85MWL5rd08cWKigrVtgzdupKpm1aun3lV4/dLc8lcdwuAjStt/jaImF6uAs3h525HBWH66Nfh6hHZFVPTl66Hf+fzcMXn4X/1beRynRTTo3u/UizWfTf8Z3enBUGYdEf+9kL4+zXhis/Df3glUuePL3Dum/Bb7dG3roS/XxOu+CL8F/XRZMO3tz3yF2fDFTXh730Z/tuW1OWdLZE/PR2uqAl//2z4rfbpxMXT9+6G/+CLcEVN+A+uROoaNSP1dN3t+DLfPxt+89G0/PI//DJcURP+nfOR0+74wCpq4vXC/ehb51M/vuUQBCHyQk3knCAI0luUlujpbxN743rUm9jupD1+F75/OvzSo8gL5yNt0hX+xF2uCf/B15Fv/EI6YjqQRTzoygJo5jyqe+3FF80ldO2tXXzRRNC/+KJ84E/eeS5jUt9gTAdQOqXN3wYR08vU9KeXw0f7BEEQeurDP6lP5lqh7Xb4B1cij0YEQZh2PIr86y/is+ndd8N/2ZxcLPrWeSm1R//ubPh413RQEISp6UcN4R9+G4/d574J/+Bc+Gjn9KQgBD2Rn34ZPtYvCIIgOCI/Oh3+yCFdHn3tfPjv2gRBEAKtkT+5FLk9NC0IQsAR+csz4U/9giAIgfuRH5yPXPPEL//pmXB6TA80h5+Jjzm+7of9giAIbXfCf/JtpCtxX577KnJ3KjEG3dl0RUz/wZnw3z2YDgrC5Eh0b2KowkjkZ2fCrz2KX/7R1fD34jF9uvpK+C8bo4EpQZiaftQYfuZSpCdt1xPTgSyevPNcxYvvvPNcKhvnFtNzi7bpa5nYTtobgxzfYGxgSQBFUdr8bRAxvTz1R37ydSJBjkT+08VId/yK6Gtnwp/KZs3v3kg0vfRF/iy5ii3yL7RbZVLzyue+Cb9wP5X+v66Nb+fbb8PbH8nWsEV+/F1UEKaPXVLM1geapDcP059eDr/Wobg8Pab31Id/+F00mPgxODQdmBIEIfp35yPNssXu3kjkbMMxXX5bT+7Efww0hX/vhmwMU5GfJmL6sa/Du2WfA/gGNE4aQ0wHsohH0ifvPJcIpvKQmmoqkQJxvLFFPeMt6z158WL6WtHok3eee+7FF59La0zJEtOTN6cZmjNOnydXTa5tfMtZm15U91dvm4m3QOpdZnB1AAmlzd8GEdPLUvfd8H9qSqXJ6itSv4cgz9kSWW96agK++WYqak+6Iy99Hf6dzxOtI7KYntimfDvRw+cT0+oKkRdqUs0nUv3odlQQom+dj4fmOM0+8qnosSvh738R/uOvI2/ejbZPCIIgCI7Ij9K2Gb8vhmO6oi8/sVhav35qp0nNMN8/Hf7zq5H321LvHOSI6UAWyfR58cW0OCtrKkn+N/tsut5aWqtpNr3EL0hd9+Sd53Q6YWRhXG8zyTYa5fYybTlLTNcdo3qb8r7/1EqGVweQUNr8bRAxvRwpGrKl+uFNKadmiOlCoDn8k7vTghB97XziNI4jkZ+dCf/sXtQnJWPlbLq5mH4u0qo9VAMxXTIx3d4Reeub8PdPR76dEgRH5EdXI9pnQC9YTJcEh6J37kX+5lz4B99GmU0HTFPkQ2WcVU8/q8KuznZ019LKnmmHkOr03mRtfE/kdY2hpY0865Yzx3TNsWS91zmsDiChtPnbIGJ6GbJFfvi1smd6JPKXX0n9Ieqml9Y6WSSdirxwPtLWFvlRsuWjLfK78rybav/Qi+lpTS99kX9olGbNFcsnqJteJlu0etMHpC6XuNNXwz9rFYSpyAunI+dGBA0bi+m6TS9T073yLpepyE8/j3ydduPEdCALVS+H1P+iESgTl2aN6bmtFU2bcVcn+OfeeSK7TLcLRh6LleFfEdO13xpojCrtQv0J+LRtqu+dydUBJJQ2fxtETC8/d2/IDwaVTFdfifdtt91JHo457XgUee5zRVT95tvwc+fDe22Jn/sjPzkd/sg1LQhC0BN961LWphdh0q44hPQXX4d/1jotCIL3fuSZM/HLJ0ei125HTvYIgiAEWiPPyA4h/cvTGjH95vXwczeijglBEIRAb+SF0/EJ+9bb4d+5GF83OBT96HrkptSG4oj86GKkI5ns70d+tzaSaFDJHtPVh5BeSd7r6EuJy4Wp6a57kR98zSGkgHnKSHrxRamZuhCz6Qa6wOXnecl6HKfyPC+KdRRZOPNsevZ9or4w+3S41oU5rA4gobT52yBiermZim4/rTwDoyAIgjD5KJI4Scv0N9fjveZ/cCXy0XfKqNoW+V1lg0dnS+RfSWc8/Cr8ZmvkhS/i88d6MV0wcELG730Z/pvGVGN3a338ZIu/cz5y+q5GTBemoqe/Df/gi8QZEluTU9qpEzJ+/2z4zdTl0V9/Hf5eTfjfN04LgiCMRCrPhitqwi89FgzFdNUJGVtTnyGkTk9ZE/7Bhcgln8ZvgJgOZKEKiIrzM+p1meu1r2ykoz1OFtRl12mvrj7le2poqiHo96abOEW6bm96qg9dvU3NmG58dQAJpc3fBhHTseU5Ij/SmjXXQ0wHskgLhYrek/RztiiDvPZ2NM/0YvScKhdfrFBm7IxNIPITumieS+W5dy5qNd5k3LJmx7yJM71o3escVgeQUNr8bRAxHVtPMPrKlci13nh/zpGvFSeez4qYDgBAuStt/jaImI6tyNse+Yuvwt+rCX/vy/Bf3E59O6kRxHQAAMpdafO3QcR0wBxiOgAA5a60+dsgYjpgDjEdAIByV9r8bRAxHTBHM6Z/CgAAykdp87dBxHTAHM2YDgAAkF/EdMAcYjoAACgC7ZgOIANiOgAAKDSNmA7AIGI6AAAoEGI6kDtiOgAAKBBFTAdgVqmfwgAAYHNiNh3IHTEdAAAUCDEdyB0xHQAAFAgxHcgdMR0AABQIMR3IHTEdAAAUCDEdyB0xHQAAFAgxHcgdMR0AABQIMR3IHTEdAAAUCDEdyB0xHQAAFMj/DyAmeVF1qJ6rAAAAAElFTkSuQmCC" alt="/DyAmeVF1qJ6rAAAAAElFTkSuQmCC" />

Also, an append sub query is required here to prevent the having clause from the virtual table apply to the entire master query.

Anyway, this may not be the most ideal solution for you, but it was what I was able to come up with so I thought I would pass it along. Please let me know if you have any questions or concerns with any of this!

Kind Regards,

Dustin

photo
1

Ping---Hi Dustin...do you have some more input for us?

photo
photo
1

Hi Dustin,


Many thanks for the input. How would that work with other filters in play...let's say there is another dimension like country and I want the users to select which country they want to see?


And when I see this right, that would be an appended row or column in the report...how would I make that to look like a total. The actual report we want to use it in, is a bit more complex...other measures, prompted filters etc.


I'm just fishing for a solution here, because we can't be the first who are trying to do something like this, I hope.


Best regards,

Ingo

photo
1

Hi Ingo,


Apologies for the delay on this response!


As far as adding another dimension, this should update the report as you would expect. It does change the initial scenario you presented initially because the report is now looking for common athlete ID's that appear in adventure/sport AND by region. For example, I have added a Region field to my previous example which produces the following result:



Filtering by Asia and Australia produces the following result:


/Dx+GS0qEToQEAAAAAElFTkSuQmCC


The only way to get a total here (that makes sense) would be to remove the region field from the sub query, and just filter the report. Then you can apply Totals > Average to the field to accomplish what you are after:



Could you let us know if this is the information you are after? Although what you are trying to accomplish does sound simple enough when you explain it, the SQL behind solving this problem does make things a bit more complicated and does limit how this particular set of data can be displayed in your report to a degree.


I look forward to hearing back on this what you think on this.

Kind Regards,


Dustin

photo
1

Hi Dustin,

We kind of found a similar solution, but since it is not very pretty, very cumbersome to implement, and almost impossible to use for non-technical users. Wwe had hoped there is a different option.

Ideally we would have liked a more "intelligent” count distinct function for the totals/subtotals. This is now more of an idea/enhancement and might sound a bit crazy.

If I want to do a total of a count(distinct) column, the aggregates based on the report data will most likely be wrong if there are overlaps possible between the group by buckets. Like in our example count customers who have bought from a product group. The count in each product group will be correct, but any total function will be wrong, if you want the total to be the distinct number of customers who bought over all the product groups.

But as a report designer I might know that and could select an advanced total function (like you do for calculated measures) The new function we would need some “magic” and reissue SQL queries to each level of the total/subtotals to be displayed and use the result instead of trying to calculate over the result set.

What do you think? Is that idea worth being put forward to the dev team?

Cheers,

Ingo

photo
1

Hi Ingo,


Thanks for the feedback here. I do see your point on this, because the context of the total aggregation doesn't really line up with what you are trying to accomplish in your report and there really isn't a way to produce a sub total without making major modifications to the report itself (removing particular fields etc).


As I'm sure you are aware, Yellowfin currently supports calculated totals which typically solve a lot of the more complex sub total aggregation issues folks run into... However, because this particular problem required the use of some non-traditional SQL via the virtual table I can't see how calculated totals could be used in our case.


What I'm going to do is convert this case into an enhancement idea for our product team to review. I'm not exactly sure what would be the best way to implement functionality like what you've mentioned, but it is certainly something the team can consider.


If you have any questions on any of this, please don't hesitate to ask.


Kind Regards,


Dustin

photo
1

Hi Dustin,


Great, many thanks! I don't know either how and if it can be implemented, but I know I have been struggling with this kind of problems as long as I'm doing BI and it would be great if someone would find a solution. ;-)


Best reagards,

Ingo

photo
1

no problems

photo
2

Hi Support,


Has there been any progress on this Idea/Enhancement or possibly a different work around?


Kind regards,

Mikayla

photo
1

Hi Mikayla,

I hope all is well,

I have looked at the status of this and I can't see any activity for this Enhancement. Going forward I will add your comments to this Enhancement ticket that we have so that we can hopefully get some movement. As an alternative it might be worth reaching out to your Account Manager (Alice) to further push this to see what options we have.

Regards,

Mark

Leave a Comment
 
Attach a file