In this article, I will show you how to create Pie Chart Report in SSRS.
1. First of all open Visual Studio 2010 and create blank report as described in my previous article.
2. Now add data source and after that add data set as describe in my previous article.
In this report we used following query:
1
2
3
4
5
6
7
8
9
10
11
| SELECT DATEPART(Year, SOH.OrderDate) AS OrderYear, PC.Name AS ProdCat, SUM(SOD.LineTotal) AS SalesFROM Sales.SalesOrderHeader AS SOH INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Sales.SalesPerson AS SP ON SOH.SalesPersonID = SP.BusinessEntityID INNER JOIN HumanResources.Employee AS E ON SP.BusinessEntityID = E.BusinessEntityID INNER JOIN Person.Person AS C ON E.BusinessEntityID = C.BusinessEntityID INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID INNER JOIN Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryIDWHERE DATEPART(Year, SOH.OrderDate) = 2008GROUP BY DATEPART(Year, SOH.OrderDate), PC.Name |
3. Now right click on report area and go to Insert–> chart.
4. A Select Chart Type window pops-up.
In that select Shape from left pane and then select Pie chart as shown in below screen:
Then click on OK button.
5. Now you will see a representation of the pie chart on the design surface.
6. Click anywhere on the chart area to activate Chart Data Window.
Click on + sign in right side of Category Groups and select ProdCat from available fields.
Similarly click on + sign in right side of Values and select Sales.
So now your chard data window looks like below:
7. Now you can see report by clicking on Preview Tab. So your result looks like below:
8. Now if you want ToolTip on Series, Right click on Series and select Series Properties from the context menu. A Series Properties Window pops-up. Then select ToolTip from the dropdown list as shown in below screen shot.
Then click on OK button.
9. Now click on Preview button to see tooltip.
10. You can also change the position of Legend. To change the position of legend, right click on legend and select Legend Properties.
11. A Legend Properties Window pops-up. In that select Legend Position.
12. Now click on Preview tab to see the results.
Congratulations! We successfully completed Pie Chart Report in SSRS.
Pie Chart in SSRS 2014
by suresh
Sometimes, it can be visually good if we display the data in Pie Chart. For example, Sales by region, Country wide customers count etc. In this article we will show you, How to create and configure the Pie charts in SQL Server Reporting Services 2014
Below screenshot will show you the, Data Source and Dataset we used for this Pie Chart Report.
SQL Command we used in the above screenshot is:
T-SQL QUERY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SELECT Geo.[EnglishCountryRegionName] AS [Country]
,Geo.[StateProvinceName] AS [State]
,Geo.[City]
,Cust.FirstName +' '+ Cust.LastName AS [Full Name]
,Cust.EnglishEducation AS Education
,Cust.EnglishOccupation AS Occupation
,SUM(Cust.YearlyIncome) AS YearlyIncome
,SUM(Fact.SalesAmount)AS SalesAmount
FROM DimCustomer AS Cust
INNER JOIN
FactInternetSales AS Fact ON
Cust.CustomerKey = Fact.CustomerKey
INNER JOIN
[DimGeography] AS Geo ON
Cust.GeographyKey = Geo.GeographyKey
GROUP BY Geo.[EnglishCountryRegionName]
,Geo.[StateProvinceName]
,Geo.[City]
,Cust.FirstName
,Cust.LastName
,Cust.EnglishEducation
,Cust.EnglishOccupation
ORDER BY [Country]
|
Creating Pie Charts in SSRS 2014
When you drag and Drop the Chart from Toolbox to Design space, a new window calledSelect Chart Type will be opened to select required chart from the available once. In this example, we are selecting Simple Pie Chart as shown in below screenshot.
Once you click on Ok button, Pie chart will be displayed in design region with dummy data.
Click on the empty space around the Pie Chart will open the Chart Data window
- Values: Any Numeric (Metric) value such as sales amount, Tax, Total Sales, Customer count etc. All these values will be aggregated using aggregate function (Sum, Count etc) because we are grouping them with the category group items.
- Category Group: Please specify the Column name on which you want to partition you Pie Chart.
In this example, we will create Pie chart for Sales by country so, Drag and drop the Sales Amount column from dataset to chart data values and Country Name in category group.
NOTE: Please be careful when dragging the columns to values region, if you mishandled or dropped in design area will end up in mess.
Click on the Preview button to see the report preview.
If you observe the above screenshot, It is providing perfect result but we are unable to identify the difference between Sales in France and Sales in Germany. To resolve these situations, We have to use the Data Labels.
Right click on the pie chart and select the Show Data Labels option from the context menu to show the values
Click on the Preview button to see the report preview.
Thank you For Visiting Our Blog
Formatting Pie Chart in SSRS 2014
by suresh
In this article we will show you, How to change the Pie Chart Title, Legend Title, Legend Position, Pie Chart Font, Pie Chart Model, Pie Chart Pallet and Display Percentage Values as Labels on Pie Chart in SQL Server Reporting Services 2014 with example.
To explain the available Format options, We are going to use below shown report. Please refer SSRS Pie Chart article to understand the Data Source and Dataset we used for this report.
Formatting Pie Chart in SQL Server Reporting Services
Change Pie Chart Title in SSRS 2014
To change the Pie Chart title, Please select the Chart title region as we shown in below screenshot
Now, change the title as per your requirement. Here we changed as Sales By Country because the report is displaying the same
Show Pie Chart Legend Title in SSRS 2014
To display the Pie Chart Legend title, Please select the Legend region and right-click on it will open the context menu. From the menu, select Show Legend Title option as we shown in below screenshot
Please rename the Legend title as per your requirement. Here we named it as Countries
Format Font and Legend Position of a Pie Chart in SSRS 2014
Select the Legend region and right-click on it will open the context menu. From the menu, please select Legend Properties option as we shown in below screenshot
Within the General Tab we have an option called Legend Position as shown in below screenshot. You can change the Legend Position by select those dot positions
Next, Within the Font tab we can change the Font Size, Font Family, Font Style and Color of a Pie Chart Legend as shown in below screenshot
Change the Pie Chart in SSRS 2014
SSRS allows us to change the chart type even after creating the chart. First, select the Pie chart and right-click on it will open the context menu. Please select the Change Chart Type… option from the context menu
Once you select the Change Chart Type… option, it will open new window to select the change. Here we are select 3D Pie chart
Click Ok button and preview the report
From the above screenshot you can observe that, We successfully changed the Chart Type
Change the Palette of a Pie Chart in SSRS 2014
We can also Change Pie Chart Color or Palette in SSRS. First select the Chart and goto properties. Change the color by select the Palette property as shown in below image
Here we selected the Chocolate color
Display Percentage Values on Pie Chart in SSRS 2014
First select the data labels on the Pie Chart and then right-click on them will open the context menu. Please select the Series Label properties… option from the context menu
Within the General Tab, Please select the Label data to #PERCENT from the drop down list. Once you select the percent a pop up window will be displayed asking, Do you want to setUseValueAsLable to false or not. Please select Yes because we are changing the default value to percent
Click Ok and click on Preview button to see the Pie Chart Preview
We successfully displayed the Percentages as Pie Chart Label
Thank You for Visiting Our Blog
