Advanced Matrix Reporting Techniques

Advanced Matrix Reporting Techniques

atrix components are great for visualizing data that you might normally export to Excel, or in a Pivot Table (year-over-year analysis is a good example). But if an analyst wants to view?for example?sales of a Month year-over-year, then some custom sorting has to be added so the months line up as expected. In this article I will cover the question that drives us, Neo. It?s the question that brought you here. You know the question, just as I did. Specifically, how do you get custom aggregates on a matrix report? That, and a few other advanced reporting techniques are covered so you won?t be so quick to grab for that blue pill and escape from working with a matrix component.

I remember the first time I used a Matrix component in SQL Server Reporting Services (SSRS). I right-clicked the detail row, selected Subtotal and was amazed that RS ?knew? that I wanted to sum the values of the rows. Without any need to specify a formula, I had aggregates with a simple mouse click. That amazement wore off soon after I realized just how difficult it might be to generate aggregates based on values other than a standard SUM.

Covered here are six advanced techniques for building more robust Matrix-style reports in SQL Server Reporting Services, including:

  • Dynamic Dataset Creation
  • Query Based Dynamic Grouping
  • Dynamic Column Names
  • Cells in Cells
  • Custom Matrix Aggregates
  • Custom Graph Colors

All of the necessary steps to build a fully functional report are included, which illustrates:

  • Key concepts for dynamic dataset creation
  • Custom matrix aggregates
  • Custom chart coloring
  • Dynamic query-based grouping with workarounds explained with the ?textbox-inside-a rectangle inside-a-textbox? technique.

Technique 1: Dynamic Dataset Creation
Start with a new report based on AdventureWorks that will show sales after some flexible user inputs. For the new dataset named AW_Sales, I?ve joined several tables using the graphical query builder in SSRS. In this example the user is allowed to select a date filter based on any of the following fields: DueDate, OrderDate or ShipDate. I?ve called this input parameter TimeSlicer, and the RDL fragment is shown in Listing 1. In addition to selecting the WHERE clause field for our filter, there are two additional parameters for Month and Year for user selection. Month has static values (1 for January, 2 for February, etc.) but allows for multiple selections. Year is a list of the years 2002, 2003, and 2004.

?
Figure 1. Unformatted SQL Query: This figure shows the SQL Query in all its unformatted glory.

Microsoft’s TechNet cautions on the use of dynamic queries. The salient points are to create your query first without the dynamic components using the query designer in SSRS, then refresh your field list, switch to the Generic Query Designer and modify your SQL as needed. Any carriage returns or formatting created by the query designer must then be removed so the SQL statement appears as one continuous string (see Figure 1). Finally, do not refresh your field list for this dataset! SSRS will not be able to parse your query and you will lose the field definitions created previously. If this happens to you, you?ll either have to manually create the field list again or revert back to your standard query to refresh those fields. You?ve been warned Neo!

The dynamic dataset used is found in Listing 2, but the relevant portion is the WHERE clause:

WHERE Month(" & Parameters!TimeSlicer.Value & ") IN (" & Join(Parameters!Month.Value,",") & ")" & " AND Year(" & Parameters!TimeSlicer.Value & ") IN (" & Parameters!Year.Value & "," & Parameters!Year.Value -1 & ")"

For the TimeSlicer selected by the user (ShipDate for example), investigate the Month and Year. Records are only returned if they are in the multiselect parameter for Month selection and in the Year chosen, or in the year prior. This is a comparison report, remember?

Technique 2: Query Based Dynamic Grouping
In this report, there are two levels of grouping that users can select so the matrix behaves in similar fashion to a Pivot Table in Excel. Users can group their analysis by Color then Size, or SalesPerson and State, vice versa, or by any combination of fields you allow. To provide this functionality you can simply create a list of field values in a new parameter. For this example however, I?ll illustrate using a query. It?s partly because I am lazy and don?t want to enter the same value list twice and partly to illustrate that the dynamic grouping can be served up from a query or stored procedure. OK, it?s mostly because I?m lazy. Like any developer I?d rather code than copy and paste. Anyway, I?ve used a simple UNION query (Listing 3) to list the fields I want to provide, but you can build on this example by storing the values in a table for a truly scalable solution.

Two parameters are then created and appropriately called Grouper1 and Grouper2 with this dataset (named what else? ?Groupers) as their source for valid values.

On the Layout tab, add a new matrix component and tie it to the AW_Sales dataset. Access the Matrix Properties dialog and you?ll see that a Row Group and Column Group were created by default. Rename the default RowGroup to matrix1_Grouper1, set the Group On Expression to =Fields(Parameters!Grouper1.Value.Value.Then click the Sorting tab and use the same expression.

Create a new RowGroup, called matrix1_Grouper2 and set the Group On Expression and Sorting to =Fields(Parameters!Grouper2.Value.Value.

Technique 3: Dynamic Column Names
For the default ColumnGroup, set the Group On Expression and Sorting to

=Month(Fields(Parameters!TimeSlicer.Value).Value)

Add a new ColumnGroup called matrix1_SLICER with the following values for Group On Expression and Sorting:

=Year(Fields(Parameters!TimeSlicer.Value).Value)
?
Figure 2. A Simple Matrix: The Report Layout tab displays a simple matrix.

Back on the Report Layout tab you?ll see a fairly simple matrix that should look like Figure 2.In the highlighted cell in Figure 2, change the name to GROUPER1. Then enter the expression =Fields(Parameters!Grouper1.Value).Value) so the grouping value is shown. Then right click each cell other than ?Data? and select Subtotal.

Right click the Data cell and select ?Add Column.? Above the cell with the Year() calculation shown, add the following expression: =MonthName(Month(Fields(Parameters!TimeSlicer.Value).Value)), which displays a user friendly name for Month rather than the number.

For the cells directly below the Year() calculation you want to show the quantity sold as well as the amount (LineTotal). For the individual months, totals will suffice, but on the summary columns you want to show the variance. You?ll need to dynamically change the column head so users understand the data displayed beneath. In the cell on the left use the expression:

=IIF((Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1")),"Order Qty","Qty Variance") 

and for the cell on the right use

=IIF((Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1")),"Line Total","Line Variance").

The SSRS function InScope() checks for the relative positioning of the data as it is rendered. You should use standard column names like ?Order Qty? or ?Line Total? unless the summary groups are not in scope, meaning this is an aggregate column being rendered. In this case you?ll display the verbiage ?Variance.?

Technique 4: Cells in Cells
The Cells in Cells technique allows you to display more summary columns than detail. Specifically, there is one column each for quantity and dollar amount sold. But on the aggregate columns you want to show a column for the difference and the percentage respectively. To do this, you?ll add a textbox inside each of the two data level textboxes and conditionally display no data in them.

Select your first data cell, then drop a Rectangle control into it. You?ll notice the background change from solid white to the transparent grid pattern. Next, select a Textbox control and drop this into the rectangle. You have to get it perfect and sometimes it?s a bit annoying when you don?t land exactly on the control. But if you do this correctly, you should have a display similar to Figure 3.

?
Figure 3. Cells in Cells Technique:This figure shows how your display appears when you perform the cells-in-cells technique correctly.

Next, drop in another textbox so it rests directly beside your first one. Repeat this process for the second data cell and it appears as though you have four data cells with which to work. Be aware that when you want to set properties for these cells, your familiar point, click, edit routine leaves you within the context of the Rectangle surrounding your cells. You will need to click twice to access the textboxes within the Rectangle. You may need to play with the sizing a bit depending upon the width of data contained. I named the left textbox in the first rectangle QTY_COL and the left textbox in the second rectangle LINETOT_COL so I can access their values later for percentage calculation.

Technique 5: Custom Matrix Aggregates
From left to right each of the cells should have the following expressions listed in order in Listing 4. Using the InScope() function you conditionally display either a simple sum or a custom calculation. In the first textbox you?ll notice the custom Sum that includes a conditional IIF() statement.

 Sum(IIF(Year(Fields(Parameters!TimeSlicer.Value).Value)=Parameters!Year.Value, CDbl(Fields!OrderQty.Value) ,CDbl(Fields!OrderQty.Value) * -1 )))

Rather than a blind total of all values in the row, you want to compare the Year. If it is the Year selected the value aggregates with a positive value. If however the value is from the set that belongs to the prior year (Year-1), you aggregate with a negative by multiplying by -1. Our end result is a summary that is the net effect of the two figures. Similar logic is used for the variance supplied as a percentage difference in the cells to the right of the field totals.

Since there is no variance to offer for an individual month, you simply supply a double quote ?? blank value when the column is not an aggregate.

To add some visual indication to the report I have added conditional color formatting in the aggregates with

 =IIF((Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1")),"Black" ,IIF(Me.Value< 0,"Red","Green"))

On a standard column the font is black, but you?ll want to display positive movement in green and a decrease in business with red. You can do the same with fonts, styles and shading. After some additional formatting, my matrix looks like Figure 4.


Figure 4. Formatting Your Report: You can format your report by selecting fonts, styles, and shading.
?
Figure 5. Visibility Tab: You need to set the Initial Visibility to hidden and allow the toggle based upon GROUPER1.

As a last touch, go to Matrix properties and select the matrix1_Grouper2 row properties. On the Visibility tab, set Initial Visibility to hidden and allow the toggle based upon Grouper1 (Figure 5).

You?ve just added a little interactivity to the display of the matrix, which is important when real estate on the page is at a premium.

Technique 6: Custom Graph Colors
To add some additional splash, you can add a chart to display the variance of sales year-over-year. Adding this chart requires the same grouping that you used on the matrix component. Add the chart above the matrix and set it to Simple Column with the same dataset as our matrix (AW_Sales).

On the Data tab, create a new Value. Then, on the Values tab use the following expression =Sum(Fields!LineTotal.Value) to display the total dollar amount of sales. Use the same expression on the Point Labels tab in the Data Label field. Be sure to check ?Show Point Labels? and format for currency (C0) so you can see the actual totals on the chart. On the Appearance tab, click the Fill tab. Set the first color to CornSilk (where do they get these color names?) and for the second color (since I am a gradient fiend) use the conditional expression:

 =IIF(Year(Fields(Parameters!TimeSlicer.Value).Value)=Parameters!Year.Value, "Gold", "Blue")
?
Figure 6. Fill Tab: You can use the Fill tab to select distinct colors, which makes the bars much easier to read.

Using the Fill tab provides distinct colors on the report for each year, which makes the bars much easier to differentiate (Figure 6).

Next, create two Category Groups: one for the Month and the second for the Year. The Month has the Group On and Sort Expression =Month(Fields(Parameters!TimeSlicer.Value).Value), though our label wraps that expression in MonthName(), which is more user-friendly.

The Category Group for Year also uses the same expressions you used earlier in the matrix component grouping. The Group On, Sort and Label expression is set to =Year(Fields(Parameters!TimeSlicer.Value).Value).

Final Output
When you Preview the report, the parameters allow for a great deal of flexibility.

Figure 7 shows the various groupings you can select, along with multiple months and a dynamic date criteria.

Figure 8 shows a summarized view of variances only and Figure 9 illustrates what the detail looks like when exposed.

The end result is a report that behaves very much like a Pivot Table in Excel. You can extend this with additional conditional formatting or provide additional value to reports that use Analysis Services as the source for a more cube-like browsing experience in SSRS. Hopefully, this example has armed you with some new skills so you can grab the red pill and tackle those tougher matrix reporting challenges in the future.


Figure 7. Previewing the Report: You can see the various groupings selected.
?
Figure 8. Previewing the Report: You can view a summarized view of variances.
?
Figure 9. Previewing the Report: You can view what the details look like as shown in this figure.

devx-admin

devx-admin

Share the Post:
Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023,

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists