Issue with Array in report

[Former Member]

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.

[JWiseman · Accepted solution]

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

[Former Member · Accepted solution]

Hey Jamie,

I attempted this solution as well, but one thing I got stuck on was if I changed the parameter type to a Number, I could not use a "*" as the default value.  It's a small thing, but our users are used to selecting "*" to select all options and it doesn't appear this method would allow for that.

Is there a way around that? Just don't want to complicate things for users who are used to things being a certain way.

[abhilash_kumar · Accepted solution]

Hi Trey,

Statements like these:

totext({rpt_EquipmentMaster.EquipmentID}) in {?Incentives} are usually NOT converted to where clause due to the datatype conversion being applied!

That shouldn't stop CR from completely applying the filter though. Although it doesn't push the filter to the database (via the where clause) it does pull all records and apply the filter locally - ofcourse that slows the report depending upon how many records you're dealing with here.

If you really want this to go as part of the where clause and if you're reporting directly against tables/views you can create a 'SQL Expression' field that just converts the {rpt_EquipmentMaster.EquipmentID} field to text and use that in the record selection formula.

An example SQL Expression,if you're reporting against Oracle would look like this:

to_char("rpt_EquipmentMaster.EquipmentID")

The record selection formula would then be:

("*" In {?Incentives} OR {%SQL_Expression_Field} IN {?Incentives})

AND {rpt_EquipmentMaster.EquipmentType}=3

You should now see a where clause!

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash!

That works.  I'm using MS SQL so I had to change the SQL Expression to:

cast("rpt_EquipmentMaster"."EquipmentID" as varchar)

But it now appears to be working correctly.

Thanks to all!

[JWiseman · Accepted solution]

hi Trey,

in case you want to do use this kind of work flow in a future report, you can have the * as a Description, 0 as the Value, and set the the parameter to prompt with the description only.

optional prompts can come in handy when you've got lots of parameters and you don't want to force the end user to put in values for all of them.

-jamie

[Former Member · Accepted solution]

Thanks Jamie,

That is good to know.

[former_member292966]

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

[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?