Mix Relative and strict dates

I am trying to write a report where I can have a drop down with values like

yesterday,today,lastweek.
But I also want the user to be able to select a specific range, say 7/1/2006 to 7/5/2006.

Also why can't i update the value of a parameter through to an expression? Like set the value to =Today(). It throws an error about the type.

Can someone point me in the right direction here?

Parameters are typed so you cannot mix relative dates with specific ones. So, you will need muliple parameters and way to determine which ones to use. I would use three parameters, the first one is a drop down with the realative dates and an entry like "Specify." The other two are DateTime parameters for specifying the start and end dates.

The "Specify" entry is to indicate that the user wants to specify specific dates. Otherwise, the user would choose the relative date and the specific dates would be ignored.

For the issue about an error being thrown when you are using the expression, =Today(): make sure the parameter's type is set to DateTime or use =Today().ToString() as the expression, if the parameter's type is string.

Ian|||

I normally use = today, no parentheses, in the parameters

99

|||

I cannot assign a parameter of type DateTime, through the web service. SetParameters() to '=Today()'

|||I think the issue is that you are passing the string '=Today()' where a DateTime is expected. Expressions are not evaluated here, so you need to specifiy the actual value, instead of an expression. E.g., DateTime.Today.

Or you can place the "=Today()" expression directly in the Report for the parameter, where it will be evaluated.|||once you deploy the report with an expression for the value of a parameter, can you change that, without re-deploying the report. Is there a call that can be made to change that expression in the report?|||Expressions are compiled at publish-time and cannot be changed without re-publishing the report. This re-publishing can be done programmatically, but I think there are better alternatives.

What are you looking to accomplish with changing the expression?

Ian|||I am trying to get the user be able to select either the relative dates, yesterday, lastweek... OR select a strict date range in a linked report. Also do you know if there is a control which only displays the parameters of the report, which can be used to display them.|||What would be the way to determine which one to use? Can this all be done while designing the report? do you have an example?

Missing results from stored proc

ok...I must be blind.
I have this procedure (which I didn't write):
CREATE PROCEDURE dbo.spWebqryAccountList41257
(@.StartDate nvarchar(15)
,@.EndDate nvarchar(15)
,@.ClientID int
,@.ArpCode nvarchar(15)
,@.brchid nvarchar(5)
,@.username nvarchar(10)
)
AS
SELECT *
FROM
dbo.vwWebClaimDetailNext4
WHERE
([Date Handled] between @.StartDate AND @.EndDate)
AND ([Client ID] = @.ClientID)
and ARPCODE in (@.ArpCode)
and BranchID = @.brchid
and username = @.username
Note that all it is doing is selecting from a view and narrowing the results
I have two executions:
EXEC spWebqryAccountList41257
@.StartDate = '2/1/2006',
@.EndDate = '3/3/2006',
@.ClientID = 2287,
@.ARPCode = 'A',
@.BrchID = 'A#79',
@.Username = 'ME2287'
Result = 4 records
EXEC spWebqryAccountList41257
@.StartDate = '2/1/2006',
@.EndDate = '3/3/2006',
@.ClientID = 2287,
@.ARPCode = 'A',
@.BrchID = 'Mil#104',
@.Username = 'ME2287'
Result = 0 records
The only difference between the two is the @.BrchID parameter.
The underlying view run with the same parameters as the second execution in
the WHERE clause returns records (7, to be exact) for the second set, but
the stored proc does not.
If I do this:
SELECT *
FROM
dbo.vwWebClaimDetailNext4
WHERE
([Date Handled] between '2/1/2006' and '3/3/2006')
AND ([Client ID] =2287)
and ARPCODE in ('A')
and BranchID = 'Mil#104'
and username = 'ME2287'
I get the right results...
I've been looking at the # sign as the culprit, but both branches have one
(as do many others).
Help?
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htmNevermind...the @.brchid parameter isn't big enough to hold what is being
passed...
Thanks for looking :-)
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Kevin3NF" <Kevin@.DontNeedNoSpam3NF-inc.com> wrote in message
news:ORLUL$ZQGHA.3896@.TK2MSFTNGP15.phx.gbl...
> ok...I must be blind.
> I have this procedure (which I didn't write):
> CREATE PROCEDURE dbo.spWebqryAccountList41257
> (@.StartDate nvarchar(15)
> ,@.EndDate nvarchar(15)
> ,@.ClientID int
> ,@.ArpCode nvarchar(15)
> ,@.brchid nvarchar(5)
> ,@.username nvarchar(10)
> )
> AS
> SELECT *
> FROM
> dbo.vwWebClaimDetailNext4
> WHERE
> ([Date Handled] between @.StartDate AND @.EndDate)
> AND ([Client ID] = @.ClientID)
> and ARPCODE in (@.ArpCode)
> and BranchID = @.brchid
> and username = @.username
>
> Note that all it is doing is selecting from a view and narrowing the
> results
> I have two executions:
> EXEC spWebqryAccountList41257
> @.StartDate = '2/1/2006',
> @.EndDate = '3/3/2006',
> @.ClientID = 2287,
> @.ARPCode = 'A',
> @.BrchID = 'A#79',
> @.Username = 'ME2287'
> Result = 4 records
> EXEC spWebqryAccountList41257
> @.StartDate = '2/1/2006',
> @.EndDate = '3/3/2006',
> @.ClientID = 2287,
> @.ARPCode = 'A',
> @.BrchID = 'Mil#104',
> @.Username = 'ME2287'
> Result = 0 records
> The only difference between the two is the @.BrchID parameter.
> The underlying view run with the same parameters as the second execution
> in the WHERE clause returns records (7, to be exact) for the second set,
> but the stored proc does not.
> If I do this:
> SELECT *
> FROM
> dbo.vwWebClaimDetailNext4
> WHERE
> ([Date Handled] between '2/1/2006' and '3/3/2006')
> AND ([Client ID] =2287)
> and ARPCODE in ('A')
> and BranchID = 'Mil#104'
> and username = 'ME2287'
> I get the right results...
> I've been looking at the # sign as the culprit, but both branches have one
> (as do many others).
> Help?
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
>
>