A subscript must be between 1 and the size of the array 1

[cpjeffm]

Getting this if I run my report on a system that I know has no data 3 years ago. I need it to return a 0 if there's no data. Can't, for the life of me, figure this out and I'm no Crystal expert. I'm trying to fix this report that was created by someone else. Here's the formula that's erroring...

whileprintingrecords;
shared stringvar partssoldYr3;
local stringvar temp1 := Left(partssoldYr3, len(partssoldYr3)-1);
tonumber(split(temp1,"^")[groupnumber]);

The highlighted portion when it errors is...

split(temp1,"^")[groupnumber]);

 

[CrystalizeCanada]

You could try the following:

whileprintingrecords;
shared stringvar partssoldYr3;
local stringvar temp1 := Left(partssoldYr3, len(partssoldYr3)-1);
if Count(split(temp1,"^"))>= groupnumber then tonumber(split(temp1,"^")[groupnumber]) else 0;

You should get a zero instead of an error. You can then format the result to suppress if zero (on the Number Tab) if you don't want the zeros to appear.


Gordon BOCP
Crystalize

 

[cpjeffm]

Worked perfect! Thank you!!!

 

[cpjeffm]

I thought this was good but found an issue. The problem is if I have no sales in January, or February but then have sales in March, the March sales are showing up in January. Any ideas?

This report shows me 4 years worth of data and then data for each month. I'm attaching a screenshot. Notice the $500.00 Part Sales in January 2016. That 1 sale for $500.00 was done in March. Not sure why it's showing up in January.

 

[CrystalizeCanada]

The formula that you posted has a shared variable called partssoldYr3.
A formula is normally shared (in scope) because it is populated by a sub-report. Otherwise it is normally global in scope.
So I guessing that there is a sub-report that is used to populate this string variable with each months figure so we end up with "760^650^500^".
The ^ is used to separate each entry. The 760 is for Jan, 650 for Feb and 500 for Mar.
But if there aren't any sales in Jan and Feb I suspect we end up with a variable that has "500^" and not "0^0^500^".
So the problem is that the Mar figure is in the first "slot" and that's why it shows up in January in the main report.

If you create a formula :
shared stringvar partssoldYr3;
and display this in the month group you can see the variable value and confirm if that is the case.
If it is then that would explain why the 500 shows against January.

Then problem would have to be on the Sub Report that is passing this value.
Dealing with 'missing months' is fixable but it can involve some work.




Gordon BOCP
Crystalize

 

[CrystalizeCanada]

One way of dealing with the problem of missing months is to use a formula like the one below:

//This formula is placed in the month group header in the sub-report which populates the shared variable
//It collects the months data into the right slot and it won't matter if we don't have data for a particular month
Shared CurrencyVar Array MonthSales;
Redim Preserve MonthSales [12];
MonthSales[1] := if Month({Orders.Order Date}) = 1 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[1] ;
MonthSales[2] := if Month({Orders.Order Date}) = 2 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[2] ;
MonthSales[3] := if Month({Orders.Order Date}) = 3 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[3] ;
MonthSales[4] := if Month({Orders.Order Date}) = 4 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[4] ;
MonthSales[5] := if Month({Orders.Order Date}) = 5 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[5] ;
MonthSales[6] := if Month({Orders.Order Date}) = 6 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[6] ;
MonthSales[7] := if Month({Orders.Order Date}) = 7 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[7] ;
MonthSales[8] := if Month({Orders.Order Date}) = 8 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[8] ;
MonthSales[9] := if Month({Orders.Order Date}) = 9 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[9] ;
MonthSales[10] := if Month({Orders.Order Date}) = 10 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[10] ;
MonthSales[11] := if Month({Orders.Order Date}) = 11 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[11] ;
MonthSales[12] := if Month({Orders.Order Date}) = 12 then Sum({Orders.Order Amount}, {Orders.Order Date}, "monthly") else MonthSales[12] ;
MonthSales [Month({Orders.Order Date})]

and in the main report the formula below is also placed in the month group header
//This formula is in the main report
Shared CurrencyVar Array MonthSales;
if GroupNumber <=12 then MonthSales [GroupNumber]

This might give you an idea of how to approach the problem of missing months

Gordon BOCP
Crystalize