MDX Calculated Member in where clause

Question

I am new to MDX queries. I have the following query and would like to limit the results to only show records where the Margin Pct is > 0. Any help would appreciated.


WITH 
    MEMBER [Measures].[Margin Pct] as ([Measures].[Mgmt Margin Excluding Markup]/[Measures].[Net Sales])*100,format_string="0.0" 
    MEMBER [Measures].[Mgmt Margin] as [Measures].[Mgmt Margin Excluding Markup],format_string="0.0" 
    MEMBER [Measures].[Mgmt Cost Unit] as [Measures].[Mgmt Cost Unit Excluding Markup],format_string="0.00" 
    MEMBER [Measures].[FOBPrce] as [Measures].[FOB Price],format_string="0.00" 
    MEMBER [Measures].[CommUnt] as [Measures].[Comm/Unit],format_string="0.000" 
    MEMBER [Measures].[RebUnt] as [Measures].[Reb/Unit],format_string="0.00" 
    MEMBER [Measures].[FrtUnt] as [Measures].[Frt/Unit],format_string="0.00" 
    MEMBER [Measures].[PriceUnt] as [Measures].[Price/Unit],format_string="0.00"
SELECT NON EMPTY { 
    [Measures].[Rpt Inv Shp Date], 
    [Measures].[Lbs Shipped],
    [Measures].[Net Sales], 
    [Measures].[FOBPrce], 
    [Measures].[CommUnt], 
    [Measures].[RebUnt], 
    [Measures].[FrtUnt], 
    [Measures].[PriceUnt], 
    [Measures].[Mgmt Cost Unit], 
    [Measures].[Mgmt Margin], 
    [Measures].[Margin Pct] 
} ON COLUMNS, NON EMPTY { 
    (
        [Item].[Group Sort].[Group Sort],
        [Item].[Form Sort].[Form Sort],
        [Item].[Specie Sort].[Specie Sort],
        {[Item].[Group thru Item ID].[Group].ALLMEMBERS},
        [Shrimp Group].[Shrimp Group].[Shrimp Group Name].ALLMEMBERS ,
        {[Item].[Form].[Form].ALLMEMBERS},
        [Item].[Meat - In Shell].[Meat or Inshell].ALLMEMBERS , 
        [Item].[Super Specie].[Super Specie].ALLMEMBERS ,
        {[Item].[Species].[Species].ALLMEMBERS},
        {[Item].[Item ID].[Item ID].ALLMEMBERS},
        [Item].[Desc-ItemID].[Item ID Description].ALLMEMBERS , 
        [Item].[Package Type].[Packaging].ALLMEMBERS ,
        {[Brand].[Brand].[Brand Name].ALLMEMBERS},
        {[Warehouse].[Warehouse].[Warehouse Code].ALLMEMBERS},
        [Order Invoice Lot].[Order-Invoice-Lot].[Lot].ALLMEMBERS ,
        {[Customer Account Number].[Customer Account No].Levels(1)},
        {[Ship To Customer].[Customer Name].Levels(1)},
        {[Sales Person].[Person].Levels(1)},
        [Order Invoice Lot].[Sales Order].[Sales Order].ALLMEMBERS , 
        [Order Invoice Lot].[Invoice].[Invoice].ALLMEMBERS
    ) 
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM (
    SELECT StrToSet( '{[Breaded Group].[Breaded Group].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Inventory Category].[Inventory Category].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Sold To Customer].[Customer Buying Group].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Ship To Customer Sales Group].[Ship To Customer Sales Group].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Sold To Customer].[Customer Legal Group].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Country Of Origin].[Long Name].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Is Sample].[Sample].[Description].[Regular]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Invoicing Status].[Invoicing Status-Detail].[Detail].[Sale Only],[Invoicing Status].[Invoicing Status-Detail].[Detail].[Credit Only]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Invoice Date].[Fiscal Year-Quarter-Month].[Fiscal Month].[Jul-FY13]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Sold To Customer].[Name].[All]}' ,CONSTRAINED ) ON COLUMNS FROM (
    SELECT StrToSet( '{[Is NRV].[NRV].[All]}' ,CONSTRAINED ) 
ON COLUMNS FROM [FishTrackerReporting] ) ) ) ) ) ) ) ) ) ) )

I have tried to use a where clause against the [Measures].[Margin Pct] but get this error:

The WHERE clause function expects a tuple set expression for the argument. A string or numeric expression was used.

I also tried to use a filter after the on columns part of the query but get out of memory issues so I think I am missing something.

DHN DHN · Accepted Answer · 2013-10-17T13:58:50

Too bad the reference of MDX is not the best. You can use HAVING or FILTER to achieve what you want. I'd go with HAVING since it's easier to use.

Please have a look here and find the last example.

MDX Calculated Member in where clause

1 Answers