Crystal Reports: Dynamic Pick Lists

Dynamic Pick Lists for your parameters with cViewPICK

Parameters are among the most useful objects in a Crystal Report. You can use parameters for record selection, dynamic grouping, custom formatting, and any other place where the user may need additional control over aspects of your report.

Normally, the list of values will come from the tables or views being used in the report. However there may be occasions when those tables have a large number of records or you might prefer some filtering of those field values.

With those tables one approach is to use a SQL command to build the list of values. Use the Database Expert to add a SQL command to your report. In the SQL command, build a SQL statement that selects the field values you want for your pick list.

Make sure you do NOT link this SQL command to the other tables in your report, and do NOT use any fields from the SQL command in the report itself. The only purpose of the SQL command is to retrieve a dynamic list of values for your parameter prior to selecting the data for the report.

In the parameter form (above), select the field from the SQL command and use this to populate the list of values in your report.

If you are scheduling or processing the report using Crystal Reports Server or Business Objects Enterprise, then you should use a Business View to populate the list of values. With these servers you have the additional benefit of being able to schedule these to build the list at regular intervals.

This will reduce traffic to your database.

One of the options when you create a parameter is to build a pick list to help the user select from a list of options. However, the major limitation with this is that the Pick List is then frozen in time. If your Parameter has a list of customers, then new customers do not automatically appear on the list.

We have developed a utility to help you with this. cViewPICK can be directed to a folder and it will help you maintain the parameters in that folder. It can build a list of the parameters used by all the reports and you can load a standard list of values against each parameter in all the reports it is used. 

Download an evaluation version by clicking here: cViewPICK. We have versions that support 
Crystal Reports 8.5 and below, and Crystal Reports and 9.0 and above. 

Make parameter values dynamic

If you have Crystal Reports XI or higher, you can make parameter values dynamic by selecting a Dynamic list of values, a different set of options is available. You can select a field to populate the list of values at run time. This is a feature users have been asking for many versions now.

But there is more. The list of values can come from a table or view used inside the report or a SQL command you control more directly.

If the report is managed inside Business Objects Enterprise, you must use a Business View to populate the list. Using BO Enterprise management the list can be scheduled and each series of cascading values can be scheduled at different intervals. So a field that doesn’t change frequently can be populated at a less frequent rate. And something that is very dynamic can be populated at run time.

The BO Enterprise environment also enables the sharing of the list among multiple users. The cache server retains the list for a short period so other users can make use of the same query against the database.

A final comment is that the parameter input form has been standardized to look the same to Crystal Reports and BO Enterprise users. The Windows calendar control has been replaced with an html style sheet and dates must now be entered in the format yyyy-mm-dd. risk report object.

It can summarize a large volume of data into a grid of columns and rows. Each new version of CR introduces some great new formatting features of Crosstabs, so now we have multiple rows and columns, Customized Group Names, Styles, various summary functions, and a wide range of custom formatting.

The crosstab must have a field to specify the column and row. Tony Burns wanted a crosstab where the user could select the fields to use for the columns and the rows. This can be done with the user entering a value via a parameter.

The create a formula to use the parameter to select what field to use for the column:

if {?Col1}="Customer" then {Customer.Customer Name} else

if {?Col1}="Employee" then {Employee.First Name} + " " + {Employee.Last Name} else

if {?Col1}="Region" then {Customer.Region} else

if {?Col1}="Country" then {Customer.Country} else

if {?Col1}="Product" then {Product.Product Name} else

if {?Col1}="Product Class" then {Product.Product Class} else

if {?Col1}="Product Type" then {Product_Type.Product Type Name} else "Undefined"

You could have a similar formula for the Row and even the summarized field. The structure of the crosstab report is fixed with one row field, and one column field. You could easily create other report variations with two rows and/or two columns.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.