Hello -
Goal: I am trying to build out a line item report that lists by line item, all our outlets and if they are purchasing a particular brand package.
My Process: I have built out two queries. One to list each outlet and one pulling in distribution.
Issue: The report eliminates some Outlet +brand package combination. See example image. How can I create a report that will list uniquely ALL outlets with ALL brand packages?
Your “outlet” query needs to include all outlets and all brand packages for every outlet so that all possibilities are included.
Then create two variables, one from each query, that concatenates outlet and brand packages. Use those variables to merge the queries and put the merged dimension into your query block (you can hide it).
Your other option is to build it as a crosstab report, with a matrix of product against location.
You’ll then get the full picture with gaps where there are no transactions.
If a brand package isn’t in the resulting data for any of the outlets, it won’t show up in a crosstab. If it’s important to include all values to see a complete lack of activity, you have to look at other options.
Agreed, I just thought it would be easier to spot gaps by outlet for products with a crosstab.