PBI Report Builder - Subreport with Parameters

[dkernen2]

Hi.  I need some help.  I have a report that has several row groups.  The detail rows have multiple rows, so, after many attempts at KeepTogether, it's become clear to me that, to keep the detail for one record on the same page, I need to use a subreport.  (I understand that KeepTogether only works for 2 rows, and my detail in this report is 8 rows.)

The only reason I have a subreport is because of the pagination.  The report and sub-report come from the same table with this DAX query:

DEFINE
VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'Grant Applications'[Organization Name],
			'Grant Applications'[Amount Recommended],
			'Grant Applications'[Request State Description],
			'Grant Awards'[Awards],
			'Grant Awards'[Awarded],
			'Grant Applications'[Request Name],
			'Grant Applications'[Amount Requested],
			'Grant Applications'[Organization Budget],
			'Grant Applications'[Duration In Months],
			'Grant Applications'[Grant Cycle],
			'Grant Applications'[Workflow Class],
			'Grant Applications'[City],
			'Grant Applications'[State Code],
			'Grant Applications'[Internal Project Summary],
			'Grant Applications'[Rubric Rationale Potential Impact],
			'Grant Applications'[Overall Project Amount],
			'Grant Applications'[Strategy],
			'Grant Applications'[Cap Bldg Type],
			'Grant Applications'[Grant Start Date],
			'Grant Applications'[Grant End Date],
			"R1_R2_Score__Either_IO_", 'Measures Table'[R1+R2 Score (Either IO)],
                        "Grant_Applications", 'Measures Table'[Grant Applications]
		)

EVALUATE
	__DS0Core

When I run the report with the subreport the way I have it currently set up, it runs incredibly slowly, so I certainly do not have it set up correctly.  I wonder if my query should be different in the subreport to utilize the parameter passed from the report.  I do not recall how to pass the parameter to the subreport and have it process quickly.  I've done this successfully in SQL in the WHERE clause, but I am uncertain how to do this using DAX.

Thank you!
The RDL files are here: SUB: https://kauffman.box.com/s/41lz8o7z23txp3wnge13wx9m8azv5y0a
MAIN: https://kauffman.box.com/s/armbiu1r7iettb2ge3vchqt1p77kqrvg
@reportbuilder @subreport @parameters

[Poojara_D12 · Accepted solution]

Hi @dkernen2 

You’re on the right track by using subreports for pagination, but performance suffers unless the subreport DAX query is filtered by a parameter. By adding a FILTER() clause using @RequestName in the subreport’s DAX query, you ensure only the required data is retrieved. This mimics a WHERE clause and drastically improves performance — especially if the main dataset is large.

Let me know if you want help constructing a specific version of your DAX query or want to explore alternatives to subreports.

DEFINE
    VAR __RequestNameParam = @RequestName -- This will be injected by SSRS
    VAR __FilteredTable = 
        FILTER(
            'Grant Applications',
            'Grant Applications'[Request Name] = __RequestNameParam
        )

    VAR __DS0Core =
        SUMMARIZECOLUMNS(
            'Grant Applications'[Organization Name],
            'Grant Applications'[Amount Recommended],
            'Grant Applications'[Request State Description],
            'Grant Awards'[Awards],
            'Grant Awards'[Awarded],
            'Grant Applications'[Request Name],
            'Grant Applications'[Amount Requested],
            'Grant Applications'[Organization Budget],
            'Grant Applications'[Duration In Months],
            'Grant Applications'[Grant Cycle],
            'Grant Applications'[Workflow Class],
            'Grant Applications'[City],
            'Grant Applications'[State Code],
            'Grant Applications'[Internal Project Summary],
            'Grant Applications'[Rubric Rationale Potential Impact],
            'Grant Applications'[Overall Project Amount],
            'Grant Applications'[Strategy],
            'Grant Applications'[Cap Bldg Type],
            'Grant Applications'[Grant Start Date],
            'Grant Applications'[Grant End Date],
            "R1_R2_Score__Either_IO_", 'Measures Table'[R1+R2 Score (Either IO)],
            "Grant_Applications", 'Measures Table'[Grant Applications]
        ,
            __FilteredTable
        )
EVALUATE
    __DS0Core

@RequestName is a report parameter, injected dynamically.

__FilteredTable filters 'Grant Applications' to only the relevant row(s).

SUMMARIZECOLUMNS is scoped to this filtered table, so the subreport processes only what it needs, significantly improving performance.

If Using More Than One Filter:

VAR __FilteredTable = 
    FILTER(
        'Grant Applications',
        'Grant Applications'[Request Name] = @RequestName &&
        'Grant Applications'[Organization Name] = @OrgName
    )

Make sure the parameter @RequestName is exactly the same type as the field (Request Name). Sometimes SSRS sends it as text while the field may be nvarchar with trailing spaces — use TRIM() if needed.

Always test the DAX query in DAX Studio or SSMS connected to the semantic model to make sure it's returning results fast with the parameter.

[dkernen2]

This is incredible.  I think I got it.  I did need to wrap a CALCULATETABLE around the SUMMARIZECOLUMNS to accept the filtered table.  Does that seem right?

It takes a good 20 seconds to load, but this is a paginated report and it shouldn't be run that frequently anyway.  A single record runs very quickly in DAX Studio, but when I am running 200+ records through it and 200 subreports, it takes while.  Does that seem reasonable to take that long to load?

VAR __DS0Core = 
CALCULATETABLE(SUMMARIZECOLUMNS(
	'Grant Applications'[Request ID],
	'Grant Applications'[Request Name],
	'Grant Applications'[Grant Cycle],
	'Grant Applications'[Workflow Class],
	'Grant Applications'[Organization Name],
	'Grant Applications'[City],
	'Grant Applications'[State Code],
	'Grant Applications'[Organization Budget],
	'Grant Applications'[Amount Requested],
	'Grant Applications'[Amount Recommended],
	'Grant Applications'[Request State Description],
	'Grant Applications'[Duration In Months],
	'Grant Applications'[Internal Project Summary],
	'Grant Applications'[Rubric Rationale Potential Impact],
	'Grant Applications'[Overall Project Amount],
	'Grant Applications'[Strategy],
	'Grant Applications'[Cap Bldg Type],
	'Grant Awards'[Awards],
	'Grant Awards'[Awarded],
	"R1_R2_Score__Either_IO_", 'Measures Table'[R1+R2 Score (Either IO)],
	"Grant_Applications", 'Measures Table'[Grant Applications])
,
__FilteredTable
)