cancel
Showing results for 
Search instead for 
Did you mean: 

Formula that returns Array from Parameter in Crystal?

Former Member
0 Kudos
695

I have a parameter called {?SIC} that allows users to enter multiple unique values OR enter the word "ALL".

I am trying to write a formula that checks whether the user entered multiple values or "ALL" and then have the formula return the array from the parameter (something along the lines of the formula below):

IF {?SIC}[ubound({?SIC})] in "ALL"

    THEN {zzcrm110.T$CSIC} // standard field containing all values

    ELSE {?SIC}[ubound({?SIC})] // array

I then want to pass this formula into select expert.

Currently the above formula only filters on the last {?SIC} value that is inputted. For example, if the user enters "3577" and "3514", the report will only filter on 3514.

Any insight on this one would be greatly appreciated! Please note that I am only a beginner when it comes to working with arrays. Thanks in advance for the help!

View Entire Topic
abhilash_kumar
Active Contributor
0 Kudos

Hi Elisa,

If you're trying to filter the report based on what's selected by the user, then the formula would be:

IF {?SIC} = "ALL"

    THEN True

ELSE {zzcrm110.T$CSIC} IN {?SIC}

-Abhilash

Former Member
0 Kudos

Hi, Abhilash. Thanks for your help!

That didn't seem to work for me when I tried to pass the formula into select expert per below. It prompted an error because it's looking for a string.

{zzcrm110.T$CSIC} = {@filterSIC} // formula field

abhilash_kumar
Active Contributor
0 Kudos

You don't need to pass the formula in the Select Expert. The code that I posted above has to be used 'as is' in the Selection Formula.

Go to Report > Selection Formulas > Record and paste the code that I posted above.

-Abhilash

Former Member
0 Kudos

Worked like a charm. Awesome! Thank you very much!