When selecting which field to display, it’s important to choose the proper table. As we discussed earlier in the chapter, some tables have a complete list of all the available values and some tables only show the values that have been selected as part of another record. This second list is most often incomplete. For example, the Customer table will list every customer your company has. But the Invoice table will only show customers with a current invoice on file. The Customer table will be complete, whereas the Invoice table will probably be missing values.
The table you choose has a big impact on the prompts. For example, if you select the Customer table, then the user will be able to see and choose from a complete list of customers. But this list could be quite large and it will take longer for the user to find the customer they want to print. Selecting the Invoice table makes it easier for the user to choose a customer because the list will have fewer entries. However, the user might still want to see the customers with no current invoices so that he can print a report that shows the user had no activity for the month. This can be just as important as showing which users do have activity. If you would have shown only the list of customers with current invoices, then this wouldn’t be possible. This shows that there can be a tradeoff between speed and thoroughness.
Another possible point of confusion is that if you select the Invoice table, then some users might get confused when seeing that a certain customer isn’t in the list. They might wonder if the customer is no longer working with the company or if it’s a new customer, then they might question whether the report has all the current data. So you have to consider the goals of your user before deciding which table will populate the list of values. Again, a properly phrased Prompt Group Text can help prevent any possible confusion from your users.
After selecting a field for populating the list of values, you can also select a field for showing a description within the list of values. Displaying a description is useful for fields whose values wouldn’t make sense to the layman. For example, rather than displaying a cryptic Inventory ID, you could display the Inventory Name as the description and the user immediately knows which inventory item to choose.
The last column is the Parameters column. This is used for creating a parameter that references the user’s selection. Since there are DCPs involving multiple prompts, each one can be assigned its own parameter. This parameter can be referenced anywhere within the report. The last prompt is required to have a parameter associated with it, but not the prompts leading up to it.
The primary purpose of DCPs is to use the selection from one prompt as the filtering criteria for the next prompt. The final prompt will have a significantly simpler list of values for the user to choose from than if DCPs were not used. This implies that the only prompt that would be used on the report is the last prompt. Thus, only the last prompt needs to have a parameter field associated with it so that the report can be filtered on it. However, this isn’t always the case. There are times when the report will need to reference every value that the user selected within this prompt group. For example, if the DCPs use the following order of fields: Country -> Region -> City, then it’s very likely that in addition to filtering on the City selection, the report will print out the Country and Region the user selected as well. To achieve this, you need to have a parameter associated with each prompt level.
By clicking on the third column, the text within the cell changes to [?] My Parameter and a parameter is automatically created to store the value that the user selected with this prompt. If you decide later not to use a parameter then you can click on the cell again and the parameter is removed.
There are times when you want a parameter to be actively linked to the data source, but you don’t need to use multiple prompts. To do this, create a DCP that only has a single prompt. It doesn’t have the cascading property associated with it. This is a big improvement over the static prompts that you created earlier in the chapter.
The last prompt in the group must always be associated with a parameter. If you forget to click on the third column to create a parameter for the final prompt, then Crystal Reports will do it for you when you click the OK button to save the parameter. This makes sense for two reasons. The first being that if you didn’t want to assign a parameter to any of the prompts, then there would be no reason to create DCPs in the first place. The second reason is that since the ultimate goal of DCPs is to create an optimized list of values for the last prompt, then this is obviously the prompt you will want to use in the report.
After entering all the prompts for the parameter, you can set the options in the grid at the bottom of the dialog box. These options are the same as what was already discussed for static parameters. They let you set whether it stores discrete or multi-value parameters, allows range values, etc. What makes these options unique is that each prompt gets its own set of options. If you remember from the discussion of static parameters, the options normally apply to the current parameter. Since DCPs create a prompt group within a single parameter, this is almost like having multiple prompts. Thus, each prompt gets its own set of options. For example, the first prompt could be a discrete value and the second prompt could be restricted to a multi-value.
By combining a combination of parameter options with each prompt group, Crystal Reports lets you create sophisticated prompt groups. But you need to understand how these features work together and what the limitations are. The first thing to be aware of is that not every option is available for each prompt. The final prompt always gets access to all the options. But the prompts leading up to it must be discrete values. They can’t use range values. However, you are allowed to have multiple values associated with each prompt. In summary, all prompts prior to the final prompt can set all the options except for allowing range values. The final prompt is allowed to use range values. Figure 4-18 shows a prompt group using the fields Region and City.
Figure 4-18. Setting options for the first prompt in a prompt group.
There is a strange behavior with DCPs that isn’t applicable to static parameters. After you click the OK button and save the DCP, you can’t go back and edit the fields associated with each prompt. You are literally locked into using those fields with that prompt group. If you wish to change the fields associated with the value or description, you can’t do it. Instead, you have to click on the New option and rebuild the list of prompts from scratch.
Question: My field has over 200 unique values in it, but the DCP is only showing 30 of them. Is there a limit to how many records a DCP displays to the user?
Answer: Yes, there is a limit to how many records are displayed in a DCP. To conserve resources, Crystal Reports reads a maximum of 1,000 records from the database. If there are a lot of duplicates in the table, then considerably fewer records are displayed. To get around this behavior, you need to add a Windows registry entry that tells Crystal Reports to override the internal default value of 1,000 records.
On your computer, select the Windows menu options Start > Run. Enter RegEdit. Create the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\ DatabaseOptions\LOV.
If you are using XI R2, then change the 11.0 to 11.5. Add a string value called MaxRowsetRecords and give it a new value for the maximum number of records to read. I suggest you make it very large so that no data is missed.
