Measure Expression in Query ?

Hi - I want to do a query like this, in management studio, but it is giving me an error message.

select sum({TopCount([Asset].[Asset].[Asset].members,5,([Portfolio].[Portfolio].&[DD2342342],[Measures].[Value Base]))}, [Measures].[Portfolio Weighting]) on columns
from [MIQB Daily]

I tried different strto.. functions but didn't find any proper solution.

The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used.

Thanks

Noordin

Hi Noordin,

Unfortunately you can't just insert an MDX expression that returns a value on an axis - you have to declare a calculated member first, use the expression as its definition, and then place the calculated member on the axis. Here's an example from Adventure Works:

with member measures.demo as

sum(topcount([Date].[Date].[Date].members, 5, [Measures].[Internet Sales Amount]), [Measures].[Internet Sales Amount])

select {measures.demo, [Measures].[Internet Sales Amount]} on 0,

[Product].[Category].members on 1

from

[Adventure Works]

HTH,

Chris

|||

Thanks Chris,

Actually I was playing with Excel 2007 cube... formulas ... may be I need to do some work around.

do you have an idea of #IND value, what does it mean, is it infinity?

Regards

Noordin

|||I'm afraid I don't have an install of Office 2007 handy at the moment, so I'm not sure. Anyone else? #IND isn't what AS will return for infinity though - it returns 1.#INF.

MDX Script - Infinit Recursion Detected

I'm getting the error message: "Infinit Recursion Detected. The loop dependencies is: 98 -> 99"

I have this script in Cube Calculations, in this order:

[account dimension].[account hierachy].[bank application] = IIF( [account dimension].[account hierachy].[Asset]>[account dimension].[account hierachy].[Liability], [account dimension].[account hierachy].[Asset]-[account dimension].[account hierachy].[Liability], 0 );

[account dimension].[account hierachy].[bank loan] = IIF( [account dimension].[account hierachy].[Asset]<[account dimension].[account hierachy].[Liability], [account dimension].[account hierachy].[Asset]-[account dimension].[account hierachy].[Liability], 0 );

"bank application" is children of "asset" account and "bank loan" is children of "liability". How can I fix that?

To calculate [bank application], you need the value of [Asset]. But [Asset] is the parent of [bank application], in the simplest case, the value of [Asset] is the sum of the values of all children which include [bank application], hence the recursion. A simplistic fix is to use the ~ unary operators to exclude [bank application] from contributing to the value of [Asset]. I would examing the hierarchy and calculations to see if they make sense in the first place.|||

It's a little bit difficult to say how to fix it without knowing a little bit more about your requirements.

But with the following assumptions:

that your account hierarchy is parent child|||

Yes, account hierarchy is parent child, I can't move the calculated members to another position but I want that "bank application" or "bank loan" rolling up to their parent.

I don't know in other countries, but in Brazil for oficial balance reports the assets and lialbility must have the same value, because the liability aggregates the profits and let say that: it's a convention here.

My application is a budget application, then the values are planning values and for simplicity we make asset and liability have the same value putting the diference (asset - liability) in the cash application or if the value is positive in the "bank application" and if the value is negative in the "bank loan" account.

That is my case, If the diference of asset and liability is positive I need to put these diference in "bank application" and if the diference is negtive I put the value in the "bank loan". Then the value rollup to their parent and asset and liability account will always have the same value.

With freeze I can do that kind of thing? I need to break the recursion, make the recursion run only one time.

|||

Yes the Freeze works fine!

Thanks!