Charting whileprintingrecords

[ebstarasia]

I have a report with a Group Header which displays a date in the format of MM/YYYY. Now when the report is refreshed its asking to refresh the parameters of the date which is called T_OIM.F_PostDate. Therefore, the Group Header is based off said field/parameters.

There is a field that takes the sum of sales/volume of each month's week and displays that total number.

ie: 3/2004 1,047.75

there is a third field that takes the sum of the previous 12 months and divides that sum by 12 (Averaging the past 12 months). We are calling this field Trailing.

So the report would look something like:
Date Month Sum Year Avg/Trailing
6/2003 915.25 76
7/2003 945.00 155
8/2003 685.00 212

What I am trying to do is create a chart that plots both the Month Sum and the Trailing. However, I'm unable to chart the trailing because its not showing up as an available field in the chart expert.

here is the code that i have:

@Trailing
numberVar array Accum;
numberVar x;
numberVar y := 0;
numberVar z :=0;

if OnFirstRecord then
ReDim Accum [13]
else
ReDim Preserve Accum [UBound(Accum)+1];

Accum [UBound(Accum)] := sum({@MonthSum},{T_OIMMAIN.F_PostDate},"Monthly");
If UBound(Accum) < 13
then y := Sum(Accum) Else
(for x := (UBound(Accum)-11) to (UBound(Accum)) do
(y := (y + Accum [x]);
);
z := y/12;
)

I also have a second variation of the trailing formula

@TrailingV2
WhilePrintingRecords;
NumberVar mth1;
NumberVar mth2;
NumberVar mth3;
NumberVar mth4;
NumberVar mth5;
NumberVar mth6;
NumberVar mth7;
NumberVar mth8;
NumberVar mth9;
NumberVar mth10;
NumberVar mth11;
NumberVar mth12;
NumberVar All12months;

mth1:= mth2;
mth2:= mth3;
mth3:= mth4;
mth4:= mth5;
mth5:= mth6;
mth6:= mth7;
mth7:= mth8;
mth8:= mth9;
mth9:= mth10;
mth10:= mth11;
mth11:= mth12;
mth12:= (Sum ({@TEU}, {T_OIMMAIN.F_PostDate}, "monthly")/12);
All12months:= mth1 + mth2 + mth3 + mth4 + mth5 + mth6 + mth7 + mth8 + mth9 + mth10 + mth11 + mth12;


does anyone have any suggestions as how to chart these fields?

 

[lbass]

You need to use the method described in this article:

http://www.sdn.sap.com/irj/boc/go/p...2a54ff4cd?QuickLink=index&overridelayout=true

You shouldn't have started two threads on the same topic, by the way.

-LB

 

[ebstarasia]

I have seen this article and been trying to use it as a guide. However, the first error I came across said, "A string can be at most 65534 characters long." Then it refers tot the OnChangeOf formula. What i have written for that is:

whileprintingrecords;

Stringvar strOnChangeOf;

strOnChangeOf := strOnchangeOf + toText({T_OIMMAIN.F_PostDate}) + "^";

I have played around with these lines of coding and it allows me to bypass further by taking out strOnChangeOf.


strOnChangeOf := toText({T_OIMMAIN.F_PostDate}) + "^";

After continuing with the instructions, i then get an error message stating that "A date-time is required here," in this formula for the Record Selection Formula.

{T_OIMMAIN.F_PostDate} in split({?Pm-@OnChangeOf},",");

Any advice?

 

[lbass]

First, determine whether your date field is really a datetime data type by running the mouse over it and seeing the datatype identified in the tooltip. Since you are grouping on change of month, try using the following, and place your onchangeof formula in the date group section of the main report:

whileprintingrecords;
Stringvar strOnChangeOf;
strOnChangeOf := strOnchangeOf + toText(date(year({T_OIMMAIN.F_PostDate}),month({T_OIMMAIN.F_PostDate}),1),"MM/dd/yyyy") + "^";

Then in the sub, try:

toText(date(year({T_OIMMAIN.F_PostDate}),month({T_OIMMAIN.F_PostDate}),1),"MM/dd/yyyy") in split({?Pm-@OnChangeOf},"^");

-LB

 

[ebstarasia]

So I got as far as creating the chart in the subreport but now when I go to preview the whole report I get the error message, "A string can be at most 65534 characters long." Still referencing @OnChangeOf

 

[lbass]

One of the chart formulas references "recordnumber" in it--try changing that to groupnumber.

-LB

 

[ebstarasia]

ok no errors now but the chart isn't displayed when you preview the report.

 

[lbass]

To troubleshoot this, I would have to know the content of the main report formulas, where they are are located, the selection formula of the subreport, and the content of the formulas you are using in the chart expert.

-LB

 

[ebstarasia]

The chart is now coming up, however, the series/values its plotting is incorrect. It should be plotting the third field which is the @Trailing (12 month average).

In the main report, the ShowValue formula is

stringvar strShowValue;
strShowValue := strShowValue + totext({@Trailing}) + "^";

Which I am assuming that the subreport should be getting the data to plot from this correct?

As far as what you said above:

Main Report:

@Trailing (12 Month Average)
numberVar array Accum;
numberVar x;
numberVar y := 0;
numberVar z :=0;

if OnFirstRecord then
ReDim Accum [13]
else
ReDim Preserve Accum [UBound(Accum)+1];

Accum [UBound(Accum)] := sum({@TEU},{T_OIMMAIN.F_PostDate},"Monthly");
If UBound(Accum) < 13
then y := Sum(Accum) Else
(for x := (UBound(Accum)-11) to (UBound(Accum)) do
(y := (y + Accum [x]);
);
z := y/12;
)

@OnChangeOf
whileprintingrecords;
Stringvar strOnChangeOf;
strOnChangeOf := strOnchangeOf + toText(date(year({T_OIMMAIN.F_PostDate}),month({T_OIMMAIN.F_PostDate}),1),"MM/dd/yyyy") + "^";

@ShowValue
stringvar strShowValue;
strShowValue := strShowValue + totext({@Trailing}) + "^";



Subreport:
-Selection Formula
toText(date(year({T_OIMMAIN.F_PostDate}),month({T_OIMMAIN.F_PostDate}),1),"MM/dd/yyyy") in split({?Pm-@OnChangeOf},"^");

@SubShowValue
ToNumber(Split({?Pm-@ShowValue}, "^")[GroupNumber])

The chart expert fields have:
-On the change of T_OIMMAIN.F_PostDate (for each month)
-Show value(s) @SubShowValue (don't summarize)

 

[lbass]

I'm not understanding what it is you are seeing in the chart. You seem to think it is plotting something else, but it can't be. I'm wondering in what report section you have placed the subreport and in the subreport, in what section you placed the chart.

You do have a group in the sub correct? On Date on change of month?

-LB

 

[ebstarasia]

i placed the subreport in "report footer b" and in the subreport i placed the chart in "report footer a."

as for the group in the sub no.

 

[lbass]

Add the group in the sub, and suppress it. Otherwise, you don't have an effective groupnumber in the showvalue formula.

-LB

 

[ebstarasia]

ok group is added and suppressed, when I preview the subchart it shows under the group frame/panel only one date "3/2011."

when I was playing with the select expert, I said select between 6/2003 to 3/2011. That showed all the months on the side panel but the chart is not displayed. Is there something in the selection formula that is not grabbing the values?

 

[lbass]

I'm not following what you did. Do you mean you selected 6/2003 to 3/2011 for the main report? When you do this, do you see all the groups in the subreport group tree?

-LB

 

[ebstarasia]

no. the main report is looking at data between the selected date (6/2003 to 3/2011), but that does not make its way down to the subreport. my previous statement was changing the selection within the subreport to see the group tree.

 

[lbass]

All of your formulas should start with "whileprintingrecords;" (except the selection formula). Please make that change where it applies.

Then, in the main report do you see a series of values in the report footer for each of the two formulas if you reference them there by adding these formulas?

whileprintingrecords;
stringvar strShowValue;

...and

whileprintingrecords;
Stringvar strOnChangeOf;

-LB

 

[ebstarasia]

I added the "whileprintingrecords;" to all the formula (except the selection formula) like you said. Then nothing is displayed on the chart once I place the @OnChangeOf & @ShowValue in the report footer a. However, values are displayed if i put those formulas in the group footer, but the values are altered. So instead of the actual value, lets say is 1500 for the average, the number will then be 1617.

 

[ebstarasia]

Also if I follow the PDF instructions and place those formulas in details b, I get an error box stating "An array's dimension must be an integer between 1 and 1000," referencing the @Trailing formula.

In the design view, the main report format is

Report Header : TEU
Page Header : Suppressed
Group Header #1: [Group #1 Name] [sum of @TEU] [@Trailing]
Details a: [F_PostDate](suppressed)
Details b: suppressed
Group Footer #1: [@OnChangeOf] [@ShowValue]
Report Footer a: suppressed
Report Footer b: SubChart

 

[lbass]

And what do you see if you place the two formulas I mentioned in my last post in the main report footer? Do you see all of the values?

-LB

 

[ebstarasia]

if I place the formulas in report footer a then the trailing values are correct but the subchart is blank.