Using Report Parameters in SQL Server Reporting Services

Data Body

We have defined and configured three parameters. Now we need to set a data set for main data body. I have defined dsCustomer data set with following T-SQL.

SELECT        i.CustomerID, c.FirstName, c.MiddleName, c.LastName, c.Phone, c.EmailAddress, a.AddressLine1, a.AddressLine2, a.City, a.PostalCode

FROM            Sales.Individual AS i INNER JOIN

                         Person.Contact AS c ON c.ContactID = i.ContactID INNER JOIN

                         Sales.CustomerAddress AS ca ON ca.CustomerID = i.CustomerID INNER JOIN

                         Person.Address AS a ON a.AddressID = ca.AddressID INNER JOIN

                         Person.AddressType AS at ON ca.AddressTypeID = at.AddressTypeID

WHERE        (a.City IN (@City))

Where the @City parameter contains multiple values of city, you need to use the IN function. Table control was used to present data.

Display Parameters

Normally you need to display the selected report parameters value in the report. For country and province it is straight forward; =Parameters!Country.Label and =Parameters!Province.Label  are values needed to display for the country and province. As city is a multi-value parameter, you cannot use =Parameters!city.Label. Instead, you need to use =Join(Parameters!City.Label,","). From the JOIN command, it will concatenate all the values in the City parameter. You can define the character you want to join the values from the JOIN command.

Passing Parameters

Most of the time, you may not stop after developing just one report. Instead, you will build series of reports. In that case, you may need to pass parameters from one report to another. There are two ways of calling one SSRS report from another. One is subreport and the other is the jump to report option. There is not much of a difference in passing parameters using either method.

For example, if you want to display customers in Berks and Berkshire cities in England, UK, you would need to drag & drop the text box and type in the necessary text value in the text box. Right click the text box and select properties. Select the Navigation tab, then select the Jump to Report option and finally select the report you want to navigate to. In this case, it will be the report named Report. Then select the parameters button to pass the correct parameters.

You should remember that you need to pass the parameter value, not the label. You can see that we have passed GB for country (not United Kingdom), and the same for Province. As city is multi-value parameter, you need to use the Split command with the string of cities.

In case you need to pass parameter values from a parameter, you need to specify the parameter as indicated in following image, which shows an example from the subreport. However, there is nothing different between this and the earlier method.

You can see that the user interface is also the same as the Navigation method. In addition, it doesn’t make a difference whether it is a multiple parameter or not.