How do I format a row within subtotal on matrix?

I have a dataset that I need to report as a matrix. Within the dataset, I pull together the individual totals for CDNs within a week, along with the overall total for all CDNs within the week. I do this to make it easy to calculate the weekly percentages开发者_开发问答 within the report.

I am using Visual Studio 2005 to build the report. You can see the matrix definition on Sheet2.

Everything works out fine until i get to the bottom Total. As you can see on Sheet3, the total Offered is correct, but the % to Total is not correct. In fact, i do not even need this row visible on the Total section. How can i hide this row?

---

Here are two possible ways to hide the rows or row values in sub-totals section of SSRS reports. The examples shown below use SSRS 2005.

A. First option is to set the Initial Visibility Expression on the cells to hide the rows. Totals label cell will span to two rows, which might not look good. Refer screenshot #1.

B. Second option is to display the data in the cells based on the scope. This will not hide the row but won't show any data. Refer screenshot #7.

Following sections explain how each of the above options can be achieved.

Using Option A:

1. Right-click on the cell that has Phonenumber and CDN source expression and select Edit Group... as shown in screenshot #2.

2. Make note of the Name. Here, this example uses the name PhoneNumber. Click OK or Cancel. Refer screemshot #3.

3. Right-click on the cell containing the text % to Total and select Properties as shown in screenshot #4.

4. On the Textbox Properties dialog, click on the Visibility tab and select Expression radio button. In the Expression, enter the value =Not(InScope("PhoneNumber")). Refer screenshot #5.

5. Right-click on the cell containing the expression to calculate the % to Totals value and select Properties as shown in screenshot #6. Repeat the step 6 for this cell as well.

Using Option B:

6. Perform steps 1 & 2 mentioned in previous option A.

7. Right-click on the cell containing the text % to Total and select Expression as shown in screenshot #8.

8. Change the expression to =IIf(InScope("PhoneNumber"), "% to Total", Nothing)

9. Right-click on the cell containing the expression to calculate the % to Totals value and select Expression as shown in screenshot #9.

10. Change the expression to =IIf(InScope("PhoneNumber"), Round((IIf(Sum(Fields!Offered.Value) = 0 Or Sum(Fields!WeekTotal.Value) = 0, 0, Sum(Fields!Offered.Value)/Sum(Fields!WeekTotal.Value))) * 100, 1).ToString() + " %", Nothing)

11. Make sure that you change the expressions according to your requirements.

Output of option A can be seen in screenshot #1 and output of option B can be seen in screenshot #7.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

---

I was able to figure out another way to do this. Because of how my data is set up, the WkTotOff.Value in the SubTotal section is always going to be a factor of the number of CDN_Values there are. Therefore, i could place =iif(Sum(Fields!WkTotOff.Value)/CountDistinct(Fields!CDN_Val.Value) = Sum(Fields!Offered.Value), "Silver", "White") in the Background Color in the properties of the row i want to hide (in the detail section). Since the individual detail level will never meet this criteria, these will remain visible.

This seems a bit sloppy but was all i could figure out. I will try your methods as well.

PK