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

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

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

#Error in total fields

I am getting a #error in my subtotal and total fields,
I have data in a field (on a table) as follows which displays 0.00 if there is a zero in the divide by field, otherwise it performs the divide and gives me a percentage difference from the two fields.
This works fine

=iif(Fields!REPCYINC.Value=0,0,(Fields!REPCYINC.Value) / iif(Fields!REPCYPROD.Value=0,1,Fields!REPCYPROD.Value)) * 100

Yet when i try to get this in a total field by doing an AVG it fails, and gives #error, please help, no matter how many iifs a wrap aroun dit it doesnt help.

This is the statement that #error's

=round(avg(iif(Fields!REPCYINC.Value=0,0,(Fields!REPCYINC.Value) / iif(Fields!REPCYPROD.Value=0,1,Fields!REPCYPROD.Value)) * 100) ,2)

Brian Weckler's blog has a posting related to this.

http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

cheers,

Andrew

|||

Andy,

I had a similar issue recently when I used this statement:

Format(Sum(IIf(Fields!Product.Value="HEALTH",Fields!ID.Value,0)),"C")

Report Service kept giving me the #error, I located the detail error message and then I change my statement to this

Format(Sum(IIf(Fields!Product.Value="HEALTH",Fields!ID.Value,nothing)),"C") and everything worked okay. I remember the error message about mixing different types or something.

I hope this works for you.

Ham

|||

Unfortunately this post just explains what i have already posted, the issue is not the divide by zero, I have iif's to work around that and my inline divide give 0 result not #error,

I experience the #error in the group totals when i sum (As described in my original post)

If the main iif works and gives 0 then why not the sum?

These things should be straightforward!!

|||

That doesnt help, the inline iif *, 0, 0 works fine, I did try changing it to nothing although i still get the same issue, the group totals when i do a sum doesnt work, it #error's!!!

|||

sorted, regardless of how many iif's you throw around the numbers within the SSRS you MUST format the field as number otherwise #error occurs, seems like Microsft still have some work to do here.....

At LEAST make some kind of error handling reporting so we know where the error is coming from, it could be anything....