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

Overview

 

Hi friends! This is the third article I have written for this series of Getting Started With SQL Server Reporting Services (SSRS). In my first article of this series we saw how to start with SQL Server Reporting Services, requirements for SQL Server Reporting Services, installation and we created one simple report. For those who have not read this article they can read it from Getting Started With SQL Server Reporting Services (SSRS) - Part1.

In my second article we saw how to create parameterized reports, how to format report data and how to set visibility for various report controls using various expressions. You can read this article here Getting Started With SQL Server Reporting Services (SSRS) - Part 2.

 

Now, in this article we will see how to add a Chart control to our report. I am using here the same solution of the Report Server Project to make it simple to understand.

 

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

 

Step 1

 

Open the 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. I think if you are reading all my articles for this series then you are familiar with this step and I hope all of you can add a report to a Report Project solution. So I am not going to provide screen shots for that. If anyone is facing any problem please refer to my previous articles. I have created a new report Report3.rdl in the same report solution we are using for this series.

 

 

Step 2

 

Now, from the ToolBox drag and drop a Chart Control to your report. This will open a new window with the heading "Select Chart Type".

 

Here, I am selecting column chart that is the first chart type in the very first line. Click on the OK button.

 

Step 3

 

Now, you will see a Dataset Properties window. Enter the Dataset Name TestData. We will use a Dataset embedded in the report so select the option "Use a dataset embedded in my report".

 

To set the Data Source click on the New Button. You will see the "Data Source Properties" window. Select the "Use shared data source reference" option and select a data source name from the available drop down list. Here it is TestDS. Click on the OK button.

 

Step 4

 

Here, I am using a simple SQL Statement that displays the Registration Date and Count Of User Registrations for that date from the User Details table.

  

In the Dataset Properties window select Query Type as Text and enter the preceding SQL Statement in the Query window. You can validate this SQL Statement using the Query Designer. Click on the OK button.

 

You will see a Column Chart Control on your report.

 

Step 5

 

Click on the Chart control, you will see a chart data window on the right side of the Chart control.

 

To set values for the Chart Control click on the Plus sign as shown in the image. You will see two columns, RegistrationDate and Registration_Count. Select Registration_Count.

 

To set the Category Groups for the Chart Control click on the Plus sign as shown in the image. You will see two columns, RegistrationDate and Registration_Count. Select RegistrationDate.
 

Step 6

 

Here, in our database the RegistrationDate column format is "yyyy-MM-dd" and also it contains time. In our report we do not need to show this time part. To change the Registration Date format right-click on Registration Date in the Category Group and click on the Category Group Properties.

 

You will see the Category Group Properties window. To change the Registration Date format click on the () button in the front of the Label tag.

 

In the set expression for label window change =Fields!RegistrationDate.Value to =Format(Fields!RegistrationDate.Value,"dd/MM/yyyy") and click on the OK button.

 

Step 7

 

Change Chart Title as "Registration Date", the Axix X title as "Registration Date"  and the Axix Y title as "User Count".

 

Step 8

 

So here we are ready with our Report Design. 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.

 

Step 9

 

Now, click on the 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.

 

In this article we learned how to use the Chart Control in SSRS. I hope you enjoyed reading this article. You can try with other chart types like Line Chart, Bar Chart, Shape Chart and and so on. We will learn how to add a subreport to SSRS in my next article. So keep reading and don't forget to post your valuable feedback.