SSRS Open sub report with provided parameters

Question

I have a report which has 5 parameters and i want to open another report from this report which has 13 paramaters. When i try to open this report it says some parameters are missing. I want to know if it is possible like, if i do not pass parameters then it should ignore these parameters in repport and also in SQL query (all these are multivalue parameter). I cannot use "allow null values" as these are multivalue parameter.

This is main report

These are parameters in sub report which i want to call

Can someone help me please? or i need to create seperate subreport with exact parameters everytime

Alan Schofield Alan Schofield · Accepted Answer · 2020-12-10T14:43:16

There are two common ways of doing this.

Populate parameters with a list default values In your subreport, taking @service as an example. If you can get a list of services from your database then create a dataset tat gives a list of distinct services, use this as the default value for your parameter. Then when you do not pass anything from the main report, the subreport will use all services.

Set the default parameter value to a fixed constant The other option is to set the subreport parameters to a default value such as *all and then handle this when it is passed to your subreport's dataset query.

So the dataset query might be something like

SELECT *
  FROM myTable
  WHERE location in (@location)
    AND (service = '*All' OR service IN(@Service)

SSRS Open sub report with provided parameters

1 Answers