MDX Date Range Query with Missing Transfer Date

Question

I need an MDX query for Mondrian, filtered by date, where one or both of the restriction dates may not exist. I use the following query, which works as long as the measurements exist on both 2013-01-01 and 2013-01-08. If one of the two dates does not exist, then it does not return any results, although sizes exist between them. How can I get this request to work even if there is a missing border date size?

SELECT NON EMPTY {Hierarchize({[Measures].[Number of Something]})} ON COLUMNS, NON EMPTY {[Date].[2013-01-01]:[Date].[2013-01-08]} ON ROWS FROM [Users] 

Answer 1

MDX is built with the assumption that every member you belong to exists; then it is best to make sure that all conceivable elements of the date dimension exist, having a pre-computed table with these values.

You can get a difficult task and implement this table as a stored procedure, but date measurements do not take up much space in the great scheme of things, so you are unlikely to ever do this.

I do not know another way to solve your problem.

Answer 2

try to eliminate NON EMPTY

Answer 3

Even I still do not understand the reasons for implementing this logic, you can hide it by adding. If you add a custom item to Mondrian, try it.

  /* Exclude Missing Member */ Create Set CurrentCube.[MissingMemberSet] As iif(IsError(StrToMember("[Dimension].[Hierarchy].&[MEMBER]")), {}, {[Dimension].[Hierarchy].&[MEMBER]}); Create Member CurrentCube.Measures.[Calculation on Missing Member] AS IIF ([MissingMemberSet].Count > 0, ([Dimension].[Hierarchy].&[MEMBER],Measures.[X Measure]), 0 ) , FORMAT_STRING = "Currency", LANGUAGE = 1033, NON_EMPTY_BEHAVIOR = { [X Measure] }, VISIBLE = 1 , DISPLAY_FOLDER = 'Display Folder' ; 

You can also implement IIF (IsError or IIF (Exists MDX) functions in use.