MDX Queries 1

Using Axis & Dimensions
SELECT NON EMPTY

[Measures].[NQ Learning Resource Publish To Count] ON COLUMNS
, NON EMPTY
([Tbl Status].[STS Value].[STS Value].ALLMEMBERS * [Tbl Copyright].[CPR Label].[CPR Label].ALLMEMBERS ) ON ROWS


FROM [LRRCreationCube]

-----------------------------------
Using tuples

SELECT // select a cell (tuple)
NON Empty
([Measures].[NQ Learning Resource Publish To Count] , [Tbl Status].[STS Value].&[Live],[Tbl Publish To].[PUB Value].&[TaLe]) ON COLUMNS

,
NON Empty [Tbl Copyright].[CPR Label].ALLMEMBERS ON ROWS // This axis is optional
FROM [LRRCreationCube]

note &[TaLe]

------
Using Set

SELECT
NON Empty
{
([Measures].[NQ Learning Resource Publish To Count],[Tbl Status].[STS Value].&[Live],[Tbl Publish To].[PUB Value].&[TaLe])
,
([Measures].[NQ Learning Resource Publish To Count],[Tbl Status].[STS Value].&[Live],[Tbl Publish To].[PUB Value].&[Public])
} ON COLUMNS

,
NON Empty
[DimCreationTime].[Year].ALLMEMBERS ON ROWS

FROM [LRRCreationCube]

------

Calculated member

WITH MEMBER Measures.Count10 AS
[Measures].[NQ Learning Resource Publish To Count]*10

SELECT NON Empty
{[Measures].[NQ Learning Resource Publish To Count],Measures.Count10} on columns
,NON Empty
[Tbl Status].[STS Value].&[Live]* Filter( [Tbl Copyright].[CPR Label].[CPR Label].ALLMEMBERS, Instr( [Tbl Copyright].[CPR Label].currentmember.Properties( 'Member_Caption' ), 'Share' ) > 0 ) ON ROWS

FROM [LRRCreationCube]
--------------------------------------------
Hint: Comma=Tuple, brace {}= Set

----------------------------------------------

Using Where
it is like slicing (adding another non displayed dimension to slice the result)

select Non Empty
[Measures].[NQ Download Count]
on COLUMNS
,
Non Empty

(
[NQ Download].[Source].&[PUBLIC]
,[DimRegionUsersCombined].[Org Type].[Org Type]
,[DimCreationTime].[Year].[Year]

) on ROWS

from [LRRDownloadsCube]

//Remove the &[PUBLIC] member value and added to where condition and will give the same effect, except will not appear

//where [NQ Download].[Source].&[PUBLIC]


--------------------------------------------
Using Filter

• SELECT NON Empty

[Measures].[NQ Learning Resource Publish To Count] on columns

,[Tbl Status].[STS Value].&[Live]* Filter( [Tbl Copyright].[CPR Label].[CPR Label].ALLMEMBERS, Instr( [Tbl Copyright].[CPR Label].currentmember.Properties( 'Member_Caption' ), 'Share' ) > 0 ) ON ROWS

FROM [LRRCreationCube]

• SELECT NON Empty

filter([DimCreationTime].[Year].[Year],[Measures].[NQ Download Count]>3000)
on COLUMNS
from [LRRDownloadsCube]

• SELECT NON Empty

filter(
([NQ Download].[Source].[Source],[DimCreationTime].[Year].[Year],[Measures].[NQ Download Count])

,[Measures].[NQ Download Count]>3000)

on COLUMNS
,Non Empty
[NQ Learning Resource Live].[RES Depositor].ALLMEMBERS on rows
from [LRRDownloadsCube]