Dynamic cell references in cube functions

[Guest]

What is the syntax to make for instance the cubevalue function pick up
parameter values from the spreadsheet so my report becomes dynamic?

I have the following statement:
=CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")

I want to make a dynamic report so that the year [2007] and the month
[200710] changes according to dates I put into the spreadsheet (for instance
today()) so the report automatically updates the query to current time
periode.

Thanks in advance!

 

[Ian]

I don't know cubevalue and it isn't in Excel help on my machine. The normal
way to would be to refer to a value in a cell.

Assuming =TODAY() is in A1.
You may be able to replace 2007 with YEAR(A1) or you may have to put
=YEAR(A1) in eg B1 and replace 2007 with B1.
Similarly you may be able to use CONCATENATE(YEAR(A1),MONTH(A1)) in place of
200710 or you may have to put =CONCATENATE(YEAR(A1),MONTH(A1)) in eg C1 and
replace 200710 with C1.

Ian

 

[Guest]

Thanks for your input.

The problem is that the cube statement is within " " so excel see it as
text. I have to make it dynamic and at the same time convert it to text for
the cubevalue function to understand it. That is what I do not manage.

Clearer now?

Gunder
------------

I don't know cubevalue and it isn't in Excel help on my machine. The normal
way to would be to refer to a value in a cell.

Assuming =TODAY() is in A1.
You may be able to replace 2007 with YEAR(A1) or you may have to put
=YEAR(A1) in eg B1 and replace 2007 with B1.
Similarly you may be able to use CONCATENATE(YEAR(A1),MONTH(A1)) in place of
200710 or you may have to put =CONCATENATE(YEAR(A1),MONTH(A1)) in eg C1 and
replace 200710 with C1.

Ian

What is the syntax to make for instance the cubevalue function pick up
parameter values from the spreadsheet so my report becomes dynamic?

I have the following statement:
=CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")

I want to make a dynamic report so that the year [2007] and the month
[200710] changes according to dates I put into the spreadsheet (for
instance
today()) so the report automatically updates the query to current time
periode.

Thanks in advance!

 

[Ian]

Perhaps something like

=CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[" & B1 &
"].[" & C1 & "]")

based on yyyy in B1 and yyyymm in C1 as in my last post.

If this (or something very similar) doesn't work, then I'm afraid I don't
know. As I said, cubevalue is an unknown function to me.

Ian

Thanks for your input.

The problem is that the cube statement is within " " so excel see it as
text. I have to make it dynamic and at the same time convert it to text
for
the cubevalue function to understand it. That is what I do not manage.

Clearer now?

Gunder
------------

I don't know cubevalue and it isn't in Excel help on my machine. The
normal
way to would be to refer to a value in a cell.

Assuming =TODAY() is in A1.
You may be able to replace 2007 with YEAR(A1) or you may have to put
=YEAR(A1) in eg B1 and replace 2007 with B1.
Similarly you may be able to use CONCATENATE(YEAR(A1),MONTH(A1)) in place
of
200710 or you may have to put =CONCATENATE(YEAR(A1),MONTH(A1)) in eg C1
and
replace 200710 with C1.

Ian

What is the syntax to make for instance the cubevalue function pick up
parameter values from the spreadsheet so my report becomes dynamic?

I have the following statement:
=CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")

I want to make a dynamic report so that the year [2007] and the month
[200710] changes according to dates I put into the spreadsheet (for
instance
today()) so the report automatically updates the query to current time
periode.

Thanks in advance!