Report Studio 10 - Dynamic filter Data Item with OR and AND Operator

[cmlo]

Hi,

I have a report requirement where have 6 Text Prompt (Optional) and a Radio Button (1=AND condition, 2 = OR condition).

If user choose 1 for Radio Button then Data Item will filter by AND condition.

If user choose 2 for Radio Button then Data Item will filter by OR condition.

I have follow this link - https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014826789 , but it not work for me.

It only work, if user enter data in all 6 Text Prompt. If 1 of Text Prompt is empty/not value enter, the report will not apply any filter and will show all data.

Can anyone give me some advice on this ? Thanks in advance.

[BigChris]

What does your filter look like at the moment? In my head you'd have something like:

Code Select Expand

(?pOption? = 'And' and [TextField1] = ?pTextPrompt1? and [TextField2] = ?pTextPrompt2?...)
OR
(?pOption? = 'Or' and ([TextField1] = ?pTextPrompt1? or ?[TextField2] = ?pTextPrompt2?...))
From your question it sounds like you're getting problems when the user doesn't select anything for ?pTextPrompt3? (for example). I would be tempted to try setting a default value for each of the prompts, say "All" Then your filter might look something like

Code Select Expand

(?pOption? = 'And' and ([TextField1] = ?pTextPrompt1? or ?pTextPrompt1? = 'All') and ([TextField2] = ?pTextPrompt2? or ?pTextPrompt2 = 'All)...)
or
(?pOption? = 'Or' and (([TextField1] = ?pTextPrompt1? or ?pTextPrompt1? = 'All') or ([TextField2] = ?pTextPrompt2? or ?pTextPrompt2? = 'All')...))

[cmlo]

Thanks.

My filter look like :

[DataItem1] = ?pTextPrompt1? OR
[DataItem2] = ?pTextPrompt2? OR
[DataItem3] = ?pTextPrompt3? OR
[DataItem4] = ?pTextPrompt4? OR
[DataItem5] = ?pTextPrompt5? OR
[DataItem6] = ?pTextPrompt6?

By setting a default value for each of prompts (All), it solve the issues.
But, this may not a "Full" solution, as all my textbox is optional and if user remove the default value "All" and let it blank, then the filter won't work.


I'm now, using the SQL - (Query Explorer) and write the sql statement with OR Operator. This solve my issues.

Anyhow, thanks you every much for the suggestion.