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