10-Histogram Chart

Paginated Reports eBook Introduction, Resource and Sample Data Downloads

A histogram is a graphical way of presenting a frequency distribution, typically in the form of vertical columns. It shows what proportion of cases fall into each of several categories (intervals for numeric data). It is constructed by selecting a total number of intervals to be used, or by specifying a fixed width for each interval. The choice is between reducing the information sufficiently while still providing enough variability to picture the shape of the distribution.

This quick recipe demonstrates how column charts in Reporting Services can be easily configured to automatically compute and visualize the frequency distribution.

Recipe Ingredients

Product Versions (one of the following):

  • SQL Server Reporting Services 2016 or newer
  • Report Builder 2016 or newer

What You’ll Need

  • AdventureWorksDW sample database

Difficulty Level: 200

Contributed by Robert Bruckner

Report Summary

This example uses the AdventureWorksDW for SQL Server sample database. The query retrieves customer demographic data, specifically the unique customer identifier and the calculated age of each customer.

Figure 1. Customers by age group, as histogram frequency distribution
[Histogram Chart – Final Report.png]

Designing the query

This example uses the AdventureWorksDW for SQL Server sample database, which you can download from here: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/adventure-works

To design the dataset, use the following steps:

Launch Report Builder and create a new data source for the AdventureWorksDW sample databases.

Create a dataset query to include the FactResellerSales, DimProduct, DimProductSubcategory and DimProductCategory tables, as shown below:

SELECT CustomerKey

, BirthDate

, DATEDIFF(YYYY, BirthDate, GETDATE()) as Age

from AdventureWorksDW.dbo.DimCustomer

Designing the report

Overall the report design requires only a few steps, as Reporting Services column charts have the ability to automatically group data and compute histogram frequency distributions. The necessary configuration settings are however a little buried, with the most crucial settings explained in step 5.

Add a new Chart, of type simple column, to the report surface.

Prepare chart settings as follows, with Figure 2 showing the resulting initial chart layout:

    • Delete the series legend to increase the available chart area.
    • Drag the CustomerKey field into the “Category Groups” drop zone of the chart.
    • Drag the Age field into the “Values” drop zone of the chart.
    • Set the chart title to “Customer Age Histogram” and adjust font settings as desired.
    • Right-click on the x-axis labels, and select “Show Axis Title”
    • Set the x-axis title to “Age Group” and adjust font settings as desired.

Figure 2. Initial column chart layout
[Histogram Chart – Initial Chart Layout.png]

Transform the column chart into a histogram chart, applying the following steps:

    • In the chart layout, select one of the data points (requires two clicks, as the first mouse click only selects the overall chart), so that the Properties Window in Report Builder shows the properties of the “Age” Values.
    • In the properties list, expand the CustomAttributes section and set ShowColumnAs: Histogram, and HistogramSegmentIntervalWidth: 5.
      The HistogramSegmentIntervalWidth setting determines the width of each segment. Setting this value to 0 results in automatic width interval calculation, using the HistogramSegmentIntervalNumber setting that can be used to configure a fixed number of intervals automatically applied over the whole dataset. Figure 3 shows the final CustomAttributes settings.

Figure 3. Configuring the column chart to automatically group data values, and visualize as histogram
[Histogram Chart – Histogram Settings.png]

Figure 4 shows the final chart layout with customer age values automatically categorized into age groups of size 5, and frequencies automatically computed.

Figure 4. Preview of report, rendering an automatically computed customer age distribution
[Horizontal Table – Final Report.png]

Final Thoughts

Histogram charts are a form of visual data binning, a data categorization technique to reduce to effects of minor data inaccuracies. Histograms show the frequency distribution of the resulting categories (how many values fall into each category). With the release of Reporting Services 2008 and newer, charts provide an easy way to automatically perform this type of statistical analysis visually in a report.