Let’s Parameterize our SSRS RDL Reports!

Let me show you, how to add parameters to SSRS RDL Reports, while populating the reporting data based on those parameters dynamically!

You can add any number and any type of parameters into your Reports, so that during the execution users can easy load different sets of data into the Reports for analysis.

Backstory…

Since my last few posts I’ve been revealing to you guys that I’ve been working with Microsoft Dynamics 365, where I built those Reports using SQL Server Reporting Services RDL Reports, where those RDL files are uploaded to the Dynamics 365, allowing use to execute them directly from there.

In those Reports, I’ve added a bunch of Parameters where they can load different sets of data into the Reports through those reports for different analytics of the data. Yeah sounds pretty cool eh! 😉 So I thought of sharing this experience with you all, how I added the feature of passing Parameters into my RDL Reports for Microsoft Dynamics 365!

the Set up!

So my set up was on Visual Studio 2015 with SSRS in order to build Reports using fetchXML based data sources that pulls data from Microsoft Dynamics 365 cloud, which was a helpless requirement specifically with the Report Authoring Extension. Let’s set up Microsoft Dynamics 365 Fetch Extension in VS2015!

Although I believe the process of adding parameters any SSRS RDL Report is the same, so you should be able to follow along despite your targeted deployment source. 🙂

Parameters for Reports!

So you need to add Parameters for your RDL Report eh, which means you’ll be passing a bunch of data values into the filtering pipeline of the Report and repopulate its results based on it.

So how do we add those Parameters? We need to build each one of those Parameters in our SSRS project. This is where you configure what is the type of the parameter, whether its Text, Date, Boolean, and so on, you can even define your own set of data values available for this parameter, which the user will choose from during execution. It also facilitates you to define the data source in which those values will be loaded from.

Then you add those Parameters into the Report, by adding a dependency between them. Once they are set you need to determine the refresh cycle for re-loading the data based on the new parameters given. 😉

Now as you can see there’s all kinds of cool stuff you can do with it, even though the set up seem a bit complicated, its actually quite straight forward! 😀

We need to get started by defining the Parameters in our Report! So go ahead and open up your RDL Report with Design View…

Let’s get Started!

In order to add any Parameter, you need to first define it in your project, so first make sure you have the “Report Data” View is opened up.

To open up the Report Data View if not visible on your Visual Studio 2015 solution, go to View -> Report Data or hit the short cut keys, Ctrl + Alt + D on your keyboard! 😉

Let’s dive right into it by, Right click on the Parameters -> Add Parameters

This will open up the dialog to create a new Parameter to be added to your Report…

Now this let’s you define your Parameter with the properties you want to set for, starting from the values available in it, to the refresh action up on the user’s selection. As you can see I have here “ReportParameter1” which is labeled as “Report Parameter 1 :” for the display fields in the Report. You can choose the type of data this parameter represents and the type of inputs it accepts from the user.

So let’s go over each section you need to consider when you’re creating a Parameter.

Parameter: Available Values!

This allows you to determine the set of values available for your Parameter, that is presented to the user during execution. You can also leave this in the “None” option which will let the user type in the value they prefer.

If you want to load a specific list of values in to this parameter, then you can select “Specify values”, which will allow you to build the list of data values with their representing label value as shown before. This will provide the user with a drop down list of data using this source.

If you’d like to populate the data for this Parameter using a data query source that you can build, then feel free to select the “Get values from a query” option, in which case the list of data will be loaded from this dataset source based on the query defined in it, and be presented to the user accordingly with the Labeled values.

I will get in to this query based data loading into the Parameter, later in this article 😉

Then the next section you need to consider is the Default Values…

Parameter: Default Values!

This is where you define the default value that’s loaded into your Parameter during the execution, in which case you could also leave it with the “No default value” option selection if you don’t want to have any pre-selected values for loading of the data.

If you select “Specify values” option here, it will allow you to choose the default value to be loaded from the list of data that you defined in the “Available Values” section as I’ve shown before.

Now the same way you could populate your default values based on Expressions as well, in a situation like setting the default value for a Date type parameter can easily be done as below…

But if you’ve defined a data source based on a query, then you can select the data from that source to be loaded here as well from the option “Get values from a query“. You need to make sure to select the same dataset that you selected in “Available Values” section here as well.

Next the final section you need to consider when building your Parameter, “Advanced” section!

Parameter: Advanced!

This is where you define the refresh action based on the selection of a new value for the Parameter from the user during execution. Simply put let report refresh its data every time a user selects a new parameter value, or let the user refresh manually to see the results once change the selection.

Now that’s quite straight forward eh! So those are key sections you need to focus on when building any Parameter for your RDL Report.

Next let’s deep a little more into loading values from query into your Parameter… 😀

Let’s get Values from a Query!

As I’ve shown earlier you can load data into your Parameter from a dataset source based on a query. Let me show you how easy it is to set this up! 😉

Let’s get started by creating the dataset that will handle loading the data from the data source that we target. Right click on the “Datasets” section -> Add Dataset… option.

Create a new dataset using an existing shared dataset, or using an existing data source by accessing the data through the preferred query type.

As you can see below here in my example, I’m using a fetchXML query to be execute through my TestDataSource which is a MS Dynamics 365 data source. So you need to build your own query based on your data source here.

Once the dataset is defined, you need to make sure the Fields are properly loaded as expected. You can check this in the dataset property window itself and even after click “OK” and save it under the dataset you just created as shown below.

Now that we have set up our Dataset Source, then we can map this into our Parameter to load these data during the execution.

First thing, you need to make sure to set up the type of the Parameter, accordingly to the type of values that you’re loading from the dataset. In this example, “Text” would preferred.

So now, you can set up the Available Values with the “Get values from a query” option. You just have to select the dataset that you created as shown below.

Here you got Value field, which refers to the actual value that’ll be passed into the Report up on selection from the User. And then the Label field, that represents the display value of your dataset items, as you may have noticed in the previous steps.

Finally you set up the Default Values with the same set up, which will load the first item provided by the dataset query during execution into the Parameter, which will then trigger the loading of the Report data based on that.

Here make sure to set the same Value field selection as you set up on Available Value sections. 😉

Once the set up is done, you may see your Parameters now visible in the Design View of your RDL Report as shown below… 😀

You still need to set up the those Parameters as dependencies for your Report, which we will discuss next…

Let’s tie up Parameters with Report!

Here’s the final step fellas! 😀 Let’s open up the properties dialog of the dataset that you’re using to populate your Report,

Go to the Parameters section, and add the Parameters you just created in the previous step!

Yep it’s that simple! 😀 as you can see I have added the ReportParameter1 that I have created here, where as Parameter Name is set up as it is, and then the Parameter Value field is set up as [@ReportParameter1], which is how you address the Parameters you have defined in the Report. Subsequently you can add any number of Parameters following the same.

Notice how each Parameter set up has the Expression button next to it? that is to allow you to modify the value coming in from the Parameter using Expressions, if you wish to! 😉

Next you have to set up the Filter based on the Parameters that you just registered, so go to the Filters section, and add the filtering logic you require based on those parameter value for the repopulating of the Report data.

Right here you need to pay extra attention to details, where you need to consider which field in your data set you want to set up the parameters against for filtering, and what type of operator you need for this logic. You need to make sure both values you’re comparing are of the same type, and also you can modify their values based on Expressions before they’re applied for the filter.

As you can see above I’m using the DataField1 text field’s value to check whether its equal to the value given in the ReportParameter1 for each data row.

If I’m to show another example, take a look at below, I have added a second parameter which is type of Date/Time, which then compares itself with the value of DataField2 date/time field in the report dataset.

Now both those filters will act as an AND junction and filter out the data based on the combined logic. Save it up, you’re all set! 😀

Well… that’s how you tie up your Parameters with the Report’s dataset! 😉

You can now run this Report and see the beauty in action! 😉

Conclusion

Microsoft SQL Server Reporting Services – RDL Reports allows you to add Parameters to them, so that you could allow Users to filter the data according to different business logic for ease of analysis. You can add many different types of Parameters which could be used to filter the report data in kinds of different ways, using Expressions.

• Define your Parameters in the RDL Report
• Set up the available values for your Parameters
  • load data into Parameters based on specific list of values, or
  • load data into Parameters from a query based data set
• Set up the Refresh action event
• Add them as dependencies for your Report
• Set up Report data filtering based on Parameters

This will allowing the Report data to be filtered dynamically based on the Parameter values that are passed in by the selections from the User. Well it is quite straight forward as you can see… 😉

Hope this little step by step guide helped someone, somewhere in the world… happy building my fellow devs! 😀

Share the love! 😀 Cheers!