SSRS — Part 2

In the last article we created simple SSRS Report. Today we will dive deeper into making our reports more interesting. We will see how parameters play an important role in generating reports for the organizations.

What are parameters?

Think of parameters as the customizable filters that allow you to tweak your reports in SSRS. They’re like the magic wand that lets you tailor your data views, play around with different options, and create reports that are both dynamic and easy to use. In this article, we’re going to take a closer look at how you can use parameters to create reports that speak directly to your specific business needs.

Let’s play with our Reports

This was our last simple report generated yesterday.

The above report looks boring, isn’t it? Let’s play with the formatting of our report.

Click on Home Tab. Choose whatever color you like to fill in our headers. You can Bold the header. Add borders and much more.

Click on “Run”

Now the report looks more amazing!

Adding Page Number

Let’s imagine you have to work on heavy reports which contains huge data breakdown into multiple pages. So, Let’s create a page number for our reports using Expressions.

Open our Dashboard -> Click on Expressions

In the text window write expression as below

=Globals!ExecutionTime & “- Page “ & Globals!PageNumber

Click on “Ok”

Now “Run” the report to see the output

You can see the Page number output below in the report.

Use parameters in your reports

Step 1) Create a SP “dbo.GetPlayers” in your database as below

CREATE PROCEDURE dbo.GetPlayers @City varchar(50)
AS
SELECT * 
FROM Players
WHERE City = @City
GO

Step 2) Set “Dataset” in Report Builder.

Right Click on DataSet1 -> Dataset Properties.

Now, Tick Stored Procedure in Query Type and Select the SP Name from our dropdown i.e “GetPlayers”.

Click on “OK” and “Run” the report.

You will see that a new “City” Selector has been appeared in the report at the top.

Write any City name and click on view report. We will fetch players from “Delhi”

All the players from Delhi are been displayed in the report.

We can add multiple parameters too in the report.

Let’s fetch the records with City and Skills. Example fetch the reports from Delhi who has Batting skills.

ALTER our SP as below:

CREATE OR ALTER PROCEDURE dbo.GetPlayers @City varchar(50), @Skills varchar(50)
AS
SELECT * 
FROM Players
WHERE City = @City AND Skills = @Skills
GO

Now, Go to report builder and refresh the Dataset. Click on “Run”

You can see Now two text boxes are added “City” and “Skills”. Let’s fetch the reports from Delhi who has Batting skills.

Our report has been generated.

Add DropDown Selector In Reports

Entering the data manually is a hectic process. What if we are able to select values directly from the dropdown box. It is possible in SSRS.

Step 1) Create a new DataSet in Report Builder

Write simple query as below

SELECT * FROM Players

Click on “Ok”

Step 2) Click on Parameters -> Add Parameters

Go to Available Values Section.

Select “Get values from a query” and select the value and label field.

Click on “Ok” and “Run” the reports

You can see a new Dropdown has been appeared in the report. Select the City and Your report will be generated.

Today we saw how to use Parameters in our reports and how to format our reports. Coming days we will create more complex reports to understand the flexibility of SSRS. Stay tunned and comment below if you have any doubts.

Happy Learning :)