Let us discuss about “How to show MEAN and MEDIAN” of a series in a chart in this post. This post is specially for those who doesn’t know that there is an option available in SSRS to show MEAN and MEDIAN in reports. To demonstrate the same lets us take a simple query from Adventure Works database. This query pulls Product wise sales from the database. PFB the query –
SELECT Top 30 DimProduct.EnglishProductName, SUM(FactInternetSales.SalesAmount) AS Sales
FROM FactInternetSales INNER JOIN
DimProduct ON DimProduct.ProductKey = FactInternetSales.ProductKey
GROUP BY DimProduct.EnglishProductName
To make the report look better, I am pulling ONLY top 30 records from the database.
Now create a “Dataset” in SSRS report using the above query.
Now take a line chart from ToolBox and drag and drop EnglishProductName to “Category Fields” and Sales to “Data Fields” of Line chart as shown in below pic.
Now Right click on “Sales” which is added to “Data Fields” and select “Add Calculated Series” option.
Select “Mean” from the dropdownlist next to label “Formula” and select OK.
Repeat the last TWO steps and add MEDIAN to the Line chart. Now click on PREVIEW to preview your report. You can see MEAN and MEDIAN series added to your chart.
That’s it .. It is as simple as this. Hope you understood and Happy coding !!
Roopesh Babu V