filter based on user input 2

[Hsiao]

Hi,

Does anyone know how I can create a report that would use a different filter based on user input from the picklist? I tried to create conditional filters, but Cognos doesn't appear to like expressions following "else" and "then". I'm new to Cognos and would appreciate any help or suggestion. Thanks!

Hsiao

 

[Hsiao]

Just to add a code example here for my question posted above. Thanks in advance.

If (?\Prompts\Pick University? <> '*' and ?\Prompts\Pick Fiscal Year? <> '*')

then (Building Picklist University ID in (?\Prompts\Pick University?) and Building Picklist Fiscal Year Code in (?\Prompts\Pick Fiscal Year?))

 

[griffindm]

The way you use conditionals in the filter is by comparison to a constant, as in:

[Rest of filter] and 1 = if (test condition1 true) then (1) else if (test condition2 true) then (1) else (0) ...

This capability is not obvious, and I wish more people were aware of it.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[Hsiao]

Hi Griffindm,

Thank you for your response. I really appreciate it.

I do not quite understand the constant you put in your code sample. Could you tell me how 1 is defined? I see that you put &quot;1 = if(test condition1 true) then (1)...&quot; If this is where it is defined, could you explain to me, then, where is the following condition that I want to use defined in your code?

(Building Picklist University ID in (?\Prompts\Pick University?) and Building Picklist Fiscal Year Code in (?\Prompts\Pick Fiscal Year?)

Thank you and hope to hear from you!

Regards,
Hsiao

 

[griffindm]

Hsiao,

1 is a constant integer value.

Let's look at some data. Say you have three rows, such as


City State Zip
-------- ----- ------
Topeka KA 12345
Omaha NE 23456
Detroit MI 34567

And say we define a prompt called ?GetKey? as a string, with a prompt message of:

&quot;Filter by (C)ity, (S)tate or (Z)ip?&quot;
Expecting the user to type in either &quot;C&quot;,&quot;S&quot;, or &quot;Z&quot;. A second string prompt just gets the filter value the user types in, let's say &quot;Topeka&quot; for know.


The filter could dynamically change the column to filter on in two different ways. It could be based on a calculated column which becomes equal to different database columns based on the first prompt, such as GrpKey1 defined as:

If Upper(?GetKey?) = &quot;C&quot then (City) else if (Upper(?GetKey?) = &quot;S&quot then (State) else (Zip) ...

The report filter would then look like:

1 = If (GrpKey1 = ?GetKey?) then (1) else (0) ...

Let's say the user types in a &quot;C&quot; at the ?GetKey? prompt and &quot;Topeka&quot; in the second string prompt. The GrpKey1 would evaluate to City and the first row would return a 1 value (as in 1 = if (City = 'Topeka&quot then (1)), thus passing the filter (i.e. 1 = 1 or True). The other rows would return a zero, and would not pass the filter (i.e. 1 = 0 or false).

You can do this without creating the calculated column, and in some circumstances you can get better performance, as the whole filter can more easily be passed to the database. This illustration, though, can also be used to create a dynamic grouping column, through the same GrpKey1 calculation.

I hope this helps illustrate how to use conditional filters.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[Hsiao]

Hi Dave,

Thank you very much for the elaboration of the conditional filter construct. I wonder if you can look at my code and offer me your insight into my Cognos piece? Here it is:

Basically my code is trying to retrieve a list of buildings from picking from a university and fiscal year lists (the building list also includes --ALL-- option)

1 = if (?\Prompts\Pick University? = '*' and ?\Prompts\Pick Fiscal Year? = '*') then (1) else if (?\Prompts\Pick University? = '*' and Building Picklist Fiscal Year Code in (?\Prompts\Pick Fiscal Year?)) then (1) else if (Building Picklist University ID in (?\Prompts\Pick University?) and ?\Prompts\Pick Fiscal Year? = '*') then (1) else if (Building Picklist University ID in (?\Prompts\Pick University?, '*') and Building Picklist Fiscal Year Code in (?\Prompts\Pick Fiscal Year?, '*')) then (1) else (0)...

Do you know why the fiscal year picklist falls back to the dropdown (instead of the list that I intended)? Also would you know a way to copy the filter content from Impromptu? I tried right click to copy but it doesn't show up as a paste option on Notepad.

Thanks again and hope to hear from you!

Regards,
Hsiao

 

[griffindm]

Hsiao,

Your prompts can only return a '*' value if they are a type-in prompt, or you go to extraordinary measures to include a '*' value in a report, catalog, or list type prompt. That said, your use of both a ?prompt? = '*' and a subsequent use of the same prompt in a ?prompt? IN indicates that you expect the user to be able to either type in a '*' or pick a value from a list, which is not possible in Impromptu for the same prompt.

You're on the right track, but you have to work within the prompt limitations of the product.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[Hsiao]

Hi Dave,

Thank you! I inserted a dummy row ('*') in the tables where I draw the University, Fiscal Year, and Building. That's how I expect the user to select their response from the list. If you have any suggestions on this use, please advise I'd love to hear it.

Could you see why I am getting a list for the University Picklist and a dropdown for Fiscal Year. My experience with Impromptu so far tells me a equality check gives a dropdown and IN operator gives a list, so would you know why I'm getting one in list and the other in dropdown format?

Thanks again and hope to hear from you!

Regards,
Hsiao

 

[griffindm]

Hsiao,

I'm not sure what you mean when you say you are getting a list for the University Picklist and a dropdown for Fiscal Year. By dropdown are you referring to list-box for selecting a single value as opposed to a list box where you can select multiple values? If so you are correct, and this may be where the confusion is occurring. When you have a non-type-in prompt, you will always get a list box. the difference is, when you are using the IN operator, you can select multiple values, whereas when you use an equality you can only select one.

In your filter you are doing both, so I think Impromptu is confused as to which to show.

The way I would handle your situation is to have three prompts. The first would be a simple text type-in prompt with the message &quot;Filter by (U)niversity, (Y)ear, (B)oth, or include (A)ll.&quot;, expecting a selection of &quot;U&quot;,&quot;Y&quot;,&quot;B&quot;, or &quot;A&quot;. If &quot;A&quot; is selected then pass all rows (i.e. 1 = 1).

The other two prompts would be list-box prompts for University and Year. In the &quot;A&quot; case you would ignore the results of both. In the &quot;U&quot; case you would use the results of the University prompt with an IN clause. In the case of a &quot;Y&quot; you would use the results of the Year prompt with an IN clause. In the case of a &quot;B&quot; you would do both.

As the list-box prompts are only used with an IN clause, the results should be predictable and you should always get a prompt that allows multiple selections.

Let me know if this solves the problem.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[Hsiao]

Hi Dave,

Thanks for your suggestion of alternate design scheme. I have tried it and it works! Meanwhile, I tried rewriting my statement in a slight different manner and finally got the multiple selection list box to show up fine for both University and Fiscal Year prompts.

1 = if ('*' in (?\Prompts\Pick University?) and '*' in (?\Prompts\Pick Fiscal Year?)) then (1) else if ('*' in (?\Prompts\Pick University?) and Building Picklist Fiscal Year Code in (?\Prompts\Pick Fiscal Year?)) then (1) else if ('*' in (?\Prompts\Pick Fiscal Year) and Building Picklist University ID in (?\Prompts\Pick University?)) then (1) else if (Building Picklist University ID in (?\Prompts\Pick University?, '*') and Building Picklist Fiscal Year Code in (?\Prompts\Pick Fiscal Year?, '*')) then (1) else (0)...

Above all, great appreciation for your response and finally led me to where I want to go.....

Regards,
Hsiao

 

[griffindm]

Good Job! This uses only the IN statement, rather than a combination of the IN and equality, and thus fixes your problem.

Super!

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[dm21]

Dave,

I tried to do following way i.e
t.team_id = if (?prompt?='C') then (x.team_id)
else if(?prompt? = 'B') then (e.team_id) else
t.team_id

With this it worked but performance was not good for monthly data.

 

[griffindm]

dm21,

What is your database and Impromptu version? Have you checked your SQL to see how much of your filter is getting passed to the database? Post your entire filter statement and SQL here so others can see what is going on.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com