It would be much easier to offer suggestions, at least for me, if you posted the RDL so we could look at what you have developed. Even a small set of sample data with a desired result would help.

While I'm sure there is a way that you could get this done in the way you are attempting, I'd look at it from a different direction. Why not create a calendar table or even a calendar CTE in your query and return the information in your query that way. Then use the report to do all grouping and totaling.

Your data set then would return the data like this:

Project Name

MOnth

Week

Day

Date

Total Open

Total Closed

In your report you would have groups Project, Month, and Week and it would be much easier to deal with since the data is being returned in one dataset and just needs to be made to look pretty by SSRS. A query that does this with a CTE to make the calendar table would look something like this:

/* the N cte's do is create a virtual numbers table then the final CTE

uses the Numbers table, the current date and date functions to get a

vritual calendar table */

WITH N0

AS (

SELECT

0 AS N

UNION ALL

SELECT

0

),

N1

AS (

SELECT

A.N

FROM

N0 AS A

CROSS JOIN N0 AS B

),

N2

AS (

SELECT

A.N

FROM

N1 AS A

CROSS APPLY N1 AS B

),

N3

AS (

SELECT

A.N

FROM

N2 AS A

CROSS APPLY N2 AS B

),

N4

AS (

SELECT

ROW_NUMBER() OVER (ORDER BY (

SELECT

NULL

)) AS N

FROM

N3 AS A

CROSS JOIN N3 AS B

),

theLastYear

AS (

SELECT TOP 367

CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE())) AS theDate,

CONVERT(DATETIME, CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE()))) AS DayStart,

CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE())))) AS DayEnd,

DATENAME(weekday, CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE()))) AS theDay,

DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE()))), 0),

CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE()))) + 1 AS weekOfTheMonth,

DATENAME(MOnth, CONVERT(DATE, DATEADD(DAY, -(N - 1), GETDATE()))) AS theMonth

FROM

N4

)

SELECT

P.project_name,

TLY.theMonth,

TLY.weekOfTheMonth,

TLY.theDay,

TLY.theDate,

SUM(CASE WHEN WI.STATUS = 'Open' THEN 1

ELSE 0

END) AS openItems,

SUM(CASE WHEN WI.STATUS = 'Open' THEN 0

ELSE 1

END) AS closedItems

FROM

dbo.projects AS P

JOIN dbo.work_items AS WI

ON P.project_id = WI.project_id

JOIN theLastYear AS TLY

/* Greater than or equal to and less than because DayEnd is really the start of the next day */ ON WI.open_date >= TLY.DayStart AND

WI.open_date < TLY.DayEnd

GROUP BY

P.project_name,

TLY.theMonth,

TLY.weekOfTheMonth,

TLY.theDay,

TLY.theDate;

Now the report part is really simple.