Picklist Limit Problem

[anumala28]

Hi,

I am facing problem with Picklist data limit. In Impromptu we can show data in the prompts up to 50,000 or 60,000 recrods. I hope Picklist list maximum limit is 50,000 or 60,000.

But i need to show more than that in the prompts, How can i show all data in the prompts. Because One view has data 300,000 records. User wants to see these data in the prompt.

How can i solve this promblem? I am using catalog picklist in the report. Like this we have many reports. Any soluation to display all data in the prompts or i have to follow any different logic.

Please help out.

Thanks,
Sri

 

[griffindm]

Sri,

Why would you want the end user to wade through 300K picklist entries? Why not try a cascading picklist approach and let the end user pick some other higher-level attribute first and then just show them the valid values for that attribute?

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[anumala28]

Hi Dave,

Because users are used Access Report like that. So They need like that in impormtu also. I can use Cascading prompts, but here the situation is not like that, they have to apply filters from that Catalog picklist only.

I can tell to them the picklist limit this much(i.e 50,000 etc). But they are asking Access report is giving that kind of facility, why is not possible in Impromptu???

Please give any soluation if you have,

Thanks,
Sri

 

[kajarekar]

Hi Sri,

Have you read replies to your thread401-662505 ?

 

[kajarekar]

Sorry Sri,

I noticed afterwards that you have read the earlier posts. There I had specifically mentioned that picklist limit is 65000. I got confused because you have posted "I hope Picklist list maximum limit is 50,000 or 60,000."

To me also, Dave's suggestion seems to be the only way of handling prompting requirements exceeding 65,000 values.

Vivek

 

[anumala28]

Hi all,

I try to follow the Dave's suggestion , but higher-level attribute(Customers) have 300k data, So How can I solve this problem?? Please give any other way to show the all 300k data for users.

Thanks,
Sri

 

[griffindm]

Sri,

What is meant by "higher-level attribute" is some other data that is significant to the end-user, but can be used to present a subset of the total number of attributes you wish to filter on in the final report. For example, if I have 60,000 projects that all have a customer id assigned to each one, but only 2,000 distinct customers, I could ask the end-user to select the customer first (from a picklist of 2,000 items), and then show just the projects for the customer selected.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[anumala28]

Hi Dave,

Main filter column is Customer No. But Customer Master data has around 300k data. User want select multiple values from the prompt. But i can't show all Customer No in the prompt.
If i use cascading also i have to show first Customer No only, there is no other column to filter first. But dats is 300k data.

If i use type in promptu, end-user can't select multiple values.
I hope you understand my problem.
Is there any way to do this problem??

thanks
Sri

 

[BruceReed]

Sri

How about selecting customer number from a range 1st?

ex. 1 to 10000, then have have the filter show only these 10k customer numbers, then 10000 to 19999 for the next 10k, etc, etc.

Would this be helpful to you? If you had customer names I would suggest filter by 1st letter of name.

Bruce

 

[anumala28]

Hi
This logic helpful, but how should i implement this logic, i have create report picklist or catalog picklist?
If i use catalog picklist how should i filter first 10k records???
If i use report picklist i have create 3 or 4 etc., picklist files.
But we are using catalog picklist. How should i go with catalog picklist?
Please help out.

Thanks,
Sri

 

[CognosProfessional]

It still seems odd to me to try to weed through that much data for a prompt. However, if you must, I would use Cascading Prompts with Bruce's idea. Otherwise, play around with creating a calculated column or filtered columns in the catalog for picking. Does each user have access to all the 300K items? or are they apexed down to a manageable level when user is prompted? Use the "user classes" to weed through groups within catalog. Nevermind. I think I know the answer to that. Are various users picking the same ones routinely or always picking different items for their reports? I don't understand why pick from 300K items in a prompt. ...Good luck to you.

CP

 

[BruceReed]

Sri

It depends on how the numbers are in Impromptu, are they consecutive, or random? I would use a calculated field to find what "group" it is in.

Can you use another attribute to filter as CP suggested? It seems to all of us, very laborious to weed through 300,000 entries on a pick list. It would seem to be better to eliminate a huge amount of them by cascading filters. This way the list would be more manageable to look at.

Would the end user always look at the same type of customer? Does this customer belong to the same "class" as the other picks? If yes setup this customer breakdown, by region, country, product group, sales person or what ever makes sense for you. It should be easy to come up with this data, most companies group customers (especially 300,000 of them) by some criteria.

Good luck
Bruce

 

[anumala28]

Hi all,

If i go for level of columns or cascading or cascading, If i filter the data using First level column, I am getting customers more than 50,000 customers. Not only customers we have 2 more columns like this. Like this we have many reports around 100 reports.
Is it possible to show the data using Power Prompts?

If any suggesstions please help out.

Thanks,
Sri.