08-Horizontal Table Report

Paginated Reports eBook Introduction, Resource and Sample Data Downloads

Displaying data rows utilizing the horizontal space available first, before adding a vertical row can help in creating compact list representations of data.

Although there is no native “horizontal table” report item in Reporting Services, a horizontal table can be simulated with a matrix. This technique uses nesting of data regions and grouping.

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: 300

Contributed by Robert Bruckner

Report Summary

The report design uses Report Builder, based on a grouped query to return a product category, subcategory, and an aggregated business measure. Figure 1 shows the finished report.

A screenshot of a cell phone Description automatically generated

Figure 1. An example of a horizontal table report
[Horizontal Table – 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

DimProductCategory.EnglishProductCategoryName AS Category

, DimProductSubcategory.EnglishProductSubcategoryName AS Subcategory

, SUM(FactResellerSales.OrderQuantity) AS Qty

FROM

DimProduct INNER JOIN DimProductSubcategory

ON DimProduct.ProductSubcategoryKey

= DimProductSubcategory.ProductSubcategoryKey

INNER JOIN DimProductCategory

ON DimProductSubcategory.ProductCategoryKey

= DimProductCategory.ProductCategoryKey

INNER JOIN FactResellerSales

ON DimProduct.ProductKey = FactResellerSales.ProductKey

GROUP BY

DimProductCategory.EnglishProductCategoryName,

DimProductSubcategory.EnglishProductSubcategoryName

ORDER BY

Category, Subcategory

Designing the report

Add a new matrix report item to represent the fixed layout for each set of data to repeat (in this example, product information). Figure 2 shows a new empty matrix on the report design surface.

A screenshot of a cell phone Description automatically generated

Figure 2. Matrix report item, before defining grouping and binding to data
[Horizontal Table – New Matrix.png]

Right-click into the Rows header, and in the context menu, select Row Group -> Delete Group, and then chose the option to “Delete group and related rows and columns”, as shown in Figure 3. Thereby, the row header and the dynamic group are deleted, and this matrix will only repeat its, now static, rows horizontally.

A screenshot of a cell phone Description automatically generated

Figure 3. Delete the matrix row group to achieve the horizontal layout
[Horizontal Table – Delete Row Group.png]

Prepare the horizontally repeating record layout, by using the field selector in Report Builder as shown in Figure 4, to select Subcategory for the Columns header textbox and Category for the textbox beneath it.

A screenshot of a cell phone Description automatically generated

Figure 4. Use the field selector to quickly select fields for the matrix column grouping
[Horizontal Table – Column Group Field Selection.png]

Click on the row header left of the Category textbox and select Insert Row, to add a third (static) layout row that is used to display the Qty field, as shown in Figure 5.

Figure 5. Insert an additional (static) row to display the Qty field
[Horizontal Table – Add Static Row.png]

At this stage, you have a matrix (really a table) that renders horizontally. Since horizontal tables can end up rather wide, let’s make your table wrap around to the next “line” after a specific number of columns. In this example, we want it to wrap to the next line after 5 columns. The following steps explain how to achieve this.

Into an empty area in your report design surface, add a new List report item. Then drag the matrix (table) from step 6 inside the list body.

Select the List report item, select its list detail group header, and open the group properties, as shown in Figure 6.

Figure 6. Add a list grouping expression that increments by one after every five rows, and thereby creates a new horizontal “line”
[Horizontal Table – Add List Grouping.png]

In the group dialog, add a new group expression to group on every five rows. So you’ll get a separate table for every five rows. The divisor (in this case, five) determines how many records are shown next to each other horizontally before a row break occurs.

=Ceiling(RowNumber(Nothing)/5)

Adjust the matrix column group expression from Step 6 due to the matrix now being embedded inside the list. Edit the column group expression in your matrix and change it to

=RowNumber(“Details”)

Figure 7 shows the final report layout with additional textbox formatting and styling applied.

Figure 7. Horizontal table embedded within a List
[Horizontal Table – Table embedded within List.png]

Figure 8 shows the final report layout with 5 records per row (as defined in step 10), before the horizontal table wraps around to a new line.

Figure 8. Preview of report, rendering as horizontal table, wrapping around every 5 records
[Horizontal Table – Report Preview.png]

Final Thoughts

Nesting of data regions in Reporting Services is a powerful technique, as demonstrated in this recipe to achieve the horizontal table effect.

Credits and Related References

A similar technique is demonstrated on Chris Hays’s blog titled “Horizontal Tables” that may be found at https://docs.microsoft.com/en-us/archive/blogs/chrishays/horizontal-tables

%d bloggers like this: