我是MDX查询的新手 . 我有以下查询,并希望将结果限制为仅显示保证金百分比> 0的记录 . 任何帮助将不胜感激 .
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] ) ) ) ) ) ) ) ) ) ) )
我试图对[Measures] . [Margin Pct]使用where子句但是得到这个错误:
WHERE子句函数需要参数的元组集表达式 . 使用了字符串或数字表达式 .
我也尝试在查询的on列部分之后使用过滤器,但是出现了内存问题,所以我想我错过了一些东西 .
1 回答
太糟糕了,MDX的参考并不是最好的 . 您可以使用
HAVING
或FILTER
来实现您想要的效果 . 我会选择HAVING
,因为它更容易使用 .请看一下here并找到最后一个例子 .