Hello all
Running Crystal XI Professional 11.0.0.2495 on a Citrix network running Windows XP.
Having issues with array formulas (and it's my first real use of them and I'm struggling).
I have a report with 5 subreports.
The main report looks an Access dbase that contains a list of record IDs & also to the SQL dbase that contains the detail of the records. (long story but this was done in a desparate effort to speed the report up).
I have a parameter that picks the month, so the data I'm currently looking at only has 68 valid record IDs.
Each record has a log which identifies each approval step.
The main report is grouped on document ID (Grp Hdr 1) and then Step ID (Grp Hdr 2).
The main report is looking at records with director approval.
This all works fine.
However, I also need to access info for each record from another approval step. I’m using a subreport that points to the SQL dbase that contains the individual steps. This info is contained in a text field, which I'm having to split into 4 separate strings, eg
line 1: Red
line 2: deteriorating condition, self discharge from hospital
line 3: Personal Care
line 4: homeless, interstate transfers
Although the user should have entered the data, I've made allowances for the field being null. This works fine.
I'm then (attempting) to use arrays to count the instance of lines 2, 3 & 4 (I don't need line 1).
Thus, my other 3 subreports are printing the results each array (eg lines 2, 3 and 4).
My first attempt was with line 3, as it can only contain 1 array element. At the moment it appears to be working - but I suspect ONLY because I have valid entries for all array elements.
The array for line 4 is always missing the first element & the counts are garbage.
Here is line 4's array code.
I have initialized the array via a formula called: InitializeFundingCriteria & placed it in the report header
Shared StringVar array RFSFundingCriteriaName;
Redim Preserve RFSFundingCriteriaName[10];
RFSFundingCriteriaName[1]:="Homeless";
RFSFundingCriteriaName[2]:="Risk of homelessness";
RFSFundingCriteriaName[3]:="Families SA transition";
RFSFundingCriteriaName[4]:="Novita transition";
RFSFundingCriteriaName[5]:="Interstate transfer";
RFSFundingCriteriaName[6]:="No funding criteria";
" ";
The first subreport is placed in the main report's Grp Header 1 (record ID).
This subreport is grouped on record ID (Grp Hdr 1) and Step ID(Grp Hdr 2). I have created a formula called "GetFundingRequest" which splits the string. All this works fine.
I have another formula used to assign the array elements, called BuildingFundingCriteriaCount. Line 4 can contain more than 1 element, so if the line contains 2 elements, I need to increment the count for each array element. The data I'm able to access only has 1 element.
BuildingFundingCriteriaCount:
Shared NumberVar array RFSFundingCriteriaCount;
Local NumberVar ArrayElement5;
Redim Preserve RFSFundingCriteriaCount[10];
ArrayElement5:=0;
if lowercase({@GetFundingCriteria}) like "*homeless*" and
not(lowercase({@GetFundingCriteria}) like "*risk*") then ArrayElement5:=1;
if ArrayElement5>0 then
RFSFundingCriteriaCount[ArrayElement5]:=RFSFundingCriteriaCount[ArrayElement5]+1;
ArrayElement5:=0;
if lowercase({@GetFundingCriteria}) like "*risk of homelessness*" then ArrayElement5:=2;
if ArrayElement5>0 then
RFSFundingCriteriaCount[ArrayElement5]:=RFSFundingCriteriaCount[ArrayElement5]+1;
ArrayElement5:=0;
if lowercase({@GetFundingCriteria}) like "*families sa*" then ArrayElement5:=3;
if ArrayElement5>0 then
RFSFundingCriteriaCount[ArrayElement5]:=RFSFundingCriteriaCount[ArrayElement5]+1;
ArrayElement5:=0;
if lowercase({@GetFundingCriteria}) like "*novita*" then ArrayElement5:=4;
if ArrayElement5>0 then
RFSFundingCriteriaCount[ArrayElement5]:=RFSFundingCriteriaCount[ArrayElement5]+1;
ArrayElement5:=0;
if lowercase({@GetFundingCriteria}) like "*interstate transfer*" then ArrayElement5:=5;
if ArrayElement5>0 then
RFSFundingCriteriaCount[ArrayElement5]:=RFSFundingCriteriaCount[ArrayElement5]+1;
ArrayElement5:=0;
if lowercase({@GetFundingCriteria}) like "No funding criteria" then ArrayElement5:=6;
if ArrayElement5>0 then
RFSFundingCriteriaCount[ArrayElement5]:=RFSFundingCriteriaCount[ArrayElement5]+1;
" ";
My third subreport links to the SQL dbase with the steps.
PrintFundingCriteriaArrayName is used to print out the names of the array (eg the same listing as intialized in the main report in InitializeFundingCriteria).
Global NumberVar F=0;
Shared StringVar array RFSFundingCriteriaName;
Redim Preserve RFSFundingCriteriaName[10];
F:=F+1;
if F<=10 then
RFSFundingCriteriaName[F]
else " ";
My other formula called PrintFundingCriteriaArrayCount displays the count (as found in the 1st subreport).
Global NumberVar FC=0;
Shared NumberVar array RFSFundingCriteriaCount;
Redim Preserve RFSFundingCriteriaCount[10];
FC:=FC+1;
if FC<=10
then
Totext(RFSFundingCriteriaCount[FC],"#####")
else " ";
Both formulas are placed in the details section (there is no grouping).
I have another formula called @ArrayElementF :
Global NumberVar F;
F:=F+1;
F;
The report expert has
These combination is used to prevent multiple rows of zeros.
There's been a recent change to the contents of the text field that the arrays are built on & so it can be:
- the text field is null (it shouldn't be, but I need to allow for it)
- the text field is empty or only has 1 line (it shouldn't be, but I need to allow for it).
- after splitting the text field, there are 4 strings, but the contents do not match my array (eg old criteria).
Currently - I have valid data for "Homeless" & "Risk of homelessness" for line 4.
But the report is showing:
Risk of homelessness number //no. not always correct
Families SA transition number //incorrect as there are none
Novita transition 0 //correct as there are none
Interstate transfer 0 //correct as there are none
No funding criteria 0 //correct as there are none
Homelessness (the first element of the array is missing and it shouldn't be, as it has valid data). And if I play around with suppression via either the details section or the report expert, the count changes.
If it helps, the report takes a long time to run (up to several hours) - a little sad for 68 valid records & approx 10-15 steps per record.
I have trawled the net but can't find what I'm doing wrong. I've now achieved total confusion. Can somebody please shine the light of clarification (after you've stopped laughing ..)
IP Logged