I have two Tablix/tables in Report Builder, one is a Profit & Loss (P&L) and the other is Key Ratios. Both the P&L and ratios are separate datasets in Report Builder with their values being defined by DAX queries that reference Calculation Groups in my Model. The issue I have is that I want the P&L and Key Ratios grouped together by Area Manager (the report parameter) e.g.
If Area Manager 1 has 3 stores, Store A, B and C they would receive a report like this: - Store A P & L Store A Key ratios
Store B P & L Store B Key ratios
Store C P &L Store C key ratios
At the moment, the report reads like this: -
Store A P & L
Store B P & L
Store C P & L
Store A Key ratios
Store B Key ratios
Store C Key ratios
I’ve tried all sorts of ways to get round this: - I’ve created a DAX query that unions two queries to have the two datasets consolidated into one and thus just one table/tablix. Line breaks are added between the two queries in DAX and then conditionally format the different data in Report Builder to give the appearance of two separate reports grouped by store. However, this is not ideal, and it seems to be interfering with the calculations being performed on the key metrics.
The other method I have tried is to use sub reports. I’ve tried a number of variations but the closest I can get to is a situation where I get the first P&L, followed by all key ratios, followed by the second P&L and all key metrics, etc: -
Store A P & L Store A Key ratios Store B Key ratios Store C Key ratios
Store B P & L Store A Key ratios Store B Key ratios Store C Key ratios
The problem here is that the first report receives the Area Manager Parameter displaying the first store, but the key ratios report receives the parameter and lists every single store key ratios underneath the first P&L. I somehow need to group by Area Manager (the parameter in both reports), but then loop through each individual store for that Area Manager separately to pass to the second report (the sub-report) that relates to the first store in the first report, then the second store in the first report, etc. Is that possible in SSRS/Paginated Reports? If it is, how would I go about creating this? Many thanks for your help.
