I need an MDX query for Mondrian filtered by date, where one or both of the boundry dates may not exist. I'm using the query below that works as long as both 2013-01-01 and 2013-01-08 dimensions exist. If one of the two dates does not exist then it returns no results, even though the dimensions in between do exist. How would I get this query to work even in the case of a missing boundry date dimension?
SELECT NON EMPTY {Hierarchize({[Measures].[Number of Something]})} ON COLUMNS, NON EMPTY {[Date].[2013-01-01]:[Date].[2013-01-08]} ON ROWS FROM [Users] 3 Answers
Answers 1
MDX is built with the assumption that every member that you refer to exists; it is best then to make sure all conceivable date dimension members do exist by having a separate table with these values precomputed.
You could get tricky and implement that table as a stored procedure but date dimensions don't take up a lot of space in the grand scheme of things so you'd hardly ever do this.
I don't know of any other way to solve your problem.
Answers 2
try to eliminate the NON EMPTY
Answers 3
Even I haven't yet understand the reason of implementing this logic, you can hide this by adding . If you add custom member in 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' ; Also you can implement in using IIF(IsError or IIF(Exists MDX functions.
0 comments:
Post a Comment