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.
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..
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.
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 ;)
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.
(
?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.
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
Thank you very much for your answers.
I'll test solutions and I will keep you posted
good day