Cascading Dynamic Prompts misbehaving?

[poujor]

Hi

I'm doing some testing on Cascading dynamic prompts. The 'issue' is found in XI, XI R2 and 2008.

Do Use the customer table from the Xtreme Sample 11.5 database (part of XI R2) - can be downloaded at

https://smpdl.sap-ag.de/~sapidp/012002523100005852352008E/cr_xi_xtreme_rep_smpl_en.zip.

Insert Country, Region and City fields in the report and get 269 records.

Now Create a Dynamic Cascading prompt using the 3 previously mentioned fields. Allow Multiple values on all 3 levels.

Use the Select Expert and set Selection Formula:

{Customer.City} = {?City} and
{Customer.Region} = {?Region} and
{Customer.Country} = {?Country}

Add all Countries, all Regions and All Cities when prompted
Now only 265 records are returned.

What is done wrong, and how can it be solved...

Regards Poul

 

[lbass]

I did a little exploring on this, and it appears that 3 (4 records) of 4 cities (5 records) in the region "Distrito Federal" do not appear using the cascading parameters above Ciudad de Mexico (Mexico-two records), Harare (Zimbabwe), Caracas (Venezuela). Brasilia does appear and the only difference I could see was that "Address 2" was populated for Brasilia but not the others. When the City parameter is removed, the record count returns to 269.

It seems to me that this might be a problem with the Xtreme database, rather than the cascading parameter functionality, but I could be wrong. Maybe you should report this to SAP/Business Objects.

-LB

 

[poujor]

Hi LB

Thank you for the reply. Now I have made further investigations too.
Selecting all Countries and only the Region "Distrito Federal" when prompted for paramtere input only returns Brasilia

It Ought to return 4 as you stated.

I think Crystal Ought to use the query

SELECT Distinct `Customer`.`City`
FROM Customer AS Customer
WHERE ( `Customer`.`Region`='Distrito Federal' ) AND (`Customer`.`Country`='Argentina' OR `Customer`.`Country`='Aruba' OR `Customer`.`Country`='Australia' OR `Customer`.`Country`='Austria' OR `Customer`.`Country`='Bahamas' OR `Customer`.`Country`='Bangladesh' OR `Customer`.`Country`='Barbados' OR `Customer`.`Country`='Belgium' OR `Customer`.`Country`='Bermuda' OR `Customer`.`Country`='Bolivia' OR `Customer`.`Country`='Brazil' OR `Customer`.`Country`='British Virgin Islands' OR `Customer`.`Country`='Canada' OR `Customer`.`Country`='Chile' OR `Customer`.`Country`='China' OR `Customer`.`Country`='Colombia' OR `Customer`.`Country`='Costa Rica' OR `Customer`.`Country`='Czech Republic' OR `Customer`.`Country`='Denmark' OR `Customer`.`Country`='Dominican Republic' OR `Customer`.`Country`='Ecuador' OR `Customer`.`Country`='Egypt' OR `Customer`.`Country`='England' OR `Customer`.`Country`='Finland' OR `Customer`.`Country`='France' OR `Customer`.`Country`='Germany' OR `Customer`.`Country`='Greece' OR `Customer`.`Country`='Hungary' OR `Customer`.`Country`='India' OR `Customer`.`Country`='Indonesia' OR `Customer`.`Country`='Ireland' OR `Customer`.`Country`='Israel' OR `Customer`.`Country`='Italy' OR `Customer`.`Country`='Jamaica' OR `Customer`.`Country`='Japan' OR `Customer`.`Country`='Kenya' OR `Customer`.`Country`='Luxembourg' OR `Customer`.`Country`='Malaysia' OR `Customer`.`Country`='Mexico' OR `Customer`.`Country`='Mongolia' OR `Customer`.`Country`='Nepal' OR `Customer`.`Country`='Netherlands' OR `Customer`.`Country`='New Zealand' OR `Customer`.`Country`='Nicaragua' OR `Customer`.`Country`='Northern Ireland' OR `Customer`.`Country`='Norway' OR `Customer`.`Country`='Pakistan' OR `Customer`.`Country`='Panama' OR `Customer`.`Country`='Peru' OR `Customer`.`Country`='Philippines' OR `Customer`.`Country`='Poland' OR `Customer`.`Country`='Portugal' OR `Customer`.`Country`='Romania' OR `Customer`.`Country`='Russia' OR `Customer`.`Country`='Saint Lucia' OR `Customer`.`Country`='Saudi Arabia' OR `Customer`.`Country`='Scotland' OR `Customer`.`Country`='Singapore' OR `Customer`.`Country`='South Africa' OR `Customer`.`Country`='Spain' OR `Customer`.`Country`='Sweden' OR `Customer`.`Country`='Switzerland' OR `Customer`.`Country`='Thailand' OR `Customer`.`Country`='Turkey' OR `Customer`.`Country`='Ukraine' OR `Customer`.`Country`='United Arab Emirates' OR `Customer`.`Country`='USA' OR `Customer`.`Country`='Venezuela' OR `Customer`.`Country`='Vietnam' OR `Customer`.`Country`='Wales' OR `Customer`.`Country`='Zimbabwe')

when returning possible cities to choose from. Running above directly as a query in Access works fine and returns all 4 cities...

So for me it seems the way Crystal builds pick-lists is faulty...

Poul

 

[lbass]

Please do contact SAP/BO with your findings.

-LB

 

[IdoMillet]

See various threads on how to increase the maximum number of rows in dynamic parameters. If you dont set a registry entry to high enough number, you will indeed see strange behaviors.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Ido,

I don't think that can be the issue here as the maximum number of records is only 269, and I believe the limit is 1000.

-LB

 

[samiam07]

I've played around with this as well. I have only 275 values in the pick list and in v2008 the prompt will display 250 in batch 1 and the remainder in batch 2. We get this behavior even after making the registry entry 2000.

It looks like something we're going to have to live with.