how best should I handle this

1999 will be the farthest back I need to go. That is in stone. The plan is to set all the closed dates prior to 1999 to 1950 so i can easily filter them out.

yes, but what I will be doing is grouping them based on a filter:

date_recieved < "first day of the year" and date_closed > "first day of the year"

This will begin on the current year then go back to 1999.

Hopefully this answers both questions:

A case may be open or closed. For this section of the report, I need to calculate the average days open as of the first of the year. So if it closes after the first day of the year, it will be filtered out the next year. EX: case is open on 1/20/2000 then closes 3/5/2003. It will be "open" on years 2001, 2002 and 2003 but will be filtered out for 2004 to present.

This is where it got sticky for me... The other part of the report was just deteremine average age of case based on the difference in days from open to close which I used this expression:

=RunningValue(DATEDIFF("d",Fields!date_received.Value,Fields!closed_date.Value), SUM, "Details") /COUNTDISTINCT(Fields!case_sk.Value)

I exported the list to Excel and manually did the calc using AVERAGE/Row# to confirm the calc.

But I can't seem to wrap my head around the open at the 1st of the year... That is why I was thinking of creating dataset that generates the year then pass as a parameter to use for the grouping and filters...

The other thought was to make this dynamic so that I do not have to manually update the report for each new year.

Does that make since or is there a better solution?

Is this near what you are after?

/****** create 1st temp table #cases and insert test data ******/

IF OBJECT_ID('TempDB..#cases','u') IS NOT NULL

DROP TABLE #cases

CREATE TABLE #cases

(

case_sk INT NOT NULL,

case_number VARCHAR(15) NULL,

date_received DATETIME NULL,

closed_ind CHAR(1) NULL,

a_initials VARCHAR(4) NULL,

b_initials VARCHAR(4) NULL

)

INSERT INTO #cases (case_sk, case_number, date_received, closed_ind, a_initials, b_initials)

SELECT 71, 001070,'3/8/2003', 'C','EEE', NULL UNION ALL

SELECT 8675, 001306,'2/9/2004', 'C','AAA', NULL UNION ALL

SELECT 8913, 001054,'2/18/2003', 'C','EEE', NULL UNION ALL

SELECT 9914, 001247,'10/19/2003', 'C','TTT', NULL UNION ALL

SELECT 13231, 000977,'3/7/2004', 'C','AAA', NULL UNION ALL

SELECT 15066, 000174,'9/2/1999', 'C','AAA','BBB' UNION ALL

SELECT 16335, 000105,'5/29/1999', 'C','AAA','WWW' UNION ALL

SELECT 16438, 000015,'1/22/1999', 'C','AAA', NULL UNION ALL

SELECT 16811, 000151,'7/29/2001', 'C','EEE', NULL UNION ALL

SELECT 16869, 000086,'4/24/1999', 'C','AAA','WWW' UNION ALL

SELECT 16899, 000097,'5/19/1999', 'C','AAA','WWW' UNION ALL

SELECT 17035, 000128,'7/1/1999', 'C','AAA','BBB' UNION ALL

SELECT 17629, 001044,'2/3/2003', 'C','DDD', NULL UNION ALL

SELECT 17864, 000199,'10/7/1999', 'C','DDD','BBB' UNION ALL

SELECT 17946, 000358,'5/21/2000', 'C','RRR', NULL UNION ALL

SELECT 18133, 000294,'2/24/2000', 'C','LLL','FFF' UNION ALL

SELECT 18825, 000343,'5/4/2000', 'C','RRR','FFF' UNION ALL

SELECT 18947, 000373,'6/16/2000', 'C','RRR','OOO' UNION ALL

SELECT 19016, 001050,'2/8/2003', 'C','DDD', NULL UNION ALL

SELECT 19478, 000415,'8/16/2000', 'C','WJR','MMM' UNION ALL

SELECT 20005, 001049,'2/7/2003', 'C','DDD', NULL UNION ALL

SELECT 20341, 000764,'1/3/2002', 'C','RRR', NULL UNION ALL

SELECT 20394, 000561,'3/16/2001', 'C','AAA', NULL UNION ALL

SELECT 20473, 000627,'1/14/2001', 'C','AAA', NULL UNION ALL

SELECT 20527, 000537,'2/12/2001', 'C','AAA', NULL

-- select * from #cases

/** create 2nd temp table #case_closed **/

IF OBJECT_ID('TempDB..#case_closed','u') IS NOT NULL

DROP TABLE #case_closed

CREATE TABLE #case_closed

(

case_sk INT NOT NULL,

closed_date DATETIME NULL

)

INSERT INTO #case_closed (case_sk, closed_date)

SELECT 71, '6/21/2004' UNION ALL

SELECT 8675,'11/4/2004' UNION ALL

SELECT 8913, '9/4/2003' UNION ALL

SELECT 9914, '5/3/2004' UNION ALL

SELECT 13231, '8/20/2004' UNION ALL

SELECT 15066, '5/9/2003' UNION ALL

SELECT 16335, '3/3/2000' UNION ALL

SELECT 16438, '6/3/1999' UNION ALL

SELECT 16811, '12/19/2000' UNION ALL

SELECT 16869, '11/10/1999' UNION ALL

SELECT 16899, '11/19/1999' UNION ALL

SELECT 17035, '11/29/1999' UNION ALL

SELECT 17629, '1/26/2006' UNION ALL

SELECT 17864, '2/23/2000' UNION ALL

SELECT 17946, '6/26/2002' UNION ALL

SELECT 18133, '6/30/2000' UNION ALL

SELECT 18825, '12/21/2001' UNION ALL

SELECT 18947, '7/11/2003' UNION ALL

SELECT 19016, '6/12/2003' UNION ALL

SELECT 19478, '4/11/2001' UNION ALL

SELECT 20005, '8/27/2003' UNION ALL

SELECT 20341, '6/10/2002' UNION ALL

SELECT 20394, '12/26/2001' UNION ALL

SELECT 20473, '6/19/2003' UNION ALL

SELECT 20527, '5/20/2004'

-- select * From #case_closed

/** Query **/

;WITH years AS (

SELECT YEAR(GETDATE())-(ROW_NUMBER() OVER(ORDER BY @@SPID)-1) AS [YEAR]

FROM

(SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) first10(A),

(SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) second10(B)

)

SELECT

years.[Year] ,

c.case_sk,

c.case_number,

c.date_received,

closed_ind,

cc.closed_date,

ISNULL(b_initials, a_initials) AS Initials,

DATEDIFF( d , c.date_received , CONVERT(CHAR(4),years.Year)+'0101' ) AS DaysOpen

FROM years

JOIN (

#cases AS c

LEFT OUTER JOIN #case_closed AS cc

ON cc.case_sk = c.case_sk

)

ON years.Year BETWEEN YEAR(c.date_received)+1 AND YEAR(ISNULL(cc.closed_date,GETDATE()))

WHERE(c.date_received > CONVERT(DATETIME, '1999-01-01 00:00:00', 102))

AND (ISNULL(cc.closed_date,GETDATE()) > CONVERT(DATETIME, '1999-01-01 00:00:00', 102))

ORDER BY c.case_number ,years.Year