I have a report parameter that is set to accept multiple values from the Prompt Pages.
Because of the following filter below to a Detail Query, it is not letting me to select multiple values.
?pProject?=substring([Project ID],1,character_length(?pProject?))
could some help me to fix this syntax issue ?
You're using what is called a prompt alias. Cognos performs some quick logic when rendering the report based on the context of an alias. Here it's not letting you select multiple values due to the way you're filtering. An equal sign will only be used for a single value.
Consider the following where clause:
'ABC';'Purple';'Tardis';'Elephant' = substring([Project ID],1,character_length('ABC';'Purple';'Tardis';'Elephant'))
You would get a parsing error if Cognos permitted this. Is there any reason you can't do:
[Project ID] in (?pProject?)
If it's coming from a value prompt, set the use value to the Project ID. The display value can be cleaned up however you want.
Thanks for the Reply.
Yes there is a reason why i am not able to use the following syntax
[Project ID] in (?pProject?)
?pProject? id input value could be something like this 8540.05
Database [Project ID] value could be like this 8540.05.02.01
8540.05.02.02
8540.05.02.03
8540.05.02.04
we would like to show the total at the input level.
Is there user selecting one value then? Or could the user go for 8540.05 to cover the four you mentioned, and 8540.03.02?
And does the project ID always follow the same pattern? Could you just take the substring([Project ID],1,7)?
Changing the filter to the following syntax worked. provided we always have all the projects filtered length same.
substring([Project ID],1,?pProjectLevel?) in (?pProject?)
Thanks @CognosPaul and @BigChirs