December 7, 2012 at 5:52 am
Hi,
Ive been having some difficulty preparing an MDX statement that gathers information from two cubes and displays a combined total row.
Cube A
Contains information about logical objects
Cube B
Contains information about physical objects
Each has (slightly differently named) dimensions for
Department
Object Type
RAG Status
And a count measure.
Im trying to get to a point where I have:
____________(Cube A: RED) (Cube A: AMBER) (Cube A: GREEN) (CUBE A: Total RAGs (A+B+C) (Cube B: RED) (Cube B: AMBER) (Cube B: GREEN) (CUBE B: Total RAGs (A+B+C)
Department A
Department B
Department C
Total
Using the following MDX ive managed to get pretty close, but the mod i put in to get around the "different dimensionality" issue is causing the total row to show under cube B in all occurances - even though having a look at the numbers tells me that they are the totals from cube a.
I have split the totals into seperate rows for clarity, but intend for them to be on one row.
-- Department Source = [Department].[Source].&[2]
-- Parent Department Key DataMember = [Computer Department].[Parent Department Key].[Level 01]
-- Parent Department Key = [Computer Department].[Parent Department Key].[All]
-- Directory Object Parent = [Directory Object].[Parent Object Key].[All]
-- Directory Name = [Directory Object].[Directory Name].[All]
-- Location = [Location].[Location].[All]
/*************************************************************
End of automatically generated trace info/params
*************************************************************/
WITH
// Computer elements
//====================================
MEMBER [Computer Department].[Parent Department Key].[This Department]
AS ([Computer Department].[Parent Department Key].[Level 01].DataMember, [Measures].[Fact Computer Specification Count])
MEMBER [Measures].[Computers]
AS ([Measures].[Fact Computer Specification Count], [Computer Directory Object].[Object Type].&[Computer])
MEMBER [Computer Department].[Parent Department Key].[Computer Total]
AS ([Computer Department].[Parent Department Key].[All], [Measures].[Fact Computer Specification Count])
MEMBER [Measures].[Department Key]
AS [Computer Department].[Parent Department Key].CurrentMember.Properties("Key")
MEMBER [Measures].[Migration Rag Key]
AS [Computer].[Migration RAG].CurrentMember.Properties("Key")
MEMBER [Measures].[Department Name]
AS [Computer Department].[Parent Department Key].CurrentMember.Properties("Caption")
MEMBER [Measures].[Red Computers]
AS ([Measures].[Fact Computer Specification Count], [Computer].[Migration RAG].&[Red])
MEMBER [Measures].[Amber Computers]
AS ([Measures].[Fact Computer Specification Count], [Computer].[Migration RAG].&[Amber])
MEMBER [Measures].[Green Computers]
AS ([Measures].[Fact Computer Specification Count], [Computer].[Migration RAG].&[Green])
MEMBER [Measures].[Unknown Computers]
AS ([Measures].[Fact Computer Specification Count], [Computer].[Migration RAG].&[Unknown])
MEMBER [Measures].[Total RAGU Computers]
AS
AGGREGATE
(
(
EXCEPT([Computer].[Migration RAG].Children, [Computer].[Migration RAG].&[Unknown])
)
, [Measures].[Fact Computer Specification Count]
)
// User elements
//====================================
MEMBER [Measures].[Users]
AS LOOKUPCUBE
(
"Directory"
, "
(
[Measures].[Directory Object Count]
, [Directory Object].[Object Type].&[User]
, [Department].[Source].[Location Feed]
, [Directory Object].[Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].&[" + [Measures].[Department Key] + "]
)"
)
MEMBER [Computer Department].[Parent Department Key].[User Total]
AS
LOOKUPCUBE
(
"Directory"
, "
(
[Measures].[Directory Object Count]
, [Directory Object].[Object Type].[User]
, [Directory Object].[Migration RAG].&[" + [Measures].[Migration Rag Key] + "]
, [Department].[Source].&[2]
, [Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].[All]
)"
)
MEMBER [Measures].[Red Users]
AS
LOOKUPCUBE
(
"Directory"
, "
(
[Measures].[Directory Object Count]
, [Directory Object].[Object Type].&[User]
, [Directory Object].[Migration RAG].&[Red]
, [Department].[Source].&[2]
, [Directory Object].[Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].&[" + [Measures].[Department Key] + "]
)"
)
MEMBER [Measures].[Amber Users]
AS
LOOKUPCUBE
(
"Directory"
, "
(
[Measures].[Directory Object Count]
, [Directory Object].[Object Type].&[User]
, [Directory Object].[Migration RAG].&[Amber]
, [Department].[Source].&[2]
, [Directory Object].[Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].&[" + [Measures].[Department Key] + "]
)"
)
MEMBER [Measures].[Green Users]
AS
LOOKUPCUBE
(
"Directory"
, "
(
[Measures].[Directory Object Count]
, [Directory Object].[Object Type].&[User]
, [Directory Object].[Migration RAG].&[Green]
, [Department].[Source].&[2]
, [Directory Object].[Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].&[" + [Measures].[Department Key] + "]
)"
)
MEMBER [Measures].[Unknown Users]
AS
LOOKUPCUBE
(
"Directory"
, "
(
[Measures].[Directory Object Count]
, [Directory Object].[Object Type].&[User]
, [Directory Object].[Migration RAG].&[Unknown]
, [Department].[Source].&[2]
, [Directory Object].[Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].&[" + [Measures].[Department Key] + "]
)"
)
MEMBER [Measures].[Total RAGU Users]
AS
LOOKUPCUBE
(
"Directory"
, "
AGGREGATE
(
([Measures].[Directory Object Count]
, [Directory Object].[Object Type].&[User]
, [Department].[Source].&[2]
, [Directory Object].[Directory Name].[All]
, [Directory Object].[Parent Object Key].[All]
, [Location].[Location].[All]
, [Department].[Parent Department Key].&[" + [Measures].[Department Key] + "]
, EXCEPT([Directory Object].[Migration RAG].Children, [Directory Object].[Migration RAG].&[Unknown])
)
)"
)
// MDX Select
//====================================
SELECT
{
[Measures].[Department Name]
, [Measures].[Users]
, [Measures].[Red Users]
, [Measures].[Amber Users]
, [Measures].[Green Users]
, [Measures].[Unknown Users]
, [Measures].[Total RAGU Users]
, [Measures].[Computers]
, [Measures].[Red Computers]
, [Measures].[Amber Computers]
, [Measures].[Green Computers]
, [Measures].[Unknown Computers]
, [Measures].[Total RAGU Computers]
} ON COLUMNS,
{
[Computer Department].[Parent Department Key].[This Department],
ORDER
(
EXCEPT
(
DESCENDANTS([Computer Department].[Parent Department Key].[All], 1, SELF)
, [Computer Department].[Parent Department Key].[All].DataMember
)
, [Measures].[Department Name]
, BASC
)
, [Computer Department].[Parent Department Key].[Computer Total]
, [Computer Department].[Parent Department Key].[User Total]
--, [Measures].[UserTotal]
} ON ROWS
FROM [Hardware Inventory]
WHERE
(
[Computer Department].[Source].&[2]
, [Computer Directory Object].[Directory Name].[All]
, DESCENDANTS([Computer Directory Object].[Parent Object Key].[All], 0, SELF)
// , [Computer Location].[Location].[All]
)
December 7, 2012 at 5:54 am
PS. 2008 R2.
Ive only really been using MDX for about 3 days (I did some stuff a few years ago), so any advice would be helpful!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply