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
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.
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!