User Multi Select on Excel and opening balances

Question

Hi

I've got a calculated member that provides and opening asset. If the user selects member on the month level, then that is what he gets, if he's on the year level then it returns January's opening asset - this is great.

e.g.

CREATE MEMBER CURRENTCUBE.[Measures].[Forecasted Opening Assets]
AS
IIF(
        [Budget Date].[Year-Quarter-Month-Day Hierarchy].CurrentMember.Level
						IS 
			[Budget Date].[Year-Quarter-Month-Day Hierarchy].[Calendar Year]
		,
            (
                [Measures].[Forecasted Opening Assets Persisted]
                , [Budget Date].[Year-Quarter-Month-Day Hierarchy].CurrentMember.FirstChild.FirstChild
            )
        , [Measures].[Forecasted Opening Assets Persisted]
    )

My problem is this:

If the user multi selects June to December, I want to return the opening balance for June.

I just don't know how to check if the user has returned a set, obviously current member is not going to help.

Any thoughts ?

Regards,

Jason

---

MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

Answer 1

You can use EXISTING key word to identify the selections on the Month attribute and return the first item selected.

Sample query based on Adventure works :

with  member measures.val as ({existing [Date].[Month Name].[Month Name].members}.item(0),[Measures].[Internet Sales Amount])
select {measures.val,[Measures].[Internet Sales Amount]} on columns
from [Adventure Works]
where({[Date].[Month Name].&[2005]&[7],[Date].[Month Name].&[2005]&[8],[Date].[Month Name].&[2005]&[9]})

Your user selections would go in the where clause. FOr closing balance, item(0) wont work, so you have to use Tail function.

---

amit kar

Answer 2

Hi Amit

Thanks, didn't event think of using Existing clause.

Appreciate the quick response, and the example!

Regards,

Jason

---

MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)