Three-level cascading parameter that duplicates/drops values

Monday 25 February 2008 @ 1:02 pm

This one is hard to understand or even describe without an example but here is the general description: A three-level cascading parameter will work fine as long as all of the values in level 2 are unique. However if any values in level 2 can be found in more than one top level group, then you will likely lose values in level 3. Like I said, an example is needed. Say you used the following 8 records to create a three-level cascade for Vendor, Category and Model:

Dell / Notebook / Model_A
Dell / Notebook / Model_B
Dell / Desktop / Model_1
Dell / Desktop / Model_2

Compaq / Notebook / Model_X
Compaq / Notebook / Model_Y
Compaq / Desktop / Model_8
Compaq / Desktop / Model_9

Your first choice would be Dell or Compaq or both. Your second choice would be Notebook, Desktop or both, and then you would pick a model. If you picked Dell then Notebook you should only see models A and B. If you picked both Compaq and Dell and then selected Notebook you would expect to see all 4 Notebook models ( A, B, X and Y ) – but you wouldn’t in Crystal versions 11.5 or 12 (also know as XIr2 and CR 2008). You would only see two of the four Notebook models. If you select Compaq before Dell at level 1 you would see X and Y. But if you you select Dell before Compaq you would see A and B.

Apparently this stems from a “fix” for a related problem in version 11 (XI). In version 11 when you selected both Compaq and Dell you would see Desktop twice and Notebook twice, once for each vendor. But there was no way to distinguish between the duplicates, as explained in a BO knowledge base article. Apparently they fixed the problem by simply dropping one of the duplicates, which is fine if that is the end of the cascade. But any downstream values tied to that duplicate are lost.

Hopefully this will be fixed but for now there is one workaround that I have found. You use a command to feed the dynamic parameter and replace the duplicate field with an expression that combines the duplicate field with a portion of it’s parent value. So the data above might look like this:

Dell / Notebook-D / Model_A
Dell / Notebook-D / Model_B
Dell / Desktop-D / Model_1
Dell / Desktop-D / Model_2

Compaq / Notebook-C / Model_X
Compaq / Notebook-C / Model_Y
Compaq / Desktop-C / Model_8
Compaq / Desktop-C / Model_9

This works in all versions because it ensures that all the category values are unique across vendors. If you want to see all Notebooks you would select both vendors and then select Notebook-D and Notebook-C in the second level. A few extra clicks but at least this works today. If you need help writing a command or a concatenation expression you might find the Expert’s Guide to SQL Expressions, Options and Commands helpful.

And, thanks to Rob Hershfield of United Data Strategies, Inc. for discovering this problem and sharing the original KB article.

Leave a Reply

Recrystallize Pro