Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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.

Tags:
Former Member
replied

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

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question