SSRS MATRIX InScope formula - get percentage of sales in group against total sales in row - HELP!

Posted on 2010-09-02
Last Modified: 2012-08-14
This is my most puzzling issue ever. I have 1 row with 4 nested groups below it. I inserted a total column to the left of the Column Group. The calculations (Sales and Pct) on the Total are correct. The Group has two body cells. Sales and Pct (percent of sales). Sales is the value in the expression. For each group, I want the percentage of sales against the total sales at each row group.
This is giving me an error.
=IIF(INSCOPE ("Matrix1_DivN")    AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_RegN")  AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_AreaN") AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
 IIF (INSCOPE ("Matrix1_Store") AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_StoreN") AND ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  0  ))))  )

Help please! I havce been at this for two days and its due tomorrow!
Question by:Laura2112
LVL 14

Accepted Solution

Emes earned 500 total points
ID: 33618603
=IIF(INSCOPE ("Matrix1_DivN")    AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_RegN")  AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_AreaN") AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
 IIF (INSCOPE ("Matrix1_Store") AND INSCOPE  ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  IIF (INSCOPE ("Matrix1_StoreN") AND INSCOPE ("Matrix1_Brand"),Fields!Sales.Value/SUM(Fields!Sales.Value),
  0  ))))  )

Author Closing Comment

ID: 33618901
thanks so much!

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now