BLACK FRIDAY OFFER: Flat 50% Off with Free Self Learning Course | Use Coupon BLACKFRIDAY50

- SQL Server Blogs -

SSRS Pie Chart - Having a Slice of the Pie

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.

  1. Pie Chart
  2. Bar Charts
  3. Indicators
  4. Gauge

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: What is the Career Path for a SQL Server DBA?

 

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: What is SQL Server Replication and How it Works?

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

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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: Why Is There Need for Notification In Jenkins?

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: What is Data Mining SQL? Data Mining SQL Tutorial Guide for Beginner



    Sanchayan Banerjee

    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.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 04 Dec 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

14 days 14 Dec 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

9 days 09 Dec 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

11 days 11 Dec 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

4 days 04 Dec 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

5 days 05 Dec 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

4 days 04 Dec 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

4 days 04 Dec 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

0 day 30 Nov 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

5 days 05 Dec 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

-1 day 29 Nov 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

19 days 19 Dec 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews