Hi,

I need to get Balances for all the accounts on a Date . In case the
Balance Amount is zero for that Date in then i want to get the last
existing record .

Similarily along with the current month ,

i want this recursion to start from Prevoius month of the Date selected


My Transaction Date Hierachy is

-Year
-Quater
-Month
-Week
-Day

My CoalesceEmpty Function returns me desired results when i pass
([Transaction Dates].[Day], [Transaction Dates].CurrentMember in the
Closing Period() function and

[Transaction Dates].currentmember.prevmember in my CoalesceEmpty()
function

However to start the recusion from the Previous Month of the Selected
Date I
passed

[Transaction
Dates].CurrentMember.parent.parent.prevmember.lastchild. lastchild

in my Closing period function and

[Transaction
Dates].currentmember.parent.parent.prevmember.lastchild. lastchild.prevmember

in my CoalesceEmpty() function . This returned me only the value for
the Last Date of the Previous month but did not get into the recursion
into previous dates when the Balance was empty.


I wrote a Query :
------------------------------------------

WITH MEMBER [Measures].[OutstandingPrincipal] as

'([Measures].[Outstanding Principal],ClosingPeriod ([Transaction
Dates].[Day], [Transaction Dates].CurrentMember))'

MEMBER [Measures].[Relative Total Outstanding Principal Amount] as
' CoalesceEmpty(([Measures].[OutstandingPrincipal], [Transaction
Dates].CurrentMember),
([Relative Total Outstanding Principal Amount], [Transaction
Dates].currentmember.prevmember))'


-------------------------------------Previous Actual
Month-----------------

MEMBER [Measures].[OutstandingPrincipalPrevMonth] as
'([Measures].[Outstanding Principal],ClosingPeriod ([Transaction
Dates].[Day], [Transaction
Dates].CurrentMember.parent.parent.prevmember.lastchild. lastchild))'

---------Recursive Previous month
-------------------------------------------------------------

MEMBER [Measures].[RelativeOutstandingPrincipalPrevMonth] as
' CoalesceEmpty(([Measures].[OutstandingPrincipalPrevMonth],
[Transaction Dates].CurrentMember),
([Measures].[RelativeOutstandingPrincipalPrevMonth], [Transaction
Dates].currentmember.parent.parent.prevmember.lastchild. lastchild.prevmember))'



Select {[Measures].[Relative Total Outstanding Principal
Amount],[Measures].[RelativeOutstandingPrincipalPrevMonth] } on 0

from Cube where
[Transaction Dates].[All Transaction Dates].[2006].[Quarter
1].[January].LastChild.lastchild


-----------------------------------------

Please help me on this .

TIA

A couple of issues to consider:

- It seems that the ClosingPeriod() is superfluous in
[OutstandingPrincipalPrevMonth], because the other code assumes that
[Transaction Dates].CurrentMember is already at the day level (not sure
if that's a valid assumption)

- Even at the day level, [Transaction Dates]
.currentmember.parent.parent.prevmember.lastchild. lastchild will not
work as expected: start with June 15th. Initially, it evaluates May
31st, but if that's empty, the next recursion will evaluate April 30th,
not May 30th.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Thanks for your reply .

1. I understand that theres no need to use Closing Period() Function

2. How to apply CoalesceEmpty() for getting correct Balances starting
for Current Month as well as starting for Previous Month in the Same
Recursive Manner when i have one
Transaction Date (at Day Level ) in my where Clause(Slicer) of MDX
Query.

Thanks again.






Please tell me a possible solution .

Deepak Puri wrote:

I found out a solution to :

------Actual Current Current
month------------------------------------------
MEMBER [Measures].[OutstandingPrincipal] as '([Measures].[Outstanding
Principal], [Transaction Dates].CurrentMember)'

----------------Recursive Current Month---------------------
MEMBER [Measures].[Relative Total Outstanding Principal Amount] as
' CoalesceEmpty(([Measures].[OutstandingPrincipal], [Transaction
Dates].CurrentMember),
([Relative Total Outstanding Principal Amount], [Transaction
Dates].currentmember.prevmember))'

----------------Recursive Previous Month---------------------

MEMBER [Measures].[RelativeOutstandingPrincipalPrevMonth] as '
([Measures].[Relative Total Outstanding Principal
Amount],PARALLELPERIOD(
[Transaction Dates].[Month])) '


Thanks .


agarwalshuchi (AT) gmail (DOT) com wrote: