Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a Reporting Services Histogram Chart for Statistical Distribution Analysis


By:   |   Last Updated: 2011-05-31   |   Comments (9)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services Charts

Problem

Typically transactional data is quite detailed and analyzing an entire dataset on a graph is not feasible. Generally such data is analyzed using some form of aggregation or frequency distribution. One of the specialized charts generally used in Reporting Services for statistical distribution is Histogram Charts. In this tip we look at how Histogram Charts can be used for statistical distribution analysis and how to create and configure this type of chart in SSRS.

Solution
In this tip we will create a histogram chart in SSRS to show how to aggregate data will very little effort.

Step 1
Let's create a sample table called Employee that has columns EmpID and EmpAge with the data as shown in the below screenshot.

creating a histogram chart in ssrs

Step 2
Create a new SSRS report and configure it to use the data from the table we just created. Add a bar chart to the report and configure it as shown in the below screenshot.

create a new ssrs report and configure it

Step 3
Execute/Preview this report and your report should look like the below screenshot and you will see data listed for all fifteen employees.

In reality, an employee table can contain records for hundreds to thousands of employees and therefore if the requirement is to analyze age distribution this type of report won't serve the purpose. A typical approach to solve this issue would be to retrieve the count of employees falling into a few predefined age categories and show it in a distribution chart like a pie-chart. In the next few steps we will see how a histogram chart can be a much simpler approach.

retrieve the count of employees falling into a few predefined age catagories

Step 4
Select the EmpAge chart series and open the properties window. In the CustomAttribute category, select "ShowColumnAs" property and set the value as "Histogram" as shown in the below screenshot.

the custom attribute catagory

Step 5
Execute the report and you should find results similar to the below screenshot. This is a histogram chart, but the problem with this chart is that by default the chart has selected all fifteen distributions, so you get one employee for each age category which is not very helpful.

execute the report in ssrs

Step 6
Let's say we want to see the data in three age groups 20 - 25, 25 - 30 and 30 - 35 and the number of employees in each age group. In the CustomAttribute category, you will find a property "HistogramSegmentIntervalWidth" and the default value is zero. Change this value to "5", so that the age group we have is divided into 3 categories as desired. Execute the report and the report should look like the below screenshot. This chart shows 4 employees in the 20 - 25 group, 5 employees in the 25 - 30 group and 6 employees in the 30 - 35 group.

the completed histogram chart

Also note that the "Percentage of Total" axis is generated and calculated automatically without any programming efforts and this axis is very useful in statistical analysis. This type of axis is not possible out-of-box in charts like pie-charts and hence charts such as histogram charts are preferred.
Next Steps
  • Check out other properties associated with Histogram Charts.
  • Experiment with other charts that can be used for statistical distribution analysis.
  • Review these other SSRS development tips


Last Updated: 2011-05-31


next webcast button


next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 10, 2017 - 4:03:51 AM - Enrico Domingo Back To Top

Hi,

 

I just want to know if its possible to add a bell curve on this chart or density line. thanks!


Wednesday, December 12, 2012 - 4:30:49 AM - Nagi Back To Top

HI,

pls help any body.

how to show x-axis values in top means the horizental x-axis values shows on top like revese of chart.

y

-

-

   ------------- iniatially the x-axis values

 

i need below like this way

---------- x-axis values

-

-

y

 


Monday, September 10, 2012 - 12:21:02 PM - Colin Back To Top

I believe you made a small mistake when you said: "Add a bar chart", I think you meant to say: "Add a column chart"


Monday, April 09, 2012 - 7:59:58 AM - Kishlay Anand Back To Top

Hi,

If u see in the diagram, Axis Title Properties has Frequency, When I rename to some" Kishlay" , its still showing me frequency word.

How we can chnage that. 


Monday, March 19, 2012 - 5:37:01 AM - Bram Back To Top

Hello,

I want to show several histograms in different plots. Is it possible to set the scale of the x-axis? I have tried to set the Scale properties max and min of the Chart Axis but can't make it work for histogram. It looks like it only works for normal bar chart but not for histogram

Many thanks!

 


Wednesday, November 23, 2011 - 9:37:22 AM - Todd Payne Back To Top

Just curious about how it groups the records.  It seems that 25 year old are not included in the first group 20-25, because they are included in the second group.  So it is counting records with age greater than or equal to 20 and less than 25.  The second group is filterig for greater than or equal to 25 and less than 30.  Why is the final group filtering for greater than or equal to 30 and less than or equal to 35.  If we added a group for 35-40 would there only be 1 in the new group and only 5 in the 30-35 group?

Nevertheless this is a great feature.  I am going to use it with our water main rating system that predicts the remaining life span of our water mains.  This will give us good overview of the state of the district, knowing how many and perhaps total footage of our water mains have a remaining life span of 5, 10, and 20 years will help our commissioners with long term budgeting for  CIP (Capital Improvement Programs) planning.


Tuesday, May 31, 2011 - 3:17:49 PM - Brian Schiller Back To Top

Instead of specifying "HistogramSegmentIntervalWidth", the user specifies "HistogramSegmentIntervalNumber" which allows for X bins.  The range of data being reported on can vary drastically.  Sometimes the report will be run where the total deviation is less than 0.1, but there is till enough resolution in the data for a 20 bin histogram.  Othertimes the deviation might be in the 1000s.  It is very common for our users to drill into a bin to see the what specific data points make up the outlying bins.  Even the ability to have show the min/max values of the bin in a tooltip would be nice.


Tuesday, May 31, 2011 - 2:45:16 PM - Siddharth Mehta Back To Top

1) I would wonder why would anyone want to drill a histogram ? To do statistical analysis, data is shown in an aggregated manner. If you want to drill, there is no hierarchy defined. At the max you do change the display from histogram to a bar chart which would be as good as a full drill.

2) As you would have configured your histogram chart, so you would be knowing the config. Based on the same you can also create a table and add a group by accordingly.

I hope you find this article helpful.


Tuesday, May 31, 2011 - 2:02:39 PM - Brian Schiller Back To Top

Is there anyway to get information about the data in each bin?  I am looking at 2 scenarios:

  1. Drill thru to another report with detailed information based on the min/max values of the bin
  2. a details table on the same report with grouping based on bin.
Currently I am doing all of the bin calculations on the SQL side so that I am able to have this information, but the builtin histogram adds the very nice "Pct of total" Y-axis, and makes things very easy.
Thanks,
Brian


Learn more about SQL Server tools