Tutorial 4-7. Mixing Static and Dynamic Parameters
In the discussion about DCPs, I said that Crystal Reports doesn’t allow you to mix static and dynamic parameters. If you want to use DCPs, then every prompt within the prompt group must be dynamic (i.e. pull its data from the data source). The problem with this is that if you want to customize the list of values presented to the user, then you are limited to using the data already in the data source. There are times when this isn’t going to work for you. For example, when I originally wrote Tutorial 4-5 I wanted to show how to replace static parameters with DCPs. But I ran into a problem – in the earlier tutorials I only let the user pick from the countries Canada and USA. I had to change the report requirements around a little bit because using DCPs required me to show the user every country in the data source. Ideally, I would have been able to use a static list of values for the country and then followed up with a dynamic list of customers based on the country that the user selects. But Crystal Reports doesn’t let you mix static and dynamic parameters and I had to modify the tutorial accordingly. Fortunately, there is a way to get around this rule by simulating static parameters using Command objects.
Command objects are discussed in more detail in Chapter 11, but for now you need to understand that a Command object is a custom SQL statement that you write manually. By getting creative with SQL statements, we can filter out certain data we don’t want from the list of values before it is shown to the user. Very cool!
There are two criteria for using a Command object with DCPs.
- All levels for the entire DCP must be derived from a single Command object. Each field in the DCP must come from this one Command object.
- The Command object must not be linked to the other tables in the report. It has to be completely independent of the report data.
To create a Command object, select the menu items Database > Database Expert. Find the database and click Add Command. This opens a text area where you can type in your SQL statement. After you type in the SQL statement, click the OK button to save it. You can now reference the Command object from your DCPs and use it as the source for the list of values.
The one thing to be careful of is that after creating the Command object, Crystal Reports will try to link it to an existing table in the data source. If this happens, delete the link. To use the Command object with DCPs, you want it to be completely separate from the other tables. The second thing that will happen is that Crystal Reports will give you a couple of warning messages that it doesn’t recommend using tables that aren’t linked to the other report tables. Just ignore these messages and click OK a couple times to make them go away.
To solve the problem I had in Tutorial 4-5, where I just wanted to list the countries Canada and USA, I’ll create a SQL statement that selects the same customer data from the report but this time it will pre-filter all countries except Canada and USA.
In this SQL statement, I selected the three fields to be used by the DCP prompts. I use the WHERE clause to specify that I only want data with the countries Canada and USA. When I create the DCP, I only use these three fields for the prompts and the report works just like I want it to.
You have to be careful when using Command objects so that you don’t accidentally select a field from the report’s data source instead of from the Command object. The reason this is easy to do is because the Command object will have some of the same fields used in the report and the Command object is always listed last. Thus, it is easy to mistakenly pick a field from the report’s data source, since those fields are shown first. DCPs do not support mixing fields from the live data source along with fields from the Command object. If you find that the list of values in your DCP is missing data or it looks unusual, then go back and make sure that all the DCP fields are coming from the Command object.
