Query in report studio

[a.info]

I want to use a query in report studio. but I want to have in my where clause 'AND' or 'OR' dynamic depending on the choice of the user.
example: where id_cat = 1 (or / and) id_cat = 2

Is it possible to have this treatment?
thank you in advance.

[blom0344]

An academical issue..  Cognos allows for conditionally showing report pages and queries associated with it (and its datacontainers)   So, it is merely a matter of offering a prompt to either show a report on the 'AND' scenario or the 'OR' scenario.

Rendering the set of report pages triggers the associated queries and the filters..

[a.info]

Thank you for your response.
I do not understand your proposal.

I want a single query, but the operator (AND / OR) will be dynamic depending on the choice of the user.

Thank you for your help.

[blom0344]

What part of the proposal do you not understand?  Conditionally showing/hiding datacontainers / report pages is a good way to trigger a certain scenario.  The object that is conditionally displayed triggers the query associated with it.

In my proposal you would indeed build 2 queries, depending on the scenario chosen (use prompt with fixed list) one of the queries is executed.

Dynamically changing the AND / OR in the filter expression?  I think you are looking for a non-existing feature, but I hope someone proves me wrong   ;)

[Lynn]

Two queries with two pages conditionally rendered based on a prompt selection is an option, as blom describes.

Another option is to use a single query and a single layout container, but set your filter expression to behave differently based on the prompt selection of the user.

Code Select Expand


(
?AndOrPrompt? = 'AND'
and
[id_cat] = 1 and [id_cat] = 2
)
or
(
?AndOrPrompt? = 'OR'
and
[id_cat] = 1 or [id_cat] = 2
)


I should further point out that your 'and' condition is not possible the way you've described it. A single query item cannot contain 1 AND 2 so it will never return rows in the result set if the user selects the 'AND' option.

[RobsWalker68]

Hi,

Another possible way to do it would be to utilise a prompt macro with a token data type.  Define a static prompt with the values of 'or' 'and', then in expression for your filter include a prompt macro.  As it's a token type it will place into the expression the value selected by the user.  If no value is selected it will, in this example, default to 'or'.     

id_cat = 1 #prompt('Logical Operator Prompt', 'token', 'or')# id_cat = 2

As Lynn mentioned though the 'and' statement won't return a value.

Regards

Rob

[a.info]

Thank you very much for your answers.
I'll test solutions and I will keep you posted

good day