Monday, February 20, 2012

Record selection formula with Is Null

Hi all ,
Can any one correct my Record selection formula.
if IsNull ({?Start}) and IsNull ({?End})
then
{Query1.Completion Date} in CurrentDate-3 to CurrentDate-1
else
{Query1.Completion Date} in {?Start} to {?End}What if only one of the dates is set?

( IsNull({?Start}) and IsNull({?End})
and {Query1.Completion Date} in CurrentDate-3 to CurrentDate-1
)
or
( Not(IsNull({?Start}) and IsNull({?End}))
and {Query1.Completion Date} in {?Start} to {?End}
)

It's sometimes easier (if only to read) to create a formula for each parameter and use the formula in the record selection.
e.g. formula @.Start
if IsNull({?Start}) then CurrentDate-3 else {?Start}

e.g. record selection
{Query1.Completion Date} in {@.Start} to {@.End}

You might want to check how/whether this affects what's sent to the database (e.g. is the where clause set?)|||Hi Jagan,
Thanks for reply,
I have a report which will be schedule on every Monday at 1 am.
In this report they want Prev Friday to sunday data.So i am using date range
"{Query1.Completion Date} in CurrentDate-3 to CurrentDate-1"
this is the formula I have in my select expert.But some times user want to give his own parameters.So i want create a parameter that by default it has to bring data for prev Fri to Sun and if user select any date range then it has to bring data for that particular range dates..
I know this one we can create using stored procedures is there any other way with out stored procedure.

No comments:

Post a Comment