Formula as A Parameter? 1

[Cort]

Hello all. I'm sure this is a somewhat newbie question but I can lcoate anything in keyword search or the FAQ's.

CR 9.0

I have a date range parameter used in my record selection and I want to use the results of a formula as the defaults for that range parameter. Is this possible?

This is the formula:

{dbo_Master_Tbl.CMT_Submitted_By_Date} < CurrentDate and
{dbo_Master_Tbl.CMT_Submitted_By_Date} in WeekToDateFromSun

What I would like to happen is for

{dbo_Master_Tbl.CMT_Submitted_By_Date} < CurrentDate

to be the min default date in the parameter and

{dbo_Master_Tbl.CMT_Submitted_By_Date} in WeekToDateFromSun

to be the max default date in the parameter. Does that make any sense and is it possible?

Thanks

 

[synapsevampire]

It sounds like you want:

{dbo_Master_Tbl.CMT_Submitted_By_Date} >= CurrentDate
and
{dbo_Master_Tbl.CMT_Submitted_By_Date} <= minimum(WeekToDateFromSun)+6

It sounds like you want the current date through to the next saturday, but it's a little tricky to tell.

Rather than trying to use bad syntax to desribe a need, show example data and the expected output.

-k

 

[Cort]

I must have not communicated myself properly.

The formulas work great. They get me exactly what I need which is a date range from the current Sunday to Midnight of the currentdate.

Far Example: for today the date range would be
June 27 through July 1st.

What I would like to know is if those two criteria can be used as the defaults in a date range parameter field (created by going to parameter fields in the explorer and clicking on new)?

That help?

 

[kskid]

If you are looking for a week from Sunday for any date parameter, then

{dbo_Master_Tbl.CMT_Submitted_By_Date} in
[{?Pdate} - ([0,1,2,3,4,5,6][(dayofweek({?pdate}))]) to
{?Pdate}]

 

[lyanch]

If you mean:

I want to create date parameters and set a default that changes to **whatever**.

The answer is no. The default is either a static date or if no default is set the default is now. There are some cases where there really isn't a default (connectivity dependant mostly I think). But there isn't any way to get the default of the parameter to change to the last saturday at midnight...

Lisa

 

[synapsevampire]

You can't alter default values in parameters without external code.

Your post stated:

What I would like to happen is for
{dbo_Master_Tbl.CMT_Submitted_By_Date} < CurrentDate
to be the min default date in the parameter
and
{dbo_Master_Tbl.CMT_Submitted_By_Date} in WeekToDateFromSun
to be the max default date in the parameter. Does that make any sense and is it possible?

The maximum of WeekToDateFromSun is what I coded for, as your post requested.

Since you want to use these as default parameters, use something like 1/1/1970 as the default and in the prompting text explain to the users that selecting the default of 1/1/1970 will result in your default parameter.

Then in formulas create the appropriate values based on the selection and reference the formulas in the record selection formula.

-k

-k

 

[kskid]

Still confused or I am misreading it again.

How are you obtaining the default static date? Is it on a table somewhere, user-supplied or part of a formula?

If you are still wanting everything from last Sunday (since Midnight Saturday) to the currentdate, then simple change {?Pdate} to currentdate and placed it directly in Select Expert

{dbo_Master_Tbl.CMT_Submitted_By_Date} in
[currentdate - ([0,1,2,3,4,5,6][(dayofweek(currentdate))]) to
currentdate]

The select expert will show the following for 7/1/2004

{dbo_Master_Tbl.CMT_Submitted_By_Date} >=
{ts '2004-06-27 00:00:00.00'} AND
{dbo_Master_Tbl.CMT_Submitted_By_Date} <
{ts '2004-07-02 00:00:00.00'}

 

[synapsevampire]

Wutchita: I believe the requirement is to change the default of the parameter prompt, not simply change what's being passed to the server.

-k

 

[lbass]

You could try the following which seems to work and passes to the SQL, too:

(
if minimum({?date}) = currentdate and
maximum({?date}) = currentdate then
{dbo_Master_Tbl.CMT_Submitted_By_Date} in weektodatefromsun
) or
{dbo_Master_Tbl.CMT_Submitted_By_Date} = {?date}

Of course, you would want to add prompting text that tells the user that making no choices for the range values will result in a report on data for the week to date from Sunday.

-LB

 

[Cort]

Thanks everyone. There was an error in my original post from yesterday that I didn't see until now. Lyanch understood what I was looking for, thanks . Was I was wanting was the MIN date in the default to be weektodatefromsunday and the MAX date in the default to be < currentdate

I work with a set of users that require a LOT of handholding and lbass's code would probably work for everyones set of users in the country except for mine.