Data Regions - SQL Server 2008

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:

  1. Open the new Report Designer Preview application from within the Reporting Services section of the Microsoft SQL Server 2008 program group.
  2. Add a data source by selecting the “Click here to create a data source and data set for your report” link. The Data Source Properties dialog box appears.
  3. Leave the default type set to Microsoft SQL Server and click the Edit button. The Connection Properties dialog box appears. Set the server name to the name of your server and set the database name to AdventureWorks. Click the Next button.
  4. In the Query Designer window, enter the following SQL statement:
    select pprov.Name as StateProvinceName, paddr.City,
    datepart(yy, sheader.OrderDate) as OrderYear,
    sheader.SalesOrderID, sheader.TotalDue
    from Sales.SalesOrderHeader as sheader
    inner join Person.[Address] as paddr
    on sheader.BillToAddressID = paddr.AddressID
    inner join Person.StateProvince as pprov
    on paddr.StateProvinceID = pprov.StateProvinceID
    where pprov.StateProvinceCode = 'AL' or pprov.StateProvinceCode = 'AZ'
    order by pprov.Name, paddr.City
  5. Test the query by clicking the Run Query in the Query Designer window. The results should look something like those in Figure.

    Query Designer with reults preview

    Query Designer with reults preview

  6. Click Next. A new data source and dataset are created within the report.
  7. Add a Matrix data region to the design surface of the report. You can add this region by selecting the Insert menu and double-clicking the Matrix icon, by right-clicking anywhere within the design surface of the report and selecting Insert ➤ Matrix, or by dragging the Matrix control from the Toolbox if you’re using Report Designer within BIDS. At this point, you have an empty Matrix region, along with a data source and a dataset, as shown in Figure.

    Report Designer with the data source, dataset, and empty matrix

    Report Designer with the data source, dataset, and empty matrix

Adding Values

We are now in a position to start building our report. We’ll start out by adding row, column, data, and total values.

  1. Add StateProvinceName to the Rows section of the matrix.
  2. Add the City value to the right of StateProvinceName within the Rows section of the matrix. This creates a hierarchical group that will show all the cities within each state.
  3. Drag and drop SalesOrderID to the Data region. Notice that the expression [Sum(SalesOrderID)] is automatically entered within the cell text box. We’ll change this later.
  4. Add OrderYear to the Columns area of the matrix.
  5. Right-click the [City] cell (second row, second column) and select Add Total ➤Before. This will create a total row for each StateProvinceName.
  6. Right-click the [OrderYear] header (first row, third column) and select Add Total ➤After. This will create a new column within the report that will provide the total for [OrderYear] within each row.

    At this point, the report should look similar to Figure when previewed.

    Preview of report

    Preview of report

    Clearly, there are some problems:

    • The columns are not ordered properly.
    • We are calculating the sum of OrderID when we really want the count of OrderID.
    • The state and city need to be combined into one column, with the city slightly indented from the state value.
    • Our total line does not need the Total heading.
    • We cannot easily distinguish between headers, totals, or data values.
    • Our report doesn’t have a title.

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.

  1. In design mode, reposition the Matrix data region so that you can add a new text box above it.
  2. Right-click the design surface and select Insert ➤Textbox.
  3. Reposition and resize the text box to align with the Matrix data region. Notice the new guidelines feature within the designer, which helps you align report items. The guidelines are shown in Figure.

    The new alignment feature enables easier placement of report items.

    The new alignment feature enables easier placement of report items.

  4. Enter Sales Quantity Report in the text box.
  5. Set the font size to 20, make it bold, and underline the text.

Updating the Formulas

Now we’ll update the sales formulas to use the Count function instead of the Sum function.

  1. Right-click the City data cell (third row, third column) of the matrix and select Expression.
  2. We want to count the number of sales orders within each city not summarize the value of the SalesOrderID values. To do this, we modify the expression to use the Count function instead of the Sum function. Take this opportunity to review the various operators and common functions available within the Expression Editor. The final expression should be =Count(Fields!SalesOrderID.Value).
  3. We now need to update all the total fields with the new expression. Simply copy and paste the new expression over the old expression within the total fields. You should be working with cells 3 and 4 within the second row, and cell 4 within the third row of the data region. Your report should look similar to Figure.

    Report with title and updated expressions for data and totals

    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.

  1. The cell within the second row and second column has the heading Total in it. This cell is directly above the [City] data field and directly below the City column title. Select the StateProvinceName Total heading (row 2, column 2), click the field picker that appears in the upper-right corner of the field, and select StateProvinceName.
  2. Right-click the first column and select Delete Columns.
  3. Right-click the City row heading (row 3, column 1) and select Textbox Properties. On the Alignment tab, set the Left property under Padding Options to .125in and click OK. This causes the City values to be indented 0.125 inch when displayed underneath the State value.
  4. Delete that text for the City heading (row 1, column 1). The report should look similar to Figure when in design mode. Also take this opportunity to preview the report to see how things look.

    Report with stepped column headings for StateProvince and City

    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).

  1. In design mode, select the Tablix data region.
  2. Right-click the [OrderYear] group within the Column Groups section at the bottom of the designer and choose Edit Group.
  3. Select the Sorting section within the dialog box.
  4. Click the Add button and choose OrderYear within the Sort By drop-down. Leave the Order as A to Z. Click OK.

Formatting Numeric Values

We need to format the numeric values so that they don’t display decimals or zero values.

  1. Right-click the cell that represents the StateProvinceName total for each OrderYear (second row, second column) and select Textbox Properties.
  2. Within the Number section, choose Number for the Category value.
  3. Set the Decimal places to 0. Check Use 1000 Separate (,). Set the Show Zero As option to.

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.

  1. Update the Name property for the StateProvinceName text box (second row, first column) to StateProvinceName.
  2. Within the Row Groups area at the bottom of the design surface, select the City group, right-click, and select Edit Group.
  3. In the dialog box, select the Visibility section and set “When the report is initially run” to Hide. Select the check box for “Display can be toggled by this report item” and select the StateProvinceName text box from the list.
  4. Preview the report. You should see the result of your changes, as shown in Figure.

    Drill-down enabled for State/City details

    Drill-down enabled for State/City details

Final Touches

Finally, we can make some formatting changes to highlight the various totals and sections of our Tablix data region.

  1. In design mode, set the background color for the first row to Blue-Gray, bold the text, and set the text color to White.
  2. Bold the text of the StateProvinceName row heading (second row, first column).
  3. Set the background color for the StateProvinceName row (second row) to Ocean.
  4. Set the background color for the Total column (third column) to Blue-Gray, bold the text, and set the text color to White.

    The final report should look similar to Figure, which shows the City details for Alabama displayed.

    Final report showing city details for Alabama

    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.

  1. Open the report that you created in the previous example (alternatively, open the report named DataRegions – Step1 - Hierarchical Rows and Dynamic Headers.rdl that you downloaded). It may be a good idea to save the report with a new name before making the changes for this example. That way, you can always start over if things get messy.
  2. We need to add a new value to our dataset. Open the properties for DataSet1 and open the Query Designer. Modify the query so that it returns the OnlineOrderFlag value from the Sales.SalesOrderHeader table, as follows and as shown in Figure.
    select pprov.Name as StateProvinceName, paddr.City,
    datepart(yy, sheader.OrderDate) as OrderYear,
    sheader.SalesOrderID, sheader.TotalDue, sheader.OnlineOrderFlag
    from Sales.SalesOrderHeader as sheader
    inner join Person.[Address] as paddr
    on sheader.BillToAddressID = paddr.AddressID
    inner join Person.StateProvince as pprov
    on paddr.StateProvinceID = pprov.StateProvinceID
    where pprov.StateProvinceCode = 'AL' or pprov.StateProvinceCode = 'AZ' order by pprov.Name, paddr.City

    Modified SQL statement to return OnlineOrderFlag

    Modified SQL statement to return OnlineOrderFlag

  3. Right-click the Total column (first row, third column) and select Add Column Group ➤Adjacent Right. Select the OnlineOrderFlag as the Group Expression.
  4. Add the TotalDue data value to the detail row (third row, fourth column) for this group. In this case, the default expression (Sum) is the correct function to use.
  5. Copy the expression from the detail row and paste it into the cell directly above (second row, fourth column) for the StateProvinceName row group.
  6. Add a total for the new column group by right-clicking the OnlineOrderFlag header (first row, fourth column) and selecting Add Total ➤After. Preview your report.
  7. In order to get some familiarity with a more detailed expression, let’s convert the True/False that displays for OnlineOrderFlag to Online or In-Store, based on the value. In design mode, right-click the OnlineOrderFlag header (first row, fourth column) and select Expression.
  8. We will modify the expression to use the Immediate If (IIf) function to do the conversion between the values True/False and Online/In-Store. The final function looks like this:
    =IIf(Fields!OnlineOrderFlag.Value, "Online", "In-Store")
  9. To provide some clarity, let’s add some static headings within the Tablix region. Right-click the first row and select Insert Row ➤ Outside Group - Above. The Tablix region allows for cells to be merged. In the new row (first row), select cells two and three, right-click, and select Merge Cells. Do the same for the last two cells. Enter Sales Quantity as the first heading and Online Sales vs. In-Store Sales as the second heading. Columns may need to be resized at this point to fit the heading text.
  10. Before previewing, let’s change the formatting of the TotalDue cells to currency. For each of the cells that uses the expression [Sum(TotalDue)], change the Number section of the text box properties to have a category of Currency, to use the 1000 separator, and to show zero as -.
  11. Update the formatting of the new columns to match that of the Sales Quantity section with regard to coloring, and then preview the report.

    Your report should look similar to that shown in Figure.

    Sales report showing sales quantities and online sales volume

    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.

  1. Use Report Designer to open the sales report that we created in the previous exercise (alternatively, open the report named DataRegions – Step2 – Parallel Dynamic Groups.rdl that you downloaded).
  2. It is possible to insert a gauge within a cell in aMatrix data region. In this example, we’ll add a gauge to the Sales Quantity Total column to visually indicate which state has the greatest sales. Resize the StateProvinceName row (second row) group to about four times its current height.
  3. Right-click the Sales Quantity Total column (second row, third column) and select Insert Column ➤Right. This adds a new column within the Total section of the report. Merge the Sales Quantity heading (row 1, column 2) with the new column. Merge the Total heading for Sales Quantity (row 2, column 3) with the new column.
  4. From the Insert menu, or Toolbox if you’re using BIDS, select Gauge. The Select Gauge Type dialog box appears, as shown in Figure. Select the Radial Meter gauge type and click OK. Cut and paste the Gauge data region from the design surface into the new StateProvinceName Total cell (row 3, column 4). At this point, you should be comfortable enough with Report Designer to be able to resize the cells to get a professional-looking gauge.

    Gauge types

    Gauge types

  5. Once inserted into the proper cell, drag and drop the SalesOrderID data value onto the RadialPointer1 data value, which will become visible when you drag the SalesOrderID over the gauge. Note that the expression will default to [Sum(SalesOrderID)]. You will need to view the pointer properties and edit the formula to use the Count function instead of the Sum function.

    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

    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

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.

  1. Open the report that you have been building for the data regions examples (alternatively, open the report named DataRegions – Step3 – Gauge Data Region.rdl that you downloaded).
  2. Move the Matrix data region down the report a good distance to make room for a chart between it and the title. Insert a new column chart between the report title and the Tablix data region. Resize the chart to be the same width as the matrix by using the guidelines.
  3. Drag the OrderYear to the Category Fields section of the chart designer. Then edit its sort properties so that it sorts in ascending order. To do this, right-click the field, select Category Group Policies, select the Sorting section of the Category Group Properties dialog box, and then add a new sort on OrderYear.
  4. Add the StateProvinceName field to the Series section of the chart designer.
  5. Add the SalesOrderID field to the Data Fields section of the chart designer. Modify the expression for this field to use the Count function instead of the Sum function. A preview of the report should look similar to Figure.
  6. Sales report with a Chart data region

    Sales report with a Chart data region

  7. Now we want to add order amounts within the same Chart data region. In design mode, add the TotalDue field to the Data Fields section of the chart.
  8. Right-click the new data field and select Change Chart Type to display the Select Chart Type dialog box. Change the chart type to Line.
  9. Right-click the TotalDue series and open the Series Properties dialog box. In the Axes and Chart Area section, set the value axis to Secondary. This will enable the sales quantity value to display on the left axis and the sales amount values to display on the right axis. Preview the report and check that it looks similar to Figure.
  10. Chart with two data series with primary and secondary axes

    Chart with two data series with primary and secondary axes

  11. Because sales in Alabama are substantially lower than those in Arizona, it is very hard to see the SalesAmount line series for Alabama. Enabling scale breaks for the secondary axis will take care of this problem. In design mode, right-click the secondary axis series (the right axis) and select Axis Properties. In the Axis Options section of the Secondary Value Axis Properties dialog box, select Enable Scale Breaks.
  12. Now we just need to add some finishing touches. Set the Chart Title to Sales Quantities and Amounts.
  13. Right-click the legend and set its position to bottom-middle. Figure shows the various legend position choices.
  14. Right-click the legend and set its position to bottom-middle. Figure shows the various legend position choices.
  15. Setting the legend location to bottom-middle

    Setting the legend location to bottom-middle

  16. Set the left-axis title to Sales Quantity.
  17. Set the right-axis title to Sales Amount.
  18. Open the properties for the TotalDue series. In the Border section, set the line width to 2pt.
  19. Set the horizontal axis title to Sales Year.

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

Final report containing Chart, Gauge, and Tablix data regions


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics