Adding Parameters in Reporting Services

Adding parameters into a report or dashboard you’re publishing with Reporting Services can add that extra shine. It allows the end users to tailor their experience and can allow for the same report to meet different needs. This may require a little extra effort by the developer if you’re looking to go and retrospectively update reports to add parameters, however typically you can tease these out of the requirements early on and build them into the design at the earliest opportunity.

This isn’t necessarily aimed at folks who are well versed with report creation however I’m looking to set a baseline and follow this up with another post diving deeper into multi-value parameters which can be a little trickier to implement effectively. For reference this is being done via the Report Builder UI which is very similar to what you’ll find if you develop via the extension in Visual Studio too.

The report

To start the report I’m using a copy of the AdventureWorks2019 database where I’ve pushed the dates forward so we’ve got relevant data for 2022. In addition to this I’ve created a report procedure to get some sales for a specified month. This takes the first day of the month as a parameter which we’d like to specify in the report:

CREATE PROCEDURE GetSalesForMonth(@MonthStartDate DATE)
AS

SELECT
	p.Name,
	p.ProductNumber,
	SUM(d.LineTotal) LineTotal
FROM
	Sales.SalesOrderHeader h
	INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
	INNER JOIN Production.Product p ON d.ProductID = p.ProductID
WHERE
	h.OrderDate >= @MonthStartDate
	AND h.OrderDate < DATEADD(MONTH, 1, @MonthStartDate)
GROUP BY
	p.Name,
	p.ProductNumber

Adding a parameter

As we’ve set up our report to run via a stored procedure, adding the parameter to the report will be done automatically. When setting up the dataset in the report we simply direct it to the stored procedure as below and it’ll take the procedure parameter and create a report parameter for it automatically:

When this is done you’ll see a ‘+’ symbol appear next to the Parameters node within the Report Data pane. If we expand that then we’ll see a parameter with the same name that is used in the procedure.

If we select that parameter and look at it’s properties we’ll spot a couple of useful features for us:

• There will be a Name which is defaulted to match our parameter (this isn’t seen to the end users)
• The Prompt (what users see) will default to the same as the name but insert spaces to make it more user friendly
• The data type can be defaulted to better align with the one we’ve used in the procedure

I’m leaving everything as it is for now and just going to run the report. When it comes up we’ve got a parameter on the report which is empty – but does have a calendar picker as it knows its a date. If I set this to 1st of the month and then run it I get some results back. Just by using a parameter for the procedure the report has done all of the plumbing for us.

Limiting the parameters

Our initial parameter looks good but since we only want to run the report monthly the date picker isn’t the best option for us. What I’d like is for user to have the option of the last 12 months to run the report for. To drive this I’m going to add a new procedure in the database:

CREATE PROCEDURE GetMonthParameters AS

SELECT
	TOP 12
	DATEADD(DAY, -(DATEPART(DAY, h.OrderDate)-1), h.OrderDate) [MonthDate],
	FORMAT(OrderDate, 'MMMM yyyy') [MonthName]
FROM
	Sales.SalesOrderHeader h
WHERE
	h.OrderDate < GETDATE()
GROUP BY
	DATEADD(DAY, -(DATEPART(DAY, h.OrderDate)-1), h.OrderDate),
	FORMAT(OrderDate, 'MMMM yyyy')
ORDER BY
	MonthDate DESC

You’ll see we’re filtering by GETDATE() so this will automatically change over time and we won’t need to maintain the list. Also we’re returning two columns in the data – we’ve got the MonthDate field which will return the date to be used for the parameter, and then we have the MonthName field which is the user friendly version such as ‘March 2022’. Finally note that we’re sorting the data inside this procedure as the order of records will determine how they’re displayed in the parameter list. With that in place we’ll add a new dataset into the report for this procedure.

Going back into our parameter where we were looking previously we now want to jump into the ‘Available Values’ tab which is where we’ll be able to limit the options for the user. Under here you’ll see 3 different options:

• None – this will provide a field for the user to change the parameter as they like (or in the case of a date they’ll get the picker)
• Specify values – this allows you to manually specify the values the operator can choose – only use this if you have a small list of options and you won’t need to update them in the future. This isn’t good for our use case
• Get values from a query – this is what will allow us to specify the range of values to be used from a dataset (exactly what we’re doing!)

So we’ll select the ‘Get values from a query’ option and we’ll then be prompted to choose the dataset we’ve just added. After this we can choose the Value and Label fields. The Value field is the column which contains the value which we’ll pass to the report, and the Label is the column which will determine what text is presented to the user. In our case we’ll set it as follows:

Once we’ve got this in place if we go to run our report the parameter will look a little different and we’ll now be presented with a list from our procedure, much better:

Defaulting the value

The final part of our parameterisation is to add a default value. This isn’t always needed or wanted as defaulting the value will cause the report to run instantly when opened and there may be times when this isn’t required – for example if you expect the report to run for a couple of minutes you might want to ensure the user selects the parameters they need.

This is going to take a similar approach as we’ve done with the other data sets so we’ll create a procedure to provide us the default value as follows:

CREATE PROCEDURE GetDefaultMonthParameter AS

SELECT
	CAST(DATEADD(MONTH, -1,
		DATEADD(DAY, -(DATEPART(DAY, GETDATE())-1),
		GETDATE())) AS DATE) [DefaultDate]

You’ll see this procedure is much simpler, we only need a single record and field to be returned. With that we’ll create a new dataset for this in our report and go back into the Parameter properties. This time under the properties we’ll go into the Default Values tab and we’ll see the same options like with the available values – No default, Specify values (manually), and Get values from a query.

We’ll set this to get the values from a query like we did last time but now we’re only asked for a dataset and the Value field. When setting defaults we need to pass in a default Value which aligns to the Value field being used in the Available Values section. Critically this means two things:

• The data type should be aligned between the available options and the default option
• The default option must exist in the available options if they are specified

Once that’s configured your dataset and the default value should look something like this:

With that in place we can go back to our report for one final execution and here we go, no need to select a parameter and it just runs for the previous month (February in our case):

It’s worth noting here that you don’t need to use the ‘Available Values’ section to use the ‘Default Values’ – we could have left the parameter as a date picker and still provided the date for the 1st of the month. You might want to use this approach when you have a report with a start and end date and you only want to provide default values for an initial execution and then the user can fine tune them when needed.

Wrap up

In this post we’ve covered adding parameters into a Reporting Services report, limiting the values available to the end user and how to default them. This isn’t necessarily quick or trivial to plumb these bits in but the end result is a much slicker experience for the users.

The strength in the way that we’ve achieved this is that we’re using GETDATE() in our queries to dynamically adjust the results being returned. This will mean that if we come back to this report in a few days then the available options will shift by a month and the default will move from February to March automatically.

Automating the time periods tends to be a really good use case for reporting since its a very common parameter. This could be extended so you could look at who is running the report to default to a certain sales region, or you could change to a certain category of products based on seasonality too.