Week number as parameter in Reporting Services

[jodjim]

In my 2005 RS report, there is an option to enter a week number as a parameter. Instead of manually entering the week number, how do I create a drop-down list for week 1 to 53 without placing each figure as a non-query value of the paramater.

Will appreciate any help.

 

[unclerico]

not sure about SSRS 2005, but in 2000 you could define a dataset where you loop from 1 to 53 and use that dataset to provide the values for your parameter...make sense??

 

[jodjim]

Yes, it does make sense. Except my idea of creating a dataset for the weekrange is to create/add 53 fields and assigning values 1 to 53 to each. In effect, it's like creating a non-query parameter for the weekrange and defining 53 labels and values. Or in short, I don't know how to create a dataset and assigning value using a loop.

Would you mind showing me how? Thanks.

 

[unclerico]

define a new dataset and try something like this:

DECLARE @i int
DECLARE @table TABLE(WeekNo int NOT NULL)

SET @i = 1

WHILE @i <= 53
  BEGIN
    INSERT INTO @table
    VALUES (@i)

    SET @i = @i + 1
  END

SELECT WeekNo
  FROM @table

 

[jodjim]

Just what I thought. It's something I've never encountered before and it's always nice to learn new things. Thanks a lot, unclerico.