Thread: Dynamic change of cell info based on user input?

I suppose you could use a stored procedure for this. Converting each month to a datetime would be the best way for the iteration. Here is a sample of what that might look like:

Code:

-- This would be your parameter:
DECLARE @StartMonth int
SET @StartMonth = 5 -- Just for testing: this is the month the user selects

-- This is just to convert the month to a date.
DECLARE @StartDate datetime
SET @StartDate = CAST(CAST(@StartMonth as varchar(2)) + '/01/' + CAST(YEAR(GETDATE()) as varchar(4)) as datetime)

DECLARE @EndDate datetime
SET @EndDate = DATEADD(year, 1, @StartDate)

-- Now create a table to store the list of months in
DECLARE @MonthTab TABLE
(
    [month_num] int,
    [month_name] varchar(20)
)

-- Create a counter variable
DECLARE @d Datetime
SET @d = @startdate

-- Now loop through the list to get your months.
WHILE @d < @EndDate
BEGIN
    INSERT INTO @MonthTab VALUES( MONTH(@d), DATENAME(month, @d) )
    SET @d = DATEADD(month, 1, @d)
END

-- Finally get the list of months from you table.  Now this can be inserted on your report.    
SELECT * FROM @MonthTab

I know this seems lengthy, but there is no way to do this without some sort of iteration.

You can cut and paste that into your query window. Just change the bit with the parameters so it's a stored procedure accepting a month as an input.

Then in your report create a value list with numbers for month values and names for the labels.

Let me know if you need help with any of that.

The sample I posted is ready to test as is. So feel free to try it in your query window.