Trying to group two tables using subreports in Report Builder

[Anonymous]

Hello all,

I've tried my best to describe this situation, apologies if this is somewhat convoluted: -

I have two Tablix/tables in Report Builder, one is a Profit & Loss (P&L) and the other is Key Ratios derived fro the P&L. Both the P&L and ratios are separate datasets in Report Builder with their values being defined inside Calculation Groups in my Model. The issue I have is that I want the P&L and Key Ratios grouped together by Area Manager 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 the conditionally formatting 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 two 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

To achieve this, I created two reports, one which has the P&L and the other with the Key Ratios subreport. The Key Ratios sub-report which is published to the Power BI Service. In the main report, I have created a separate table with two rows that contain the separate reports: -

The grouping is by store and the table is bound to the P&L dataset (this is the first report in the first row)

The two reports that are within this table/tablix (the P&L and Key ratios) are both grouped by store and details: -

The parameter for all the reports is the Area Manager. 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’m sure there must be a way to achieve the grouping by store, but I’ve reached the limits of my paginated report skills and really need some help. Thank you in advance.

[v-zhangti]

Hi, @Anonymous 

Please refer to the following links in the hopes of helping you.
Lesson 6: Adding Grouping and Totals (Reporting Services) - SQL Server Reporting Services (SSRS) | Microsoft Learn

Solved: How to get sum of measure group by dimension in po... - Microsoft Power BI Community

Grouping multiple tables (tablix) with different datasets - SSRS 2008 R2 (microsoft.com)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-zhangti,

Thanks for your response. I’ve read the articles that you have provided links for, but I still can’t achieve what I was hoping for.

I’m looking to group by Area Manager (AOM) for the stores that he manages then to display the two separate tables (the Profit and Loss report and the Key Ratios report) to be grouped together by store.

I somehow need to group by AOM (the parameter in both reports), but then loop through each individual store for that AOM 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.

Currently, for AOM 1 with three stores, A, B and C I end up with the following, using sub-reports: -

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

I’d like to achieve: -

Store A P&L

Store A Key Ratios

Store B P&L

Store B Key Ratios

Store C P&L

Store C Key Ratios

Ultimately, I’d like to create a series of sub-reports that work for the hierarchy of the business, Director, Regional Manager, Area Manager and store.