Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-02
2
Medium Priority
?
2,108 Views
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!
0
Comment
Question by:Laura2112
2 Comments
 
LVL 14

Accepted Solution

by:
Emes earned 2000 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  ))))  )
0
 

Author Closing Comment

by:Laura2112
ID: 33618901
thanks so much!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 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