Order of values in cascading prompts changes subsequent values

[elsenorjose]

Hello everyone,

I've inherited a report that uses a SQL command to create a list of values for a group of dynamic cascading prompts.

The first parameter is Product with values, we'll say, Product A through Product Z.

The next parameter is Country with values Country A through Country Z.

The final parameter is Status with values Status A through Status E.

If you run the report by selecting Product A and Product B, the Country parameter correctly populates with Countries in which those products are distributed. However, the Status list only displays the Statuses that are valid for Product A. If you re-run the report and select Product B THEN Product A, the Country list populates correctly as before, and now, all Statuses for Product B and Product A display. It appears that the Status list is populated based on the first Product selected since Product B has more Statuses than Product A -- it has all the Statuses associated with A as well as all Statuses associated with B.

Is there a reason why this would happen? I am still familiarizing myself with the SQL command but it looks like it is written correctly. All the data in the report is being placed in the details section and there is no grouping.

We are using Crystal Reports XI on Oracle 10.2.0.3.0

Thank you.

 

[lbass]

Dynamic cascading parameters are limited to 1000 resulting values, so you are only seeing partial results, depending upon your selections. There is a way to increase this in the registry, but you would have to do this with caution.

-LB

 

[elsenorjose]

Hi lbass,

I read about that before and thought that might be the case, but what I don't get is why changing the order of the first parameter would make a difference. Is the limit based on the sum of all the parameters, or is it multiplicative? By selecting just 2 products in step 1, the subsequent lists don't exceed 1000 values combined. As soon as I select the first 2 products, the resulting combination of countries and statuses is exactly 214. Further filtering by a particular country, I get 8 statuses. I'm digging further into the LOV SQL command to see if there is an error upstream.

Jose

 

[lbass]

Okay, I should have read more carefully. I wonder whether the status fields can be null--that might stop the generation of the entire list.

-LB

 

[FireGeek21]

You mentioned, "why changing the order of the first parameter would make a difference". Are you talking about sort order?



FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[elsenorjose]

I checked that too. I ran the SQL for the LOV directly and there are no NULLs in any of the fields -- Product, Country, or Status.

I tried recreating the report using a view which has all the same fields and created the dynamic cascading prompt directly off the field values and even then, if I change the order of the selection of Product, it affects the Status list. Interestingly, it doesn't affect the Country list, probably because most products are distributed in all countries but basically, the SQL for the LOV is only populating the subsequent parameters lists based on only the first product selected.

I suggested using static prompts for each of the fields but since not all products will have all statuses, the users do not want to be presented with a list of statuses that might not have any correlation to the product(s) selected.

I'm really stumped on this one!

 

[lbass]

What happens if you change the order to Products, Statuses and then Countries. It seems like the logic is Products are limited by Statuses and by Countries, but I'm not sure the logic from Countries->Statuses works, since it is really about Product, not Country. Or what if you concatenated the products and statuses for the first prompt and then added Country as the second?

-LB

 

[elsenorjose]

I'll give that a try. I'm still familiarizing myself with the relationships (I inherited the report yesterday!) so I'm open to anything. How would I concatenate the Products and Statuses in the first prompt?

Thanks again.

Jose

 

[lbass]

Depends upon your database. In the command, in Oracle, it would be:

"table"."product"||"table"."status"

-LB

 

[elsenorjose]

Oh, ok. Got it.

Thanks.

 

[lbass]

This probably isn't a productive path to go down though. I think it should work as you originally thought.

-LB

 

[elsenorjose]

I've gotten the go-ahead to try and make the parameters static but with database values displaying. I know how to append all database values from the LOV, but will these values update as the database updates? If more products, countries, and statuses are added, will they automatically be reflected in the parameters lists?

 

[lbass]

No--that's why they are considered static. You have to periodically update the lists.

-LB

 

[elsenorjose]

Thanks. That shouldn't be an undue burden on future developers.