Dynamically assign default value for a parameter

[mdtimo]

Can you dynamically set the default value for a parameter.

I have a list of dates (the first date of the month going back to 1980) that I want the user to select from and I want the default value to be the first day of the current month.

Now I have to change the report each month to the current month.

 

[BettyJ]

When you create the parameter, set the default value of the parameter to a date, say 1/1/1900.

Create a formula
@DateSelected
[tt]datevar startdate;
if {?Date}=date('1/1/1900') then
(
startdate:=Date(Year(currentdate), month(currentdate),1)
)
else
startdate:={?Date};
startdate;[/tt]


In Record Selection Formula, add
[tt]{Table.DateField}={@DateSelected}[/tt]

Hope this helps.

 

[hilfy]

No, you can't dynamically set the default value for a parameter. However, here's another possibility:

1. Add an additional static Parameter called {?Current Month}, valid values are "Yes" and "No". Default it to "Yes".

2. Default the dynamic date parameter to 1/1/1900.

3. Modify the formula in the Select Expert to be something like this:

(({?Current Month) = 'Yes' and {MyTable.MyDateField} = Date(Year(CurrentDate), Month(CurrentDate), 1))
or
({?Current Month} = 'No' and {MyTable.MyDateField} = {?My Date Param}))

Please note where I've put the parentheses - these are essential to getting this to work correctly.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[PeterDimitrov]

BettyJ and hilfy gave you a good workaround. If you are using Crystal 2008 and above you can use an optional parameter and leave it empty instead of using 1/1/1900. Crystal 2008 provides a function HasValue, which can be used to find if the user set a value in an optional parameter. If value is not set use the current month.

A report could be used in different scenarios and by different applications. The logic to set dynamic values should be part of the application, which is running it. What if one user is running the report by month and another one by week ? Or look at more common case: your report is showing data for a period and you want to be able to run it for one of the previous years, quarters, months or weeks and even YTD. If the dynamic logic is in the report you will need 5 different reports , which will retrieve and visualize the data in the same way , but will set default values differently. If the logic is in the calling application - viewer, Crystal server etc. you can set the values based on the user input without creating multiple reports . You can see a sample video for this approach here:

http://www.r-tag.com/Pages/Preview_Demo.aspx.

You need to scroll to 14:45 or to select “Parameter templates” from the menu. I am posting this here because the viewer used in the video is free. It is part of R-Tag Community edition:

http://www.r-tag.com/Pages/CommunityEdition.aspx.

Feel free to use it if this can be a solution for your case. BTW the Community edition provides also a free reports scheduler so you might be able to automate the process.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[mdtimo]

The first two suggestion don't seem to impact the drop down list at all. The default value for the drop down is not impacted.

My parameter has an associated list of dates that the user sees in a drop downlist. I want the user to pick from this list of dates. But I want the default value for this list to be the first day of the current month.

 

[PeterDimitrov]

There is no way to set dynamically the default value. The first 2 suggestions are trying to resolve the problem by moving the dynamic logic to the processing of the parameter's value because this is the only possible thing with Crystal designer/viewer.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[BettyJ]

Where does the associated list of values of the parameter come from?

 

[BobSuruncle]

You could try building a Command to drive the parameter.
This Command would be in addition to any tables that you have added to the report, and would not link to the other tables in any way.
Crystal will warn you that you have more than one datasource when you close out of the Database Expert dialog.

Here is a SQL statement for Microsoft SQL Server in order to generate the first of the month:

select CAST(CAST(year(GETDATE()) as VARCHAR) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR) + '-' + CAST(1 AS VARCHAR) AS DateTime)  AS FirstOfMonth

In the report, create a parameter, and make it Dynamic.
Point the parameter to the Command.



Bob Suruncle

 

[mdtimo]

The list of dates comes from a table in the database. The first of every month will always be in this table. My hope is that the user won't have to scroll through many dates to find the date when in most cases the date they want is the first of the current month. I want them to have the flexibility though to run the report for any date.

Based on what PeterDimitrov said this is not possible. I have to manually update the default date rathering that having it set dynamically.

Bobsuruncle's solution shows me how to get a list but doesn't state how I can create a command and define in the command what is the default date.

 

[PeterDimitrov]

I said that what you want is not possible with Crystal designer. It is possible with other viewers and I gave you links to a video and a free viewer that you can use. If you are writing your own application you can implement similar method to set the default value to a date relative to the current date.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[BettyJ]

Following are the steps to create a SQL command:
In the Database Expert, under Current Connection, below your database name click on Add Command. You will get a window where you can type the SQL statement.

[tt]Select [Your Table Field] FROM [databasename.TableName] WHERE [Your Table Field]>=trunc(trunc(sysdate,'MM'),'MM')[/tt]

I am not an expert in SQL. Maybe someone else can post a better SQL.

Create a new date parameter and in [tt]List of Values[/tt], select Dynamic. Then under [tt]Choose a Data Source[/tt] click [tt]New[/tt] radio button. Click the [tt]Insert[/tt] button. Under Value, select [Your Table Field] under the [tt]Command[/tt].

You still cannot set the default to the first of the month. This will only list the dates from the first of the current month in the parameter list.

Hope this helps.