Parameter help

[dianemarie]

Hello, I have a report that compares 3 years of data. The user wants to pick a starting month and year, and an ending month and year (usually YTD) and then have the report display that information as well as two years previous, same starting and ending months. They don't want to fill out the date ranges three times. I started by creating the 4 separate parameters (starting month & year & ending month & year), then tried doing an internal hidden parameter with formulas to pick up the previous two years. For example, I put a formula in the @PrevYr1 parameter to say (@Year - 1) (the month and year parameters are integers, I have Year and Month fields in my query). The @Month parameters need one parameter only for starting and ending, as they will not change from year to year. All works OK, except when I change the Month & Year parameters, the other parameters don't update. They stay the same. Any thoughts on this, or a better way to attack this? Thanks much.

 

[bburnell]

Try passing the parameters to a stored procedure and return that to the report? I do something similar...
Brett

--------------------------
Web/.net Programmer & DBA
Central PA

 

[RiverGuy]

I would just declare variables in the SQL itself to get the previous years (assuming SQL Server is the data source). Or you can just do the math in the select statement itself.

Let's say the user passes in
@StartMonth INT
@EndMonth INT
@Year INT

In your query, you can write code to populate other variables:

DECLARE @OnePreviousYear INT
SELECT @OnePreviousYear = @Year- 1
DECLARE @TwoPreviousYear INT
SELECT @TwoPreviousYear = @Year - 2

Then your query could look something like:

SELECT a.SomeColumn, a.Amt AS ThisYearAmount,
b.Amt AS OnePreviousAmount,
c.Amt AS TwoPreviousAmount

FROM

(SELECT SomeColumn, SUM(Amt) AS Amt
FROM SomeTable
WHERE [Year] = @Year AND [Month] BETWEEN @StartMonth AND @EndMonth
GROUP BY SomeColumn) a

LEFT OUTER JOIN

(SELECT SomeColumn, SUM(Amt) AS Amt
FROM SomeTable
WHERE [Year] = @OnePreviousYear AND [Month] BETWEEN @StartMonth AND @EndMonth
GROUP BY SomeColumn) b

ON a.SomeColumn = b.SomeColumn

LEFT OUTER JOIN

(SELECT SomeColumn, SUM(Amt) AS Amt
FROM SomeTable
WHERE [Year] = @TwoPreviousYear AND [Month] BETWEEN @StartMonth AND @EndMonth
GROUP BY SomeColumn) c

ON a.SomeColumn = c.SomeColumn