Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: sumarize with a condition on a formula calculation Post Reply Post New Topic
Author Message
Joscan
Newbie
Newbie


Joined: 04 Jun 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote Joscan Replybullet Topic: sumarize with a condition on a formula calculation
     Posted: 04 Jun 2013 at 8:27am
Hi There,
hope the subject gives somewhat of a clue.
I haven't done much Crystal Report development over the last years so please be patient with me. I just ended up in a project where I need to build a couple reports as well. All was well until I needed to just built this sum.
Did a lot of google search and always ended up on this site. So here it goes.
 

I have a report that I created against an SAP ECC 6 environment. I have a column where the results are created utilizing a formula and a record restriction. Another column that delivers different results effects if I have more or less rows returned in the report. If I have more results my initial column repeats its result (if I only have 1 result returned by its formula) for each created row.
I want to create a formula that only adds the results of my formula column if its results differ from each other, otherwise use just one value as the sum.

I guess the most important aspects are:
 
I  have created 5 groups due to the report design.

Group 1 : Project number

Group 2 : Project location

Group 3: Project Manager

Group 4: WBS

Group 5: PO#

Group 1 - Group 3 are in Group headers 1-3 and build one section.

Below those 3 groups I can have multiple WBS Each WBS has at least 1 PO# sometimes multiple.

How I need the calculation to work is the following:

WBS . . . . . . .

PO#          Date     Vendor       PO Amount     Received

1               1.1.1      A              120                  60

1                1.1.1      A             120                  50   

1                1.1.1     A               120                 10
 
Additionally possible
1                1.1.1     A               50                   50
 
Also possible (for same WBS)
2                 1.2.1    B                70                   40
2                  1.2.1   B                70                   30

The PO Amount was calculated with a formula and a record selection. What I need is the sum of column PO Amount. But only sum different amounts like in the example above: 120+50 and possibly +70. If amounts are repeated ignore and only use one as the sum.

Any advice how I do this? Thanks Stephan

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 04 Jun 2013 at 11:57am
if the values are not 'inherent' to the row in the data, you won't be able to summarize the values.

What you can do it use shared variables and build your own summarization.

Fairly simple, usually there are 3 formulas per value displayed.

initialize, usually in the group header:
shared numbervar aTotal := 0;
"" //hides the zero

display, usually in the group footer:
shared numbervar aTotal;
aTotal

increment, usually in details (usually the hardest)
shared numbervar aTotal;
if (some condition for your data) then
aTotal := aTotal + {table.field};

"" //hides the output of the formula.

HTH
IP IP Logged
Joscan
Newbie
Newbie


Joined: 04 Jun 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote Joscan Replybullet Posted: 04 Jun 2013 at 12:33pm
Thanks HTH. Yeah that's what I was working on and actually made good progress until I ran into another issue. I will post post the 3 formulas that I have and the reslut a bit later.
I tried something else and that got me actually further. Here is what I tried instead:
If (({calculation formula for PO Amount} = previous ((({calculation formula for PO Amount})) and ({EKKN.EBELN}) =({EKKN.EBELN})) then 0 else{calculation formula for PO Amount}
I also use a sorting function due to the previous so I have no mix matches and get the result I need.
This formula above returns single values as I want and sets duplicates to 0.
The issue I ran into is: The very first value which should be an amount comes back blank. Not sure why and how to get over it.
Any suggestion?
Thanks,
S
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 05 Jun 2013 at 5:18am
I believe that the first record will return matching values for the previous function.

you can use OnFirstRecord flag to alter your if statement to take this into account, like:

If (({calculation formula for PO Amount} = previous ((({calculation formula for PO Amount})) and ({EKKN.EBELN}) =({EKKN.EBELN})) and not OnFirstRecord then 0 else{calculation formula for PO Amount}

HTH
IP IP Logged
Joscan
Newbie
Newbie


Joined: 04 Jun 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote Joscan Replybullet Posted: 05 Jun 2013 at 12:49pm
Unfortunately that did not work either. Still drawing a blank......
I think I am trying something else.
 
Can I actually build a Running Total but have it build it's running totals per group?
 
I mean I have a group based on WBS's Those WBSs can have multiple POs
If a WBS has multiple POs I want them to be added and then go to the next WBS and count the PO amounts there.
 
Is that possible?
 
Thanks,
St
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 06 Jun 2013 at 4:36am
Probably, I'm not the running total expert. DBlank usually gives advice on running totals. I don't know why, but they confuse me...I have always just built them myself using variables.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Jun 2013 at 6:28am
you likley have a unique value (a PK in the table, amybe your PO#?) per record that stores the PO amount. You may not be using the field in the report design but it should be in the table.
Create a Running Total
name=whatever
field to summarize=po amount
type of summary=sum
evaluate= on change of field (pick the PK)
reset=? Depends on if you want to see totals per group or totals per report. If you want to see both you create 2 duplicate RT's with different reset values.
Place your RT's in the detail section or Group footers or report footer.
They do not work in headers.
IP IP Logged
Joscan
Newbie
Newbie


Joined: 04 Jun 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote Joscan Replybullet Posted: 06 Jun 2013 at 11:17am
DBlank,
that's what it was. The reset part helped me specify the group I wanted the totals for.
Now, all is good.
 
Thank you so much for your help.
 
Cheersm
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum