|
Hi all
I am new to mdx and iam trying to find status of a loan between year and year before that was status(x) and became status(y). i have a table (loanid,Year, month, status)
statuses are Regular, Arrear, Default,...
i need to write an mdx query to find out % of loans that were Regular and became Arrear for any two consecutive years.and display them in a scorecard.
i tried writting query but not getting any values
1-
with member measures.CountContracts as
count(
([Contract].[Fact Contract].ALLMEMBERS,
([Dim Yearly Status Date].[Date Year].currentmember, [Contract Status].[Status Description].&[Regular]) ,
([Dim Yearly Status Date].[Date Year].currentmember.prevmember,[Contract Status].[Status Description].&[Arrear] )
) )
select measures.CountContracts on columns ,
[Dim Yearly Status Date].[Date Year].currentmember on rows
from [Retail Cube]
also trying this query in SSRS and getting after passing contractid as parameter
2-
SELECT NON EMPTY { [Measures].[Fact Yearly Status Count] } ON COLUMNS, NON EMPTY { ([Dim Yearly Status Date].[Date Year].[Date Year].ALLMEMBERS * [Contract Status].[Status Description].[Status Description].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@ContractFactContract, CONSTRAINED) ) ON COLUMNS FROM [Retail Cube]) WHERE ( IIF( STRTOSET(@ContractFactContract, CONSTRAINED).Count = 1, STRTOSET(@ContractFactContract, CONSTRAINED), [Contract].[Fact Contract].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
getting back year, count, status but for [Contract].[Fact Contract].currentmember while i want sum of all contracts that were regular and became Arrear
thanks in advance
|
