Hi gurus,
I am creating a report with textbox prompt. To bypass the oracle limitation of 1000 in the in clause, the user has requested to include 3 textbox prompts for the same data item so that he can enter upto 2500 values into the prompt. So what is the best way to set up the filter in report studio, performancewise etc.,
Is this the right approach...
[Data Item] = ?prompt1? or
[Data Item] = ?prompt2? or
[Data Item] = ?prompt3?
Don't you want to use 'in' instead of '=' ?
Based on the value of the Data, you can also create another column which can have abbreviated data and then use that in the Prompt. That way, you will have less to deal with and the query might be better.
oh yes sorry with the in clause....
So gurus any ideas on this
Is this the right approach...
[Data Item] in ?prompt1? or
[Data Item] in ?prompt2? or
[Data Item] in ?prompt3?
2,500 prompt values is a lot of user entry!
Does the field being filtered often require so many filter values? If so, here is something you might consider. A firm does a lot of targeting analysis that results in very large lists of candidates. This list is then used frequently to run different reports showing metrics related to these targeted individuals.
To avoid the situation you are facing, a generic table was created in the warehouse indicating the key value for the individual, the name of the user whose list it was, a list name identifying the content (e.g., Customer Segementation Fall 2010). ETL processes load these items from files provided by users.
This table is joined in the Framework Model so the result produces the filtering you're after simply by allowing the user to select a list name. Security can be set up so users are only permitted to the lists they own.
This allows the parameter list to be re-used as needed and also preserves the values for any troubleshooting or subsequent list modifications. Not to mention eliminating the need to get around the Oracle limitiation...
Hi,
I think you better to build a table with theses parameters It will be more efficient and if you want to add an other 2500, 5000 in the future you will be able too. amg.... lots of parameter.
A join like tableA.col1 = table_param.col1(+) or left outer join if you prefer, will allow you to have all the parameter and the result associate.
Thanks .