Trying to create a selective formula

[Former Member]

Hi There, could need some help with a Crystal Reports development.  Haven't done much for a very long time so I am extremely rusty. Data source should not matter for this formula that I am trying to create.            

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. Any advice how I do this? Thanks.

[abhilash_kumar · Accepted solution]

Hi Stephan,

Do you have a data sample please? That really helps when suggesting a formula.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I can try and include a snapshot of the report and one result that was returned.

The data in column PO Amount is returned based on belows formula and record selection.

I am trying to create the sum of the returns only for cases where the values are different. (this case does not happen very often). So in case above I get the amount of ~120k returned 25 times due to the received column. It basically fills the "blanks" based on what is going on in ECC. So I want a formula that says in this case that the sum is on ~120k based on one field since all returns in column PO Amount are the same.

Does this make sense to you?

Thanks,
Stephan

[abhilash_kumar · Accepted solution]

Hi Stephan,

So, instead of summing up the whole PO column, you want CR to just sum up 'unique values' in that column right?

Try these steps:

1) Create this formula and place it beside the PO Column. You may suppress this field:

whileprintingrecords;

numbervar array PO;

numbevrar cnt;

if Not({PO_field_from_ECC} IN PO) then

(

     cnt := cnt + 1;

     redim preserve PO[cnt];

     PO[cnt] := {PO_field_from_ECC}

);

""

2) If you have a group on the report, do create this formula and place it on the Group Header:

whileprintingrecords;

numbervar array PO := 0;

numbevrar cnt := 0;

3) Create this formula to show the Summary and place it on the Group Footer:

whileprintingrecords;

numbervar array PO;

numbevrar i;

numbervar PO_Sum;

for i := 1 to ubound(PO) do

(

     PO_Sum := PO_Sum + PO[i];

);

PO_Sum;

Please let me know if this is not what you were looking for.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

thank you so much for taking the time and trying to help. Really appreciate that.

I created all 3 formulas and all but one pass the formula checker.

I have a few questions/remarks:

In formula 1 the checker complains here:

PO[cnt] := {PO_field_from_ECC}

It says "A number is required here"

To verify with you; the PO_field that you want here is the field that returns the~120k values in my example, right?

I am using a formula to calculate the value, thus I assume that is the issue?

Do you need to make changes to your formula or is there another solution?

Do I need all three formulas? Sorry your instructions are not clear to me.

2) If you have a group on the report, do create this formula and place it on the Group Header:

In total I am using 5 groups. By the time I get to PO# in my example above I am creating the 5th group.

Unfortunately there is no way around that based on the way I need to structure the report and how the data relate.

Does the number of groups have an impact on your formulas?

3) Create this formula to show the Summary and place it on the Group Footer:

I need the result to show up in GH5.

I also place the PO#, date, vendor, Received and a few more there and plan on supressing the Detail section.

I have further conbtent in the GFs.

Will this have an impact on your formulas?

Thanks,

Stephan

[abhilash_kumar · Accepted solution]

Hi Stephan,

1) What is the datatype of this field - {PO_field_from_ECC}?

Modify the line in the formula to:

PO[cnt] := ToNumber({PO_field_from_ECC})

2) Yes, you need all the three formulas

The Formula on the Group Header resets all the variables so that the summaries don't end up showing the Grand Total. We need it to sum each value in the Group and reset to zero before a new group starts.

3) The reset formula should go in the Group Header 5 if you're calculating the Subtotal for that particular group

4) CR has an execution plan due to which certain formulas are evaluated after Grouping, totaling etc. All these formulas execute at a later stage. Not only that, we need the formula in the detail section to accumulate the values being displayed and hence you cannot have these formulas show the summary on the Group Header.

There are workarounds though. It involves adding a Subreport and using some formulae we can 'source' the summary values for each group from the Subreport to show up in the Main Report's Group Header.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilesh,

this: PO[cnt] := ToNumber({PO_field_from_ECC})

took care of the formula issue.

As you can see it is a curency field based on a formula.

Your formulas work. I get the results in the group footer as you mentioned.

I have to think about the solution I was shouting for where I wanted to see the result in the group header.

But I do have a new problem.

As good as your formula works I guess now I am also getting bitten by the grouping?

How the report is structured:

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 thos 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:

The amount like the ~120k are per PO#.

If I look at the next PO#2 in my report I now see a sum of the ~120k plus the value for the actual PO#2. I do not want that. I need that the be the value of PO#2 only not PO#1+PO#2

I only want a sum if PO#1 for example has 2 different values.

I guess we need to make an exclusion somehow.

Is that possible?

Thanks so much for your help so far.

I did not anticipate that it would go into such a work.

Thank,s

Stephan

[abhilash_kumar · Accepted solution]

I guess I was missing one variable in the reset formula. It should also include the PO_Sum variable and the formula should look like this:

whileprintingrecords;

numbervar array PO := 0;

numbevrar cnt := 0;

numbervar PO_Sum := 0;

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

that was it 😉

Too simple I guess if you know what yo are doing.

Thank you so very much for your excellent help. That was awesome.

I appreciate the time you spend with me and the great help you provided.

I think I can find a solution for the cosmetic stuff.

Kind Regards,

Stephan

[abhilash_kumar · Accepted solution]

Glad I could help!

Have a great day.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I guess I called it too soon.

While going through the pages of my report I noticed that if I have multiple PO#s for a WBS your formula does not add these up. It only goes by the last listed value?

Is that something that can be corrected within your formula as a condition?

Sorry to come back on you.

Thanks,
Stephan

[abhilash_kumar · Accepted solution]

Try moving the reset formula to the WBS Group Header. This will reset the values when the WBS Group changes.

-Abhilash

[Former Member · Accepted solution]

Sorry but that is not the right place. The issue is not so much the wbs as the PO count. If I have multiple POs under 1 WBS then it is not counting the amounts of the different POs. So same result as in the GF. If I move it to the PO GH then the result is 0.

Anything else I can try?

Thanks,
Stephan

[Former Member · Accepted solution]

Here you can see. Multiple POs different amounts for Po Amount but the sum is just equal the last value. As said if I move it to WBS header it does not make a difference. The only other thing it does is it starts the count +1. So for the PO that has then120 it shows 0 and the next PO then has then 12o!

[IngoH · Accepted solution]

Hello Stephan,

looked at this entry I know that there has been a long discussion based on formulas but did you try to use the Running Total option in Crystal Reports and use the evaluate option to define which records you would like to leverage ?

it looks like you want to add each time the PO is changing.

Ingo

[abhilash_kumar · Accepted solution]

Would you be able to attach the report with saved data?

To attach the report, change its extension from .rpt to .txt and then click  the 'Use advanced editor' link in the reply window to attach.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Can't post the report here.

Can you provide me with an email and I will send it to you?

Thanks,

Stephan

[Former Member · Accepted solution]

Hi Ingo,

thanks for your response.

I only briefly looked at Running totals.

I am not really sure that it works here but will check it out once more.

Thanks,
Stephan

[abhilash_kumar · Accepted solution]

Hi Stephan,

The cost code total that you've highlighted in the screenshot is not the sum of the formula I created. It is the sum of a database field. Is that the Total you're talking about that's not coming up right?

Also, I see that there's a formula called 'PO_Amount_3' which is placed in the Group Header. Like I said, that formula should be on the Group Footer 5 to display the Subtotal for each PO Group.

If you place it on the Group Header, you'll never see correct results. To see summaries in the Group Header you need to use the Subreport approach.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I guess when testing things I moved this 3rd formula in the header at some point.

The screenshot above is what it looks like if the 3rd formula is in the footer. And instead of giving me a total it lists just the last PO amount as you can see. Maybe it is not supposed to do That?

I see correct results in the details section. And now I need the totals if I have multiple POs. If I have just one then the formulas work great.

Thanks,
Stephan

[abhilash_kumar · Accepted solution]

I'm sorry but I don't understand. Should the Cost Code Total be the addition of unique PO Values for a particular group?

If you look at the above screenshot, should it be

$1430 + $13,185.60 + $4,066.92 + $219,407.43 + $654.00 + $7,667.30?

-Abhilash

[Former Member · Accepted solution]

Sorry, I thought I was clear.

Yes that is exactly what I am saying. In some cases I have multiple POs under 1 WBS and then the values need to be added up. Or I have multiple POs with different values under one WBS. Those also need to be added up.

Does that make sense?

Thanks,

Stephan

[abhilash_kumar · Accepted solution]

Ok. Here's what you need to do:

1) Modify the PO_Amount_3 formula to:

whileprintingrecords;
numbervar array PO;
numbervar i;
numbervar PO_Sum;
numbervar cc_total;
for i:=1 to ubound(PO)do

(
  PO_Sum:=PO_Sum +PO[i];
);

cc_total := cc_total + PO_Sum;
PO_Sum;

2) Create another Reset formula and place it on the Group Header 4:

WhilePrintingRecords;

numbervar cc_total := 0;

3) Create this last formula to display the Cost Center Total and place it on the Group Footer 4:

WhilePrintingRecords;

numbervar cc_total;

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Thanks so much Abhilash for your continued effort.

I followed your steps above but I am still not getting thye count. The sum in case I have multiple POs still just shows the last amount as the sum.

Not sure what else I can provide you with to find a valid solution.

Thanks,
Stephan

[abhilash_kumar · Accepted solution]

Hi Stephan,

I tried the formulae I suggested on the report you sent to me and I see the correct Sum for all WBSs.

Also make sure the 1st reset formula should be on the Group Header 5. In the report you sent to me it was somewhere else.

-Abhilash