CODEWITHSUNDEEP
sqlmdxolap
user3512885
user3512885

Reputation: 99

Adding a filter on MDX query

SELECT  
  {[Measures].[Imports - Tons]
   , [Measures].[Imports - Metric Tonnes]
   , [Measures].[Import Customs Value]  } ON COLUMNS,  
{CROSSJOIN (
   [Date].[Month Year].members
  ,[Country].[Country Name].members
  ,[Steel Mill Product].[Steel Mill Product Name].[Plates-Cut Length]
  ,[Concordance].[HTS 10 Digit Code].members) }
ON ROWS  
FROM [AISISteel]   
WHERE ([Date].[Year].[2014], [Concordance].[HTS 2 Digit Code].[72])

I have this MDX Query.
I want to get back all this data but only the data where Import-Tons>0. How would I do this?

Upvotes: 2

Views: 167

Answers (1)

whytheq
whytheq

Reputation: 35557

You can try using the FILTER function:

SELECT  
  {[Measures].[Imports - Tons]
   , [Measures].[Imports - Metric Tonnes]
   , [Measures].[Import Customs Value]  } ON COLUMNS,  
FILTER(
  {CROSSJOIN (
     [Date].[Month Year].members
    ,[Country].[Country Name].members
    ,[Steel Mill Product].[Steel Mill Product Name].[Plates-Cut Length]
    ,[Concordance].[HTS 10 Digit Code].members) }
  , [Measures].[Imports - Tons] > 0
  )
ON ROWS  
FROM [AISISteel]   
WHERE ([Date].[Year].[2014], [Concordance].[HTS 2 Digit Code].[72])

Also the rarely used HAVING clause is a possible option for you

SELECT  
  {[Measures].[Imports - Tons]
   , [Measures].[Imports - Metric Tonnes]
   , [Measures].[Import Customs Value]  } 
ON COLUMNS,  
  {CROSSJOIN (
     [Date].[Month Year].members
    ,[Country].[Country Name].members
    ,[Steel Mill Product].[Steel Mill Product Name].[Plates-Cut Length]
    ,[Concordance].[HTS 10 Digit Code].members) }
HAVING [Measures].[Imports - Tons] > 0
ON ROWS  
FROM [AISISteel]   
WHERE ([Date].[Year].[2014], [Concordance].[HTS 2 Digit Code].[72])

Also just for conciseness there is a shortcut for the CROSSJOIN function which is nearly always preferred:

SELECT  
  {[Measures].[Imports - Tons]
   , [Measures].[Imports - Metric Tonnes]
   , [Measures].[Import Customs Value]  } 
ON COLUMNS,  
    {[Date].[Month Year].members*
     [Country].[Country Name].members*
     [Steel Mill Product].[Steel Mill Product Name].[Plates-Cut Length]*
     [Concordance].[HTS 10 Digit Code].members}
HAVING [Measures].[Imports - Tons] > 0
ON ROWS  
FROM [AISISteel]   
WHERE ([Date].[Year].[2014], [Concordance].[HTS 2 Digit Code].[72])

Edit

If you compare the above to the following are the execution times much different?

SELECT  
  {[Measures].[Imports - Tons]
   , [Measures].[Imports - Metric Tonnes]
   , [Measures].[Import Customs Value]  } ON COLUMNS,  
  FILTER(
    NONEMPTY(
     [Date].[Month Year].members*
     [Country].[Country Name].members*
     [Steel Mill Product].[Steel Mill Product Name].[Plates-Cut Length]*
     [Concordance].[HTS 10 Digit Code].members
    ,[Measures].[Imports - Tons]
    )
  ,[Measures].[Imports - Tons] > 0
  )
ON ROWS  
FROM [AISISteel]   
WHERE ([Date].[Year].[2014], [Concordance].[HTS 2 Digit Code].[72])

Upvotes: 5

Related Questions