Date Parameter Problem! Please help!

I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2... and so on.

There is not a Period column to reference the fields to and I am not sure how to
use a date range and apply it to one parameter. Is there a way to use
the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?

Basically here is what I am trying to do...

Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.

I did get some advice on using an if statement to reference the parameter but I receive the error message that I must declar the scalar value @.Period.

Here is the simple query I used to just see if the query would run based on the parameter(be nice...I am a newbie to SQL and RS)

In my Data tab:
IF @.Period = 1 SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN '03/06/2006' AND '04/02/2006'

For the report parameter:
Label Value
Period 1 1

I have used the IIF expression and such but this is just a different situation and I am pulling my hair out trying to find an answer.
Any help is greatly appreciated!

I

I'm sure there are a few different ways to approach this, but this could be one.

1) Create a parameter called @.PeriodStart like you did. Make this a datetime parameter. The label for this parameter could be a number (1, 2, 3.. n) and the value would be a date. You could hard-code the periods in through the Available Values section and select non-queried. For label you could put 1 and for value '1/1/2006' and 2 then 1/7/2006... or whatever you wanted to define for your period start dates. (If you wanted to make this more dynamic, you could create a dataset that somehow used sql functions to get these dates... You could create a DateDimension table or a Period table..)

2) Create a dataset called something like PeriodEndDataSet. The query could be something like this: SELECT DATEADD(MONTH, 1, @.PeriodStart) AS PeriodEndDate. (you can make the 1 month be anything you wanted).

3) Create a second parameter called @.PeriodEnd. You would want this to be a hidden parameter. In the Parameter editor Select Queried from available values section. Select your dataset and label & value fields. Also set the default value to come from the same query.

4) In the query for your report (the main data set), you can create your query to do this:

SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN @.PeriodStart AND @.PeriodEnd

That seems like a lot of work... I would almost recommend creating a lookup/dimension table to store this period information for you. You could then use this across many reports.

Regards,

Dan

sql