In one of my recent projects, I designed a bunch of reports using SAP Crystal Reports 2008 tool. In some of reports, I wanted to have a value like “sysdate”, “the first day of previous month”, or a formula of some kind like CurrentDate() plugged into that parameter as default for user convenience and for report scheduling onto InfoView server. Unfortunately, it doesn’t work. I have seen this question come up frequently on BOB for a long time but it seems SAP has no plan to support it in the near future. In this post, I’d like to share the work-around that I’ve used in the project. I create some kinds of placeholder to set as the default and some creative logic within code to do the substitution after the user has already responded to the prompt.
Problem Description
I have a report. The report has a date prompt. I want the date prompt to have a default value (partially possible) and I want the default value to be “today” instead of a standard date. The request is quite reasonable, in my opinion. But Business Objects does not think so. Prompt handling has been one of the weakest points of the tools for a long, long time. What you fill in the text input behind “Default Value” below will be parsed into a standard date: 2012-3-27, 2012/3/27, 3/27/2012. If the parse fails, it will be set to blank. Therefore, there is no way to specify a dynamic date which needs calculation as the Default Value. Any value entered for that parameter is treated as plain text data, and no formulas or calculations will be evaluated.
Defining the Prompt
Based on the various data sources a Crystal Report consumes, there are three ways to create parameters: Table Link, Command Object, and Stored Procedure. Consequently, we will need to deal with the parameter default for the three cases. I will be talking about them one by one below.
Table Link
Right-click the “Parameter Fields” in the “Field Explorer” view and select New… from the contextual menu to create a parameter. This is the standard way to create it. In the pop-up “Create New Parameter” window, I am able to set “Default Value” as blank and set “Optional Prompt” as “True”. Input the necessary information for the parameter and click “OK” to save it. The prompt is created properly.
As I mentioned above, the blank Default Value is just a placeholder. I design an additional formula field to receive the parameter value to do the substitution afterwards.
Right-click the “Formula Fields” in the “Field Explorer” view and select New… from the contextual menu to create a formula – {@Start Date}. Function HasValue is used to check if {?Start Date} is blank or not. Set it as currentDate if it is blank as default, or use the actual date specified, otherwise.
if not HasValue({?Start Date})
then
currentDate
else
{?Start Date}
Now, I am able to use {@Start Date} as a Record Selection or whatever I want in the report. With an optional parameter and a formula built on it, a parameter with dynamic default value works. The above is a simple sample; feel free to modify the formula to meet your needs. At the same time, revising the Prompt Text to be more readable is better below:
Command Object
If I am using a Command Object as the report data source, and at least one prompt is required within the Command Object, what can I do for the prompt default? Is there anything different with the above case? Yes, definitely it is.
Let’s start to create a prompt within the Command Object:
Save it, and review it from Parameter Fields in Field Explorer section:
To be continued
