Rate calculation with matrix reports, How?

I have a report like this, and I would need to implement drilldown on
both column group and row group, and i am running into problems:
2003
Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
Total_Rate
West 10 10% 20 20% 50 50% 20 20% | 100
100%
East 20 10% 30 15% 20 10% 130 65% | 200
100%
North 20 20% 20 20% 40 40% 20 20% | 100
100%
South 30 30% 20 20% 10 10% 20 20% | 100
100%
----
Total 80 16% 90 18% 120 24% 190 38% 500
100%
I can do sum on the numbers, but the rate calculation is difficult.
Does anyone know how to do this?You can use the scope argument to aggregate functions to define a scope over
which to calculate a total.
For a percent-of-total calculation like you describe, you would want
something like this:
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
where "matrix1_Region" is the name of your row group
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407261518.4e5582b6@.posting.google.com...
> I have a report like this, and I would need to implement drilldown on
> both column group and row group, and i am running into problems:
> 2003
> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> Total_Rate
> West 10 10% 20 20% 50 50% 20 20% | 100
> 100%
> East 20 10% 30 15% 20 10% 130 65% | 200
> 100%
> North 20 20% 20 20% 40 40% 20 20% | 100
> 100%
> South 30 30% 20 20% 10 10% 20 20% | 100
> 100%
> ----
> Total 80 16% 90 18% 120 24% 190 38% 500
> 100%
> I can do sum on the numbers, but the rate calculation is difficult.
> Does anyone know how to do this?|||Where exactly within the matrix would you put the
calculation? Thanks.
>--Original Message--
>You can use the scope argument to aggregate functions to
define a scope over
>which to calculate a total.
>For a percent-of-total calculation like you describe,
you would want
>something like this:
>=Sum(Fields!Sales.Value)/Sum(Fields!
Sales.Value,"matrix1_Region")
>where "matrix1_Region" is the name of your row group
>--
>This post is provided 'AS IS' with no warranties, and
confers no rights. All
>rights reserved. Some assembly required. Batteries not
included. Your
>mileage may vary. Objects in mirror may be closer than
they appear. No user
>serviceable parts inside. Opening cover voids warranty.
Keep out of reach of
>children under 3.
>"Nick" <deadlocklegend@.gmail.com> wrote in message
>news:313b74d.0407261518.4e5582b6@.posting.google.com...
>> I have a report like this, and I would need to
implement drilldown on
>> both column group and row group, and i am running into
problems:
>> 2003
>> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
Q4_Rate | Total
>> Total_Rate
>> West 10 10% 20 20% 50 50% 20
20% | 100
>> 100%
>> East 20 10% 30 15% 20 10% 130
65% | 200
>> 100%
>> North 20 20% 20 20% 40 40% 20
20% | 100
>> 100%
>> South 30 30% 20 20% 10 10% 20
20% | 100
>> 100%
>> ----
--
>> Total 80 16% 90 18% 120 24% 190
38% 500
>> 100%
>> I can do sum on the numbers, but the rate calculation
is difficult.
>> Does anyone know how to do this?
>
>.
>|||As the value of the second data cell.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Terry" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c47417$66c96140$a301280a@.phx.gbl...
> Where exactly within the matrix would you put the
> calculation? Thanks.
>
> >--Original Message--
> >You can use the scope argument to aggregate functions to
> define a scope over
> >which to calculate a total.
> >For a percent-of-total calculation like you describe,
> you would want
> >something like this:
> >
> >=Sum(Fields!Sales.Value)/Sum(Fields!
> Sales.Value,"matrix1_Region")
> >where "matrix1_Region" is the name of your row group
> >
> >--
> >This post is provided 'AS IS' with no warranties, and
> confers no rights. All
> >rights reserved. Some assembly required. Batteries not
> included. Your
> >mileage may vary. Objects in mirror may be closer than
> they appear. No user
> >serviceable parts inside. Opening cover voids warranty.
> Keep out of reach of
> >children under 3.
> >"Nick" <deadlocklegend@.gmail.com> wrote in message
> >news:313b74d.0407261518.4e5582b6@.posting.google.com...
> >> I have a report like this, and I would need to
> implement drilldown on
> >> both column group and row group, and i am running into
> problems:
> >>
> >> 2003
> >> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
> Q4_Rate | Total
> >> Total_Rate
> >> West 10 10% 20 20% 50 50% 20
> 20% | 100
> >> 100%
> >> East 20 10% 30 15% 20 10% 130
> 65% | 200
> >> 100%
> >> North 20 20% 20 20% 40 40% 20
> 20% | 100
> >> 100%
> >> South 30 30% 20 20% 10 10% 20
> 20% | 100
> >> 100%
> >> ----
> --
> >> Total 80 16% 90 18% 120 24% 190
> 38% 500
> >> 100%
> >>
> >> I can do sum on the numbers, but the rate calculation
> is difficult.
> >> Does anyone know how to do this?
> >
> >
> >.
> >|||Thanks much. That worked.
But the requirement has changed slightly because sometimes the Quarter
numbers don't add up to the "supposed" yearly number for several
reasons. Instead of dividing by the sum of all quarter numbers, I
need to get from proc a yearly number and calculate the percentage by
the yearly number and then tally up. I am running into problems
because when I use sum(Q_No)/First(Year_No) the rates work right, but
the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
right but the individual rates are wrong.
Any help very much appreciated.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> You can use the scope argument to aggregate functions to define a scope over
> which to calculate a total.
> For a percent-of-total calculation like you describe, you would want
> something like this:
> =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> where "matrix1_Region" is the name of your row group
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > I have a report like this, and I would need to implement drilldown on
> > both column group and row group, and i am running into problems:
> >
> > 2003
> > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > Total_Rate
> > West 10 10% 20 20% 50 50% 20 20% | 100
> > 100%
> > East 20 10% 30 15% 20 10% 130 65% | 200
> > 100%
> > North 20 20% 20 20% 40 40% 20 20% | 100
> > 100%
> > South 30 30% 20 20% 10 10% 20 20% | 100
> > 100%
> > ----
> > Total 80 16% 90 18% 120 24% 190 38% 500
> > 100%
> >
> > I can do sum on the numbers, but the rate calculation is difficult.
> > Does anyone know how to do this?|||If you need a different calculation in the total cells than in the detail
cells, that's where the InScope function comes in.
You can do something like this:
=iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
In your case, it would be:
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407271256.179a0f98@.posting.google.com...
> Thanks much. That worked.
> But the requirement has changed slightly because sometimes the Quarter
> numbers don't add up to the "supposed" yearly number for several
> reasons. Instead of dividing by the sum of all quarter numbers, I
> need to get from proc a yearly number and calculate the percentage by
> the yearly number and then tally up. I am running into problems
> because when I use sum(Q_No)/First(Year_No) the rates work right, but
> the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> right but the individual rates are wrong.
> Any help very much appreciated.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > You can use the scope argument to aggregate functions to define a scope
over
> > which to calculate a total.
> > For a percent-of-total calculation like you describe, you would want
> > something like this:
> >
> > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > where "matrix1_Region" is the name of your row group
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > I have a report like this, and I would need to implement drilldown on
> > > both column group and row group, and i am running into problems:
> > >
> > > 2003
> > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > Total_Rate
> > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > 100%
> > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > 100%
> > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > 100%
> > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > 100%
> > > ----
> > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > 100%
> > >
> > > I can do sum on the numbers, but the rate calculation is difficult.
> > > Does anyone know how to do this?|||thanks Chris.
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
didn't work, but this did,
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
However, there is a problem when the year was collapsed, the sum of
yearly number gets multiplied by the number of Q, which is 4
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value) *
CountDistinct(Fields!Q_No.Value))
Thanks a lot for your help.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<OLHBCbCdEHA.592@.TK2MSFTNGP11.phx.gbl>...
> If you need a different calculation in the total cells than in the detail
> cells, that's where the InScope function comes in.
> You can do something like this:
> =iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
> In your case, it would be:
> =iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
> Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407271256.179a0f98@.posting.google.com...
> > Thanks much. That worked.
> >
> > But the requirement has changed slightly because sometimes the Quarter
> > numbers don't add up to the "supposed" yearly number for several
> > reasons. Instead of dividing by the sum of all quarter numbers, I
> > need to get from proc a yearly number and calculate the percentage by
> > the yearly number and then tally up. I am running into problems
> > because when I use sum(Q_No)/First(Year_No) the rates work right, but
> > the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> > right but the individual rates are wrong.
> >
> > Any help very much appreciated.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > > You can use the scope argument to aggregate functions to define a scope
> over
> > > which to calculate a total.
> > > For a percent-of-total calculation like you describe, you would want
> > > something like this:
> > >
> > > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > > where "matrix1_Region" is the name of your row group
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach of
> > > children under 3.
> > > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > > I have a report like this, and I would need to implement drilldown on
> > > > both column group and row group, and i am running into problems:
> > > >
> > > > 2003
> > > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > > Total_Rate
> > > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > > 100%
> > > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > > 100%
> > > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > > 100%
> > > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > > 100%
> > > > ----
> > > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > > 100%
> > > >
> > > > I can do sum on the numbers, but the rate calculation is difficult.
> > > > Does anyone know how to do this?