Wednesday, 30 December 2009

Sample MDX Queries

MDX QUERRIES

select {[Measures].[Tax Amount],[Measures].[Discount Amount],[Measures].[Sales Amount]}on columns,{crossjoin([Dim Customer].[First Name].[First Name],crossjoin([Dim Customer].[Middle Name].[Middle Name],[Dim Customer].[Last Name].[Last Name]))}on rows from[Adventure Works DW]

----------------------------------------------------------------------------------------------------------------------select {[Measures].[Tax Amount],[Measures].[Discount Amount],[Measures].[Sales Amount]}on columns,non empty{crossjoin([Dim Customer].[First Name].[First Name].members,[Dim Customer].[Middle Name].[Middle Name],[Dim Customer].[Last Name].[Last Name])}on rows from[Adventure Works DW]where[Dim Customer].[State Province Name].&[Illinois]

------------------------------------------------------------------------------------------------------------------------select {filter(([Measures].[Tax Amount],[Measures].[Discount Amount],[Measures].[Sales Amount])and[Measures].[Sales Amount]>=500)}on columns,{crossjoin([Dim Customer].[First Name].[First Name],[Dim Customer].[Middle Name].[Middle Name],[Dim Customer].[Last Name].[Last Name])}on rows from[Adventure Works DW]

--------------------------------------------------------------------------------------------------------------------------------------------------with member [Measures].[per] as ([Measures].[Tax Amount]/[Measures].[Sales Amount])*100 select {[Measures].[Tax Amount],[Measures].[Discount Amount],[Measures].[Sales Amount],[Measures].[per]}on columns,{crossjoin([Dim Customer].[First Name].[First Name],[Dim Customer].[Middle Name].[Middle Name],[Dim Customer].[Last Name].[Last Name])}on rows from[Adventure Works DW]

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

No comments:

Post a comment