Bar Chart Report in SSRS

1. First of all open Visual Studio 2010 and create blank report as described in my previous article.

http://bhushan.extreme-advice.com/chart-report-in-ssrs/ (Step 1 & 2)

Now your report design will look like below:

2. Now add data source and after that add data set as describe in my previous article.

http://bhushan.extreme-advice.com/chart-report-in-ssrs/ (Step 4 – 7)

In this report we used following query:

?

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

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)/1000 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         Pc.Name = 'Bikes' GROUP BY    C.FirstName + ' ' + C.LastName, DATEPART(Year, SOH.OrderDate),DATENAME(Month, 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 Bar from left pane and then select Stacked Bar as shown in below screen:

Then click on OK button.

5. Now your report design looks like below:

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

Click on + sign in right side of Category Groups and select Employee 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:

You will be shocked why there are only 3 Names appearing.

8. To display all Names, right click on Vertical axis and select Vertical Axis Properties from the context menu.

9. A Vertical Axis Properties window pops-up.Now give 1 in Interval.

Now click on Ok.

10. Also give the name of Vertical Axis and Horizontal Axis. And add one text box above the chart area and give appropriate text. Now click on preview tab.

So now your result looks like below:

Congratulations! We successfully completed Bar Chart Report in SSRS.