Command with Dynamic Parameter

Monday 20 November 2006 @ 10:42 pm

Crystal Reports introduced Command objects starting in v9. They allow you to write your own SQL to generate the data for a report. You can even add parameters that allow you to replace text directly in the SQL at runtime.

Dynamic parameters were introduced in v11. They allow the parameter pick-list to be generated by a database query at runtime. However, if you change a command parameter to be dynamic you will get some funny behavior, at least in XI Release 1.

I created a report using a command with one parameter. I then added a normal table to the report. The table is not linked to the command and none of its fields are used in the report. I then changed the parameter (through the report parameter screen) to be dynamic, using a field from the unlinked table as the source. It seems to work fine with one quirk – I get prompted twice for the parameter. If I change the parameter back to static I only get prompted once.

The first prompt seems to come directly from the command. It shows me the static default value I used in the command parameter. Any value entered here is ignored by the command. The second prompt shows me the dynamic parameter value list from the table. The value selected here affects the data selected in the command.

So it works, if you can ignore the extra initial prompt. Note that this works correctly (one prompt) in XI Release 2 so if this is critical to your deployment you might want to upgrade. See my BLOG article on XI R2 for free upgrade instructions, and cautions.

Leave a Reply

Recrystallize Pro

Crystal Reports Server