Optional prompt macro using function

[brymack1]

Hi. Our database has 2 forms of id value for a person

id1: Character Value the end user knows:   S########
id2: Value we use to filter (indexed integer): ######

We have a function that converts the character version to the integer:

f_get_id(id1) will return id2

I realize this isn't the perfect scenario, but it's the way it is and we have to live with it. So we have an OPTIONAL text-based prompt page on a report studio report (cognos 10.2.1) where a user can simply type in a character based id, then in the SQL, we need an optional macro to filter based on the function-return of what they typed in.  For this example, lets say:

id1: S12345678
id2:  9876

I have tried 2 ways to do this with the id number conversion:

1)   where person_uid = f_get_id(#prompt('parm_s_number'','string','person_uid')#)
<this works if you enter an ID on the prompt, but if you leave the prompt blank, the report returns no data instead of everybody.>

2)   where person_uid = (#prompt('odsmgr.f_get_id(parm_s_number)','string','person_uid')# )
<this returns everybody no matter if you enter a single ID in the prompt or not>

Esseintially, approach 1 doesn't work as f_get_id is making the prompt be mandatory.  Approach 2 is not even using the converted version of the parameter passed into the prompt, and thus, not even using the filter.

Can anybody help

[CognosPaul]

Try something like:

#prompt('parm_s_number','string','1=1','person_uid = f_get_id(','',')')#

If the parm is empty, the filter will be 1=1. If the value is not empty, it will put person_uid = f_get_id( before whatever is entered and ) after.

[brymack1]

I just realized I never thanked you, this solution worked perfectly. I realize this is 2 years old.  I can revive it with a new practical use.

I had the need to use custom sql in a Cognos report.  In this report, there are two tables.  There is an optional cognos prompt on a vlaue in one table.  If that prompt is used, I need the join between the tables to be inner; if the prompt is ignored, I need the join to be outer. I was struggling to figure this out, but here is my solution that worked:

and ad.account_uid = sar.person_uid      #prompt('ParameterStuAttr', 'string', '(+)', '/*', '', '*/' )#

This is Oracle syntax, where (+) is used for outer joins.  So my syntax simply puts in a (+) if the prompt is ignored (default value), and if the prompt is used, it comments out the value of the prompt, thus making the sql produce an inner join. Nice. I appreciate your help on this Paul - sorry for the very delayed thanks!