Data regions are one of the key components of Reporting Services and allow for entire datasets to be displayed, summarized, grouped, sorted, filtered, and so on. This is unlike the functionality of a single text box, line, or image, which can be used to show only a single value. For those familiar with other reporting platforms such as Crystal Reports, the data region can be thought of as the body portion of the report. The differences are that it is very easy to add multiple data regions to a report and the data regions can be placed in any configuration required to achieve the desired output.
Reporting Services in SQL Server 2008 introduces the Tablix data region, which combines the matrix and table, and the Gauge data region. Furthermore, the Chart data region has been significantly upgraded, with 3D rendering capabilities and many more chart types.
Tablix Data Region
Awesome is the word that comes to mind when we think about the new functionality introduced by the Tablix data region. Its main features are as follows:
Combination of multiple tables and matrices within a single region: In the past, we were able to easily build tables of data that allow for dynamically expanding rows based on the underlying data of the report. We were also able to build matrices that allow for dynamically expanding rows and columns based on the underlying data. However, we have never been able to easily combine multiple matrices or tables within a single data region. The Tablix control provides this capability. This means that two matrices that have the same row grouping but different column grouping and totaling could be combined together side by side. An example is a sales matrix by region being combined with a sales matrix by time. The data regions within each matrix can also be different. One data region could display sales dollars, and the other could display sales quantities.
Multiple levels of grouping on both columns and rows: The Tablix data region also provides the ability to create stepped columns. For example, instead of having separate columns for country, state, and city data, you can have a single column, with each value indented a specified distance from its parent. The Tablix data region maintains the ability to group and subtotal on each individual value, even though the values are combined into a single column. The Tablix region offers very powerful functionality that will eliminate the need to develop custom solutions for building very complicated reports. To demonstrate, the following examples provide a walk-through of creating some Tablix-based reports.
Hierarchical Rows and Dynamic Headers
In this example, we’ll build a report that displays yearly sales totals by state and city. The report will provide stepped rows and the combination of total values within a group header row. We will also useadvanced formatting to provide a drill-down effect. The finished report, DataRegions – Step1 – Hierarchical Rows and Dynamic Headers.rdl, can be downloaded from the Source/Downloads area of the Apress web site (http://ww.apress.com).
Setting Up the Data Source and Dataset
Follow these steps to set up for the report:
Query Designer with reults preview
Report Designer with the data source, dataset, and empty matrix
We are now in a position to start building our report. We’ll start out by adding row, column, data, and total values.
At this point, the report should look similar to Figure when previewed.
Preview of report
Clearly, there are some problems:
So, let’s go ahead and take care of these issues.
Adding a Report Title
First things first—let’s add a text box that will contain the title for this report.
The new alignment feature enables easier placement of report items.
Updating the Formulas
Now we’ll update the sales formulas to use the Count function instead of the Sum function.
Report with title and updated expressions for data and totals
Creating Stepped Row Headings
We will now create the stepped row headings by combining the Province and City values into the same column.
Report with stepped column headings for StateProvince and City
Sorting the Rows
Let’s take care of the row-sorting issue. We need the values for year to be sorted from oldest to newest (2001–2004).
Formatting Numeric Values
We need to format the numeric values so that they don’t display decimals or zero values.
Repeat these steps for each of the other three cells that contain the formula [Count(SalesOrderID)].
Creating a Drill-Down Effect
Creating a drill-down effect within your report is pretty easy, assuming that you have used useful names for the text boxes to be used in the grouping. We want our report to open with the individual city values for each state hidden. The user would then select a + icon next to the state to see the city detail.
Drill-down enabled for State/City details
Finally, we can make some formatting changes to highlight the various totals and sections of our Tablix data region.
The final report should look similar to Figure, which shows the City details for Alabama displayed.
Final report showing city details for Alabama
Parallel Dynamic Groups
The new Tablix data region allows for the combination of two matrices within the same data region. Previously, this was next to impossible without some tricky coding. Now it is as simple as adding some parallel groups and adjusting subtotals. Note that both the dynamic column value and the data value can be different between the two side-by-side matrices.
To demonstrate this feature, we’ll continue with our previous example by adding a column group to indicate online sales, and for each column, we’ll provide the dollar value of the sales by state and city. A completed version of this report, DataRegions– Step2 – Parallel Dynamic Groups.rdl, can be downloaded from the Apress web site.
Modified SQL statement to return OnlineOrderFlag
Your report should look similar to that shown in Figure.
Sales report showing sales quantities and online sales volume
Gauge Data Region
With the KPI quickly becoming a staple of almost every business intelligence project, it only makes sense that Reporting Services would start to provide more support in this area. The introduction of the Gauge data region is a step in this direction. It provides a one-dimensional view into a single key value. The Gauge region is very useful for displaying KPIs within your reports, but gauges can also be grouped together in tables, matrixes, and panels to provide functionality over and above reporting on a single KPI.
As an example, let’s walk through adding a gauge to an existing report. The finished report for this exercise is provided as DataRegions – Step3 – Gauge Data Region.rdl.
Preview the report. As shown in Figure, the report will show the gauge side by side with the sales totals for each state.
Sales order report with an embedded meter Gauge data region
Updated Chart Data Region
The Chart data region has existed for some time, but in many ways, the charting capabilities in Reporting Services in SQL Server 2008 provide so much richness and flexibility that they could almost be thought of as a new component. It is now possible to create charts with multiple data regions, scale breaks, and detailed labeling. Furthermore, Reporting Services has a number of new chart types, such as shape, stock, polar, and range. These dramatically enhance your ability to provide proper visualizations in BI applications. Table provides brief descriptions of the various chart types.
Reporting Services Chart Types
The best way to become familiar with the new charting capabilities is to walk through an example of creating a chart. This exercise will provide less detail, based on the assumption that you are now somewhat familiar with Report Designer. A completed version of this report, DataRegions – Step4 –Chart Data Region.rdl, can be downloaded from the Apress web site.
Sales report with a Chart data region
Chart with two data series with primary and secondary axes
Setting the legend location to bottom-middle
And now we’re finished. Preview the final report, which includes Chart, Gauge, and Tablix data regions. Figure shows the final report.
Final report containing Chart, Gauge, and Tablix data regions
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.