Running Total in Crystal vs RS

In RS, Scope tells the Aggregate function when to Re-set. Is there any other
argument to define when to Evaluate the expression. (equivalent of "Evaluate
on Change of Group" in crystal report for a Running Total field)No, but I don't believe that such an argument is needed. Could you describe
what you're trying to do?
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"newmem" <""> wrote in message news:u8ooFefZEHA.2488@.tk2msftngp13.phx.gbl...
> In RS, Scope tells the Aggregate function when to Re-set. Is there any
other
> argument to define when to Evaluate the expression. (equivalent of
"Evaluate
> on Change of Group" in crystal report for a Running Total field)
>
>|||:(
I've a report which has the following groupings:
group 1 : Client Name
group 2: Business Line
group 3: Brand
group 4: RecID
I need to display the total cost in the group3 footer and has expression
Runningvalue(fields!extended_price,SUM,"group3")
where "fields!extended_price" is a formula field -
FindExtPrice(fields!recID,fields!list_price)
When there are more than 1 records in the group3, the total cost is eing
calculated for each of the record in the grop3. Since they both have the
same recID, I need to calculate the total only if the rec ID changes i.e
Evaluate on Change of Group4. I have exactly same rpeort working in crystal
report but can not replicate in RS.
Hope I'm able to explain the issue.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:O%23OCbjfZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> No, but I don't believe that such an argument is needed. Could you
describe
> what you're trying to do?
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "newmem" <""> wrote in message
news:u8ooFefZEHA.2488@.tk2msftngp13.phx.gbl...
> > In RS, Scope tells the Aggregate function when to Re-set. Is there any
> other
> > argument to define when to Evaluate the expression. (equivalent of
> "Evaluate
> > on Change of Group" in crystal report for a Running Total field)
> >
> >
> >
>|||I'm not entirely sure I understand, but it sounds like this might be a case
which would require aggregates of aggregates.
Let me draw a simplified example to see if I understand it (I'm going to
skip one of the groups and just use a simple Price field):
Based on your description, I think your data looks something like this:
Client Brand Rec Item Price
C1 B1 R1 I1 10
C1 B1 R1 I2 10
C1 B1 R2 I1 22
C1 B1 R2 I2 22
C1 B2 R1 I1 35
C1 B2 R1 I2 35
C1 B2 R2 I1 20
C1 B2 R2 I2 20
C1 B2 R2 I3 20
...etc...
The Price column includes denormalized data (it has the same value for every
Item within a Rec)
Since the data is denormalized, when calculating the total price per Brand,
you want to ignore the duplicates.
So in the above example, you would want this:
Client Brand Total Price
C1 B1 32
C1 B2 55
Rather than this:
Client Brand Total Price
C1 B1 64
C1 B2 130
And in Crystal, you're using their RunningValue function to achieve this
(even though it isn't really a running value)
Is that correct so far?
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"newmem" <""> wrote in message news:uWIzotfZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> :(
> I've a report which has the following groupings:
> group 1 : Client Name
> group 2: Business Line
> group 3: Brand
> group 4: RecID
> I need to display the total cost in the group3 footer and has expression
> Runningvalue(fields!extended_price,SUM,"group3")
> where "fields!extended_price" is a formula field -
> FindExtPrice(fields!recID,fields!list_price)
> When there are more than 1 records in the group3, the total cost is eing
> calculated for each of the record in the grop3. Since they both have the
> same recID, I need to calculate the total only if the rec ID changes i.e
> Evaluate on Change of Group4. I have exactly same rpeort working in
crystal
> report but can not replicate in RS.
> Hope I'm able to explain the issue.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:O%23OCbjfZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> > No, but I don't believe that such an argument is needed. Could you
> describe
> > what you're trying to do?
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "newmem" <""> wrote in message
> news:u8ooFefZEHA.2488@.tk2msftngp13.phx.gbl...
> > > In RS, Scope tells the Aggregate function when to Re-set. Is there any
> > other
> > > argument to define when to Evaluate the expression. (equivalent of
> > "Evaluate
> > > on Change of Group" in crystal report for a Running Total field)
> > >
> > >
> > >
> >
> >
>|||apologize for the late reply.
I think you have described the problem correctly. How do i get rid of the
duplicates and perform the aggregate in RS?
Thanks.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:%230v$NHgZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> I'm not entirely sure I understand, but it sounds like this might be a
case
> which would require aggregates of aggregates.
> Let me draw a simplified example to see if I understand it (I'm going to
> skip one of the groups and just use a simple Price field):
> Based on your description, I think your data looks something like this:
> Client Brand Rec Item Price
> C1 B1 R1 I1 10
> C1 B1 R1 I2 10
> C1 B1 R2 I1 22
> C1 B1 R2 I2 22
> C1 B2 R1 I1 35
> C1 B2 R1 I2 35
> C1 B2 R2 I1 20
> C1 B2 R2 I2 20
> C1 B2 R2 I3 20
> ...etc...
> The Price column includes denormalized data (it has the same value for
every
> Item within a Rec)
> Since the data is denormalized, when calculating the total price per
Brand,
> you want to ignore the duplicates.
> So in the above example, you would want this:
> Client Brand Total Price
> C1 B1 32
> C1 B2 55
> Rather than this:
> Client Brand Total Price
> C1 B1 64
> C1 B2 130
> And in Crystal, you're using their RunningValue function to achieve this
> (even though it isn't really a running value)
> Is that correct so far?
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "newmem" <""> wrote in message
news:uWIzotfZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > :(
> > I've a report which has the following groupings:
> > group 1 : Client Name
> > group 2: Business Line
> > group 3: Brand
> > group 4: RecID
> >
> > I need to display the total cost in the group3 footer and has expression
> > Runningvalue(fields!extended_price,SUM,"group3")
> > where "fields!extended_price" is a formula field -
> > FindExtPrice(fields!recID,fields!list_price)
> >
> > When there are more than 1 records in the group3, the total cost is eing
> > calculated for each of the record in the grop3. Since they both have the
> > same recID, I need to calculate the total only if the rec ID changes i.e
> > Evaluate on Change of Group4. I have exactly same rpeort working in
> crystal
> > report but can not replicate in RS.
> >
> > Hope I'm able to explain the issue.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> > news:O%23OCbjfZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> > > No, but I don't believe that such an argument is needed. Could you
> > describe
> > > what you're trying to do?
> > >
> > > --
> > > My employer's lawyers require me to say:
> > > "This posting is provided 'AS IS' with no warranties, and confers no
> > > rights."
> > >
> > > "newmem" <""> wrote in message
> > news:u8ooFefZEHA.2488@.tk2msftngp13.phx.gbl...
> > > > In RS, Scope tells the Aggregate function when to Re-set. Is there
any
> > > other
> > > > argument to define when to Evaluate the expression. (equivalent of
> > > "Evaluate
> > > > on Change of Group" in crystal report for a Running Total field)
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I was afraid you were going to say that.
The *right* way to do this would be with an aggregate of an aggregate, such
as this:
=Sum(First(Fields!Price.Value,"RecGroup"))
This would sum the first Price within each Rec.
Unfortunately, aggregates of aggregates isn't supported in the current
release. (Since this comes up a lot, I'm pushing to get them added to SQL
2005, but the schedule is really tight so odds aren't good at the moment).
There are several ways around this, all of which involve modifying your
query.
I'll show examples based on my earlier simplified example below.
Option 1: Remove the duplicates
Change your query to return this:
Client Brand Rec Item Price
C1 B1 R1 I1 10
C1 B1 R1 I2 NULL
C1 B1 R2 I1 22
C1 B1 R2 I2 NULL
C1 B2 R1 I1 35
C1 B2 R1 I2 NULL
C1 B2 R2 I1 20
C1 B2 R2 I2 NULL
C1 B2 R2 I3 NULL
...etc...
And then you can simply =Sum(Fields!Price.Value)
Option 2: Include a count of duplicates
Change your query to return this:
Client Brand Rec Item Price Items
C1 B1 R1 I1 10 2
C1 B1 R1 I2 10 2
C1 B1 R2 I1 22 2
C1 B1 R2 I2 22 2
C1 B2 R1 I1 35 2
C1 B2 R1 I2 35 2
C1 B2 R2 I1 20 3
C1 B2 R2 I2 20 3
C1 B2 R2 I3 20 3
...etc...
And then you can: =Sum(Fields!Price.Value/Fields!Items.Value)
Option 3: Preaggregate
Change your query to return this:
Client Brand Rec Item Price BrandPrice
C1 B1 R1 I1 10 32
C1 B1 R1 I2 10 32
C1 B1 R2 I1 22 32
C1 B1 R2 I2 22 32
C1 B2 R1 I1 35 55
C1 B2 R1 I2 35 55
C1 B2 R2 I1 20 55
C1 B2 R2 I2 20 55
C1 B2 R2 I3 20 55
...etc...
And then you can do this for Brand: =First(Fields!BrandPrice.Value)
And this for Rec: =First(Fields!Price.Value)
This approach is simplest to write the query for but least useful since you
need a preaggregate for each level (you'll need one for Client as well, for
example)
Sorry I don't have a simpler answer for you. Like I said, I really want to
get aggregates of aggregates into the next release because this sort of
thing comes up quite a lot. But for now you're unfortunately stuck with a
query-based workaround.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"newmem" <""> wrote in message
news:un6u9%23AaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> apologize for the late reply.
> I think you have described the problem correctly. How do i get rid of the
> duplicates and perform the aggregate in RS?
> Thanks.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:%230v$NHgZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > I'm not entirely sure I understand, but it sounds like this might be a
> case
> > which would require aggregates of aggregates.
> > Let me draw a simplified example to see if I understand it (I'm going to
> > skip one of the groups and just use a simple Price field):
> >
> > Based on your description, I think your data looks something like this:
> >
> > Client Brand Rec Item Price
> > C1 B1 R1 I1 10
> > C1 B1 R1 I2 10
> > C1 B1 R2 I1 22
> > C1 B1 R2 I2 22
> > C1 B2 R1 I1 35
> > C1 B2 R1 I2 35
> > C1 B2 R2 I1 20
> > C1 B2 R2 I2 20
> > C1 B2 R2 I3 20
> > ...etc...
> >
> > The Price column includes denormalized data (it has the same value for
> every
> > Item within a Rec)
> > Since the data is denormalized, when calculating the total price per
> Brand,
> > you want to ignore the duplicates.
> >
> > So in the above example, you would want this:
> > Client Brand Total Price
> > C1 B1 32
> > C1 B2 55
> >
> > Rather than this:
> > Client Brand Total Price
> > C1 B1 64
> > C1 B2 130
> >
> > And in Crystal, you're using their RunningValue function to achieve this
> > (even though it isn't really a running value)
> > Is that correct so far?
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "newmem" <""> wrote in message
> news:uWIzotfZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > > :(
> > > I've a report which has the following groupings:
> > > group 1 : Client Name
> > > group 2: Business Line
> > > group 3: Brand
> > > group 4: RecID
> > >
> > > I need to display the total cost in the group3 footer and has
expression
> > > Runningvalue(fields!extended_price,SUM,"group3")
> > > where "fields!extended_price" is a formula field -
> > > FindExtPrice(fields!recID,fields!list_price)
> > >
> > > When there are more than 1 records in the group3, the total cost is
eing
> > > calculated for each of the record in the grop3. Since they both have
the
> > > same recID, I need to calculate the total only if the rec ID changes
i.e
> > > Evaluate on Change of Group4. I have exactly same rpeort working in
> > crystal
> > > report but can not replicate in RS.
> > >
> > > Hope I'm able to explain the issue.
> > >
> > > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> > > news:O%23OCbjfZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> > > > No, but I don't believe that such an argument is needed. Could you
> > > describe
> > > > what you're trying to do?
> > > >
> > > > --
> > > > My employer's lawyers require me to say:
> > > > "This posting is provided 'AS IS' with no warranties, and confers no
> > > > rights."
> > > >
> > > > "newmem" <""> wrote in message
> > > news:u8ooFefZEHA.2488@.tk2msftngp13.phx.gbl...
> > > > > In RS, Scope tells the Aggregate function when to Re-set. Is there
> any
> > > > other
> > > > > argument to define when to Evaluate the expression. (equivalent of
> > > > "Evaluate
> > > > > on Change of Group" in crystal report for a Running Total field)
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>