Add Parameter to enable user to input value GETDATE + 'Parameter' value

Question

Add Parameter to enable user to input value GETDATE + 'Parameter' value

2Tall

SSChampion

Points: 14644
More actions
May 29, 2008 at 6:22 am

#191081

Hi. I have a query from which I wish to construct a report. The query has a WHERE clause:
WHERE QExpiry.FieldName <= GETDATE()+5
I have been advised I can use DATEADD.
When the user runs the report I would like the user to enter a value 'Expires in Less(Days)': which is currently hardcoded + 5
Can any of you gurus explain how I might achieve this?
Many Thnaks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

2Tall SSChampion Points: 14644 More actions · Answer 1

Hi. I have tried replacing the <= GETDATE() + 5 with <= @Expiry

In the report I set the parameter data type to DateTime. When I select the date from the calendar on the report parameter (american format?) the report does not return the expected results?

Any ideas?

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Within the report in VS2005:

- Go to Report, Report Parameters. Add a parameter. Call it something meaningful (like - DateOffset). Change the data type to integer. Put the prompt in as you had it (Expires in Less(Days): )

- switch back to the data tab, to show you the query. Replace the + 5 with +@DateOffset

- Preview the report.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

2Tall SSChampion Points: 14644 More actions · Answer 3

Update. OK. If iI execute the query via the Dataset then enter value for @Expiry as 03/06/2008 the query runs as expected. If I execure via the calendar on the report parameter 06/03/2008, blank.

So how do I get the calendar to display UK format?

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 4

Hi Matt. Missed your post. Thanks, will give that a whirl back at the office. The report runs as expected from within the application, just not within visual studio?

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 5

Hi Matt. Gave that a whirl. The report failed to return any records when I entered number of days as integer.

Is this due to the field being of data type 'datetime'? Query 'datetime field<= @DateOffset' (integer value)?

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Leave the parameter as an integer, and change the query to check for:

QExpiry.FieldName <= DATEADD(d, @DateOffset, GETDATE())

You should be able to have this work in both locations.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

2Tall SSChampion Points: 14644 More actions · Answer 7

Thanks Steve.

Phil

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper