How to filter the data based on textbox prompt

[Kiran Kandavalli]

Team,

I have 15000 values in an Excel file and copied into the Cognos textbox prompt.

Based on the values in the textbox prompt, I need to filter the data. Can someone help me how to apply the filter condition.

I have used the CSV(Split...) condition but it is accepting only 1000 values.

Thanks!
Kiran

[dougp]

15,000 values?  Wouldn't it be easier to list the values that should NOT be in the filter?

Please tell me you aren't choosing the values manually.  How do you determine what values go into that list?  Isn't there a column or calculation that would group these values in some way?

There should be no reason for a macro.
What have you done so far?
How do you have the properties set on the textbox prompt?

Have you considered uploading the spreadsheet, including it in a data module, using the data module for the report, and joining on that column?

Sample column names and description of other structural elements would help communication.

[Kiran Kandavalli]

Hi,

Yes, it is 15000 values. I cannot use 'NOT' as we have 1 million values in that column.

I am not choosing the values manually and below are the steps that I follow.

  1) User provides me an excel file with a list of values in a single column.
  2) I will copy that column which has 15K values and paste it in the textbox prompt.
  3) In the query I am using the filter as in the attached file.

Here are the properties that I set for textbox prompt

Required --> yes
Hide Adornments --> Yes

Remaining all options --> No


I thought the approach of using the data module but the reason for not going with that direction is user has to run this report and he doesn't have access to create the data module / data set.

Thanks!
Kiran

[dougp]

1) User provides me an excel file with a list of values in a single column.

How does the user determine which 15000 values?  I can't imagine that's a manual process.  Maybe filter on a different column.

Have you tried these properties...
Multi-select = Yes
Multi-line = Yes
...then just pasting in the list from Excel?