Showing Total in some Cells (and hinding in some) in Matrix reports

In matrix report when you are showing percentages in some columns, and then when you add subtotal, it will show total of percentages as well. This is not correct as simple addition of percentage value will give wrong results.

 I was working on one report where I need to show premiums and ratios in matrix report. Users want to group data at 2 levels (group and sub-group) and wants total for each sub group and Grand Total by group.

After "Googling" many websites, I found workable solution. I hope this will help many others.

Here is the step by step guide. ( I assuime you have already created a DataSet for the report).

Add new report to the project

From the tool box, select Matrix report

Select first row and add a "Row Group" and name it as PolicyType (for this example)

Select first row and right click on it and select "Add Column Group"

In Grouping and Sorting properties of this column group, set name as DCol and in Expression, put ''''

Then click "OK"

After this, add fields you need in the matrix report.

Delete one Column Group (the one that was default when you drag matrix report into design pane)

Select sub group (2nd row group) and then right click on it and select sub total

Select Major group (1st row group), right click on it and select sub total.

If you click on preview, the result will be like below

As you can see above, there is addition of totals in percentage (Loss Ratio) column.

So here is the trick!

Select the text box whose total you do not want to show (in our example, Loss Ratio). Go to Color Property of that text box and select Expression.

Write following code in expression

=iif(InScope("DCol"),iif(InScope("PolicyType"),"Black","White"),iif(inScope("PolicyType"),"Green","White"))

Here DCol is the Colum Type group and PolicyType is the n2d row grouping.

Setting this property will display white on white back ground so it is invisible (hide from the user). Now when you will click on preview, you will see following;

There you go now! Totals are not visible in percentage section.