I came across a requirement that requires a user to select a list of Employee IDs with specific dates from a 'Search & Select' prompt. The 'useValue' of the prompt is a concatenation of the columns 'Employee ID' and 'Date' separated by a space. I'm struggling to create a detail filter logic (to filter the report query) that would mimics the 'Report Filter Logic' mentioned below using Prompt Macros (grep, substitute etc.) without success. I would appreciate any help you could provide.
Select & Search Prompt:
useValue / displayValue : [Employee ID Column] || ' ' || [Date Column]
Employee ID Date
-------------- -------------
1234 01/01/2022
1234 01/15/2022
5896 02/01/2022
5896 02/20/2022
Report Filter Logic :
(
[Employee ID Column] = 1234
AND
([Date Column] >= '01/01/2022' AND [Date Column] <= '01/15/2022')
)
OR
(
[Employee ID Column] = 5896
AND
([Date Column] >= '02/01/2022' AND [Date Column] <= '02/20/2022')
)
You may benefit from using a Cognos macro. Here's an example of how to string together multiple prompt values. It seems like a good place to start.
https://www.cognoise.com/index.php/topic,22355.msg119990.html