Summary totals in the group footer with detail section dates

[Former Member]

Hi All,

I have a report like this,

Manager Name: XYZ

1-Account                        Status:Active

PostingDate     New   Used  Cancel                     New     Used    Cancel

1/2012               1         2       0            1/2011      0          4           3

2/2012               0         1       1            2/2011      5          8           6

2-Account                       Status:Active

PostingDate      New    Used  Cancel                   New      Used    Cancel

1/2012               4         5       2             1/2011       3          2         1

3/2012               2         1       1              3/2011      2          8          4

In the group footer, I need a total for the manager to look like this (Summarizing the values in the months for all the accounts)

1/2012              5          7         2                            3          6          4

2/2012              0          1         1                            5          8          6

3/2012              2          1         1                            2          8          4

Any help is appreciated.

Thanks

[abhilash_kumar · Accepted solution]

HI Anusha,

Here's how you can do this:

1) Create this formula - @array1 and place it on the Details section: This formula

WhilePrintingRecords;
datevar array arr;
numbervar array nw;
numbervar array usd;
numbervar array can;
numbervar i;
numbervar j;
numbervar k;
if not({Date} in arr) then
(
    i := i + 1;
    redim preserve arr[i];
    redim preserve nw[i];
    arr[i] := {Date};
    nw[i] := nw[i] + {New};
    usd[i] := usd[i] + {Used};
    can[i] := can[i] + {Canceled};
)
else if {Date} in arr then
(
    for j := 1 to ubound(arr) do
    (
        if arr[j] = {Date} then
          k :=  j;
    );
    nw[k] := nw[k] + {New};
    usd[k] := usd[k] + {Used};
    can[k] := can[k] + {Canceled};
);
""

2) Create this formula - '@Date_Disp' and place it on the Group Footer (Manager Name):

WhilePrintingRecords;
datevar array arr;
numbervar array nw;
numbervar array usd;
numbervar array can;
stringvar fin_dt;
stringvar fin_nw;
stringvar fin_usd;
stringvar fin_can;
numbervar a;
for a := 1 to ubound(arr) do
(
    fin_dt := fin_dt + totext(arr[a]) + chr(13);
    fin_nw := fin_nw + totext(nw[a]) + chr(13);
    fin_usd := fin_usd + totext(usd[a]) + chr(13);
    fin_can := fin_can + totext(can[a]) + chr(13);
);
fin_a;

3) Create this formula - '@Disp_New' and place it on the Group Footer (Manager Name):

evaluateafter({@Date_Disp});
stringvar fin_nw;

4) Create this formula - '@Disp_Used' and place it on the Group Footer (Manager Name):

evaluateafter({@Date_Disp});
stringvar fin_usd;

5) Create this formula - '@Disp_Canceled' and place it on the Group Footer (Manager Name):

evaluateafter({@Date_Disp});
stringvar fin_can;

Note: Make sure the 'Can Grow' option is checked for all the formulas on the Group Footer. Right-click the formula field > Format Field > Common tab > Can Grow

Also, reset all the variables used in the above formulas at the Group Header. I guess that'll be the Manager Name group. SO, create a formula like this:

WhilePrintingRecords;
datevar array arr := (0,0,0);
numbervar array nw := 0;
numbervar array usd := 0;
numbervar array can := 0;
stringvar fin_dt := "";
stringvar fin_nw := "";
stringvar fin_usd := "";
stringvar fin_can := "";
numbervar i := 0;
numbervar j := 0;
numbervar k := 0;
numbervar a := 0;

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Hi Abilash,

When I run the report, I get the error "the subscript must be between the size of 1 and the array"

Thanks

[abhilash_kumar · Accepted solution]

Hi Anusha,

Which formula throws the error and what part of the formula is highlighted?

- Abhilash

[Former Member · Accepted solution]

It's the array formula and the usd[i] is highlighted.

[abhilash_kumar · Accepted solution]

Hi Anusha,

Could you attach a screenshot of the Formula Editor window please?

- Abhilash

[Former Member · Accepted solution]

Attached the screenshot. Appreciate your efforts.

[abhilash_kumar · Accepted solution]

Hi Anusha,

Please add this piece of code after Line 12 in the array formula:

redim preserve usd[i];

- Abhilash

[Former Member · Accepted solution]

That worked like a charm. But if I place the reset formula in the group header i got an error "(" for datevar array. So changed it to, datevar array arr := cdate(0,0,0); I also tried datevar array arr := cdate(2012,1,1); Reset is not correct.

Both are not adding the totals properly. Also instead of 01/01/2012 in the date diaplay I want 01/2012. How would I do it?

[abhilash_kumar · Accepted solution]

Hi Anusha,

Yes, datevar array arr := cdate(0,0,0); should reset the date array to blank dates.

In the '@Date_Disp', look at Line 14.

fin_dt := fin_dt + totext(arr[a]) + chr(13); 

This is what creates the string to be displayed. You can extract the Month and Year from the array and convert it to totext. Somthing like this:

fin_dt := fin_dt + totext(Month(arr[a]),0,"")&"/"&totext(Year(arr[a]),0,"") + chr(13); 

Hope this helps!

- Abhilash

[Former Member · Accepted solution]

Hi Abilash,

Without reset formula the summary repeats for each manager with the previous managers summary,

1/2012   0   0   0   0  0

2/2012   0   0   0  1  -1

3/2012   0   0   0  1  -1

1/2012   394 170 564 122 442

2/2012   386 166 552 123 429

With the reset formula, the summary from the second managers is calculated wrong,

Instead of,

1/2012 394 170 564 122 442

2/2012 386 166 552 123 429

It shows

1/2012  91  26  117    7  110

2/2012 101 34 135 121 114

The reset formula,

WhilePrintingRecords;

datevar array arr := cdate(0,0,0);

numbervar array nw := 0;

numbervar array usd := 0;

numbervar array can := 0;

numbervar array tot := 0;

numbervar array net := 0;

stringvar fin_dt := " ";

stringvar fin_nw := " ";

stringvar fin_usd := " ";

stringvar fin_can := " ";

stringvar fin_tot := " ";

stringvar fin_net := " ";

numbervar i := 0;

numbervar j := 0;

numbervar k := 0;

numbervar a := 0;

Another question, Can I resuse the same array formula to calculates the totals for the previous appear to appear alongside for comparison?

Thanks

[abhilash_kumar · Accepted solution]

Hi Anusha,

The reset formula has to be there. How many groups do you have in the report and where have you placed the reset formula?

Also, you can use the same array formula to calculate totals, however then you would need separate variables to do that.

- Abhilash

[Former Member · Accepted solution]

I have actually 4 groups.

Group 1- Regional or Territory Manager (based on parameter)

Group 2-Regional Manager

Group 3- Territory

Group 2 and 3 will be for Regional and Territory parameter

Group 4- Accounts for the managers either in Group1 or Group2. Group 4 is never suppressed.

The formula I  did is for group1 and I placed the reset in group1.

Group headers will be suppressed based on parameters one of 3 parameters,

Regional

Territory

Regional and Territory

[abhilash_kumar · Accepted solution]

Hi Anusha,

Well, do you think the reset formula should be the Group Header 4 section?

- Abhilash

[Former Member · Accepted solution]

No,if I place it in the the Group header 4 then the summary is the last account's values.

[Former Member · Accepted solution]

I think it resets it on every page, (i.e) if I have 5 accounts in a manager and the accounts appear in 2 pages, 3 in page 1 and and 2 in page 2, then it calculates the summary of the accounts in page 2, leaving the accounts in page 1.

[Former Member · Accepted solution]

I tried to put the reset formula in the group footer. It works fine, but the does not sort properly. I added the sorted the date field asc that i use in the Disp_Date formula.

[abhilash_kumar · Accepted solution]

Hi Anusha,

Well, if you have the group header set to repeat on each page, then yes the variables will reset on each page, and you'll get incorrect results!

So, placing the reset formula on the Group Footer was the right thing to do.

And what does not sort properly? The final dates in the Group Footer?

- Abhilash

[Former Member · Accepted solution]

Yes the date in the group footer does not sort properly, when the 1st account of the manager starts with anything other than Jan.

eg.

Manager-XYZ

Account1

2/2012   1   2

3/2012   2   3

Account2

1/2012   3   2

2/2012   4   4

3/2012   2   1

Total

2/2012    5   6

1/2012    3   2

3/2012    4   4

Also from the second Manager the records for one column are pushed down eg,

2/2012        6

1/2012  5    2

3/2012  3    4

            4

Thanks for your efforts.

[abhilash_kumar · Accepted solution]

Hi Anusha,

That is not in ascending order, because the array is not sorted.

Please add the following code after line in the @Date_Display formula. This will take care of the sorting bit.

numberVar counter1;
numbervar counter2;
datevar temp_arr;
numbervar temp_nw;
numbervar temp_usd;
numbervar temp_can; 
for counter1 := ubound(arr) to 1 step -1 do
(
          for counter2 := 1 to counter1 - 1 do
          (
                    if arr[counter2] > arr[counter2 + 1] then
                    (
                              temp_arr := arr[counter2];
                              temp_nw := nw[counter2];
                              temp_usd := usd[counter2];
                              temp_can := can[counter2]; 
                              arr[counter2] := arr[counter2 + 1];
                              nw[counter2] := nw[counter2 + 1];
                              usd[counter2] := usd[counter2 + 1];
                              can[counter2] := can[counter2 + 1];
                              arr[counter2 + 1] := temp_arr;
                              nw[counter2 + 1] := temp_nw;
                              usd[counter2 + 1] := temp_usd;
                              can[counter2 + 1] := temp_can;
                    )
          );
);

Please make sure you add the variables used in this formula to the reset formula too.

Hope this helps!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Rafaee_Mustafa]

Hi Anusha,

You can use the sub report for summary with the same query or same report just group that on date instead of Account

Regards,

Rafaee M

[Former Member]

I already did sub report approach. But I want to keep the number of reports less as I have some more parameters. So I will try Abilash's formula.

[Former Member]

A subreport placed in Group Header or Group Footer of '1-Account' group that groups on 'Account'>>'Date' and a shared variable to pass back the summary values to main report's Manager Group Footer is a much better idea.

Since, it will use the core concepts of grouping-summarizing-sub report-shared variables that has better chances of giving accurate results than an array logic

(which sometimes has resetting problems.)

for e.g: the query in the following thread(go through the last 5 posts in this thread)

https://scn.sap.com/thread/3195910

Thanks,

Prathamesh