Help with an Array to Filter Records

[Former Member]

Awhile back, I received some help (from Jamie) with an array that would look through records and summarize a total.

The formula is:

whileprintingrecords;

stringvar d:= {OBI_EquipmentMaster.Description};

numbervar p:= {DriveProjectionAndCollectedTotals.ProcedureProjection};

numbervar pe:= {DriveProjectionAndCollectedTotals.ProceduresPerformed};

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

    (

        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;

And this worked great but I'd like to modify it a little to only get records that match a certain type. As of now, I have EquipmentMaster.EquipmentType=3 in my Record Selection, which prevents records that do not have items with Equipment Type of 3 being displayed on the report.

So I'd like to remove it from the Record Selection and apply it to the Array so it only calculates this particular equipment type (3).

Is there a way I can move this into the array to handle the summaries?

Thanks,

[Former Member · Accepted solution]

I tried adding another variable to the formula:

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) and et=3 then

But when I remove the record filter "EquipmentMaster.EquipmentType=3", I get the following error:

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

And it references the following line:

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

Thoughts?

[JWiseman · Accepted solution]

hi Trey,

if you could send me:

1) the report with saved data

2) a mockup / pic of what the numbers on the report should be

then if i have a bit of time i can have a look at it...can't guarantee anything though.

-jamie

[Former Member · Accepted solution]

Thanks Jamie,

Will do.

[Former Member · Accepted solution]

The report was originally designed to display events in which another event was assigned to the main event. For this to happen, I created a filter in the Record Selection of {OBI_EquipmentMaster.EquipmentType=3} so that only drives (the event) in which an Incentive (EquipmentType=3) is assigned will be displayed.

But now I’d like to report to display all events (or drives), even if they do not have an incentive assigned to the drive, but I would like the array to only calculate the values where EquipmentType=3.

Below are screenshots of how the report currently operates:

No events are displayed if they do not have an incentive assigned.  So what I’ve tried to is to remove the {OBI_EquipmentMaster.EquipmentType=3} in the Record Selection:

When I remove the filter, the report will then display all events in the date range selected:

But then the cross-tabs display all equipment types, when it should still be limited to Equipment Type = 3.

To try and fix this, I added a variable to the Array you created:

numbervar et:= {OBI_EquipmentMaster.EquipmentType};

Along with adding the variable to the if not statement created as well:

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

But when I try to run the report, I get the following error:

And it looks like this part of the formula is causing the issue:

I've uploaded the report here: https://drive.google.com/file/d/0B8ErGi0v2xIJMXpvbUszUENiS1U/view?usp=sharing

[JWiseman · Accepted solution]

hi Trey,

you're on the right track with the new variable in the arraybuilder formula.

you may wish to use an IF statement that wraps up the majority of the formula though...this way the entire formula is either acted on or not depending on var et.  e.g. the following syntax...

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;

if et = 3 then

(

// 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;

);

[Former Member · Accepted solution]

Thanks for your response Jamie,

I made the change you suggested, wrapping the entire array in parenthesis.

Now I"m getting an error in the Display String formula used within the Cross-Tab:

Subscript must be between 1 and the size of the array.

And it highlights this portion of the formula:

whileprintingrecords;

stringvar t:= GridRowColumnValue ("rpt_EquipmentMaster.Description");

stringvar array ad;

numbervar array ap;

numbervar c3:= 1;

numbervar n2;

while c3 <= count(ad) do

    (

        if t = ad[c3] then (n2 := c3; exit while);

        c3 := c3 + 1

    );

numbervar apn:= ap[n2];

totext(apn,0)

What would be the best way around this?

[JWiseman · Accepted solution]

works fine for me on the report that you uploaded. make sure that you copy the complete syntax from above and replace your entire arraybuilder formula with that syntax. if the closing paren is not at the end of the formula you will get errors. the reason is that you only want the arrays and vars (all of them) in arraybuilder to be acted on if et = 3.

[Former Member · Accepted solution]

Jamie,

Thanks again for your reply.  The report does work with the new array if I leave the {OBI_EquipmentMaster.EquipmentType}=3 in the Record Selection Formula.

But whenever I remove that so all drives (events) are displayed, I think get the error.

Here is another copy of the report where I get the error:

https://drive.google.com/file/d/0B8ErGi0v2xIJWWJDRWJTLVFaWVE/view?usp=sharing

Suggestions?

[JWiseman · Accepted solution]

in your display string formula put a 'check' in the last part of the formulas...e.g.

if n2 <> 0

then numbervar apn:= ap[n2];

totext(apn,0)

[Former Member · Accepted solution]

Thanks Jamie!

One last question, I think!. The report is now working in the two ways I was hoping for:

1) Report will show all drives (events) within the user defined date range

2) and the Cross-Tab is only calculating values where EquipmentType=3.

Would it now be possible to only display the items that are EquipmentType=3?

Thanks!

[JWiseman · Accepted solution]

if you mean in the crosstabs then sort of...a crosstab doesn't really have a filter but you can at least limit the number of rows.

create a new formula

if {OBI_EquipmentMaster.EquipmentType} = 3

then {OBI_EquipmentMaster.Description}

now right click on the crosstab row header (desc field) and choose Row Options > Group Options...change the current desc field to your new formula.

note that you will get a blank line added to your xtab...nothing you can do about this as. this is what i mean by the crosstab not having a filter.

[Former Member · Accepted solution]

Awesome. thank you very much!

[JWiseman · Accepted solution]

by the way, you can get rid of one more line by changing the new desc formula to this

if not isnull({OBI_EquipmentMaster.Description})

and {OBI_EquipmentMaster.EquipmentType} = 3

then {OBI_EquipmentMaster.Description}

[Former Member · Accepted solution]

Very cool, again - much appreciated!