on 05-11-2016 2:42 PM
I have requirement like
- project 1 starts on 2015.FEB, so my report has to show from February 2015 thru today.
- project 2 starts on 2009.JUL, so my report has to show from July 2009 thru today.
Can anybody please guide me how can I achieve it ?
Sorry, but no detailed info about dimensions, members, properties etc...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
KAN,
Custom measure might not be the best way to go about this. You could probably do something on the reporting side. But as Vadim mentioned, you are not providing enough information for us to help.
What we need is:
- BPC version
- Brief business case
- Project dimension with property IDS
- Examples of data intersection where data is saved
- Future report layout
or
- Provide MDX example that you have tried to write
- Errors
Hope this helps
Akos
BPC version 10.0
Brief Business Case :
LTD Custom Measures for Account dimension.
Example :
If I am running my BPC report for July 2016 year and transaction data for the account contain the date for 2015 , 2014 & 2013 year then LTD measure filed will display the data from 2013 to 2016 current period year data.
While changing the context the report should display the value as discuss above .
I think its clear now .
I have not yet tried the MDX formula and never tired it before that's why looking for the help.
is tis code work for the requirement ?
MEMBER [MEASURES].[LTD] AS
'IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",
(SUM(PERIODSTODATE([%TID%].[LEVEL00], [%TID%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, -[MEASURES].[/CPMB/SDATA]))) ,
IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",
(SUM(PERIODSTODATE([%TID%].[LEVEL00], c.CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, [MEASURES].[/CPMB/SDATA]))),
IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),
IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3
But below code which found in thread is doing the same right ?
correct me if I am wrong
MEMBER [MEASURES].[LTD] AS
'IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",
(SUM(PERIODSTODATE([%TID%].[LEVEL00], [%TID%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, -[MEASURES].[/CPMB/SDATA]))) ,
IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",
(SUM(PERIODSTODATE([%TID%].[LEVEL00], c.CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])+( SUM(OPENINGPERIOD([%TID%].[LEVEL00]):ANCESTOR([%TID%].CURRENTMEMBER,[%TID%].[LEVEL00]).PREVMEMBER, [MEASURES].[/CPMB/SDATA]))),
IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),
IIF([%KONTO%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TID%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3
Based on your suggest develop and tested below code
but how to get previous all year data i mean dynamically system will identify like if system has data for previous 3 years then it should consider it
any different syntax to achieve the same
MEMBER [MEASURES].[LTD] AS
'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",
SUM([%TIME%].[2006.01]:[%TIME%].CURRENTMEMBER), -[MEASURES].[/CPMB/SDATA]),
IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",
SUM([%TIME%].[2006.01]:[%TIME%].CURRENTMEMBER), [MEASURES].[/CPMB/SDATA]),
IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST", ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),
IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ", -([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3
User | Count |
---|---|
15 | |
4 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.