Hi

I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.

The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like

=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc. Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together. In other words - Is there an easier way of for say jan to july 2015 totals than

=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))

I haven't tested this but have assumed it works but a bit long and clumsy.

Thanks

Brian

Hi,

you can use CUBESET instead of CUBEMEMBER.

In CUBESET you can define range from cubemembers like this "{cubememberexpression1:cubememberexpression2}"

=CUBEMEMBER("OLAPCUBE","{[Time].[Time].[Year].&[2015].&[1].&[1]:[Time].[Time].[Year].&[2015].&[2].&[2]}")

But you should do these kind of calculations in your OLAP database. This is very common YTD measure.

• Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator 2 hours 16 minutes ago

Great - thanks for your help

Brian