I have two queries which I would like to join together,which would give me the calculated total of the row. The problem I have is that although the measures are from 1 fact table the Squad names are in two role playing dimensions. I'm extremely new to MDX.
The outcome would be something like
| Competition | Squad | Goals A | Goals B | Total (Goals A + Goals B)|
|----------------|---------|---------|---------|--------------------------|
| Premier League | Arsenal | 25 | 15 | 40 |
Thanks in advance.
Query1:
SELECT
([Measures].[Goals A]) ON COLUMNS,
NON EMPTY ([Dim Competition].[Hierarchy].[Competition ID],[Squad A].[Squad Name].ALLMEMBERS) ON ROWS
FROM
[FDC Star]
WHERE [Dim Competition].[Competition ID].&[1];
Query1 result:
Competition Squad Goals A
Premier League Arsenal 25
Premier League Aston Villa 8
Premier League Chelsea 22
Premier League Everton 17
Premier League Fulham 17
Premier League Liverpool 17
Premier League Manchester City 25
Premier League Manchester United 28
Premier League Newcastle United 13
Premier League Norwich City 13
Premier League Queens Park Rangers 8
Premier League Reading 15
Premier League Southampton 15
Premier League Stoke City 14
Premier League Sunderland 11
Premier League Swansea City 19
Premier League Tottenham Hotspur 17
Premier League West Bromwich Albion 17
Premier League West Ham United 19
Query2:
SELECT
([Measures].[Goals B]) ON COLUMNS,
NON EMPTY ([Dim Competition].[Hierarchy].[Competition ID],[Squad B].[Squad Name].ALLMEMBERS) ON ROWS
FROM
[FDC Star]
WHERE [Dim Competition].[Competition ID].&[1];
Query2 result:
Competition Squad Goals B
Premier League Arsenal 15
Premier League Aston Villa 9
Premier League Chelsea 17
Premier League Everton 18
Premier League Fulham 15
Premier League Liverpool 17
Premier League Manchester City 16
Premier League Manchester United 26
Premier League Newcastle United 14
Premier League Norwich City 11
Premier League Queens Park Rangers 9
Premier League Reading 8
Premier League Southampton 12
Premier League Stoke City 7
Premier League Sunderland 10
Premier League Swansea City 12
Premier League Tottenham Hotspur 22
Premier League West Bromwich Albion 12
Premier League West Ham United 5
After spending many hours trying to come up with a solution I have finally solved my query.
Thanks
WITH
MEMBER [Measures].[GFA] AS
(
([Measures].[Goals A]),
linkmember([Dim Squad].[Squad Name],[Squad A].[Squad Name]),
root([Squad Name])
)
MEMBER [Measures].[GFB] AS
(
([Measures].[Goals B]),
linkmember([Dim Squad].[Squad Name],[Squad B].[Squad Name]),
root([Squad Name])
)
MEMBER [MEASURES].[GF] AS
(
[Measures].[GFA] + [Measures].[GFB]
)
select
{[Measures].[GFA],[Measures].[GFB],[MEASURES].[GF]} on 0,
non empty [Dim Squad].[Squad Name].allmembers on 1
from [FDC Star]
where [Dim Competition].[Competition ID].&[1]