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]

Okay, I checked an old report of mine using this method, and the way I worked it was by making my variables shared variables. So in each formula, add "shared" before the variable. Then I linked the sub only on the "on change of" formula, and used a selection formula like you used and set up the chart the same way. Add the "shared" to the variable int the showval formula in the sub, too, and then create the chart as you did earlier.

-LB

 

[ebstarasia]

ok. let me get this straight.

----MAIN REPORT----
@Trailing
whileprintingrecords;
shared numberVar array Accum;
shared numberVar x;
shared numberVar y := 0;
shared 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;
shared Stringvar strOnChangeOf;
strOnChangeOf := strOnchangeOf + toText(date(year({T_OIMMAIN.F_PostDate}),month({T_OIMMAIN.F_PostDate}),1),"MM/dd/yyyy") + "^";

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

(PLACE @OnChangeOf & @ShowValue IN THE REPORT FOOTER A)

----SUBREPORT----
Edit link and only link @OnChangeOf
then change @SubShowValue formula

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

to
ToNumber(Split((strShowValue), "^")[GroupNumber])


Am I understanding that this is what you meant?

 

[lbass]

I'm not at the location where I have the report I referenced above, but I do have one that matches the original set-up--without using shared variables and it worked properly, so let's go back to that setup and remove the "shared" and link on both formulas. Also add a formula in the main report RHa called {@displayoco}:

whileprintingrecords;
Stringvar strOnChangeOf;

Add {@displayoco} as a third link. In the subreport, add {?pm-{@displayoco} and {?pm-@showvalue} in the RHa section. Insert a group on your date field, and use your formula in the chart:

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

Record selection formula the same as before, and use the date field as the On change of field in the chart->options->on change of Month.

I think that should work.

-LB

 

[ebstarasia]

I am still having the same issue. When I put the fields in different section of the report it will either show the chart but have wrong values, or have right values but not show values. here is a screen shot of what it looks like when I have ti showing the right values but sub chart not .

 

[lbass]

In the main report, your {@onchangeof} and {@showvalue} formulas MUST be in the group section, since they are collecting the values. I was suggesting only adding other formulas that reference the variables in the report footer.See my post from:

10 Mar 11 19:40

-LB

 

[ebstarasia]

sorry that its been awhile since i replied. Thank you for the help.

But i have a question. what would make the trailing numbers to change after putting the formulas {@onchangeof} and {@showvalue} in the group footer? It seems that its affected by the {@showvalue}. when that formula is in another footer, like report footer, the trailing numbers are correct.

 

[lbass]

In the group footer, it would only be collecting the value at the group footer level, not at the detail level. For example, create a formula like this:

whileprintingrecords;
numbervar cnt := cnt + 1;

Place it in the detail section and observe the results--it will count every record. Then move it to the group section. It will count only the group section. If you placed it in both sections, it would increase in both sections.

-LB

 

[ebstarasia]

success! i got the trailing numbers to be displayed on the subchart. now the only thing i need to do is have the trailing number and the monthly total displayed on the same chart. should i follow something similar to pass the monthly totals down to the subreport?

 

[lbass]

Yes, you would create a second showvalue variable, and follow the earlier steps, add it as link, etc.

-LB

 

[ebstarasia]

Another roadblock, lbass. What i've done is follow the same steps but the chart is now blank. Lets start from the main report.

MAIN REPORT:

>Created @ShowMonthlyValue
whileprintingrecords;
stringvar strShowMonthly;
strShowMonthly := strShowMonthly + totext({@TEU}) + "^";

>Placed @ShowMonthlyValue in the Group footer next to @ShowValue
>Linked @ShowMonthlyValue to subreport (?Pm-@ShowMonthlyValue)


SUBREPORT:

>Created @SubShowMonthly
ToNumber(Split({?Pm-@TEU Month}, "^")[RecordNumber]) //should it be groupnumber??

>Placed the PM-@ShowMonthlyValue parameter in suppressed detail section.
>Chart Expert and listed @ShowMonthlyValue as one of the show values.

Still getting a blank chart.

 

[lbass]

should it be groupnumber?? Yes, of course--since you collected the values on a group basis.

Why would you put the parameter in the detail section? Move it to the sub report header.

You should have the same group in the sub as in the main report, and the chart should be in the sub report footer.

-LB

 

[ebstarasia]

Im sorry i actually need the monthly sum to be on the subreport. basically i need to plot the sum of @TEU with the trailing.

 

[lbass]

I don't understand your response. I know you want to chart the monthly sum.

-LB

 

[ebstarasia]

In the main report you can create a chart that can plot the values "Sum of @TEU" <-- is a summary of the formula field TEU. That sum is what i need to plot.

 

[lbass]

If you want it on the sub chart you still have to follow the previous steps.

-LB

 

[ebstarasia]

it will work for a summary field? I been following the steps but the subreport goes blank once you preview the main report.

 

[lbass]

I'm confused by your post where you show the accumulation formula as being called {@ShowMonthlyValue}, but then in the sub you are trying to split {?pm-@TEU-Month}.

In the main report, your {@ShowMonthlyValue} references {@TEU}. Can you clarify these inconsistencies and also show the content of {@TEU}?

Actually, if the subreport uses the same tables so that {@TEU} could be calculated correctly right in the sub, you could add reference it in the chart expert the usual way.

-LB