Creating percentage in matrixbox
Hello, There is a matrix box that shows normal sales amount and special sales amounts by customers as follows: Customer Normal Sales Special Sales Total Sales Special Sales % a 1020 45 1065 4% b 130 45 175 26% c 45 100 145 69% I can easly create Normal Sales, Special Sales and total of those two but I cannot calculate the percentage of special sales to the total sales. What should I do? Regards, Cem Karaer
May 20th, 2011 10:48am

Hello, There is a matrix box that shows normal sales amount and special sales amounts by customers as follows: Customer Normal Sales Special Sales Total Sales Special Sales % a 1020 45 1065 4% b 130 45 175 26% c 45 100 145 69% I can easly create Normal Sales, Special Sales and total of those two but I cannot calculate the percentage of special sales to the total sales. What should I do? Regards, Cem Karaer You can try this : Add a column to outside of group and hook this expression into it =(Sum(Fields!SpecialSales.value)/(Sum(Fields!specialsales.value)+Sum(Fields!Normalsales.value)))*100 ThanksRajkumar Yelugu
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2011 11:14am

Thank you for your reply. But there is no field as SpecialSales nor NormalSales because those values are calculated as columns of the matrix box. When the value of the Special/Normal dimension is Special then the special sales amount is calculated and when the dimension's value is Normal then the normal sales amount is calculated. In the design of the matrixbox, the customer dimension was put to the row placeholder, the Special/Normal dimension, which has only two values namely Normal and Special, was put to the column placeholder. Sales_Amount measure was put to the cell placeholder whose expression was set to SUM(Sales_Amount) automatically. Finally a total column was added for the Column group that calculates the sum of the normal sales amount plus the special sales amount. At the right side of the column total, the percentage of the special sales to the total sales should be calculated. Regards.
May 21st, 2011 9:49am

Hi, Add the sub column under COLUMN group adn then select field from query it will work...Regards, Amey
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2011 8:32am

Hi, Add the sub column under COLUMN group adn then select field from query it will work...Regards, Amey
May 23rd, 2011 8:36am

Dear Ameya24 Please DO NOT PROPOSE YOUR REPLY AS ANSWER. What you suggested is not what I asked for.
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2011 12:08pm

Can you produce the necessary fields in your SQL code? that way you could use these fields in your calcs.
May 24th, 2011 12:20pm

Hello, I don't want to use any SQL code because all data come from OLAP cubes.
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2011 12:27pm

Then you should play with this: <Add a column to outside of group and hook this expression into it =(Sum(Fields!SpecialSales.value)/(Sum(Fields!specialsales.value)+Sum(Fields!Normalsales.value)))*100> adding IIFs to get same Special/Normal values and use in the above expression. I would build an additional dataset with the values to run the calcs or something along these lines. I would better base this solution on data than on some very complex IIF statements. Here is a samle of my code. Not exactly but gives some idea: =round(sum(iif(trim(Fields!task.Value)="Sewing" OR trim(Fields!task.Value)="Packing", Fields!totaltime.Value, 0))/ Fields!totalscanned.Value, 2)
May 24th, 2011 12:36pm

Is there any way to use MDX in SSRS reports? Because all I want to do is to slice the cube for Special sales and take the percentage of those sales amounts to the total sales.
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2011 12:50pm

Try Google mdx query ssrs. I personally haven't done nothing in this regard.
May 24th, 2011 12:56pm

Hi Cem, For MDX links please see; - http://www.mdxpert.com/ and http://msdn.microsoft.com/en-us/library/ms145506(v=SQL.100).aspx You could try creating a calculated member for your percentage within the query view of the data set of your report then apply Ameya24's suggestion. Kind Regards, Kieran.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2011 1:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics