Hi - I don't have much experience with joining queries in Report Studio. I think I understand what is happening but I'm not sure why and how to change it.
I want it to join two queries after it has summarized them.
I have attached examples of query 1, 2 & 3 results.
Both Query 1 & Query 2 are summarizations, where the numbers are counting distinct on a key with certain conditions.
When I outer join them together, it appears to be taking the summarized version of query 1 as above, and then joining them to each individual record in query 2 before summarizing? So, in query 3 (the join results), the numbers are quite inflated. If you see the first line in the results of Query 2 for prod# 0082283, the Bound count is 4. In the results in Query 3, the corresponding numbers from Query 1 are increased by 4. For the second prod# from Query 2 0082406 with a bound count of 5, the results from query 1 are multiplied by 5, etc.
We can change the aggregate function to "none" and the original values will be displayed but this seems to mess up the "quoted over total" calculation.
Any thoughts on how to get it to join based on the final results of each query so the numbers don't inflate?
Thanks!
Just to confirm that it is indeed doing as I described, I turned off the auto summarization. In the attachment you can see that it is taking the summarized record from Query 1 and joining it to each individual record in query 2, rather than the summarized results of query 2. Thoughts in how to prevent this?
Try changing the measure data items in the 2 sub-queries...
From: direct pull from the model
To: total([measure] for report)
Make sure all the non measure data items in the sub-queries are not set to aggregate.
Also, turn on the auto-aggregate setting on the sub-queries.
Thanks! So far adding the totals hasn't worked but I'm going to keep messing around with it. The report was all set on the other suggestions.