- SAP Community
- Products and Technology
- Financial Management
- Financial Management Q&A
- BPC 10/10.1 NW with HANA MDX / Member Formulas usi...
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
BPC 10/10.1 NW with HANA MDX / Member Formulas using property values
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 10-28-2015 4:47 PM
Hi everyone,
I'm trying to calculate a simple annualized calculation with the following formula in BPC10.1 on HANA with HANA MDX turned ON, this used to work with HANA MDX turned OFF.
([ACCOUNT].[PARENTH1].[MF_SALES_ANN])*(-12/([TIME].CurrentMember.Properties("BASE_PERIOD")))
I tried following the workaround provided in this discussion: BPC 10 NW Member formula using property value but it doesn't seem to work for me.
Is there an alternative for a member formula using properties when you have HANA MDX turned on? I get an "Internal Error" in the member formula editor if I try to use STRLEN and if I check the query in HANA Studio I get the following message:
([ACCOUNT].[PARENTH1].[MF_SALES_ANN])*(-12/(STRLEN([TIME].CurrentMember.Properties("BASE_PERIOD")))
"Could not execute 'MDX WITH MEMBER [Measures].[YTD] AS ...' in 4 ms 521 µs .
SAP DBTech JDBC: [256]: sql processing error: Error occurred while executing a MDX statement. The error message is: The usage of STRLEN in the current context is not supported"
([ACCOUNT].[PARENTH1].[MF_SALES_ANN])*(-12/(STRTOVALUE([TIME].CurrentMember.Properties("BASE_PERIOD")))
Now, if I try using STRTOVALUE in HANA Studio by modifying the query that the EPM Add-in generates, it works but the BPC formula editor gives me the same "Internal Error" while trying to validate the formula.
If I use [TIME].CurrentMember.Properties("BASE_PERIOD") I get the following error because of the different types "string" & "integer" in an arithmetical operation:
Could not execute 'MDX WITH MEMBER [Measures].[YTD] AS ...' in 119 ms 136 µs .
SAP DBTech JDBC: [256]: sql processing error: Error occurred while executing a MDX statement. The error message is: Inconsistent calculation model; $message$=Inconsistent calculation model;calculationNode (N_0141_AGGR) -> attributes -> attribute (H68241575_CM709_/B28/S_SDATA) -> expression:Expression is not valid: Evaluator: type error in expression evaluator;fixed12_13.7 fixed_12(divide(double times(double plus(double double(fixed12_13.7 minus(fixed12_13.7 "R_H68241575_N806__/B28/S_SDATA")), double times(double double(fixed12_13.7 "R_H68241575_N818__/B28/S_SDATA"), double plus(double double(int '1'), double divide(double double(fixed12_13.7 "R_H68241575_N807__/B28/S_SDATA"), double double(fixed12_13.7 "R_H68241575_N803__/B28/S_SDATA"))))), double double(int '1200')), double double([here]times(string "#_PL_682425590/CPMB/IDP5PJT", double "ALIAS_#_CM_68242559_-797741121_68241575_-301345112_/B28/S_SDATA"))))
I know that I could use a very long IIF statement with every single conditional (for each month) but I'm looking for a more "elegant" solution.
Thanks,
- SAP Managed Tags:
- SAP Business Planning and Consolidation, version for SAP NetWeaver
Accepted Solutions (1)
Accepted Solutions (1)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
I can't test with HANA, but:
"If I use [TIME].CurrentMember.Properties("BASE_PERIOD") I get the following error because of the different types "string" & "integer" in an arithmetical operation" - strange, BASE_PERIOD is numeric! Can you check Data Type in RSA1?
Also please read at the end of the page: Library of MDX Functions - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP ...
Generate the list of MDX functions using BAPI_MDPROVIDER_GET_FUNCTIONS via transaction SE37. Do you see STRLEN and STRTOVALUE?
What is your SP of BPC 10.1?
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
STRTOVALUE worked for me. For some reason I had the formula following the cameCase convention with LOWER & UPPER caps which was giving me an error in the BPC editor.
([ACCOUNT].[PARENTH1].[MF_SALES_ANN])*(-12/(STRTOVALUE([TIME].CurrentMember.Properties("BASE_PERIOD")))
BASE_PERIOD is NUMC, for some reason without STRTOVALUE it gives me the error that I described before. I'm using BPC 10.1 SP07.
Thank you Vadim!
Regards,
Message was edited by: Kermit Bravo
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Here is the list of functions that I get when I run the FM BAPI_MDPROVIDER_GET_FUNCTIONS that you provided.
Thanks Vadim.
Regards,
136 Entries |
FUNCTION | DSCRPTN | PARAM_LIST | RET_TY | ORIGIN | INTERFACE | OBJECT | CAPTION | |||||||||
ABS |
| «Numeric» | 000012 | 000001 | Numeric |
| Abs( <numeric expression> ) | |||||||||
ADDCALCULATEDMEMBERS |
| «Set» | 000012 | 000001 | Set |
| AddCalculatedMembers( <set> ) | |||||||||
AGGREGATE |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Aggregate( <set>, [, <numeric value expression>] ) | |||||||||
ALLMEMBERS |
| (none) | 000012 | 000001 | Set | Dimension | <dimension>.AllMembers | |||||||||
ANCESTOR |
| «Member», «Level» | 000012 | 000001 | Member |
| Ancestor( <member>, <level> ) | |||||||||
ANCESTORS |
| «Member» | 000012 | 000001 | Set |
| Ancestors( <member>, <level> ) | |||||||||
ARCCOS |
| «Numeric» | 000012 | 000001 | Numeric |
| Arccos( <numeric expression> ) | |||||||||
ARCSIN |
| «Numeric» | 000012 | 000001 | Numeric |
| Arcsin( <numeric expression> ) | |||||||||
ARCTAN |
| «Numeric» | 000012 | 000001 | Numeric |
| Arctan( <numeric expression> ) | |||||||||
ASCENDANTS | Returns the number of preceding elements | «Member» | 000012 | 000001 | Set |
| Ascendants( <member> ) | |||||||||
AVG |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Avg( <set>, [, <numeric value expression>] ) | |||||||||
AXIS |
| «Numeric Expression» | 000012 | 000001 | Set |
| AXIS | |||||||||
BOTTOMCOUNT |
| «Set», «Count»[, «Numeric Expression»] | 000012 | 000001 | Set |
| BottomCount( <set>, <index> [, <numeric value expression>] ) | |||||||||
BOTTOMPERCENT |
| «Set», «Percentage», «Numeric Expression» | 000012 | 000001 | Set |
| BottomPercent( <set>, <percentage>, <numeric expression> ) | |||||||||
BOTTOMSUM |
| «Set», «Value», «Numeric Expression» | 000012 | 000001 | Set |
| BottomSum( <set>, <value>, <numeric expression> ) | |||||||||
CEIL |
| «Numeric» | 000012 | 000001 | Numeric |
| Ceil( <numeric expression> ) | |||||||||
CHILDREN |
| (none) | 000012 | 000001 | Set | Member | <member>.Children | |||||||||
CLOSINGPERIOD |
| [«Level»[, «Member»] ] | 000012 | 000001 | Member |
| ClosingPeriod( [ <level> [, <member> ] ]) | |||||||||
COALESCEEMPTY |
| «String Expression»[, «String Expression»...] | 000012 | 000001 | String |
| CoalesceEmpty( <value expression> [, <value expression>] ) | |||||||||
COALESCEEMPTY |
| «Numeric Expression»[, «Numeric Expression»...] | 000012 | 000001 | Numeric |
| CoalesceEmpty( <value expression> [, <value expression>] ) | |||||||||
CORRELATION |
| «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Correlation( <set>, <expression> [, <expression>] ) | |||||||||
COS |
| «Numeric» | 000012 | 000001 | Numeric |
| Cos( <numeric expression> ) | |||||||||
COSHYP |
| «Numeric» | 000012 | 000001 | Numeric |
| Coshyp( <numeric expression> ) | |||||||||
COUNT |
| «Set»[, EXCLUDEEMPTY | INCLUDEEMPTY] | 000012 | 000001 | Numeric |
| Count( <set> [, INCLUDEEMPTY | EXCLUDEEMPTY] ) | |||||||
COUSIN |
| «Member1», «Member2» | 000012 | 000001 | Member |
| Cousin( <member>, <member> ) | |||||||||
COVARIANCE |
| «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Covariance( <set>, <expression> [, <expression>] ) | |||||||||
CROSSJOIN |
| «Set», «Set» | 000012 | 000001 | Set |
| Crossjoin( <set>, <set> ) | |||||||||
CURRENT | Returns the current tuple from a set | «Set» | 000012 | 000001 | Tuple |
| <set>.Current | |||||||||
CURRENTMEMBER |
| (none) | 000012 | 000001 | Member | Dimension | <dimension>[.CurrentMember] | |||||||||
DEFAULTMEMBER |
| (none) | 000012 | 000001 | Member | Dimension | <member>.DefaultMember | |||||||||
DEFAULTMEMBER |
| (none) | 000012 | 000001 | Member | Dimension | <dimension>.DefaultMember | |||||||||
DESCENDANTS |
| «Member»[, «Level»[, «Desc_flags»]] | 000012 | 000001 | Set |
| Descendants( <member>, [, <level> [ <desc_flag> ] ) | |||||||||
DIMENSIONS | Returns the dimension for a character string | «String Expression» | 000012 | 000001 | Dimension |
| Dimensions( <expression> ) | |||||||||
DISTINCT |
| «Set» | 000012 | 000001 | Set |
| Distinct( <set> ) | |||||||||
DISTINCTCOUNT | Returns the uniques tuples for a set | «Set» | 000012 | 000001 | Numeric |
| DistinctCount( <set> ) | |||||||||
DIV |
| «Numeric» | 000012 | 000001 | Numeric |
| Div( <numeric expression> ) | |||||||||
DRILLDOWNLEVEL |
| «Set»[, , «Index»] | 000012 | 000001 | Set |
| DrillDownLevel( <set> [, <level> ] ) | |||||||||
DRILLDOWNLEVELBOTTOM |
| «Set», «Count»[, [«Level»][, «Numeric Expression»] ] | 000012 | 000001 | Set |
| DrillDownLevelBottom( <set>, <index> [, <level> ] [, <num>) | |||||||||
DRILLDOWNLEVELTOP |
| «Set», «Count»[, [«Level»][, «Numeric Expression»] ] | 000012 | 000001 | Set |
| DrillDownLevelTop( <set>, <index> [, <level> ] [, <num>]]) | |||||||||
DRILLDOWNMEMBER |
| «Set», «Set»[, RECURSIVE] | 000012 | 000001 | Set |
| DrillDownMember( <set>, <set> [, RECURSIVE] ) | |||||||||
DRILLDOWNMEMBERBOTTOM |
| «Set», «Set», «Count»[, [«Numeric Expression»] [, RECURSIVE] ] | 000012 | 000001 | Set |
| DrillLDownMemberBottom( <set>, <set>, <index> [, <num>]) | |||||||||
DRILLDOWNMEMBERTOP |
| «Set», «Set», «Count»[, [«Numeric Expression»] [, RECURSIVE] ] | 000012 | 000001 | Set |
| DrillLDownMemberTop( <set>, <set>, <index> [, <num>]) | |||||||||
DRILLUPLEVEL |
| «Set»[, «Level»] | 000012 | 000001 | Set |
| DrillUpLevel( <set> [, <level> ] ) | |||||||||
DRILLUPMEMBER |
| «Set», «Set» | 000012 | 000001 | Set |
| DrillUpMember( <set>, <set> ] ) | |||||||||
EXCEPT |
| «Set», «Set»[, ALL] | 000012 | 000001 | Set |
| Except( <set>, <set> [, ALL] ) | |||||||||
EXP |
| «Numeric» | 000012 | 000001 | Numeric |
| Exp( <numeric expression> ) | |||||||||
EXTRACT |
| «Set», «Dimension»[, «Dimension»...] | 000012 | 000001 | Set |
| Extract( <set>, <dimension> [, <dimension> ] ) | |||||||||
FILTER |
| «Set», «Search Condition» | 000012 | 000001 | Set |
| Filter( <set>, <condition> ) | |||||||||
FIRSTCHILD |
| (none) | 000012 | 000001 | Member | Member | <member>.FirstChild | |||||||||
FIRSTSIBLING |
| (none) | 000012 | 000001 | Member | Member | <member>.FirstSibling | |||||||||
FLOOR |
| «Numeric» | 000012 | 000001 | Numeric |
| Floor( <numeric expression> ) | |||||||||
FRAC |
| «Numeric» | 000012 | 000001 | Numeric |
| Frac( <numeric expression> ) | |||||||||
HEAD |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Set |
| Head( <set> [, <count> ] ) | |||||||||
HIERARCHIZE |
| «Set»[, POST] | 000012 | 000001 | Set |
| Hiearchize( <set> ) | |||||||||
IIF |
| «Logical Expression», «Numeric Expression», «Numeric Expression» | 000012 | 000001 | Numeric |
| IIF( <condition>, <expression>, <expression> ) | |||||||||
INSTR |
| «Numeric» | 000012 | 000001 | Numeric |
| InStr([<expression>,] <string>, <string>) | |||||||||
INTERSECT |
| «Set», «Set»[, ALL] | 000012 | 000001 | Set |
| Intersect( <set>, <set> [, ALL] ) | |||||||||
IS | Checks that the parameters are identical | «Expression» | 000012 | 000001 | Logical |
| <object> IS <object> | |||||||||
ISANCESTOR | Is the member a predecessor? | «Member», «Member» | 000012 | 000001 | Logical |
| IsAncestor( <member>, <member> ) | |||||||||
ISEMPTY | Returns whether the expression has been posted | «Expression» | 000012 | 000001 | Logical |
| IsEmpty( <expression> ) | |||||||||
ISLEAF | Returns whether it is a leaf | «Member» | 000012 | 000001 | Logical |
| IsLeaf( <expression> ) | |||||||||
ISSIBLING | Are the members siblings? | «Member», «Member» | 000012 | 000001 | Logical |
| IsSibling( <member>, <member> ) | |||||||||
ITEM |
| «Numeric Expression» | 000012 | 000001 | Member | Tuple | <tuple>[.Item](<index>) | |||||||||
LAG |
| «Numeric Expression» | 000012 | 000001 | Member | Member | <member>.Lag( <index> ) | |||||||||
LASTCHILD |
| (none) | 000012 | 000001 | Member | Member | <member>.LastChild | |||||||||
LASTPERIODS |
| «Index»[, «Member»] | 000012 | 000001 | Set |
| LastPeriods( <index> [, <member> ]) | |||||||||
LASTSIBLING |
| (none) | 000012 | 000001 | Member | Member | <member>.LastSibling | |||||||||
LCASE |
| «String» | 000012 | 000001 | String |
| LCase( <string expression> ) | |||||||||
LEAD |
| «Numeric Expression» | 000012 | 000001 | Member | Member | <member>.Lead( <index> ) | |||||||||
LEFT |
| «String» | 000012 | 000001 | String |
| Left( <string expression> ) | |||||||||
LEN |
| «Numeric» | 000012 | 000001 | Numeric |
| Len( <string expression> ) | |||||||||
LEVEL |
| (none) | 000012 | 000001 | Level | Member | <member>.Level | |||||||||
LEVELS |
| «Numeric Expression» | 000012 | 000001 | Level | Dimension | <dimension>.Levels( <numeric value expression> ) | |||||||||
LINREGINTERCEPT |
| «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| LinRegIntercept( <set>, <expression> [, <expression>] ) | |||||||||
LINREGPOINT |
| «Numeric Expression», «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| LinRegPoint( <set>, <expression>, <set>, [, <expression>] ) | |||||||||
LINREGR2 |
| «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| LinRegR2( <set>, <expression> [, <expression>] ) | |||||||||
LINREGSLOPE |
| «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| LinRegSlope( <set>, <expression> [, <expression>] ) | |||||||||
LINREGVARIANCE |
| «Set», «Numeric Expression»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| LinRegVariance( <set>, <expression> [, <expression>] ) | |||||||||
LN |
| «Numeric» | 000012 | 000001 | Numeric |
| Ln( <numeric expression> ) | |||||||||
LOG |
| «Numeric» | 000012 | 000001 | Numeric |
| Log( <numeric expression> ) | |||||||||
LOG10 |
| «Numeric» | 000012 | 000001 | Numeric |
| Log10( <numeric expression> ) | |||||||||
LTRIM |
| «String» | 000012 | 000001 | String |
| LTrim( <string expression> ) | |||||||||
MAX |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Max( <set>, [, <numeric value expression>] ) | |||||||||
MEDIAN |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Median( <set>, [, <numeric value expression>] ) | |||||||||
MEMBERS |
| (none) | 000012 | 000001 | Set | Hierarchy | <hiearchy>.Members | |||||||||
MEMBERS |
| (none) | 000012 | 000001 | Set | Level | <level>.Members | |||||||||
MEMBERS |
| (none) | 000012 | 000001 | Set | Dimension | <dimension>.Members | |||||||||
MID |
| «String» | 000012 | 000001 | String |
| Mid( <string expression> ) | |||||||||
MIN |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Min( <set>, [, <numeric value expression>] ) | |||||||||
MOD |
| «Numeric» | 000012 | 000001 | Numeric |
| Mod( <numeric expression> ) | |||||||||
MTD |
| [«Member»] | 000012 | 000001 | Set |
| MTD([ <member ]) | |||||||||
NAMETOSET |
| «Member Name» | 000012 | 000001 | Set |
| NAMETOSET | |||||||||
NEXTMEMBER |
| (none) | 000012 | 000001 | Member | Member | <member>.NextMember | |||||||||
NODIM |
| «Numeric» | 000012 | 000001 | Numeric |
| NoDim( <numeric expression> ) | |||||||||
OPENINGPERIOD |
| [«Level»[, «Member»] ] | 000012 | 000001 | Member |
| OpeningPeriod( [<level> [, <member> ] ] ) | |||||||||
ORDER |
| «Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC] | 000012 | 000001 | Set |
| Order( <set>, <expression> [, ASC | DESC | BASC | BDESC] ) | ||
PARALLELPERIOD |
| [«Level»[, «Numeric Expression»[, «Member»] ] ] | 000012 | 000001 | Member |
| ParallelPeriod( [<level> [, <index> [, <member> ] ] ] ) | |||||||||
PARENT |
| (none) | 000012 | 000001 | Member | Member | <member>.Parent | |||||||||
PERIODSTODATE |
| [«Level»[, «Member»] ] | 000012 | 000001 | Set |
| PeriodsToDate( [ <level> [, <member> ] ] ) | |||||||||
PREVMEMBER |
| (none) | 000012 | 000001 | Member | Member | <member>.PrevMember | |||||||||
QTD |
| [«Member»] | 000012 | 000001 | Set |
| QTD( [ <member> ] ) | |||||||||
RANK |
| «Tuple», «Set» | 000012 | 000001 | Numeric |
| Rank( <tuple>, <set> ) | |||||||||
RIGHT |
| «String» | 000012 | 000001 | String |
| Right( <string expression> ) | |||||||||
RTRIM |
| «String» | 000012 | 000001 | String |
| RTrim( <string expression> ) | |||||||||
SIBLINGS |
| (none) | 000012 | 000001 | Set | Member | <member>.Siblings | |||||||||
SIGN |
| «Numeric» | 000012 | 000001 | Numeric |
| Sign( <numeric expression> ) | |||||||||
SIN |
| «Numeric» | 000012 | 000001 | Numeric |
| Sin( <numeric expression> ) | |||||||||
SINHYP |
| «Numeric» | 000012 | 000001 | Numeric |
| Sinhyp( <numeric expression> ) | |||||||||
SQRT |
| «Numeric» | 000012 | 000001 | Numeric |
| Sqrt( <numeric expression> ) | |||||||||
STDDEV |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| StdDev( <set> [, <numeric value expression>] ) | |||||||||
STRIPCALCULATEDMEMBERS |
| «Set» | 000012 | 000001 | Set |
| StripCalculatedMembers( <set> ) | |||||||||
STRLEN |
| «Numeric» | 000012 | 000001 | Numeric |
| StrLen( <string expression> ) | |||||||||
STRREVERSE |
| «String» | 000012 | 000001 | String |
| StrReverse( <string expression> ) | |||||||||
STRTOMEMBER | Converts a string into a member | «String Expression» | 000012 | 000001 | Member |
| StrToMember( <string expression> ) | |||||||||
STRTOSET | Converts a string into a set | «String Expression» | 000012 | 000001 | Set |
| StrToSet( <string expression> ) | |||||||||
STRTOTUPLE | Converts a string into a member | «String Expression» | 000012 | 000001 | Tuple |
| StrToTuple( <string expression> ) | |||||||||
STRTOVALUE | Converts a string into a value | «String Expression» | 000012 | 000001 | Numeric |
| StrToValue( <string expression> ) | |||||||||
SUBSET |
| «Set», «Start»[, «Count»] | 000012 | 000001 | Set |
| Subset( <set>, <start> [, <count> ] ) | |||||||||
SUM |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Sum( <set>, [, <numeric value expression>] ) | |||||||||
TAIL |
| «Set»[, «Count»] | 000012 | 000001 | Set |
| Tail( <set> [, <count> ] ) | |||||||||
TAN |
| «Numeric» | 000012 | 000001 | Numeric |
| Tan( <numeric expression> ) | |||||||||
TANHYP |
| «Numeric» | 000012 | 000001 | Numeric |
| Tanhyp( <numeric expression> ) | |||||||||
TOGGLEDRILLSTATE |
| «Set1», «Set2» | 000012 | 000001 | Set |
| ToggleDrillState( <set>, <set> [, RECURSIVE] ) | |||||||||
TOPCOUNT |
| «Set», «Count»[, «Numeric Expression»] | 000012 | 000001 | Set |
| TopCount( <set>, <index> [, <numeric value expression>] ) | |||||||||
TOPPERCENT |
| «Set», «Percentage», «Numeric Expression» | 000012 | 000001 | Set |
| TopPercent( <set>, <percentage>, <numeric expression> ) | |||||||||
TOPSUM |
| «Set», «Value», «Numeric Expression» | 000012 | 000001 | Set |
| TopSum( <set>, <value>, <numeric expression> ) | |||||||||
TRIM |
| «String» | 000012 | 000001 | String |
| Trim( <string expression> ) | |||||||||
TRUNC |
| «Numeric» | 000012 | 000001 | Numeric |
| Trunc( <numeric expression> ) | |||||||||
UCASE |
| «String» | 000012 | 000001 | String |
| UCase( <string expression> ) | |||||||||
UNION |
| «Set», «Set»[, ALL] | 000012 | 000001 | Set |
| Union( <set>, <set> [, ALL] ) | |||||||||
UNORDER | Bypasses set sorting | «Set» | 000012 | 000001 | Set |
| Unorder( <set> ) | |||||||||
VALUE |
| (none) | 000012 | 000001 | Numeric | Member | <measure>[.Value] | |||||||||
VAR |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Var( <set> [, <numeric value expression>] ) | |||||||||
VARIANCE |
| «Set»[, «Numeric Expression»] | 000012 | 000001 | Numeric |
| Variance( <set> [, <numeric value expression>] ) | |||||||||
WTD |
| [«Member»] | 000012 | 000001 | Set |
| WTD( [ <member> ] ) | |||||||||
YTD |
| [«Member»] | 000012 | 000001 | Set |
| YTD( [ <member> ] ) | |||||||||
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
STRLEN is also supported, but you can't use it like:
([ACCOUNT].[PARENTH1].[MF_SALES_ANN])*(-12/(STRLEN([TIME].CurrentMember.Properties("BASE_PERIOD")))
In my workaround I used STRLEN with a special property containing strings with different length (28,29,30,31 characters)
But STRTOVALUE is better!
Vadim
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
I created a new "custom" property (CHAR) and tried the method you described in the other discussion with the XXX's,
([ACCOUNT].[PARENTH1].[MF_SALES_ANN])*(-12/(STRLEN([TIME].CurrentMember.Properties("MONTHC")))
And still got the same message "The usage of STRLEN in the current context is not supported".
Thanks for the update on BW 7.40 SP12 too Vadim!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Vadim,
Great info (as always from you), thanks!
We recently upgraded to BW 7.4 SP 14 at one of my client's. I have a requirement to implement a custom measure 'AVGYTD'. BAPI_MDPROVIDER_GET_FUNCTIONS does list STRTOVALUE, however when I try to use it in the most straightforward way I can think of it won't save and I get an MDX error.
Here is my first attempt, which saves ok (syntactically OK) but is not correct because it just sums up the relevant periods but does not divide by the number of periods:
'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])/(3),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])/(3),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])/(3),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])/(3),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3
If I change the first expression (for ACCTYPE = "INC") to add "/ (STRTOVALUE("3")) as shown here I get an error:
'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])/(STRTOVALUE("3")),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])/(3),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])/(3),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])/(3),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3
Of course dividing by hardcoded 3 is not correct; I was just trying to figure out if STRTOVALUE is even working. Initially I was trying STRTOVALUE on the MONTHNUM property.
Can you see what I am doing wrong here? Has anyone successfully used STRTOVALUE on SP12 or greater?
Thank you,
Paul Petersen
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Answers (0)
- Manage dates-driven planning processes with SAP Analytics Cloud in Financial Management Blogs by SAP
- BART properties value in Convergent Charging in Financial Management Q&A
- UI Data Protection – How Enhanced Reveal method works in Masking scenario when Reveal Type is set as Workflow in SAPUI5 and Fiori applications in Financial Management Blogs by SAP
- What's new in SAP Convergent Charging 2022 FPS2 in Financial Management Blogs by SAP
- CPQ 2305 release highlights in Financial Management Blogs by SAP
| 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.