create a "virtual dimension" in MDX?

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 bugs

This 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]

;

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber. What I'd like is a select query which will generate a new field which counts back the consecutive number of days that ErrorNumber \= 0. I'd like the record set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days in a row.
I've been pulling my hair out about this.
Thanks!
Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist of what its doin. But I'm no database dynamo, so I'm gonna have to work with this for a bit to see what it'll actually return.
Thanks again!
|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!
|||Thats exactly it! I gotta be able to pass a parameter for each servicedate, but I'll be able to figure that out.
Thanks - I appreciate it!

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber
. What I'd like is a select query which will generate a new field which coun
ts back the consecutive number of days that ErrorNumber \= 0. I'd like the r
ecord set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days
in a row.
I've been pulling my hair out about this.
Thanks!Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist
of what its doin. But I'm no database dynamo, so I'm gonna have to work with
this for a bit to see what it'll actually return.
Thanks again!|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!|||Thats exactly it! I gotta be able to pass a parameter for each servicedate,
but I'll be able to figure that out.
Thanks - I appreciate it!