Solved

CUBEVALUE function  with multiple dimension members

Posted on 2009-03-31
3
3,913 Views
Last Modified: 2013-11-16
I'm using Excel 2007 to query a cube in SSAS.
I need to get the total of a measure, for a subset of Dimension members.

Say that the measure is TotalSales, and there's a Salesman dimension with 3 members :Fred, George and Jim.

I need the combined  total sales for Fred and George .
The function for getting the total sales for one salesman is:-

=CUBEVALUE("jago","[TotalSales]","[Salesman].&[Fred]")

But when I try to add another salesman, I get #NA

=CUBEVALUE("jago","[TotalSales]","{([Salesman].&[Fred]),([Salesman].&[George])}")

Can anyone help?



0
Comment
Question by:WizzoP
3 Comments
 
LVL 6

Accepted Solution

by:
agandau earned 500 total points
Comment Utility
Try this:

=CUBEVALUE("jago","[TotalSales]",CUBESET("jago", "{[Salesman].&[Fred],[Salesman].&[George]}"))
0
 

Author Closing Comment

by:WizzoP
Comment Utility
YES. That's perfect. Many thanks agandau.
0
 

Expert Comment

by:ManishVaidya
Comment Utility
Hi agandau:,

Do you have an idea on how to use the same function when we are referring to two members of the same hierarchy? I am  facing a #N/A error when I try to use the cubeset formula on two members of the same hierachy.

=CUBEVALUE("CubeConnection","[Measures].[Amount]",CUBESET("CubeConnection","{[Account Hierarchy].[Account Hierarchy].&[800000],[Account Hierarchy].[Account Hierarchy].&[900000]}"))
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now