CR XI - Dynamic Cascading prompts 1

[rrgkanth]

Hi,

I am having trouble with dynamic cascading prompts. I have a table which has only addresses. I am trying to create a dynamic cascading prompt for
Country->State->City.
All of the above 3 are in different columns in my address table.
Country->State works fine. The moment I bring in City, no values show up in Country, and therefore I have no means of selecting any parameter values.
Anyone knows why this is happening?
just for info, my table has more than 3 million records.

Please help asap.

Thanks.

 

[lbass]

Have you verified that, for every city, country is always populated (not null)? How did you go about setting up the parameter? Did you add all three parameters in the same parameter setup screen (country, then region, then city)? What is the record selection formula you are using?

-LB

 

[BOBJProduct]

It sounds like you might be running into the default record limit for DCPs. To prevent the system from generating a huge number of values for a single combo box, there's a default limit of 1000 entries per level in the LOV. The system will stop reading records for an LOV once it reaches 1000 records for any given level.

To control this limit, follow this procedure:

http://support.businessobjects.com/library/kbase/articles/c2017238.asp

I'd recommend that you use a stored proc or command object as a datasource for your list of values - then you can do a 'SELECT DISTINCT COUNTRY, STATE, CITY' on the datasource and instead of CR filtering through 3 million records, it would only have to read the unique combinations of Country, State, and City.

If you do this then all the combinations of country, state, and city will be populated on open of the report.

Note that a bug in stored proc/command object support is fixed in CR XI R2. See thread for more information on the bug and solution:

http://www.tek-tips.com/viewthread.cfm?qid=1157169&page=1

Thanks, Blair

 

[rrgkanth]

Thanks Blair,

What you put in here helped me. The problem was with the 1000 rows limit. I used ADO Commands, but inspite of that it was truncating my set of values. So, I increased the max rows limit. Is it alright to go on increasing that limit as per our need? How badly will the performance be affected?

Thanks,
Rrgkanth.

 

[rrgkanth]

Hi,

I seem to have run into trouble again with these cascading prompts.

I have a Union query in my ado command which returns more than 20000 rows. I have set the registry value for limiting dynamic parameter values at 2 million.

Inspite of that I am my last parameter is truncating values. I am quite clueless on what I can do next.

Please help.

Thanks,
rrgkanth.

 

[bostonbelle]

I am actually having the same issue but my value list has only 167 rows and the prompt is only showing 37!

I went in and added the registry entry that was in one of the knowledgebase articles...to no avail.

help!