I have a cube that has an amount measure (dollars), a time dimension(Month,
Qtr, Year) and an account dimension(Acct Category, Acct). For one account
I need to show the ending balance for the quarter rather than the sum of
the months for that account.

It looks like this if I do nothing:

Jan Feb Mar Q1
Account1 20 15 10 45
Account2 30 25 15 70

But I need it to like this:

Jan Feb Mar Q1
Account1 20 15 10 10
Account2 30 25 15 70

And this applies to only certain accounts in the account dimension, like
account1, and the others need to have the months summed up into the
quarter, like account2.

Any help would be much appreciated.

--
Message posted via http://www.sqlmonster.com

Hi Nathan

Well, you can always work with the lastchild functionality in a calculated
member, for example (if time dimension only has three levels, year, quarter
and month)

iif(isgeneration([time], 1),
([measures].[id], [time].lastchild),
[measures].[id])

What this says is that if the time dimension is on quarter level it shall
take the value of the last child in that dimension(that will be march, june,
september or december).

Best regards

/Martin

"Nathan Reichner via SQLMonster.com" wrote:

Martin,

Thanks for your reply. What I am trying to do is change the rollup
behavior of the amount measure for certain accounts only. So the MDX
statement below doesn't tell me how I can limit the lastchild to a
particular account. Also, the cube is accessed via the PivotTable Service
in Excel so I am thinking that the logic needs to be a custom rollup
formuala and not a calculated member.

Thanks in advance.

--
Message posted via http://www.sqlmonster.com

Hi again Nathan

First, there is no reason why you can't use Calculated members when you use
excel as front end to user. They are shown just as any measure.

But if you still want it to be a custom rollup it is possible as well. Go
into the quarter level of the time dimension and edit the Custom rollup
formula (in the advanced tab of the properties):

Use something like this:
IIf(membertostr([Account].CurrentMember) = "[Account].[Account
Name].&[Whatever account you have]",
[Time].CurrentMember.LastChild,
RollupChildren([Time].CurrentMember, "+"))

The last row is used just to sum up the values if not in one of the excluded
accounts. I you want to use the operators that you use normally you can use

RollupChildren([Time].CurrentMember,
[Time].CurrentMember.Properties("UNARY_OPERATOR"))

instead of the last row

This according to the sql-help but I havent tested this.

Good luck

/Martin


"Nathan Reichner via SQLMonster.com" wrote: