Skip to Content
avatar image
Former Member

Issue with Array in report

Hello,

I'm trying to create an parameter and associated array for a record selection I would like to give the end user. I created a parameter field called "Incentives" and created a new formula field "IncentivesArray":

If "*" in {?Incentives} then true else totext({rpt_EquipmentMaster.EquipmentID}) in {?Incentives} and true

What I was hoping would happen is that "*" would be the default and select all values in the table, or if not then which ever values the user did want.

And in my Formula Workshop -> Record Selection, I added {@InentivesArray}. But I only want certain equipment types so i added another statement to my record selection of "{rpt_EquipmentMaster.EquipmentType}=3".

When I run the report and select "*" for the Incentive parameter, all drives are displayed on the report and it looks good.  But when I do select a particular EquipmentID to be displayed, nothing appears on the report and when I check the SQL statement (under Report -> Show SQL Query), I notice the the rpt_EquipmentMaster.EquipmentID="XX" is not in my WHERE clause so it is not being applied to the report and I'm not sure why.

Suggestions?  Need more info?  Just not sure what is missing here.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 08, 2014 at 11:08 PM

    hi Trey,

    another option is to change the parameter Type to Number. then change the parameter so that it is an Optional parameter as well.

    after this you can change your record selection filter to something like

    if hasvalue({?Incentives}) then {rpt_EquipmentMaster.EquipmentType} in {?Incentives} else true

    this will allow the filter to be processed at the database. if the end user does not choose a value, then all incentives will be brought back.

    -jamie

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 08, 2014 at 10:58 PM

    Hi Trey,

    How is the Incentives parameter created?  Is it linked to a field or static?  What are the parameter values would you see?

    I would actually have you move the logic for @IncentiveArrays directly into the Selection Formula.  This way there is a better chance of it being added to the Where clause.

    Try this:

    if "*" In {?Incentives} Then

         else totext({rpt_EquipmentMaster.EquipmentID}) <> ""

    Else totext({rpt_EquipmentMaster.EquipmentID}) In {?Incentives}

              And {rpt_EquipmentMaster.EquipmentType}=3;

    Thanks,

    Brian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hey Brian,

      Thanks for the response.  It's a static, string parameter that should allow for multiple selections.  I've attached a screenshot. Ideally, the user should see 89 selections when they select this parameter in the report.

      I moved the logic to the Selection formula but I had to remove the "else" in your second line because it was giving me an error about a "number, current amount, boolean is expected here".

      But after doing this, the incentive parameter is still not appearing in the where clause when I look at the SQL code.

      Did I miss something?