I have a warehouse with bug tracking data in it. Each bug has a priority and a state, among other things. State can be Active Resolved or Closed and Priority can be 1 2 or 3.
I want to use SSRS to display a chart with the following four lines (over time):
active priority 1 bugs active priority 2 bugs active priority 3 bugs resolved bugs (total)The closest I can get is this (six lines)
active priority 1 bugs active priority 2 bugs active priority 3 bugs resolved priority 1 bugs resolved priority 2 bugs resolved priority 3 bugsThis is becuase if I put priority on the chart's legend, it will slice everything that is in the data area. I get requests such as this one all the time where the customer wants to see data combined into a graph, but may want different slicing semantics for each data series. For example, I may want to put somthing on the graph like Test Results where it doesn't even make sense at all to slice by Priority. It seems like one way to do this would be to create a new dimension on the fly that would crunch some of these down, such that the result set would look like this:
date, legend, bug count
6/1, pri1, 10
6/1, pri2, 45
6/1, pri3, 78
6/1, resolved, 5
6/2, pri1, 11
6/2, pri2, 30
6/2, pri3, 90
6/2, resolved, 3
etc.
Any ideas on how I could do this?
I've pasted the code below. BTW, this is against the TFS warehouse, but like I said, I'm really looking for a solution to the general problem.
thx.
Matt
WITH
MEMBER [Measures].[Date Key] AS
[Date].[Date].CurrentMember.UniqueName
SELECT
{
[Measures].[Date Key],
[Measures].[Cumulative Count],
[Measures].[State Change Count]
} ON COLUMNS,
(
([Date].[Date].&[2007-06-01T00:00:00]:[Date].[Date].&[2007-06-30T00:00:00]),
UNION
(
([Work Item].[System_State].[System_State].[Active],
except([Work Item].[System_Reason].[All].Children, {[Work Item].[System_Reason].&[Build Failure]})),
([Work Item].[System_State].[System_State].[Resolved], [Work Item].[System_Reason].[System_Reason].[Fixed])
),
[Work Item].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority],
[Work Item].[Microsoft_VSTS_Common_ActivatedBy]
) ON ROWS
FROM [Team System]
WHERE
(
[Team Project].[Team Project].[ACS],
[Work Item].[System_WorkItemType].[System_WorkItemType].[Bug]
)
The trick is in building the cross-dimension set. The code below shows how you can control the members in a cross join. The sample is based on Adventure Works, but you can imaging the All Customers member is equivalent to your All Severity member.
Good luck,
Bryan
Code Snippet
select
{} on 0,
{
([Product].[Category].[Category].[Bikes]*
[Customer].[Gender].[Gender].Members),
(EXCEPT([Product].[Category].[Category].Members,[Product].[Category].[Category].Bikes)*
[Customer].[Gender].[All Customers])
} on 1
from [Adventure Works]
;
