• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5143
  • Last Modified:

CUBEVALUE function with multiple dimension members

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
WizzoP
Asked:
WizzoP
1 Solution
 
agandauCommented:
Try this:

=CUBEVALUE("jago","[TotalSales]",CUBESET("jago", "{[Salesman].&[Fred],[Salesman].&[George]}"))
0
 
WizzoPAuthor Commented:
YES. That's perfect. Many thanks agandau.
0
 
ManishVaidyaCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now