Divide Sum of 2 Different Groups

Hi,
I hope I'm making this harder than it is but I am trying to divide the sum
of two different groups to obtain a percentage.
__________________________________________
Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
__________________________________________
Group #2 "grpResID" =Sum(Fields!amt_3.Value)
I need to divide the sum of amt_3 for group ResIDAmt by the sum of amt_3 for
group grpLevel2 and multiply it by 100.
I have tried every way I can think of to do this with varying errors.....
Any help would be greatly appreciated.Bill, if you are trying to find the proportion of the total, then it is
harder than you think, but not insurmountable.
You have probably tried something like;
=Sum(Fields!amt_3.Value, "grpResID") / Sum(Fields!amt_3.Value,
"grpLevel2") * 100
I believe the reason this doesn't work is that, unlike Crystal, RS
doesn't have an extra pass after all totals have been calculated in
order to keep the performance up.
So the answer is to have the group 1 totals on the individual data
lines. The query would look something like this;
Select
Fld1,
Fld2,
amt_3,
Total = (Select Sum(amt_3) From MyTable B
Where A.Fld1 = B.Fld1)
From MyTable A
Then in the field you can do;
=Sum(Fields!amt_3.Value) / First(Fields!Total.Value) * 100
Note the use of FIRST, also if you use percent format code 'p' in the
cell, you don't need to multiply by 100, it will do it for you.
You will also need to code for 'divide by zero'.
Regards
Chris
BillD wrote:
> Hi,
> I hope I'm making this harder than it is but I am trying to divide
> the sum of two different groups to obtain a percentage.
> __________________________________________
> Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
> __________________________________________
> Group #2 "grpResID" =Sum(Fields!amt_3.Value)
> I need to divide the sum of amt_3 for group ResIDAmt by the sum of
> amt_3 for group grpLevel2 and multiply it by 100.
> I have tried every way I can think of to do this with varying
> errors.....
> Any help would be greatly appreciated.|||Chris,
Thank You for taking the time to reply. Finally got it to work without
drinking.
"Chris McGuigan" wrote:
> Bill, if you are trying to find the proportion of the total, then it is
> harder than you think, but not insurmountable.
>
> You have probably tried something like;
> =Sum(Fields!amt_3.Value, "grpResID") / Sum(Fields!amt_3.Value,
> "grpLevel2") * 100
> I believe the reason this doesn't work is that, unlike Crystal, RS
> doesn't have an extra pass after all totals have been calculated in
> order to keep the performance up.
> So the answer is to have the group 1 totals on the individual data
> lines. The query would look something like this;
> Select
> Fld1,
> Fld2,
> amt_3,
> Total = (Select Sum(amt_3) From MyTable B
> Where A.Fld1 = B.Fld1)
> From MyTable A
> Then in the field you can do;
> =Sum(Fields!amt_3.Value) / First(Fields!Total.Value) * 100
> Note the use of FIRST, also if you use percent format code 'p' in the
> cell, you don't need to multiply by 100, it will do it for you.
> You will also need to code for 'divide by zero'.
> Regards
> Chris
>
> BillD wrote:
> > Hi,
> >
> > I hope I'm making this harder than it is but I am trying to divide
> > the sum of two different groups to obtain a percentage.
> > __________________________________________
> > Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
> > __________________________________________
> > Group #2 "grpResID" =Sum(Fields!amt_3.Value)
> >
> > I need to divide the sum of amt_3 for group ResIDAmt by the sum of
> > amt_3 for group grpLevel2 and multiply it by 100.
> >
> > I have tried every way I can think of to do this with varying
> > errors.....
> >
> > Any help would be greatly appreciated.
>|||I dunno about you, but my more creative solutions often come after a
pint or two!
Chris
BillD wrote:
> Chris,
> Thank You for taking the time to reply. Finally got it to work
> without drinking.
>
> "Chris McGuigan" wrote:
> > Bill, if you are trying to find the proportion of the total, then
> > it is harder than you think, but not insurmountable.
> >
> >
> > You have probably tried something like;
> > =Sum(Fields!amt_3.Value, "grpResID") / Sum(Fields!amt_3.Value,
> > "grpLevel2") * 100
> >
> > I believe the reason this doesn't work is that, unlike Crystal, RS
> > doesn't have an extra pass after all totals have been calculated in
> > order to keep the performance up.
> >
> > So the answer is to have the group 1 totals on the individual data
> > lines. The query would look something like this;
> >
> > Select
> > Fld1,
> > Fld2,
> > amt_3,
> > Total = (Select Sum(amt_3) From MyTable B
> > Where A.Fld1 = B.Fld1)
> > From MyTable A
> >
> > Then in the field you can do;
> > =Sum(Fields!amt_3.Value) / First(Fields!Total.Value) * 100
> >
> > Note the use of FIRST, also if you use percent format code 'p' in
> > the cell, you don't need to multiply by 100, it will do it for you.
> >
> > You will also need to code for 'divide by zero'.
> >
> > Regards
> > Chris
> >
> >
> > BillD wrote:
> >
> > > Hi,
> > >
> > > I hope I'm making this harder than it is but I am trying to divide
> > > the sum of two different groups to obtain a percentage.
> > > __________________________________________
> > > Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
> > > __________________________________________
> > > Group #2 "grpResID" =Sum(Fields!amt_3.Value)
> > >
> > > I need to divide the sum of amt_3 for group ResIDAmt by the sum of
> > > amt_3 for group grpLevel2 and multiply it by 100.
> > >
> > > I have tried every way I can think of to do this with varying
> > > errors.....
> > >
> > > Any help would be greatly appreciated.
> >
> >