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 subtotalit 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)
