0

We have a report that requires users to be able to search from a field [Templates] and then select the 1 or more than 1 Templates to filter by. However, the report spec. also calls to be able to return the set of all Templates. I have done a lot of searching and attempts to get this kind of behavior all to no avail. We have Cognos 10.2. The main query is populated by a sql script. The fields in the main query are Template, Attribute, and Gov Rule. Every row will have a Template, but not nec. a Attribute or Gov Rule. I have already tried: http://www-01.ibm.com/support/docview.wss?uid=swg21341603

Any help would be most appreciated.

ASheppardWork
  • 95
  • 1
  • 2
  • 12
  • Why not just make the Select & Search an optional prompt and configure your filter to also be optional? In that configuration, if the user does not touch the Select & Search nothing will be passed in and the optional filter will be ignored, effectively returning all values. – Johnsonium Aug 19 '14 at 22:54
  • I tried that. However when I put Case ?param? or If ?param? statements cognos markes the prompt as required. How do I filter on the parameter results and have it not be required? – ASheppardWork Aug 20 '14 at 13:25
  • A CASE or IF is not required. Simply use a standard [column] = ?param? or [column] in ?param? syntax and make the filter optional. Also be sure to set the Select and Search prompt to optional as well. Unless I'm missing something regarding the requirement. – Johnsonium Aug 20 '14 at 16:22
  • How would that work? Say the ?param? is empty because the user has not entered anything into it putting [column] = {empty} would not work and neither would [column] in {empty} it would need some default value; which in the link I posted is not a option for the SNS prompt. So I tried Case ?param? when isnull then [column] else [column] in ?param?. I also tried the If version. both fail. – ASheppardWork Aug 21 '14 at 21:14
  • Optional prompts are ignored by Cognos when no parameter value is supplied. The effect is the query is not filtered at all by the column specified in the optional prompt resulting in all values for that data item being represented in the results. – Johnsonium Aug 22 '14 at 06:45

2 Answers2

0

After several attempts; I came at the issue from another point of view. I made a toggle to "show" or "hide" by setting box-type as none on one of two blocks based on a style-variable. The first block was the "all" query and the second block was the "filtered" query. When the toggle said all the filtered block box-type was none and thus did not show. When the toggle said filter, the search and select would appear (based on the same style variable) then the SNS prompt could be used and the resulting filter block would display while the all display would be hidden. I know it is kind of a work-around and would love a better solution than two seperate lists to be maintained; but for now I don't think Cognos 10.2 has a great way to have a SNS default to "all" or a filter. If I'm wrong, I would love to see a working example.

ASheppardWork
  • 95
  • 1
  • 2
  • 12
  • That will work but is unnecessary. Consider a query that had a single data item [Sales]. If you filter by city with [City] = ?city?, Congnos will require you to supply a city and then it will only display that city's sales. If you then set that prompt to optional and then run the report or show the tabular data for the query, Cognos will again prompt you for city but you will be free to not enter anything in the city prompt. The results will show sales for all cities as the optional prompt is ignored since no value was supplied. Remember that the default behavior is to include all rows. – Johnsonium Aug 22 '14 at 06:53
  • I feel like a real dummy. I tried Johnsonium's way and duh it worked. Can I change my answer? or give you points? or whatever. Seriously, thanks for taking the blinders off. – ASheppardWork Aug 22 '14 at 18:29
  • I'm glad to hear it worked for you. I added an answer that merged a few of my comments. – Johnsonium Aug 22 '14 at 18:37
0

You can just make the Select & Search an optional prompt and configure your filter to also be optional. In that configuration, if the user does not touch the Select & Search prompt nothing will be passed in and the optional filter will be ignored, effectively returning all values.

Consider a query that had a single data item [Sales]. If you filter by city with [City] = ?city?, Congnos will require you to supply a city and then it will only display that city's sales. If you then set that prompt to optional and then run the report or show the tabular data for the query, Cognos will again prompt you for city but you will be free to not enter anything in the city prompt. The results will show sales for all cities as the optional prompt is ignored since no value was supplied. Remember that the default behavior is to include all rows.

Johnsonium
  • 2,005
  • 1
  • 13
  • 15