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.

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/

Then click on Add Button.

3. Now your report design will look like below:

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:

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.

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:

  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.

Then click on ok button.

In this report we used following query:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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.

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:

Then click on OK button.

10. Now your report design looks like below:

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:

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

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:

Congratulations! We successfully completed Chart Report in SSRS.