Creating mixed parameters 1

[rock786143]

I would like to create mixed parameters in a report.
I should be able to select the date i want when i'm scheduling report. This i can create with out any problem.

But i want to schedule the report on recurring basis daily. The default date should be "currentdate-1". If i want to change it , should be able to select dynamically. So its combination of static and dynamic parameters. How do i achieve this?
Any suggestions?

Thanks for your help..

 

[briangriffin]

You'll lose the ability to select a date from the calendar control - sometimes that's a good trade, sometimes not.

Make your parameter a string datatype, add 'Yesterday' to the value list, make Yesterday the default value, and leave Allow Custom Values = true.

Change your record selection to incorporate this:

if {?Date} <> "Yesterday" then yourdbfield = date({?Date}) else yourdbfield = currentdate - 1

If the user leaves the parameter alone, or if you schedule it with the Yesterday parameter value, you'll get yesterday's data. If a user refreshes the report but instead enters a valid date, such as 8/1/2010, then that date value will be used.

You'll probably want to modify the prompt text to let the users know what formats are allowed for a custom date.

 

[rock786143]

Thanks for your tip. It was very helpful.

 

[rock786143]

How do i do it for week and months durations?

Suppose weekly reports scheduled to run every Monday. I want display (date of Monday) to (date of sunday) records. Should be able to select one week date range. And default should be lastfullweek.

For month also similar.

Will string parameter work? am getting date expected error in record selection formula.

 

[briangriffin]

My understanding is that you want the user to be able to run the report for Yesterday, Last Full Week, and Last Full Month, unless they enter a specific date in which case you want either that day, the week where that date is the ending date, or the month where that date is the ending date.

Consider two parameters - first one called Report Type, with values Daily, Weekly, and Monthly. Change Allow Custom Values to false.

The other parameter is the one you already have, I'll call it Date Range. Add the values Yesterday, Last Week, and Last Month. Allow Custom Values should be true.

Then incorporate a formula such as this into your selection criteria:

if {?Report Type} = "Daily" and not ({?Date Range} in ["Last Week","Last Month"]) then
    if {?Date Range} = "Yesterday" then {yourdatefield} = currentdate - 1
    else
    {yourdatefield} = date({?Date Range})

else

if {?Report Type} = "Weekly"  and not ({?Date Range} in ["Yesterday","Last Month"]) then
    if {?Date Range} = "Last Week" then {yourdatefield} in lastfullweek
    else
    {yourdatefield} in date({?Date Range}) - 6 to date({?Date Range})

else

if {?Report Type} = "Monthly" and not({?Date Range} in ["Yesterday","Last Week"]) then
    if {?Date Range} = "Last Month" then {yourdatefield} in lastfullmonth
    else
    {yourdatefield}  in dateserial(year(date({?Date Range})),month(date({?Date Range})),1) to date({?Date Range})

So if the user selects report type of Daily, and leaves the parameter as Yesterday, then yesterday's data will be returned. It works the same way with Monthly/Last Month and Weekly/Last Week.

However, if they select Daily and enter a date, then data for that date will be returned. Same thing for weekly and monthly, but the data will be for the week or month with the ending date that the user entered.

If they enter incompatible parameters, such as Daily/Last Week then no data will be returned. You may want to trap for that and display a text box in the report saying something like "Incompatible Parameter Combination".

 

[max10]

This is similar to what briangriffin has suggested.

Create 2 parameters
1. Date Type as a string parameter
Add values - Daily, Last week, Monthly, Custom Dates
allow custom values should be set at false.

2. Date Range
Create this as a date parmeter

In the Selection Criteria

(
({?DateType}='Daily' and {table.datefld}=currentdate-1)or
({?DateType}='Last Week' and {table.datefldin Lastfullweek or
({?DateType}='Monthly' and {table.datefld} in LastFullMonth)or
({?DateType}='Custom dates' and {table.datefld}={?Date Range})
)