SSRS Consultant Tip: Defaulting Your Parameters

I’ve been doing various forms of systems consulting since the mid-90s. From my very first job, it was clear that the amount of credit you receive is rarely related to the amount of work involved. So, in that spirit, I’m going to help you become a reporting hero by showing you how to set default values in your SSRS reports.

As an SSRS consultant, I do the majority of my work with finance and accounting data. That means my reports generally run by fiscal year and period. Therefore, in this example, I’ll show you how to have fiscal year and period default on your report parameters. So, even if you have, say, 15 possible fiscal years, the current year comes up.

If you want to follow along at home, I’m using the AdventureWorks2012 database with the addition of three custom tables: FiscalYears, FiscalPeriods and DateToFiscalYearPeriod. (When conducting actual client work, we have more complex date tables—but that’s a discussion for another time.) I’ll include all the code at the end of this post for your use.

In order to select a parameter, we need to have a set of available values to work with.

So, I’ve created two simple datasets, one for FiscalYears:

And one for FiscalPeriods:

Now that I have my datasets ready to go, I can use them with my parameters.

I’ve create a very simple report that allows users to show order for a given year and period:

If we look at parameter properties, we can see how we’ve set the available values to use our FiscalYear and FiscalPeriod datasets.

For example, here’s my FiscalYear parameter:

And here are the available values which are set to come from the FiscalYears dataset:

(The same setup also applies to FiscalPeriod.)

When run, the report looks like this:

Now, if we use the dropdown box for either Fiscal Year or Fiscal Period, we see all possible values for our dataset:

Now, it’s NOT a big deal for the user to select from a list. But again, the idea is to make things look good. And if you have lots of fiscal years, it’s a little annoying to have to scroll down to choose the current year 99 percent of the time.

Now that we have our report, let’s see how we can set defaults.

Let’s start by setting the default value in the report itself.

In general, I don’t like to set hardcoded values in the report itself. I don’t want to have to go to the report to change the value every time I run it. Still, if users generally run a report one way rather than another, it can be useful.

To set the default value, we go back to our parameter, in this case FiscalYear. We choose properties, and then choose default values.

In this case I’ve chosen to specify a value:

You’ll see that the default value is set to “Expr.” If we look at the expression, we see it’s set to 2007:

And, as soon as I open the report, I see 2007:

Of course, you can use a fancier formula. For example, it might be sufficient to default to years based on the current date of the system. The formula would look like this:

And when you opened the report, you’d see the 2017 default (which, I acknowledge, won’t return any data from AdventureWorks).

But with many reports deriving the default from current data, this might not be the best thing. We generally don’t issue financial or other statements until the time period has passed. So we need more control.

We can’t use generic logic to know that folks have moved from period 1 to period 2. (Indeed, sometimes I even create applications that set defaults by user name to get even more specific, but that’s beyond the scope of this post.) To do this, our basic FiscalYears Table not only has a column for FiscalYear, but also has a column for CurrentYear.

So, we can build another dataset called “FiscalYearDefault.” Through other means, we make sure we have only one year marked as current. Then we build a simple query to bring back that one value:

If we go Query Designer, we see that this is indeed the case. The one value of 2008 is returned:

Now, we go back to Parameter Properties and use this dataset as our source for the default value:

When we run the report, we see that 2008 defaults into the report:

There is one more way to set a default: in Report Manager. Generally, I only use this method when I’m deploying the same report to different report folders, and I want to specify certain parameters based on the users who work with the folder.

We find the report in Report Manager:

We right click to the right of the report and select Manage.

For my sample report, I created a default by FiscalYear, which SSRS shows me:

We haven’t set a default for fiscal period. But if we wanted to, we would check the default box and enter a value:

Now when we run the report, we see that Fiscal Period defaults to “1”:

One thing you should also note: If you provide defaults for all your parameters, the report will run as soon as it opens—which may or may not be what you want.

(Usually, I have at least one other parameter beyond my defaults that users must choose, but it’s something to consider.)

The code to set up the tables can be found below. If you’d like the RDL and RDS files, please let me know.