Report Portal

MDX-How can I use dimension members/measures in query when sometimes they might not exists

Q: How can I reference dimension members/measures in MDX statements when sometimes they might not exists? Example: Lets say I want to create measure for 2007 sales, but maybe my cube still does not contain 2007 date values

A: You could use IsError function: IIF(IsError(YourDim), NULL, YourDim). Example, this statement will give you back NULL instead of #error in Adventure Works DW, as there are no sales for year 2007:

WITH MEMBER [Measures].[2007 Sales] AS
IIF(IsError([Date].[Calendar].[Calendar Year].&[2007]), NULL, ([Date].[Calendar].[Calendar Year].&[2007], [Measures].[Sales Amount]))
SELECT [Measures].[2007 Sales] ON 0
FROM [Sales Summary]

Tags: mdx, faq


2007-2015 VidasSoft Systems Inc.