Simple MDX Queries

— Single axis

select [Measures].[Sales Amount] on Columns

from [InternetSales];

— Double axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–two aggregation wrong no supported

Select [Measures].[Sales Amount] on 0,

[Dim Employee].[Reports To].[Level 02].ALLMEMBERS on 1,

[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–two aggregation right

Select [Measures].[Sales Amount] on 0,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country]*[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–Filter out emplty

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

–“ALLMEMBER”

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].&[Canada]) on 1

from [InternetSales];

–using tuples

Select NON EMPTY [Measures].[Sales Amount] on Columns,

NON EMPTY [Order Date].[Calendar Year].[Calendar Year] on rows

from [InternetSales];

Select NON EMPTY ( [Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year]) on columns

FROM [InternetSales];

–creating a tuple set

Select NON EMPTY {([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2006]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2007]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2008])

} on columns

FROM [InternetSales];

–Multiple Tuple Sets

Select [Measures].[Sales Amount] on Columns,

NONEMPTY(

([Dim Product].[Model Name].Allmembers, {[Order Date].[Calendar Year].[Calendar Year].&[2007]

,[Order Date].[Calendar Year].[Calendar Year].&[2008]}),[Measures].[Sales Amount]

) on rows

FROM [InternetSales];

–Functions

–TOP Percent

Select [Measures].[Sales Amount] on COLUMNS,

TopPercent([Dim Product].[Model Name].[Model Name],50,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–Top Sum (running total of category aggregattion exceed a certain amount)

Select [Measures].[Sales Amount] on COLUMNS,

TopSum([Dim Product].[Model Name].[Model Name],150000,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–use set functions

–Members and Allmembers (to see or not to see calculated members)

Select [Measures].[Sales Amount] on COLUMNS,

[Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–non emplty

Select [Measures].[Sales Amount] on COLUMNS,

Non empty [Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–nonemplty function against tuple

Select [Measures].[Sales Amount] on COLUMNS,

Nonempty( [Dim Product].[Model Name].[Model Name].Members, [Measures].[Sales Amount]

) on ROWS

from [InternetSales];

–Top/Bottom Count

Select [Measures].[Sales Amount] on COLUMNS,

TopCount( [Dim Product].[Model Name].[Model Name].Members, 5,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

Select [Measures].[Sales Amount] on COLUMNS,

BottomCount([Dim Product].[Model Name].[Model Name].Members, 31,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

— Specifying Axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–Not supported in SSMS

Select [Measures].[Sales Amount] on 0,

[Measures].[Discount Amount] on 1,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–use tuples instead has to start at 0

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–use Having clause

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS

Having [Measures].[Sales Amount] >=8000000 on 1

from [InternetSales];

–use slicers

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States];

–AND/OR not supported, use Tuples

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States]

AND [Order Date].[Calendar Year].&[2007];

–instead, from different hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ([Dim Sales Territory].[Sales Territory Country].&[United States]

,[Order Date].[Calendar Year].&[2007]);

–instead, from same hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ({[Dim Sales Territory].[Sales Territory Country].&[United States]

,[Dim Sales Territory].[Sales Territory Country].&[Canada]})

–Scope MDS Statement (create sub-cube)

Step 1: reconnect to server use “options”->”Additional Connection Parameters”

and set statement as Cube=”InternetSales”

Step 2: Run Scope Function one step a time

scope ([Measures].[Sales Amount],[Dim Sales Territory].[Sales Territory Country].&[Canada]);

this = [Measures].[Sales Amount]*1.1;

end scope;

–case statements

With Member [Measures].[Sales Targets] as

Case

when [Measures].[Sales Amount]>3000000 then “Achived”

when [Measures].[Sales Amount]<2000000 then “Below Expected”

ELSE “On Track”

End

Select {[Measures].[Sales Amount],[Measures].[Sales Targets]} on Columns,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country] on Rows

from [InternetSales]