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


Overview

Hi friends! This is the third article i am going to write 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 have created one simple report. For those who have not read this article they can read the same from here 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 different 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 chart control in our report. 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. 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 new report in Report Project Solution. So I am not going to provide screen shots for the same. If anyone is facing any problem please refer my previous articles. I have created New Report that is Report3.rdl in same report solution we are using for this series.

Step 2

Now, From ToolBox drag and drop Chart Control on your report. This will open new window with heading “Select Chart Type“.

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

Step 3

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

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

Step 4

Here, I am using simple SQL Statement which display Registration Date and Count Of User Registration for that date from User Details Table.

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

You will see Column Chart Control on your report.

Step 5

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

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

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

Step 6

Here, In our Database 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 Registration Date format Right Click on Registration Date in Category Group and Click on Category Group Properties.

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

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

Step 7

Change Chart Title as “Registration Date”, Axix X title as “Registration Date”  and 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 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 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 etc. We will learn How to add Subreport in SSRS in my next article. So keep reading and don’t forget to post your valuable feedback.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s