Problem solve Get help with specific problems with your technologies, process and projects.

Matrix configuration techniques for Report Builder 3.0

There is more to setting up a matrix table than the basics. Here are some advanced techniques for tweaking your tables to best display your data.

Part 1 | Part 2 | Part 3

So you've added a matrix table to you report – you're not done yet. Part three offers some tips for configuring matrixes using Report Builder 3.0 to ensure that your data is displayed the way you want it to appear.

Now that you've added groups to your matrix, you're ready to resize the cells and configure the properties. You can make these changes at any time, but I find it more efficient to wait until the matrix contains all of the data. Of course, you can add and delete groups as necessary, but this at least provides you with a way to get started.

What else can you do with Report Builder 3.0?

Check out Robert Sheldon's three previous series on adding chartsgauges and maps to SSRS reports.

To resize the cells, select the matrix so the thick gray borders appear on the top and left of the screen (refer to Figure 6). Move your cursor to the appropriate line on the border and then resize the cells as necessary. You should experiment with sizing by modifying the cells and then previewing the report. That way you can be certain to display the data exactly as you want it.

Now let's look at how to reconfigure the properties. For this example, we'll take it one row at a time. The first row displays the column titles. Depending on the order you added the column and row groups, the titles might not always be displayed in the first row. For this report, however, that's what we want to do. Your column titles should correspond to the data. So the first column is "Order Year", the second is "Order Quarter", and the third is "Order Month". The fourth column is actually an expression -- [SalesGroup]. This means that the name of the sales group will be inserted as the heading. The fifth column should be "Total".

Next, select all the cells in the first row, and then click the Center and Bold buttons on the Home menu. Now, with the cells still selected, go to the Properties pane at the right of the screen and set the BackgroundColor property. You can choose whatever color you like. I chose a yellow-green color with the code f7fccf. Your first row should now look similar to the one shown in Figure 8. (Note that Figure 8 shows what the matrix will look like after you make all the formatting changes. You can use the figure as a guide while you work through the rest of the article.)

Figure 8: Configuring matrix properties to improve the report's appearance (click to enlarge)
Configuring matrix properties to improve the report's appearance

The second row in your matrix should include a label in only one cell -- the [SalesCountry] cell. As with the first row, center the text and set it to bold. If there are labels in any other cells, you can delete those.

In the third row, select all the cells except [OrderYear]. Next, in the Properties pane, click the down-arrow associated with the BackgroundColor property and select Expression. Type the following expression in the Expression dialog box:

 

=Iif(Fields!OrderQuarter.Value = 1 Or Fields!OrderQuarter.Value = 3, "Cornsilk", "Ivory")

The expression uses the Iif function to set the color of the rows. If the value of the OrderQuarter column is 1 or 3, the color is set to Cornsilk. Otherwise, the color is set to Ivory. As a result, the row colors for even quarters will differ from odd quarters. This makes the report far easier to read. Another change I made was to center the text in the [OrderQuarter] cell.

In the fourth row, select all the cells except [OrderYear] and set the type to italics. Then right-click the [OrderQuarter] cell and select Expression. In the Expression dialog box, type the following:

=Fields!OrderYear.Value & " Total"

The expression changes the text box's Value property so the year is included with the word "Total". After you add the expression, center the text.

For the fifth row, simply select all the cells and set the text to bold. Also set all the cells in the Total column to bold.

You should now configure the data values to display as currency. For each cell that contains a dollar amount, right-click the cell and select Test Box Properties. On the Number page, select Currency as the category followed by the Use 1000 separator option.

If you don't want to configure the numerical cells individually, you can configure them as a group (if you know the correct formula, that is). First, select all the numerical cells. In the Properties pane, click the down-arrow associated with the Format property and select Expression. In the Expression dialog box, enter the following:

 

'$'#,0.00;('$'#,0.00)

The expression specifies that the value be displayed as currency, with two decimals and a comma used to separate the digits.

Another change you should make is to set how the month names are sorted. In the Row Groups pane at the bottom of the design surface, click the down-arrow next to the OrderMonth group, choose Group Properties, and select the Sorting page. In the Sort by drop-down list, select the MonthNumber column. That way, the months will be sorted by number, rather than name.

The only other formatting changes I made were to add a title of "AdventureWorks Sales" and modify the footer so it uses the following expression:

 

[&ReportName] report: [&ExecutionTime]

This way, whenever the report is rendered, the report name is displayed along with the execution time. Now, when you preview your report, it should look similar to the one shown in Figure 9.

Figure 9: Previewing a matrix in a Report Builder 3.0 report (click to enlarge)
Previewing a matrix in a Report Builder 3.0 report

The figure displays only part of the results. As you can see, the rows are now color-coded according to quarter, the aggregated sales amounts are in a currency format, the annual totals are italicized, and the data in the Total column is bold. You can, of course, make other formatting changes, just like you can add or subtract groups.

The matrix provides a powerful tool for displaying aggregated data in a way that can be easily customized and effectively rendered. While this has just been a basic overview, the more you work with the matrix, the more valuable it will become.

 


THE MATRIX IN REPORT BUILDER 3.0


 Part 1: Enter the matrix
 Part 2: Creating matrix reports
 Part 3: Matrix configuration techniques

 

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

This was last published in March 2010

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close