Excel OLAP question referencing other cells

aivoryuk

Board Regular
Joined
Nov 18, 2009
Messages
130
I originally posted this in the powerpivot forum but as powerpivot is not supported in 2007 it might be better here so apologies.

I have this simple query

=CUBEMEMBER("localhost RiskCube3 Risk Cube",{"[Client].[PK Accountid].&[2]","[Date].[PK Date].&[2011-11-23T00:00:00]"})

But what I would like to do is rather than hardcode the values I would like them to reference another cell in excel

I can do this by

=CUBEMEMBER("localhost RiskCube3 Risk Cube","[Date].[PK Date].&["&D3&"]")

and for the date

=CUBEMEMBER("localhost RiskCube3 Risk Cube","[Client].[PK Accountid].&["&C2&"]")

but I can't get these to work together

=CUBEMEMBER("localhost RiskCube3 Risk Cube",{"[Client].[PK Accountid].&["&C2&2]","[Date].[PK Date].&["&D3&"]"})

I keep getting the formula error message.

Also for the date part the reference cell may not be in the standard SSAS format by in dd/MM/yyyy format (and without time) so what would be the best way to format this?
I can use a helper cell =CONCATENATE(TEXT(D2,"yyyy-mm-dd")) but then i need to add the T00:00:00

Regards
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I can't test, but try:

=CUBEMEMBER("""localhost RiskCube3 Risk Cube"",{""[Client].[PK Accountid].&["&C2&"]"",""[Date].[PK Date].&["&D3&"]""}")
 
Upvote 0
How about?

=CUBEMEMBER("localhost RiskCube3 Risk Cube","{""[Client].[PK Accountid].&["&C2&"]""","""[Date].[PK Date].&["&D3&"]""}")
 
Upvote 0
Do these 2 formulas return what you expect?

="{""[Client].[PK Accountid].&["&C2&"]"""
="""[Date].[PK Date].&["&D3&"]""}"

They are the second and third arguments in the formula I gave you.
 
Upvote 0
Do these 2 formulas return what you expect?

="{""[Client].[PK Accountid].&["&C2&"]"""
="""[Date].[PK Date].&["&D3&"]""}"

They are the second and third arguments in the formula I gave you.

Hi Andrew
I have checked these 2 formulas and they both return N/A"

Regards
 
Upvote 0
Hi Andrew

I've managed to solve it. I should have been using Cubevalue rather than cube member

=CUBEVALUE("localhost RiskCube3 Risk Cube",("[Client].[PK Accountid].&["&C2&"]"),("[Date].[PK Date].&["&D3&"]"),("[Measures].[False Positive]"))

I have also used a helper column for converting the date from dd/mm/yyyy
to =CONCATENATE(TEXT(D2,"yyyy-mm-dd"),"T00:00:00")

Thanks for your time
 
Upvote 0

Forum statistics

Threads
1,215,220
Messages
6,123,695
Members
449,117
Latest member
Aaagu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top