Paginated Reports: Group within a Group

[AliPoTD]

Hello,

I am new to Paginated reports and would appreciate help with the below query. I thought it would be straightforward but I can't seem to get it working! Any help much appreciated.

I have a Power BI report from which I am pulling 2 tables into my Paginated report

Table A gets its data from a live system database

Date	Farm ID	Order Qty
02/09/2023	1	10
02/09/2023	1	20
02/09/2023	2	5
15/10/2023	1	5
15/10/2023	2	15
15/10/2023	2	30

Table B gets its data from an archived data set which has already been summed by month

MonthEnd	Farm ID	Order Qty
30/09/2023	1	30
30/09/2023	2	5
31/10/2023	1	5
31/10/2023	2	45

I have created a paginated report which shows all the information per month from Table A, looped so that the information is shown per farm on each page to create an invoice. I have set up Filters on Table A to show the information based on the Month parameter selected by the user running the report, so that it shows a monthly invoice per farm.

I now need to add an additional table, per customer (farm) invoice, to show the historical totals by month, per customer (farm).

I think i need to add an additional loop within the existing one, which ignores the Month parameter but is linked to the Farm number. 

I cannot figure out how to do to this, can anyone provide any help as to how I can achieve this?

The image below shows the output I'm trying to achieve, whereby the first table shows all information from the system db for the selected month (Table A) and the bottom table shows the static data from a seperate datasource (Table B) for historical data. This is duplicated per customer (Farm) and the link between the two tables is the Customer (Farm) ID.

The below image is my report, with Table A being Invoice and Table B being FarmMonthTotals.

Hopefully this makes sense. 

I'm sure there is a straightforward way of linking the two tables to display what I need but I can't figure it out. Many thanks in advance for any help received.

[Anonymous · Accepted solution]

Hi @AliPoTD 

You can follow these steps:

Add a New Dataset for Table B:
Create a new dataset in your paginated report that pulls data from Table B.

Create a Subreport:
Create a subreport that will display the historical totals by month for each farm. This subreport will use the dataset from Table B.

Design the subreport to display the data grouped by Farm ID and MonthEnd.

Add the Subreport to the Main Report:
In your main report, add the subreport to the section where you want to display the historical totals.

Pass the Farm ID from the main report to the subreport as a parameter. This way, the subreport will filter the data based on the Farm ID of the current farm being processed in the main report.

Ignore the Month Parameter in the Subreport:
Ensure that the subreport does not use the Month parameter from the main report. Instead, it should only filter based on the Farm ID.

Best Regards,

Jayleny

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

[AliPoTD]

Many thanks for this Jayleny.

I created a subreport as you suggested and it is now working. Thank you so much!

I have noticed that the report takes much longer to run, is this normal? It's merging the reports by 165 customers so not a huge number. Regardles,, we will continue as is as it's working!

Many thanks,

Alison