Add Data Filter within an Array

Question

I have a report that I'm trying to modify to remove a Record Selection so the report will display all events in the date range, but apply the same filter within an array so it only calculates only events that have the filter that I'm removing in the Record Selection.

So, in the Record Selection I have the following:

and {OBI_EquipmentMaster.EquipmentType}=3

Which limits the report to only drives (events) in which a promotion is assigned. Promotions being Equipment Type = 3.

But I would like to remove this and place this filter in the Array formula instead so only Promotions are calculated in Cross-Tab.

In my array builder, the following formula was used:

whileprintingrecords;

stringvar d:= {OBI_EquipmentMaster.Description};
numbervar p:= {DriveProjectionAndCollectedTotals.ProcedureProjection};
numbervar pe:= {DriveProjectionAndCollectedTotals.ProceduresPerformed};
//numbervar et:= {OBI_EquipmentMaster.EquipmentType};

stringvar array ad;
numbervar array ap;
numbervar array ape;
numbervar c2:= 1;
numbervar n:=0;

// check to see if the description has been added to the string array
// if not, add it plus add the initial projection value to the number arrays
if not (d in ad) then

// tried to add filter for only certain equipment type - assigned to variable above
//if not (d in ad) and et=3 then
(
    numbervar c:= c + 1;
    redim preserve ad[c]; ad[c]:= d;
    redim preserve ap[c]; ap[c]:= p;
    redim preserve ape[c]; ape[c]:= pe;
)

else

// if the description is already in the array, find its position
// then add the new projection as a running total to the appropriate number array values
(
while c2 <= count(ad) do
    (
        if d = ad[c2] then (n := c2; exit while);
        c2 := c2 + 1
    );
ap[n]:= ap[n] + p;
ape[n]:= ape[n] + pe;
);

// grand running totals
numbervar gp:= gp + p;
numbervar gpe:= gpe + pe;

My modification of the array included adding another variable for equipment type:

numbervar et:= {OBI_EquipmentMaster.EquipmentType};

And adding a line in the If statement:

if not (d in ad) and *et=3* then

The formula checks out fine, but when I try to run the report I get an error indicating:

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

ap[n]:= *ap[n]* + p;

Specifically, the ap[n] it does not seem to like.

Any suggestions on how to remedy this?

I guess the problem is with `numbervar c`, it was not initialised before using... so first initialize to `numbervar c:=0` then use it as `c:=c+1` — Siva, Dec 17 '14 at 04:47
Seems to me like you're greatly over-complicating the solution. Why not just insert a subreport which retains the `{OBI_EquipmentMaster.EquipmentType}=3` portion of the record selection and build the crosstab there? — Ryan, Dec 17 '14 at 16:11
Hey @Ryan, I had actually went back and created a sub-report but now I'm having some issues displaying the data on the report, while the SQL returns data in SSMS. I'll make another post to address this. — MISNole, Dec 18 '14 at 17:39

score 0 · Answer 1 · answered Dec 18 '14 at 17:44

I went back and created and sub-report to summarize the information I'm needing. For the sub-report, I created the following links:

OBI_EquipmentID
@StartDate
@EndDate

And linked them via:

{OBI_EquipmentMaster.EquipmentID} = {?Pm-OBI_EquipmentMaster.EquipmentID}
and {rpt_DriveMaster.FromDateTime} >= {?Pm-@StartDate}
and {rpt_DriveMaster.FromDateTime} <= {?Pm-@EndDate}
and {OBI_EquipmentMaster.EquipmentType}=3

I only wanted drives that occurred between the original start and end dates the user selected in the parameter field and then only wanted drives with that were assigned a promotion (EquipmentType=3).

Now I'm not sure exactly why the data isn't appearing in the sub-report however, because when I look at my SQL query that Crystal Reports generates, I am returning data.

Add Data Filter within an Array

1 Answers1