SQL Server: Designing Chart Report in SSRS 2008

In this article, we will see how to design a Chart report in SQL Server Reporting Services 2008. For this demonstration we will use AdventureWorks sample database provided by Microsoft.

You can also check my previous article on SQL Server: Designing Tablix Report in SSRS 2008

To design this report, let’s open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –

clip_image001

Once your project is ready, let’s create a data source which will fetch the data from AdventureWorks database. To fetch the data let’s write the following query –

clip_image002

SELECT YEAR(SOH.OrderDate) OrderYear,PC.NAME AS CategoryName,PSC.NAME as SubCategoryName,SUM(UnitPrice) TotalPrice, SUM(OrderQty) TotalOrders
FROM Production.ProductCategory PC INNER JOIN Production.ProductSubcategory PSC ON PC.ProductCategoryID=PSC.ProductCategoryID
INNER JOIN Production.Product P ON PSC.ProductSubcategoryID=P.ProductSubcategoryID INNER JOIN Sales.SalesOrderDetail SOD ON P.ProductID=SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY YEAR(SOH.OrderDate), PC.Name,PSC.Name
ORDER BY OrderYear,CategoryName,SubCategoryName


If you execute this query, you should get a similar the result as shown below –

clip_image003

To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. This will show you a shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –

clip_image004

Now let’s add a blank report using our solution explorer. To add a blank report, right click the Report and click on ‘Add New Item’ and choose ‘Report’. Name the report as ‘AdventureWorksYearlySalesChartReport’ as shown below –

clip_image005

Now go to ‘View’ menu and click on ‘Report Data’. In the ‘Report Data’ window, add a new dataset as shown below –

clip_image006

This will show you the ‘Dataset Properties’ window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. This displays a ‘Data Source Properties’ window. Choose ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box as shown below –

clip_image007

Now we are ready to design the report. Drag and drop a ‘Chart’ control from tool box on our report. As soon as you drop the chart control on the report, it will ask you to ‘Select Chart Type’. Make a choice of Column chart and click ‘OK’ as shown below –

clip_image008

Now click on the Chart and you will see the view of the chart with different axis, as shown below –

clip_image010

Now from the ‘Report Data’ Window drag and drop ‘TotalPrice’ on ‘Drop data fields here’ and drag and drop OrderYear on ‘Drop category fields here’. Now your report will look like this –

clip_image011

Now click on the preview report tab and see your report. It will look similar to the following –

clip_image012

Now let’s modify the report. First of all change the title of the report to ‘Adventure Works Yearly Sales’. Change the ‘Y’ axis title to ‘Total Sales’ and change ‘X’ axis to ‘Order Year’. Now preview your report and it will look like the one shown below –

clip_image013

Now right click the ‘Total Price’ and click on ‘Add Calculated Series’ as shown below –

clip_image014

You will see the ‘Calculated Series Properties’ window. From here, choose ‘General’ section. Under formula, choose ‘Exponential Moving Average’ as shown below –

clip_image015

Now preview your report and it will look like the one shown below –

clip_image016

Now right click the Chart column and choose ‘3D Effects’. This will show you ‘Chart Area Properties’ window. Check the check box ‘Enable 3D’ and click ‘OK’ button by keeping the other properties default. Preview your chart and this is what you see –

clip_image017

Summary – In this article, we have seen how to design a Chart report using SQL Server Reporting Services 2008.


No comments: