Feb 27, 2020 
5.7k
27
DecChristmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
- SQL Server Blogs -
Reports are the visual representation of data that are stored in a particular database. You can represent the data in a grid format or a matrix format. You can design the grid or matrix with different colours to make them look more attractive. You can also send attractive looking reports via email to different users or drop the generated reports in the shared folder.
But no matter how attractive you make the grid or matrix level report, at the end of the day they are nothing but datasets displayed in a colourful and meaningful way. There is an old saying “A picture is worth a thousand words.” So, a visual representation of data is always more meaningful than grids and matrices, no matter how attractive they are.
SSRS provides a wide range of visual display tools such as below.
Over the next couple of pages, we will discuss in detail about the utility of these tools and how to use them in the SSRS reports.
What is a Pie Chart
A Pie Chart is a kind of diagram that shows information in a circular graph... As it was, each cut of the pie is comparative with the size of that classification in the group as a whole. The whole "pie" speaks to 100 percent of an entire, while the pie "cuts" represents portions of the whole.
Creating a Simple Pie chart
In the next few paragraphs, we will go through different steps required to create a simple pie chart. We will be using the Adventureworks2016 database for most of our examples in this write-up.
Step1
We create a new project. We select the report server project wizard.
Step 2
The report wizard screen comes up.
Step 3
We need to connect to a database. As discussed earlier, we will be using Adventureworks2016.
Step 4
Next, we will use the query. We will use the below query for the sample Pie chart report.
Select 'New South Wales' as State,3934486 as Amount
union
Select 'Queensland' as State,1988415 as Amount
union
Select 'South Australia' as State,618256 as Amount
union
Select 'Tasmania' as State,239938 as Amount
union
select 'Victoria' as State,2279906 as Amount
This gives fictitious amount collection details of few states of Australia.
Step 5
Select the report type. We will use a tabular type.
Step 6
Select the fields to be displayed and click Finish.
Step 7
The report screen will look like below.
Step 8
Now let us add the Pie Chart
Right-click on the report. Select insert and then chart.
Select Pie Chart
Step 9
Next, we would configure the chart data. We will take the amount as values and state as groups.
Step 10
The final output looks like below.
Read: How to Prevent SQL Injection Attacks?
In the next section, we will learn how to format the pie chart to make it more presentable.
Formatting the Pie Chart
Over the next few paragraphs, we would learn to make certain cosmetic changes to the SSRS Pie Chart Report that would make the report more presentable to the user.
Change Chart Title
Select the chart title bar and right-click on it. Select Title Properties to open the Chart Properties Wizard.
Change the chart title to “Sample Pie Chart” and click ok. You will see the change in the chart heading like below.
Change the chart title position
You can change the position of the title by using the Title Position option of Set Position and Docking tab.
The output will look like below.
Change Title background colour
Right-click on Chart Title. Go to Chart title properties. Select Fill and select Colour and style as below.
The final output will look like below.
Change Chart Style
You can change the format of the pie chart. Right-click on the chart and change chart types. You can change the type of pie chart format from the list provided.
Change the chart background
Right-click on the chart and click on chart properties. The chart properties window will come up.
Select Fill and select Pick Color to the colour of your choice. The chart background will change as below.
Legend properties
Like chart title, the alignment, font colour, background colour etc. of Legend can also be changed using the Legend properties dialogue box.
Data bars in SSRS
Data bars are another form of charts to visually display data in SSRS reports. Following are step by step processes on how we can incorporate Data bars in SSRS reports.
Step 1
Create a new SSRS project using Report Server Project option.
Step 2
Right-click on the Report tab and select add a new report. The report wizard toolbox will pop up.
Step 3
Click next and put the connection string.
Click ok and the connection string will be displayed as follows.
Step 4
Next step is to put the query in the query string. We will write the select statement using the product table under the production schema of Adventureworks2016 database. Below is the query.
Select ProductID,Name,ProductNumber,SafetyStockLevel from [Production].[Product]
We put the query in the report wizard query window.
Step 5
Next we select the type of report. We’ll select a report type table.
Step 6
Read: SQL Server Recovery Models-Simple, Full and Bulk Log
We select all the fields under detail section in the Design the table window.
Click finish to generate the report.
Step 7
Right click on the report. Click on the insert and select chart. The chart wizard is displayed. Select bar chart.
A bar chart will be added to the report below.
Step 8
We need to configure the chart and provide values and category groups. Right click on the chart once and the chart data window will come up. Provide Productid in the values section and SafetyStocklevel in the category groups.
Step 9
The final report will look like below.
As you can see, although we have incorporated the bar chart inside the report, the report does not look visually appealing. The reason being both the chart and report grid appears to be a separate entity. It seems that the two entities, the report grid and the bar chart are not correlated. To avoid such issues we can incorporate the bar chart within the report grid itself. In the next few steps, we will learn how to do that.
Learn SQL Server in the Easiest Way
Incorporating Bar chart within a table report.
The process of creating the table reports will be the same as the previous example. So we are skipping it.
Step 1
Add a new column on the right side of the grid report. Click on the section of the report after which you want to add the column. Click on the insert column and select on which section you want to add the column.
Step 2
Then right-click on the data cell and select insert and then data bar.
Step 3
Select the type of data bar you want to insert.
Step 4
Double click on the Chart cell and the chart data properties will pop up.
Step 5
Since we are adding the data bar on each row we do not need to configure the category group. Instead we only need to configure the value section. Select safety stock field in the value section.
Step 6
Put a heading to the column which would hold the data bar.
Step 7
The final output will look like below.
As you can see here, this format of the report looks much for user friendly as the user can directly understand the safety stock level fluctuations by just looking at the report at a glance. Like the pie chart, the bar chart reports can be formatted in the same way and its colour, design etc. can be changed.
SQL Server Training & Certification
Indicators in SSRS
Here is the step by step description on how to add an indicator in the SSRS report.
Step 1
Create a new SSRS project. Select Report Server Project.
Step 2
Right-click on the report tab and select a new report option. The report wizard tab pops up.
Step 3
Read: What is SQL Formatter? Features of SQL Formatter Online
Add a connection string in the SSRS report.
The connection string is added into the report wizard.
Step 4
Next we will create the query. We will use the Adventureworks2016 database for generation of the report. Following is the query which we will be using.
SELECT [City]
,[FirstName]+' '+[LastName] AS [FullName]
,[JobTitle]
,[SalesYTD]
,[SalesLastYear]
FROM [Sales].[vSalesPerson]
Step 5
We will use the tabular format of the report.
Step 6
Select the fields to be displayed in the report in the design table section.
Click on finish to generate the report.
Step 7
Now let us add the indicator in the report. For that we need to add a new column in the right side of the report at the end.
Step 8
Right-click on the newly created column and select gauge.
Select a type of indicator.
Also, add a suitable heading on the indicator column.
Step 9
Right on the report cell containing indicator and select indicator properties.
The indicator properties window pops up.
Step 10
Now let us configure the indicator properties as below.
Select the value section value to [Sum(SalesYTD)] and State Measurement unit to Numeric.
In the red section set the start value as 0 and the End value as =Sum(Fields!SalesLastYear.Value) + 1
In the green up arrow section set the start value as =Sum (Fields!SalesLastYear.Value) + 1 and end value as blank.
The final configuration will look like below.
Step 11
The final output of the report looks like below.
As you can see if the sales of the current year are greater than sales of the previous year then you can see the indicator is up otherwise the indicator is shown as down.
The configuration of the gauge is the same as the indicator.
Conclusion
The above write up gives you an introduction to pie charts, bar chart and gauges. The reader would be able to get the basics of the above mentioned SSRS tools from the write-up and would be interested to pursue further study based on the knowledge gained from the above writeup.
Read: SQL Career Path - Step By Step Microsoft SQL Server Career Guide
FaceBook
Twitter
LinkedIn
Pinterest
Email
I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Aug 11, 2019
8.1k
8.1k