SQL 2014 Tutorial | SQL Server 2014 will be an amazing release regarding all the various performance enhancements that are part of this new release. And know I wanna tell you about how to create Histogram Chart in SQL.
In this tips we will create a histogram chart in SQL 2014 to show how to aggregate data will very little effort.
- Create a sample table called Employee that has columns EmpID and EmpAge with the data as shown in the below screenshot.
- 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.
- 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.
- 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.
- 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.
- 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.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.
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
