MDX, can''t make correct totals at [(All)] level

Hi,

I'm trying to make a calculate member using this script:

Code Snippet

iif([Stores].[Store Name].CurrentMember.level is [Stores].[Store Name].[(All)],

sum(

(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

(([Stores].[Store Name].currentmember, [Measures].[S v net price budget]) <> 0)

), [Measures].[S v net price disc])),

[Measures].[S v net price disc])

but the total generated is always the same as if I made a sum for the [Measures].[S v net price disc].

I checked folowing:

Code Snippet

with member measures.test1 as sum(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

[Measures].[S v net price budget] <> 0)

, [Measures].[S v net price disc])

select

measures.test1 on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

Code Snippet

select

([Stores].[Store Name], [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

it gives the same result, but the:

Code Snippet

select

(FILTER(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price budget] <> 0), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 92 colums, and:

Code Snippet

select

(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 146 colums.

So the totals should be different.

I have been trying many different scripts and the result was always the same, so if You have any idea how to make it work, help me !!!

Thanks,

Micha?

Assuming you're using AS 2005, it may be worth explictly specifying member and level in Descendants(), rather than relying on hierarchy defaults - does that make any difference?

Descendants([Stores].[Store Name].CurrentMember, [Stores].[Store Name].[Store Name])

MDX, can''t make correct totals at [(All)] level

Hi,

I'm trying to make a calculate member using this script:

Code Snippet

iif([Stores].[Store Name].CurrentMember.level is [Stores].[Store Name].[(All)],

sum(

(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

(([Stores].[Store Name].currentmember, [Measures].[S v net price budget]) <> 0)

), [Measures].[S v net price disc])),

[Measures].[S v net price disc])

but the total generated is always the same as if I made a sum for the [Measures].[S v net price disc].

I checked folowing:

Code Snippet

with member measures.test1 as sum(FILTER(

descendants([Stores].[Store Name], [Stores].[Store Name]),

[Measures].[S v net price budget] <> 0)

, [Measures].[S v net price disc])

select

measures.test1 on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

Code Snippet

select

([Stores].[Store Name], [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

it gives the same result, but the:

Code Snippet

select

(FILTER(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price budget] <> 0), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 92 colums, and:

Code Snippet

select

(descendants([Stores].[Store Name], [Stores].[Store Name]), [Measures].[S v net price disc]) on 0,

[Dates].[Date].[Month Desc].&[09/2007] on 1

from sales;

has 146 colums.

So the totals should be different.

I have been trying many different scripts and the result was always the same, so if You have any idea how to make it work, help me !!!

Thanks,

Micha?

Assuming you're using AS 2005, it may be worth explictly specifying member and level in Descendants(), rather than relying on hierarchy defaults - does that make any difference?

Descendants([Stores].[Store Name].CurrentMember, [Stores].[Store Name].[Store Name])

MDX Script: How do I create a YTD-Balance Measure?

Hi,

I am using the Standard Edition of SQL Server 2005. I have a financial reporting cube with the measure Amount and several dimensions (Time, Account, etc). A simplified version of the data is:

AccountType

Month1

Month2

Month3

Month4

etc...

Asset

200

20

25

30

Liability

-100

-5

-10

-15

If I use the default cube then this is how data will show. What I would like to do is to add a YTD-Balance measure to the cube. This YTD-Balance measure would show the following data:

AccountType

Month1

Month2

Month3

Month4

Asset

200

220

245

275

Liability

-100

-105

-115

-130

So instead of showing the difference in a month (eg 20) like the underlying data, I would like to show a running total for the year (eg 200+20=220). My attempt so far is:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM({PeriodsToDate([Time].[(All)])}, [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Amount] },
VISIBLE = 1;

This doesn't do what I want however: it sums all months in a quarter, all quarters in a year, and all years in the cube. What I instead want is for each month to be a summary of the months in the year so far. Is anyone able to help me out at all with this?

Thanks, Matt

Assuming that there is a [Year] attribute in the [Time] dimension, whose type is "Years"; and that there is a [Time].[Calendar] hierarchy (as in the Adventure Works Date dimension), then you should be able to use the YTD() function:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM(YTD([Time].[Calendar].CurrentMember), [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Measures].[Amount] },
VISIBLE = 1;

|||A word of caution: You should only set the NON_EMPTY_BEHAVIOR if you are certain that there actually is a value in [Measures].[Amount] for the cells in which you want to display YTDBalance. Example: If you wanted to show the YTDBalance for all months in 2006, but you only had data up until July 2006, the calculated member suggested by Deepak would only return values for YTDBalance up until July 2006.

MDX script to scope on all but the leaf levels

Hi,

I am trying to specify a scope statement on all non-leaves members of all hierarchies of a dimension (time dimension basically) so I would need to say something like this:

scope (MyMeasure, not leaves(TimeByDay));
this = (MyMeasure,timeByDay.currentHierarchy.currentmember.firstChild);
end scope;

Does anybody see a way of doing this without explicitly repeating a scope statement for each implemented hierarchy.level-above-leaf like the one below?

scope (
{[Measures].[RG Queue State],[Measures].[RQ Distinct Queue State]},
[Processed Statistics Period].[YMD].[Month of year],
[Processed Statistic Type].[Processed Statistic].[Processed Statistic Action].&[1.]);

this = ([Processed Statistics Period].[YMD].currentmember.FirstChild);

end scope;

Thanks

I'm not sure of your particular implementation, but have you considered inverting the problem. Something like:

a) Set the aggregation method for your measure to FirstChild
b) If necessary, redefine the aggregation at the leaves level:

Scope (myMeasure, leaves(TimeByDay));
this = custom aggregation;
end scope;|||using leaves in the scope statement makes the performance go waaaay down...|||Hi Zoran,

What you should be able to do is to scope your calculation on the All Member of the granuarity attribute of your dimension. So if the granularity attribute of your TimeByDay dimension is Month, then

SCOPE([MEASURES].[MYMEASURE], [TIMEBYDAY].[MONTH].[ALL]);
THIS={EXISTING([TIMEBYDAY].[MONTH].[MONTH].MEMBERS)}.ITEM(0).ITEM(0);
END SCOPE;

should do the job of returning the first month that exists with every member on every attribute of the dimension, which is what you want to do I think. Does this work for you? Is the performance ok?

Chris

mdx script context execution

Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.

Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.|||

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.

|||

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)?

It'd be great.

have you any examples?

Thank you very very much!!!!

|||Check out this link for examples: http://www.codeplex.com/ASStoredProcedures|||

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

That value would be calculated by a stored procedure. When the stored procedure is deployed it can be configured to run under a specific user account. In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query. This would return a cellset and the stored proc would extract the value of the required cell and return that value|||

Thank you very much again.

You say: “

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

“

But I wonder if in this way the “poor” user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to… In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way?

However, I give you an example of what I need to do. Let’s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the “[DIM Mesi].[DIM Mesi].&[01]”, “[DIM Mesi].[DIM Mesi].&[02]” and the ”existing” with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I’d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can’t see that value) so that the total is not twice, in case I don’t use VisualTotals.

If possible, I’d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong?

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope

mdx script context execution

Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.

Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.|||

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.

|||

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)?

It'd be great.

have you any examples?

Thank you very very much!!!!

|||Check out this link for examples: http://www.codeplex.com/ASStoredProcedures|||

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

That value would be calculated by a stored procedure.

When the stored procedure is deployed it can be configured to run under a specific user account.

In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

This would return a cellset and the stored proc would extract the value of the required cell and return that value|||

Thank you very much again.

You say: “

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

“

But I wonder if in this way the “poor” user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to… In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way?

However, I give you an example of what I need to do. Let’s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the “[DIM Mesi].[DIM Mesi].&[01]”, “[DIM Mesi].[DIM Mesi].&[02]” and the ”existing” with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I’d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can’t see that value) so that the total is not twice, in case I don’t use VisualTotals.

If possible, I’d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong?

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope

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!