Search code examples
ssrs-2012percentagessas-2012

SSRS 2012 total of a percentage measure

I have a percentage measure calculated in SSAS tabular and its correct. But when i use it in a report i face the following problem (in all percentage measures).

enter image description here

the values under male and female (which are subgroup of saudi and non-saudi) are correct but if you look at the total under "Both sexes" it is incorrect,because it shows summation of male and female percentage, where the right thing is it shows the percentage of the saudi or non-saudi.

If i calculate the measure in SSRS in the expression fields i get it right as follows

enter image description here

But i want to use the measure to show the right values.

So.. can anyone help me with this i have serious issues in later reports and i just cant simply recalculate the SSAS measures in SSRS using expressions.

Note: dont mind the decimal places differences between the two pics.

Thank you.

Solution

  • You can change your MDX query in SSRS to return the subtotal rows then change your Textbox formulas in the body of the Report to say =Aggregate instead of =Sum. This process is described more here by Stacia. You can use manual MDX queries. Don't miss the comment at the bottom:

    Romuald Coutaud March 17, 2011 at 7:52 pm Hi Stacia,

    In fact, it works in query mode too in 2008R2. But, I have to admit that the way that RS and AS work together when you want to use aggregate function is very difficult to perfectly understand and I have struggle very long time to make one sample report running in this way. In query mode you need to write MDX by referencing each level of the hierarchies from the top one to the deepest one want to display in your report, even you don’t need to use all these ones. As an example you have to put Year, Quarter and Month to be sure to display Month and be able to use aggregate function.

    Basically in your report =Aggregate will try to retrieve a row where Nationality is not null and Gender is null. If you get stuck please write back with the list of each field used in each group in SSRS and a screenshot of the MDX query designer.