Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shagarwal
Starting Member
5 Posts |
 Posted - 2006-03-03 : 10:58:40
|
| I have a Loan Dimension containing many Different Loan Ids Loan Indicator Dates Outstanding BalanceThe requirement is that we need to get the Outstanding Balance against Loan Date that user has specified. If the date does not exist in my cube then I should be able to get Principal Amount for the nearest date against all the different Loan Ids in my data e.g. I have a IndicatorDate dimension in my Cube which has a hierarchyIndicatorDate-Year--Month---DayI have 3 records:October 11 2005 November 14 2005November 24 2005User can enter any date in the Report ParameterFor Example, If user enters the date as "15 November 2005" in a Report parameter Then I want a formula that would give me Outstanding Balance for "14 November 2005" ie (Nearest previous existing date)Similarly if the user enters "13 November 2005" then the formula should return me "11 October 2005" i.e. (Nearest previous existing date is in October month)I tried using Head Function in MDX along with not empty function but the problem is that Head function starts from the beginning and returns the item that we specify. I do not Know the "count" of item in the hierarchy.Currently I m trying like:Select {[Measures].[Arrear30to59]} on 0,{Head (Filter (Descendants([LoansIndicatorDate].[All LoansIndicatorDate].children,3),NOT IsEmpty([LoansIndicatorDate].CurrentMember.Name="25" and [LoansIndicatorDate].Parent.Name="November" and [LoansIndicatorDate].parent.parent.Name="2005")),1).item(0)} on 1from LoansBut this is not returning correct results.Please let me know if u need any more details.Thanks in Advance. |
|
|
|
|
|
Problem in Writing MDX: