Chart Report in SSRS

Chart Report in SSRS

1. First of all open Visual Studio 2010 and open your SSRS project.In Solution Explorer Right Click on Reports and select Add –> New Item.

Chart Report in SSRS-1

 

2. In Add New Item Window we will select Custom Template which we generated in previous article.

To know how to create custom template check below link

http://bhushan.extreme-advice.com/custom-report-template-in-ssrs/

Chart Report in SSRS-2

Then click on Add Button.

3. Now your report design will look like below:

Chart Report in SSRS-3

4. Once you created a blank report, your next step is to create a data source.

To create a data source, right click on Data Sources in Report Data Pane and click on Add Data Source as shown in below screen shot:

Chart Report in SSRS-4

5. It will take you to the screen of Data Source Properties, where you can use either shared data source or embedded data source in report. Here we used a shared data source.

Chart Report in SSRS-5

Then click on OK button.

6. Now you need to create a data set.

To create a Dataset, right click on Datasets in Report Data Pane and click on Add Dataset as shown in below screen shot:

Chart Report in SSRS-6  7. It will take you to the screen of Dataset Properties, where you can use either shared dataset or embedded dataset in report. Here we use embedded dataset.

Chart Report in SSRS-7

Then click on ok button.

In this report we used following query:

SELECT      C.FirstName + ' ' + C.LastName AS Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear, 
            DATENAME(Month, SOH.OrderDate) AS OrderMonth, 
            PC.Name AS ProdCat, SUM(SOD.LineTotal) AS Sales
FROM        Sales.SalesOrderHeader SOH INNER JOIN
            Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
            Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID INNER JOIN
            HumanResources.Employee E ON SP.BusinessEntityID = E.BusinessEntityID INNER JOIN
            Person.Person C ON E.BusinessEntityID = C.BusinessEntityID INNER JOIN
            Production.Product P ON SOD.ProductID = P.ProductID INNER JOIN
            Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN
            Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE       DATEPART(Year, SOH.OrderDate) <= 2008
AND         DATEPART(Month, SOH.OrderDate) = 4
AND         SOH.SalesPersonID = 281
AND         Pc.Name <> 'Bikes'
GROUP BY    C.FirstName + ' ' + C.LastName, DATEPART(Year, SOH.OrderDate),DATENAME(Month, SOH.OrderDate), PC.Name

8. Now right click on Report Area and go to Insert–> Chart.

Chart Report in SSRS-8

9. A Select Chart Type window pops-up.In that select Columns from left pane and then select first column report as shown in below screen:

Chart Report in SSRS-9

Then click on OK button.

10. Now your report design looks like below:

Chart Report in SSRS-10

11. Now click on Chart Area. It will open Chart Data Pane.

Click on + sign in right side of Category Groups and select ProdCat from available fields.

Similarly click on + sign in right side of Series Groups and select Order Year.

Similarly click on + sign in right side of Values and select Sales.

So now your chart data window looks like below:

Chart Report in SSRS-11

12. Now to see how your report will looks like click on Preview Tab.So your result looks like below:

Chart Report in SSRS-12

13. To modify Chart Title and Axis Title, click on it and give a suitable title for your chart.

Here in Chart Title I will give Product Wise Sales. In X Axis I will give Product Category and in Y Axis I will give Sales. So now report preview will look like below:

Chart Report in SSRS-13

Congratulations! We successfully completed Chart Report in SSRS.