Shared Variable with Array using range parameter 1

[mfroggie]

I hope somebody can help me with this.

I have a main report with 2 subreports that have been working fine requesting info for only 1 fiscal pd. Now they want a range so I changed the selection formula to the following:

(tonumber({OESHDT.YR})=tonumber({?FiscalYear}) or tonumber({OESHDT.YR})={@LastFiscalYear}) and
{OESHDT.PERIOD}=tonumber({?begFiscalPeriod}) and {OESHDT.PERIOD}=tonumber({?endFiscalPeriod}) and
{OESHDT.SALESPER} in {?SalesRepFrom} to {?SalesRepTo}

and reset the links to the subreports.

I then changed the shared variable declarations in the subreports, here is the the declaration from one of the subreports:

WhilePrintingRecords;
Shared numberVar array currtotal ;
currtotal[1]:=currtotal[1]+{@SaleQty};
currtotal[2]:=currtotal[2]+{@SalesAMT};
currtotal[3]:=currtotal[3]+{@SalesRet};

Shared numberVar array currrep;
currrep[1]:=currrep[1]+{@SaleQty};
currrep[2]:=currrep[2]+{@SalesAMT};
currrep[3]:=currrep[3]+{@SalesRet};

Here is an example of the formulas for the SalesAmt:

IF ISNULL(Sum ({OESHDT.FAMTSALES})) THEN 0
ELSE Sum ({OESHDT.FAMTSALES})

And the Shared Variable declaration in the main report where I am tryin to print the results:

WhilePrintingRecords;
Shared numberVar array currrep ;
Shared numberVar array lastrep ;
totext(currrep[2],2)+chr(10)+totext(lastrep[2],2)

The report works fine if I choose a single fiscal period for example 01 to 01, but if I choose multiple it doesn't bring up any data. It makes me think it has something to do with the running total funciion but it looks correct to me. Anybody have any ideas?

Thank you much in advance.

Mfroggie

Mfroggie

 

[pmax9999]

It looks to me that there is a problem with the data selection, specifically:

{OESHDT.PERIOD}=tonumber({?begFiscalPeriod}) and
{OESHDT.PERIOD}=tonumber({?endFiscalPeriod})

The only way the period could be equal to both the beginning and end parameters would be if they were the same. Perhaps change the record selection to:

[Code RecordSelection]
(
tonumber({OESHDT.YR})=tonumber({?FiscalYear}) or
tonumber({OESHDT.YR})={@LastFiscalYear}
) and
{OESHDT.PERIOD}>= tonumber({?begFiscalPeriod}) and
{OESHDT.PERIOD}<= tonumber({?endFiscalPeriod}) and
{OESHDT.SALESPER} in {?SalesRepFrom} to {?SalesRepTo}
[/Code]

Cheers
Pete

 

[mfroggie]

Thank you pmax9999. That makes more sense, I had tried "to" and got an error message so changed it to "and". Unfortunately when I changed the selection formula to your suggestion I still get the same results when trying a range. Any other suggestions?

Mfroggie

Mfroggie

 

[pmax9999]

Can you provide more info on the report structure (grouping, and location of sub reports) and what it is doing please.

For example, is it the main report or sub report(s) that is (are) not providing the expected results? If it is one of the sub reports, does it work as espected if run independently of the main report?

Is it that the report (or sub report) is returning no data (ie the record count in the status bar at the bottom of the screen shows zero) which indicates a problem with the record selection, or is it returning records but not giving the expected results.



Cheers
Pete

 

[mfroggie]

I will try, bear with me.

There are 2 subreports which are both placed in the Group Header 2 and the variables for the running totals on the main report are placed in the group footer 1a. Group based on Category of parts sold. The report that works and has been working based on only one fiscal period as well as the new report based on a range of fiscal periods throws an error when trying to run just the subreports, eiter subreport. The error is:

"A subscript must be between 1 and the size of the array." and highlights the red text in the formula

Shared numberVar array currtotal ;
currtotal[1]:=currtotal[1]+{@SaleQty};
currtotal[2]:=currtotal[2]+{@SalesAMT};
currtotal[3]:=currtotal[3]+{@SalesRet};

Shared numberVar array currrep;
currrep[1]:=currrep[1]+{@SaleQty};
currrep[2]:=currrep[2]+{@SalesAMT};
currrep[3]:=currrep[3]+{@SalesRet};

On the original report or the new report if only selecting one period I will get a breakout of the categories for current year and previous year with sales qty total, sales dollar total and returns dollar total and grand totals at bottom.
On the new report and with your change to the selection formula I get the same list of categories broken out but instead of expected totals I get all zero's.

On the records count, if I run the original report for 2012/01 I get a records count of 52 and of course my expected totals. If I run the new report for just pd 01 to pd 01 I get a records count of 52 and the totals are correct. If I run the new report for pd 01 thru pd 06 I get a record count f 220 but all totals are 0 so returning records but without expected results.

I hope this is all the information you were looking for.

Thank you,

Mfroggie

 

[pmax9999]

OK, we are making some progress. The reason that the sub report is getting the error when run on its own is that the Array has no values at that point.

As you are not getting that error message when running the main report, initial establishment of the arrays must be occurring in the main report. It therefore seems likely that the problem is due to the arrays are being reset to zero during processing of the report, probably in one of the group headers or footers of the main report.

As an initial test to make sure the sub reports are actually returning data, add the following formula to the Report Header of each of the sub reports and check the record counts when run individually:

Shared numberVar Array currtotal := [0, 0, 0]

Once you are happy the sub report is actually returning data that formula should be removed. If the record count on the sub report is zero, the problem is in its record selection.

The next step will be to determine where the resetting of the array values is occurring. Possibly the formula that establishes the array has been placed in a group header/footer rather than in the report header, or in a group below where it should have been. Alternatively, one of your formulas contains a line that resets the array to zero.

As a way to test where the problem is occuring, add formulas to the report footer of each of the sub reports to display the 3 array elements. If these are all zero, the resetting is occuring in the subreports themselves.

Hope this helps.


Pete

 

[mfroggie]

This formula

Shared numberVar array currtotal := [0,0,0];
Shared numberVar array lasttotal := [0,0,0];
""
is in the Report Header of the Main Report

and this formula

Shared numberVar array currrep := [0,0,0];
Shared numberVar array lastrep := [0,0,0];
""

is in the Group Header #1 of the Main Report

I placed these 2 formulas

formula 1: Shared numberVar array currtotal := [0,0,0];
""

formula 2: Shared numberVar array currrep := [0,0,0];
""

in the Report Header of Subreport 1

and these 2 formulas

formula 1: Shared numberVar array lasttotal := [0,0,0];
""

formula 2: Shared numberVar array lastrep := [0,0,0];
""

in the Report Header of Subrepot 2.

I did this in the original report, which works correctly, and the new report but in all subreports I got no record counts.

Thank you,

Mfroggie

 

[pmax9999]

This becomimg more complex so we may need to take it a step at a time. If the sub reports are not returning any records, any values calculated by those sub reports aregoing to be zero.

The problem must be in the record selection formulas for those sub reports. Please show the respective record selections, and indicate was has changed when you moved from single period reporting to a range.

 

[mfroggie]

So when I put the selection formulas here for you I noticed I hadn't made your changes to those with the greater than and less than. So I made those changes, took out the formulas you had me put in the headers of the subreports and now I get totals. The only problem I see now is my "last year" totals, subreport 2 are not correct so I will go through the 2 reports and see why. It is only showing total for the 1st month I select so I must have my formula wrong for my running total. I will let you know if I have good luck but Thanks so much for your help, I so appreciate it.

Mfroggie

 

[mfroggie]

It always helps to review your typing. I put <=, <= in my 2nd subreport selection formula and once I changed that to the correct >=, <= my totals look correct.

Thank you again for your abundance patience and remarkable help.

Mfroggie

 

[pmax9999]

Just glad I could help.

Pete.