SSRS Parameters

Most reports benefit from being parameterised to allow the user to decide what filtering to apply to the data, or which fields they’d like to see, or some other behaviour or visualisation option. I’m going to show you a really simple example of grabbing some parameter options from the database, presenting them to the user, and sending the selection to the stored procedure to run the report data. I’m going to set up a Store parameter to select either a single store to filter the data down to, or all stores. First, you need a new stored procedure to select the parameter options from the database:

CREATE PROCEDURE GetParameterItems_Store

AS

SELECT StoreName  AS 'Value'
       ,StoreName AS 'Label'
       ,StoreName AS 'SortOrder'
FROM Dim_Store
WHERE ValidTo IS NULL

UNION ALL

SELECT '-20'
       ,'All'
       ,'---'
ORDER BY SortOrder

I need a label to show the user (Store name), a value which I will use to do the actual filtering (I’ve used store name again, but it could be an ID or code instead), and something to order by. The SortOrder field won’t be picked up by the report, it’s just to get the ordering done in the database. I’ve included an ‘All’ option, with a dummy value of -20 to represent all stores.

Next I need to set up the report parameter in my master RDL. I add a dataset named ‘ParameterItems_Store’ which specifies my new proc, no parameters needed, and picks up the Value and Label fields. I then add a new parameter, calling it StoreName. ‘Available Values’ specifies my new dataset. For ‘Default Values’ I specify the -20 code so that it initially runs with all stores. I’m creating a single select parameter here, but you can allow multiple values – you just need to handle passing a list of parameters rather than a single value.

MasterRDLDesign
Master RDL design

Once a user has made the selection, we need to feed it through to the report stored procedure. First it goes to the subreport (assuming we’ve already set up a Store parameter in the subreport – no available or default values needed). This is done by right-clicking the grey subreport item, Parameters section, Name is the subreport parameter name, Value is the master report parameter name. Next we send it to the stored procedure. In the main dataset on the sub, add a parameter mapping to the stored procedure parameter name.

SubRDLDesign
Subreport RDL design

Finally, the report stored procedure should obviously be set up to do the filtering:

CREATE PROCEDURE GetSalesData
@storeName VARCHAR(8000) = '-20'

AS

SELECT Store.StoreName       AS 'StoreName'
       ,Prod.ProductName     AS 'ProductName'
       ,YEAR(Dat.[Date])     AS 'SaleYear'
       ,MONTH(Dat.[Date])    AS 'SaleMonth'
       ,SUM(TotalSaleAmount) AS 'TotalSaleAmount'
FROM       Fact_Sales  Sale
INNER JOIN Dim_Store   Store ON Sale.StoreSK = Store.StoreSK
INNER JOIN Dim_Product Prod  ON Sale.ProductSK = Prod.ProductSK
INNER JOIN Dim_Date    Dat   ON Sale.SaleDateSK = Dat.DateSK
WHERE IsCorrect = 1
  AND Sale.ValidTo IS NULL
  AND (
          Store.StoreName = @storeName
       OR @storeName = '-20'
      )
GROUP BY Store.StoreName
         ,Prod.ProductName
         ,YEAR(Dat.[Date])
         ,MONTH(Dat.[Date])
HAVING SUM(TotalSaleAmount) >= 100
ORDER BY TotalSaleAmount DESC

This is a simple example, but hopefully demonstrates the principles at work. To further develop, you can:

  • make the parameter multi-select
  • make the default item(s) dynamic by selecting from the database
  • add extra parameters
  • make the parameter items dependent on previous parameters – cascading parameters
  • create a ‘parameter set’, consisting of a set of defaults. This enables you to set up a pre-defined report selection, where you make one ‘parameter set’ selection and it fills the other parameters with default values.
RunningReport
Running the report
Advertisements

One thought on “SSRS Parameters”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s