MS SQL Server OLAP - P&L report must give diff. figures for Jan then for other months, how to do?

Author

P&L report must give diff. figures for Jan then for other months, how to do?

Christophe Berton

2006-01-26, 4:58 pm

Hi,

I have made a cube-repport that gives me the delta between two months
(profit&Loss report),
only for hte month January, I wnat to see the figures of Jan, not
delta(Jan - dec).
I don't seem to get that last thing right:

with member [measures].[delta] as
'(time.currentmember,[measures].[MTD]) -
(time.prevmember,[measures].[MTD])'

Select non empty
{descendants([time].[2005],[time].[month])} on columns,
{[type3].[plan3].[profit and loss]} on rows
from [analytical cube]
where ([measures].[delta], [entity].[all entities].[entity001])

I thought doing something like

with member [measures].[delta] as '
IIF(time.currentmember = [time].[januari],
(time.currentmember,[measures].[MTD]),
(time.currentmember,[measures].[MTD]) -
(time.prevmember,[measures].[MTD])'

which is
member = (if (month= january) then currentmonth.amount else
current.amount - prev.amount)

I had it running, but with ERR in all cells telling me that an endless
loop caused the problem.

------------------------------------------------------------------------
IT Interview Questions : http://www.geekinterview.com IT Tutorials and Articles : http://www.geekarticles.com Oracle and Oracle Apps Training : http://www.exforsys.com

Deepak Puri

2006-01-26, 4:58 pm

You may be encountering the problem explained in Mosha's blog below, so
see if this small change fixes it:
[color=darkred]
with member [measures].[delta] as '
IIF(time.currentmember is [time].[januari],
(time.currentmember,[measures].[MTD]),
(time.currentmember,[measures].[MTD]) -
(time.prevmember,[measures].& #91;MTD])'[color=dar
kred]

http://sqljunkies.com/WebLog/mosha/...11/04/4959.aspx[color=darkred]
...
It is not uncommon to see people trying to write something like
following:

[Account].CurrentMember = [Account].&[Flow]

While the above expression looks very natural, it is completely wrong !
The reason is that AS2000 implementation of operator "=" can only
compare numbers or strings. Therefore the implicit data type conversion
from member object to scalar is applied (I hope to write an article
about MDX data types and type conversions soon) by converting
[Account].CurrentMember to [Account].CurrentMember.Value and converting
[Account].&[Flow]to [Account].&[Flow].Value . So instead of comparing
the member objects, this will compare their cell values !
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links