September 15, 2014 08:42 by Ben
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.
Let's 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.
Reason Why you Must Choose ASPHostPortal.com
ASPHostPortal.com offers the newest Microsoft SQL 2014 database with all our hosting plans. We are your dependable SQL web hosting provider who will fulfill your needs!
Full Remote Access
We allow you full remote connectivity to your SQL Server 2014 Hosting database and do not restrict access in any way.
Easily transfer your existing SQL Server database
With our SQL Server hosting package, there's no need to rebuild your database from scratch should you wish to transfer an existing SQL Server database to us. If you already have a database hosted elsewhere, you can easily transfer the contents of your database using SQL Server Management Studio which is fully supported by our packages. SSMS provides you with an Import/Export wizard which you can use to upload your data and stored procedures with a couple of clicks.