When you create a parameter field in Crystal Reports you can create a “pick list” of values so that the user can select a value rather than typing the value. If your pick list is a series of codes you can also add a second column of values to provide a description for each code. So your pick list might look like this:
A – Adjustments
C – Credits
D – Debits
The challenge comes when you want to display the user-selected values on the report. A single-value parameter can be displayed by placing it on the page header. A multi-value parameter needs formula #7 on my web site’s formulas page. But in both of these cases only the user-selected codes will be displayed, not their corresponding descriptions. Crystal doesn’t provide a way to display the descriptions, except when the user is selecting their values. But there are several workarounds:
1) If the values are available as a column of data in the report or in one of the report’s tables you could construct a cross-tab to list the codes and values in the report. The code could be the Row field while the description could be the summarized field. You could even turn off all of the grid lines in the cross-tab so that it looks like a list. The only downside of this simple method is that the list will display the chosen values only if they also appear in the report. So in the example above, if you had picked A, C and D but there were no Adjustments that month, the cross-tab would only show:
C Credits
D Debits
2) Another option is a subreport. You can use this if the codes and descriptions are available in a table or command. You could pass the original parameter to a parameter in a linked Subreport and create the list of codes and descriptions inside that subreport. If this is a dynamic parameter you could use the table or command that the parameter is using. If this is a static parameter and you don’t have a table that contains those values you could always create a table by exporting the static pick list to a file and importing that into a database (even Excel or Access would work) and then creating a linked subreport to read list from the new table.
3) If the values can NOT come from a table or command you will have to duplicate the literals of the parameter in a formula. For a short list you could do something like this:
(If "A" in {?parm} then "A - Adjustment" & CHR (13)) &
(If "D" in {?parm} then "D - Debit" & CHR (13)) &
(If "C" in {?parm} then "C - Credit" & CHR (13))
For a long list you might consider creating 2 arrays (codes and descriptions) and create a For Loop to see which are used and then append them into the display string, but that would be my last resort.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)
I’ve been waiting for a built-in function for this since the v6 days!
Nice work-around/bodge, but I’d still kill for a description({?MyStringParam},”OptionA”) kind of function….
Is there anywhere we can suggest future changes/enchancements directly to BO?
PS: Cheers, Ken – been reading you for something like 7 years now!
bettername,
Apparently something like this has been added in CR2008 SP5 and CR2011 SP5 (or maybe SP6). I tested it and got a single value parameter to show me the description by dragging it onto the report. I have read that the join function function should also pull in descriptions, but I couldn’t get that to work in CR2008 SP5.
And thanks for being a loyal reader. Hard to believe I have been writing the newsletter since 2001.