Showing Different Sub-Reports Depending on a User Driven Parameter

Question

I want to create an SSRS Report that has two Sub reports links in it.

Report A has Group A content.

Report B has Group B content.

Then I have a Main Report with links to Report A and Report B.

I'm trying to get the main report to have a parameter to allow the user to select which Report they want to look at either Report A or Report B.

This is where I am having trouble with. Any guidance will be appreciated.

Answer 1

There are a few things to consider.

1. Hide the subreport based on the parameter selected. For example the Hidden property for ReportA might use the expression =Parameters!SelectedReport.Value <> "A".
2. You need to consider the final output (screen/pdf/excel etc). Depending on this, overlapping the subreports might not work. It will work on screen though. So once the main report is complete, the final thing to do is set the location of both subreports to be the same.
3. Prevent the subreport query from running if not required. To do this you can pass the SelectReport parameter to your subreport. Then the subreport's dataset query might look something like this..

(Bear in mind that the dataset query must always return the same columns with the same datatypes not matter if it's empty of not)

CREATE TABLE #t (ColA int, ColB varchar(10), ColC decimal(10,2)) -- or whatever your real data looks like

IF @SelectReport = 'A'
    BEGIN 
        INSERT INTO #t 
          SELECT ColA, ColB, ColC FROM myRealTable
    END 

SELECT * FROM #t

In the above example, when SubreportA runs, the dataset query will only do any real processing is the parameter passed in is "A", otherwise it will return an empty dataset.