date parameters

I need a parameter with the following options:
current week
last week
current month
current quarter
I have a dataset with today, monday of current week, current quarter number
and current month number, but i'm at a loss as to how to tie this in with my
report and how to put it into a parameter...any suggestions or referrals
would be most appreciated.Hi,
It is possible to ty values to a labels for a parameter through a query. All
you have to do is to create the dataset with two columns. One with you're
desired options and the other column with the corresponding number. In this
way it is possible to show the user the list of the four option, but use in
your report your own variables.
Jan Pieter Posthuma
"jmann" wrote:
> I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter number
> and current month number, but i'm at a loss as to how to tie this in with my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.|||This logic probably isn't right for your data but the basic idea should be
the same. For simplicity, I'm figuring that current week/month/quarter means
one week/month/quarter ago through today. You'll likely need to figure out
when each period begins using your data and make adjustments.
SELECT * FROM YourTables
WHERE OrderDate BETWEEN
CASE
WHEN @.Period = 'Current Week' THEN DATEADD(Week, -1, GETDATE())
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -2, GETDATE())
WHEN @.Period = 'Current Month' THEN CAST(CAST(MONTH(GETDATE()) AS
VarChar(2)) + '-1-' + CAST(YEAR(GETDATE()) AS VarChar(4)) AS DateTime)
WHEN @.Period = 'Current Quarter' THEN DATEADD(Quarter, -1, GETDATE())
END
AND
CASE
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -1, GETDATE())
ELSE GETDATE()
END
Paul Turley
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:E474B451-652E-405F-B698-D0D1642D907D@.microsoft.com...
>I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter
> number
> and current month number, but i'm at a loss as to how to tie this in with
> my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.sql