Getting Started With SQL Server Reporting Services (SSRS) - Part 2

Overview

 

Hi friends! In my previous article we learned how to create a simple report using Microsoft SQL Server Reporting Service (SSRS). In that we used a simple SQL Statement to display data from a SQL Database in our report. For those who have not read my previous article, they can read it from Getting Started With SQL Server Reporting Services (SSRS) - Part 1. So now in this article we will learn how to create parameterized reports using SSRS, some basic data formatting concepts and how to set visibility for report controls. I am using here the same Solution of Report Server Project to make it simple to understand.

 

So let's start with our previous Report Server Project.

 

Step 1

 

Open Report Server Project Solution. We will add a new report to it. So just right-click on the Reports folder in Solution Explorer. Select Add and then click on New Item. Here, some people may ask, when we right-click on the Report Folder we see the Add New Report command so why am I using Add, New Item and then Report. When you select Add New Report it will create a new report and you will get the Report Design wizard. The Report Design wizard helps us to design reports quickly with some simple instructions. But since I am writing these articles for beginners it will be good for all to go step-by-step instead of using shortcuts or the wizard.

 

 

 

This will open a New Window. Select Report from the available list and click OK. This will create a new blank report. Drag a Table control from the ToolBox onto your report.

 

Step 2

 

Once you have added the Tablix control to your report, you will get a Data Set Properties window. Enter the Data Set Name then select Use a dataset embedded in my report.

 

 

To add a shared data source to this report click on the New button. That will open a new window with the name Data Source Properties. Select Use shared data source reference and select Shared data source from the available list and click on the OK button.

 

 

Step 3

 

Now in the query type select Text. Here I am using the previous sample of data of the User Details with the additional column Registration Date.

 

 

 

Write your SQL Satement in the query window. You can use the Query Designer to create or validate your SQL Statement. Just click on the Query Designer button.

 

 

Step 4

 

After clicking on the Query Designer button you will see a new Query Designer Window. In the Query Designer window you can validate your SQL Query and check the query output. To validate our report SQL Query just click on the Run () sign and you will get a Query Parameter window. Here we are using two parameters in our SQL Query, one is FromRegistrationDate and the second is ToRegistrationDate.

 

 

To validate our SQL query just enter values for these two parameters and click on the OK button. If there is no error in the SQL statement then you can see the query results as shown in the following picture. Click the OK button to close all the open windows.

 

 

If you open Report Data from the Visual Studio Menus by selecting View -> Report Data then you can see our shared Data Source and Data Set with available columns for report and parameter required for the Data Set that we will use in our report.

 

 

 

Step 5

 

So here we are ready to design the report . To set the Data Set for the Tablix control select the Tablix control. Right-click on the upper-left corner of the tablix control and select Tablix Properties.

 

 

It will open a new Tablix Properties window. In that, select the data set name from the available list (here our data set name is TestDataSet) and click on the OK button.

 

 

Now the data set columns are available for our tablix control. When you move the mouse over the tablix column then you will see a () sign. Click on that sign and select the columns from the available list.

 

 

Step 6

Note that see the default format for the Registration Date column is yyyy-MM-dd. To change the format of that right-click on the Registration Date column. Select Expression.

 

 

You will see a new Expression window. Here we will use the Format function to change the Registration Date format to dd/MM/yyyy. Just change =Fields!RegistrationDate.value to =Format(Fields!RegistrationDate.Value, "dd/MM/yyyy") and then click OK.

 

 

Step 7

 

So here we are ready with our report. I have added one additional TextBox below tablix in the report with the message "No User Details found for selected registration dates". Here we will learn how to set conditional visibility for report controls. In our report if data is present for a selected Registration Date range then we will show that data in tablix and hide the TextBox message. If no data is present then we will show a No data found message and hide the tablix control. So after adding a TextBox control our report will look like this.

 

 

Step 8

 

To set the visibility expression for the Text Box, right-click on the Text Box control and select Text Box Properties.

 

 

You will see a new Text Box Properties window. In the left panel click on Visibility then you will see the three options Show, Hide and Show or Hide based on an expression. Here we are using the third option to set the Text Box control visibility.

 

 

Click on the () button. Here to set the visibility expression we will use the CountRow function. You can read more details about this function here. In the expression window set this Expression =CountRows("TestDataSet")>0 and click on the OK button. It indicates that if the TestDataSet contains rows more than zero then this TextBox will automatically be hidden from the report otherwise it will be displayed on the report.

 

 

Now we will set the visibility for the Tablix Control. Select the Tablix control and then right-click on the upper left corner of Tablix and click on Tablix Properties.

From the left panel select Visibility and the same as above from the change display options select Show or hide based on an expression click on the () button and set the expression  =CountRows("TestDataSet")<1 and click on the OK button.

 

 

Step 9

 

So here we are ready with our Report Design. Our parameterized report is ready to test. Here for this report we are passing date parameters so we will set the data type Date/Time for both parameters FromRegistrationDate and ToRegistratioinDate. To set the data type of the parameter go to the View menu then select Report Data. In that in the Parameters section double-click on Parameter. You will see a Report Parameter Properties window. In that select the data type from the drop down list and click on OK. Here we are passing a date so I am selecting Date/Time.

 

 

So our final report will look something like this.

 

 

Step 10

 

Now, click on Preview to see the report output. You will see a report preview with two parameters. Select the From Registration Date and To Registratioin Date using a Date Picker and click on the View Report button. If data is present for that period you will get this output.

 

 

If data is not present for the given period you will get the following output.

 

 

In this article we learned how to create a parameterized report using SSRS. I hope you enjoyed reading this article. We will learn some additional features provided by SSRS in my next article. So keep reading and don't forget to post your valuable feedback.