% of Total

I have a matrix report built on a database not on the cube.

2006

Q1

Volume MS %

Sub Channel Product

X X1 10

X2 20

Subtotal 30

Y Y1 20

Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

Why dont you make 1 more column in your query for the subtotal

it will be like

select subchannel, product, year, quarter, volume,
( select sum(volume)
from table t2
where t2.subchannel=t1.subchannel and t2.year=t1.year and t2.quarter=t1.quarter
group by t2.subchannel, t2.year, t2.quarter
) as subtotal
from table t1

After getting this column, you can use the inscope function for changing the field in the details column in the matrix.

|||

Can you please elaborate how to use the InScope on the details column, please

|||

hi,

i guess you have 2 groups in your table, 1 for subchannel and 1 for product

where subchannel has a footer where the subtotal is calculated

just name the field where "30" stand SubTotal

and the field where "10" stands Volume

and add a new field next to the "10"-field with the forumlar =ReportItems!Volume.value/ReportItems!SubTotal.value

should work, otherwise calculate it in your query in a new column:

SELECT subchannel, product, volume,

(SELECT Sum(Volume)

FROM TableXY

WHERE SubChannel=A.SubChannel) AS SubTotal

FROM TableXY AS A

i would solve it with the query ...

greets

gerhard

|||

Now you have the field subtotal in your dataset.

lets say the group name for the group year is "group_year".

Then instead of using the expression Fields!volume in the details column, use this expression

Inscope("group_year",Fields!volume/Fields!subtotal,Fields!volume)