Cognos Analytics – Multi-Select Prompts

Danny Gargus, March 6, 2023

One of the most important features of Cognos is the ability to create interactive reports that provide valuable insights to decision-makers.  One of the key components of creating these reports is the use of prompts, which allow users to filter and select the data they want to see.  In this article, we will focus on one specific attribute of the prompt, the multi-select property, which enables users to select or input multiple values at once.

As many of you probably already know, Cognos Analytics boasts a multitude of prompts available for ingestion into your reports.  Some of the most common are date prompts, value prompts and text box prompts.  You probably also know that you can set most prompts to be multi-select so that you can enter or choose more than one value at a time.  We will go over some of the options available when choosing to make a prompt multi-select.  We will be using the ‘Coffee sales and marketing’ data module that comes with the samples from IBM.

Table of Contents

Multi-select with Dates

The date prompt has probably the fewest options when it comes to multi-select.  Choosing this option just allows you to select multiple dates to feed to your query.  These would be individual dates like if you needed just the first day of the month for two months.  Here I have used the Edit box style.

When I run it, I can pass individual dates to my queries.

The most important thing to remember is, if you use multi-select, don’t forget to change the filter in your query that the parameter (in this case p_date) references to allow multiple values.  In many cases this means changing an equal sign to an “in” clause (with opening and closing parentheses).  If you forget to do this and the filter says something like “Some_Date = ?p_date?” then when you run the report, the prompt will only allow you to pick one date.  You must change it to “Some_Date in (?p_date?)” for the multi-select to work correctly.  Or you can use a range which we will talk about next.

If you turn on the Range property in the date prompt you can enter a start and end date for the report using, in the filter, the in_range function.  For example, “Some_Date in_range ?p_date?”.  Interestingly enough, when using multi-select with a date range, the parameter does not have to be enclosed in parentheses.  So the same filter function “Some_Date in_range ?p_date?” works for multi-select or single select date ranges.  Well, that’s not confusing at all!

Multi-select with Text Box

The text box prompt allows you to type in any value to pass to your report.  For instance, typing in “Coffee” passes the value to a filter such as “Product Category = ?p_product?” and returns just the coffee products.  There is no option to ignore case here so it would have to be typed in exactly as the data appears in your database or file.  This should only be used with well-known values such as state abbreviations.  We can enter one value at a time and use the arrow to add items to our list.

We can also remove any items that were previously added by selecting them from the right-hand side and pressing the left-arrow button to remove them.

That’s all well and good, but what if I have 50 items that I want to enter stored in an Excel spreadsheet or something?  I don’t want to enter all of those individually for obvious reasons.  There is an option that only exists with the text box prompt which is Multi-line. 

Multi-line allows you to enter more than one value at a time to pass to your filter.  It should be used in conjunction with Multi-select if you are going to use it.  Turning on Multi-line without turning on Multi-select will not return results if you use the same filter “Product Category = ?p_product?”.  Cognos will not throw an error and it will look like it is running but passing Coffee and Tea to the prompt will not yield the results for coffee and tea.

To fix this, turn on the option for Multi-select and change your filter to use the in clause again such as “Product Category in (?p_product?)”.  Now that I have activated both of those in my Value Prompt then I can copy and paste my list of values into the prompt.

Here I have a list of Product Types that I know exist in my data.  Make sure you don’t use commas because Cognos will treat the comma as part of the string and it will attempt to look for “Organic Beans,” which it will not find.

I can copy and paste them all at once into my prompt – which looks different and gives me space to paste them – making sure to select the right-arrow button to move them to the Selections: field.  And when I run it, I get the data returned for the product types I entered saving me a lot of typing, time and possible spelling errors.

Multi-select with Select & Search

The Select & Search prompt allows you to enter a list of static values and then search them for the one you want.  But the more popular way to use it is to assign a query to the prompt that contains all of the possible values and let the user pick from those.  Here I have created a query of all possible product types and assigned it to my prompt.  The use values and display values can be different but in this case they are the same so I don’t need to populate the display value field.

We do have the option of making this case sensitive or insensitive when we are searching for our values.  The default is case insensitive.  Now when I search for “organic” I get my results returned that I can choose from.  Because I don’t have Multi-select chosen I can only choose one of the items to send to my query.  I also have options at the bottom to control how I search for items.

If I change the prompt to Multi-select, the process is the same except now I can select all or any number of the results to send to my query.

In both cases I need to make sure that the filter in my query says = for single select or “in” for multi-select.  In fact, it’s just easier to use “in” all of the time so you don’t forget because passing one value or multiple values to an in clause works the same way.

Multi-select with Value prompt

The Value prompt is similar to the Select & Search in that you can assign static values or a query to the prompt.  There are several differences though, with the biggest being that all the values are returned to the user when the prompt is generated instead of searching for values.  So, if you have a small data set that returns less than 100 rows, for example, then using a value prompt may be a good idea.  If your data set has 5000 items for the user to choose from then scrolling through those may not be a good idea and you may want them to search for the values they are looking for instead.

When making the value prompt Multi-select, you get two options for the Select UI property.  I prefer the Check box group because it is easier to see which items have been selected.  When using the Range property with Multi-select you will have to change your filter to use the “in_range” function.  For example, Property Type “in_range ?p_property?”.  Cognos will not recognize the Range property if you are using an “in” clause in your filter and it will behave as if you had not selected Range at all.

Conclusion

There are other types of prompts available but the ones we have covered are the most common.  There are also many other properties for each type of prompt that you may want to explore. 

I hope that this has been informative.  If you have any questions about Multi-select prompts, as always, we are here to help. Be sure to subscribe to our newsletter for more PMsquare articles, updates, and insights!