Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

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

  • 5 January 2010
  • Author: ashishnaik1
  • Number of views: 6571
  • 0 Comments

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

Add New Item

From the tool box, select Matrix report

Select Matrix Report

 

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

Row Group

 

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

Col Grp

 

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

Properties

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)

Extra Cols

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

Total in all

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;

Final report

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

Print
Categories: Reporting Services
Tags:
Rate this article:
No rating

ashishnaik1ashishnaik1

Other posts by ashishnaik1

Please login or register to post comments.