September 5, 2013 at 12:45 pm
I am somewhat stumped on how to fix a report that I am creating. Currently, I send out a daily report to my manager for the number of defects open and closed per day and on a per project basis. This is done via a vbscript that queries the database and dynamically builds an email. The current report is limited to yesterday, whatever date that happens to be. We recently began moving to SSRS and I thought I would expand that report to include information for up to 1 year.
The new report would take each project, break it down by month, then by week, then show daily information as needed. On each level such as project, month, and week, I am including subtotals of the number of defects opened and closed for that given time period. The daily level will show the day of the week, it's date, and the number opened or closed.
In the new report, I have been able to take a single project that uses my test system datasouce to get all of the names of projects that are either opened or closed within the past couple of months and group on those. In a subreport, I used a calendar table to create the month, week, and day breakdowns with subtotals. I used two lookup expressions to get the total opened and total closed due to the way my test system database is set up. These two expressions match on the date opened or date closed.
The challenge I have is that since I am comparing in my lookup values based on date, my count of defects opened or closed isn't broken out by project. For example if I have a project A and Project B then the data that is being viewed between those two is the same because of how my report is structured. I have attached an image to this post to try to help explain.
Could you all offer any suggestions as to how I could break out the defects opened by Project then by date even though those are two totally separate data sets.
September 6, 2013 at 8:59 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply