问题描述:

I have a query in which I need to do some filtering. I can do it in a subcube, but I am wondering if I could do this in a WHERE clause without subcube. I think this solution would be faster/cleaner. I need to filter out product models with IB>0 in last month, this is my solution so far (only part of a query):

SELECT {[Measures].[AFR],[Measures].[IB]} ON COLUMNS,

([dim_ProductModel].[ODM].children)*[Dim_Date].[Date Full].children ON ROWS

FROM

(

SELECT

FILTER([dim_ProductModel].[Product Model].children,

([Measures].[IB]*[Dim_Date].[Date Full].&[2014-04-01]>0)) ON COLUMNS FROM

[cub_dashboard_spares]

)

however, I would prefer to have it in one query without subquery something like this (its not working though):

SELECT {[Measures].[AFR],[Measures].[IB]} ON COLUMNS,

([dim_ProductModel].[ODM].children)*[Dim_Date].[Date Full].children ON ROWS

FROM

[cub_dashboard_spares]

WHERE FILTER([dim_ProductModel].[Product Model].children,

([Measures].[IB]*[Dim_Date].[Date Full].&[2014-04-01]>0))

I get some error message kind of:

he MDX function CURRENTMEMBER failed because the coordinate for the ... contains a set..

I basically understand why is he not accepting is as in an WHERE clause I should be more specific but I wonder if there is some possibility to rewrite it so that it works.

I don't want that ProductModel appears in the results set.

网友答案:
SELECT  {[Measures].[AFR],[Measures].[IB]} ON COLUMNS,
([dim_ProductModel].[ODM].children)*[Dim_Date].[Date Full].children  ON ROWS 
FROM 
[cub_dashboard_spares]
WHERE 
   ({[dim_ProductModel].[Product Model].children},
    [Measures].[IB],
    PERIODSTODATE( 
      [Dim_Date].[Date Full], //<<needs to be a level from your Dim_date
      [Dim_Date].[Date Full].&[2014-04-01])  //<<needs to be a member from the levelyou have used in above argument
      )
相关阅读:
Top