Author Topic: How do you filter for a long list of values in a report?  (Read 361 times)

Offline pacificbeavs

  • Full Member
  • ***
  • Join Date: Jan 2018
  • Posts: 21
  • Forum Citizenship: +0/-0
I'm told that using Query Explorer in previous version of Cognos, you were able to copy/paste in long lists of values to filter for.  For example, if I wanted to filter for 100 values, I could copy them from Excel and paste them in to my query filter. 

As far as I can tell, that isn't possible in Cognos 11 when using a list report (or any other type for that matter).  It appears that editing the query with an "IN" statement is the best way to do that (e.g. IN ('abcd', 'efg', 'hij')).  Do you know of a way to filter for many values?  Some use cases require filtering for as many as a thousand values... Thanks!

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 270
  • Forum Citizenship: +14/-1
Re: How do you filter for a long list of values in a report?
« Reply #1 on: 15 Feb 2018 06:30:59 pm »
Filter uses "in".
Create a prompt page and add and configure a text box prompt to multi-select = yes and multi-line = yes.
As-of 11.0.7, there is a bug in the text box prompt in the interactive viewer.  It may still be there in 11.0.9 and 11.0.10.  To use the non-interactive viewer, change the report's Run with full interactivity property to No.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,208
  • Forum Citizenship: +620/-10
  • Cognos Software Muppet
Re: How do you filter for a long list of values in a report?
« Reply #2 on: 16 Feb 2018 03:33:31 am »
Filter uses "in".
Create a prompt page and add and configure a text box prompt to multi-select = yes and multi-line = yes.
As-of 11.0.7, there is a bug in the text box prompt in the interactive viewer.  It may still be there in 11.0.9 and 11.0.10.  To use the non-interactive viewer, change the report's Run with full interactivity property to No.

11.0.10? I'm guessing you must be wearing your clairvoyance cap today. Sadly, mine never seems to work, no matter how hard I concentrate ;)

MF.
Meep!

Offline pacificbeavs

  • Full Member
  • ***
  • Join Date: Jan 2018
  • Posts: 21
  • Forum Citizenship: +0/-0
Re: How do you filter for a long list of values in a report?
« Reply #3 on: 16 Feb 2018 12:54:55 pm »
Filter uses "in".
Create a prompt page and add and configure a text box prompt to multi-select = yes and multi-line = yes.
As-of 11.0.7, there is a bug in the text box prompt in the interactive viewer.  It may still be there in 11.0.9 and 11.0.10.  To use the non-interactive viewer, change the report's Run with full interactivity property to No.

Thanks- it worked although that's definitely much more involved than the previous version.  When I first create the filter, in the query it defaults to "=".  I then have to go in to my query, and then edit the filter to "IN".  Shouldn't Cognos do that automatically when you edit the filter properties to multi-select?

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 270
  • Forum Citizenship: +14/-1
Re: How do you filter for a long list of values in a report?
« Reply #4 on: 14 Mar 2018 12:06:50 pm »
Quote
11.0.10? I'm guessing you must be wearing your clairvoyance cap today. Sadly, mine never seems to work, no matter how hard I concentrate

IBM Support acknowledged this problem fairly recently.  It may not have been soon enough to correct it in 11.0.10.  I know it's still a problem in 11.0.9.

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 270
  • Forum Citizenship: +14/-1
Re: How do you filter for a long list of values in a report?
« Reply #5 on: 14 Mar 2018 12:08:29 pm »
Quote
Shouldn't Cognos do that automatically when you edit the filter properties to multi-select?

That sounds intuitive, but as a report developer, so you really want to give up that level of control?  Be careful what you ask for.

Offline charlie

  • Senior Member
  • ****
  • Join Date: Oct 2010
  • Posts: 60
  • Forum Citizenship: +3/-1
Re: How do you filter for a long list of values in a report?
« Reply #6 on: 22 Mar 2018 04:11:53 am »
I had been pulling my hair out over why I couldn't get it to work and then came across this thread. I have 11.0.6 running in the environment. Now need to get back to the users on why they cannot paste multiple values in a text prompt after the upgrade to the new and shiny Cognos Analytics  ::)

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 270
  • Forum Citizenship: +14/-1
Re: How do you filter for a long list of values in a report?
« Reply #7 on: 22 Mar 2018 12:14:58 pm »
The problem I described was corrected in 11.0.10.

 


       
Twittear