I wanted to achieve a requirement for below scenario ::
Value Prompt Static Choice Collection -
Display Value USE Value
Product 1
Customer 2
Database Records
Col1Product Col2Customer Col3
Null C1 Row1
P2 Null Row2
Null Null Row3
If in the Prompt, Value "Product" is selected, Report should show Row1 and Row3. If Value "Customer" is selected, Report should show Row2 & Row3.
If both are selected, all three rows should be shown.
It means whatever attribute is selected, rows having that attribute as NULL in the database should appear as a result set.
There are so many attributes in real requirements but just to explain the scenario, i have shown only two attribute Product and Customer.
Looks Simple so i created two new dataitems for each of the attribute value in Prompt -
DataItem1 for Product = If (Col1Product is Null and ?Prompt? In ('1')) Then (1) Else (0)
DataItem2 for Customer = If (Col2Customer is Null and ?Prompt? In ('2') Then (1) Else (0)
And Added a filter
DataItem1 In (1) or DataItem2 In (1)
The Surprise or the issue is after enabling the filter, Prompt Automatically goes to Single Select from MultiSelect. If filter is disabled, the prompt is still multiselect having both the data items.
Tried to troublshoot but seems like issue is with clause ?Prompt? inside data item itself but it starts to appear when filter is enabled.
Any suggestions...Appreciate your help
I don't know if this will be any diferent, but it's worth a try. You could make your filter to be something more like:
([Col1Product] is not null and ?Prompt? contains '1') or ([Col2Customer] is not null and ?Prompt? contains '2')
You might need to tweak it a bit...that was just off the top of my head
Thanks BigChris.
I also tried that but still the MultiSelect ValuePrompt was getting converted to SingleSelect.
The solution to stop this converstion - we added a dummy filter ?Prompt? in (?Prompt?) and now we are able to have and select Multiple Values from ValuePrompt in prompt page.
Now next issue is - Even though we select multiple items, At the backend query, (?Prompt?) holds only first value and not all which were selected.
Seems solution would be to pass the multiple selections from ValuePrompt to TextBoxPrompt via PromptAPI or HTML java scripts.
If anybody has implemented the the same and could share the script, would be a great help.
I recommend adapting this to a promptmany macro. The prompt alias just has too many places it can go wrong. In fact the only time that I actually recommend using a prompt alias is for an in_range predicate.
replace all instances of ?Prompt? with #promptmany('Prompt','string')#
The next problem is that ?Prompt? contains '2' is implicitly a single select. If it was multi select it would look something like. ('ABC','BCA') contains '2' which simply doesn't work. Since it's a contains filter, how about:
#sq(promptmany('Prompt','token'))# contains '2' which would then become 'ABC,BCA' contains '2'
Thanks a lot CognosPaul.
It worked successfully this time.