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.
Previous Page
