on 12-02-2013 5:51 PM
Hi SAP BPC experts,
We are facing an issue regardind a member formula that cannot be longer than 255 characters.
Once we are in this point we have two questions:
Best regards and thanks in advance
Hi Lauro,
What is the version SAP BPC?
What is the version Excel?
Regards
Gustavo
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lauro,
I have done some tests with long formula and found that it's working in this case:
I have created text file CEDT.LGF with the following function inside:
*FUNCTION CEDT(%PE%)
IIF(%PE%=0,NULL,([2020202010723]+[2020202010722]+[2020202010721]+[2020202010720]+[2020202010719]+[2020202010718]+[2020202010717]+[2020202010716]+[2020202010715]+[2020202010714]+[2020202010713]+[2020202010712]+[2020202010711]+[2020202010710]+[2020202010709]+[2020202010708])/%PE%)
*ENDFUNCTION
I have used the same name for the file and for the function but it's not required.
I have uploaded this file using UJFS to the root\webfolders\sim\systemlibrary\logic library\cedt.lgf (sim - is the environment name).
Then, for the member EDTFRCOSTPEP I have created simple formula:
CEDT([PAGESEDT]);SOLVE_ORDER=10
Then - processed the dimension (If you change the uploaded file you need to reprocess the dimension - simulate change in formula, then save, then - reprocess)
And tested results in the Excel report.
Result: Calculations are Correct!
The resulting formula:
IIF([PAGESEDT]=0,NULL,([2020202010723]+[2020202010722]+[2020202010721]+[2020202010720]+[2020202010719]+[2020202010718]+[2020202010717]+[2020202010716]+[2020202010715]+[2020202010714]+[2020202010713]+[2020202010712]+[2020202010711]+[2020202010710]+[2020202010709]+[2020202010708])/[PAGESEDT]);SOLVE_ORDER=10
Text length - 306 chars!
B.R. Vadim
P.S. PAGESEDT and 20202020107XX - are members of ACCOUNT dimension - no dimension prefix.
Message was edited by: Vadim Kalinin P.S. Added
Hello All,
I have also same issue as my formula is exceeding char. length 255. I have followed as Mr. Vadim replied above, but getting error when processing member formula.
Error is "Invalid or unimplemented "*SYSLIB <filename>".
I have used member formula in text file which I have uploaded is as below:
*FUNCTION TEST
[PROFITCENTER].[H2].[P0100_NEXTYR]+[PROFITCENTER].[H2].[P0500_NEXTYR]+[PROFITCENTER].[H2].[P0103_NEXTYR]+[PROFITCENTER].[H2].[P0511_NEXTYR]+
[PROFITCENTER].[H2].[P0640_NEXTYR]+[PROFITCENTER].[H2].[P0601_NEXTYR]+[PROFITCENTER].[H2].[P0603_NEXTYR]+[PROFITCENTER].[H2].[P0607_NEXTYR]+
[PROFITCENTER].[H2].[P0611_NEXTYR]+[PROFITCENTER].[H2].[P0800_NEXTYR]
*ENDFUCTION
I am using BPC 10.0 Release 801 SP-level 005
Could anyone please help me in this to resolve above issue.
Best Regards,
Deepak
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Vadim, given below is my code that I wrote in file and uploaded in logic library via UJFS with file name TEST:
*FUNCTION TEST(%A1%,%A2%,%A3%,%A4%,%A5%,%A6%,%A7%,%A8%,%A9%,%A10%)
[PROFITCENTER].[H2].[%A1%]+[PROFITCENTER].[H2].[%A2%]+[PROFITCENTER].[H2].[%A3%]+
[PROFITCENTER].[H2].[%A4%]+[PROFITCENTER].[H2].[%A5%]+[PROFITCENTER].[H2].[%A6%]+
[PROFITCENTER].[H2].[%A7%]+[PROFITCENTER].[H2].[%A8%]+[PROFITCENTER].[H2].[%A%]+[PROFITCENTER].[H2].[%A10%]
*ENDFUCTION
After this I have create a member formula as given below:
TEST(P0100_NEXTYR,P0500_NEXTYR,P0103_NEXTYR,P0511_NEXTYR,
P0640_NEXTYR,P0601_NEXTYR,P0603_NEXTYR,P0607_NEXTYR,
P0611_NEXTYR,P0800_NEXTYR)
And also mentioned file name in option with TEST.
But while processing I got error like "Invalid or unimplemented "*SYSLIB TEST".
Could you please now help on this.
Best Regards,
Deepak Palsaniya
Vadim,
I have tried as per your suggestions above, I am getting the below error.
Bad request.
Server message:
code: UJO_READ_EXCEPTION_018
severity: error
description: MDX statement error: The argument ' IIF([/CPMB/AXDE8F5 PARENTH1]=0,NULL,([/CPMB/AXDE8F5 PARENTH1].[COCF] +[/CPMB/AXDE8F5
log id: 4eOpLjhK7kINwXeFFNIM3W
DATAVALUE:The argument ' IIF([/CPMB/AXDE8F5 PARENTH1]=0,NULL,([/CPMB/AXDE8F5 PARENTH1].[COCF] +[/CPMB/AXDE8F5
MDX:
V1:The argument ' IIF([/CPMB/AXDE8F5 PARENTH1]=0,NULL
V2:,([/CPMB/AXDE8F5 PARENTH1].[COCF] +[/CPMB/AXDE8F5
V3:
V4:
-----------------
Framework message:
The remote server returned an error: (400) Bad Request.
The remote server returned an error: (400) Bad Request.
Vadim,
I have fixed the above issue. My only problem now is referencing. Please help.
I am writing formula for the Account member (CFCF) of Account dimension and below is the function in .LGF
*FUNCTION BPCGACCOUNT(CFCF)
IIF(CFCF=0,NULL,([BPCGACCOUNT].[COCF] +[BPCGACCOUNT].[OT_DIS_ENVPPE] +[BPCGACCOUNT].[OT_DIS_PROFIT_PPE] + [BPCGACCOUNT].[CF_MAINTPPE] +[BPCGACCOUNT].[CF_REINSURANCE])/CFCF)
*ENDFUNCTION
In Member formula i am referencing for the Member CFCF like below
BPCGACCOUNT([CFCF]), SOLVE_ORDER=10
If i try this then i am getting the below error.
"Formula of member CFCF contains a nested formula in member CFCF for model Consolidations,"
Somwhere my references are wrong. Please help.
I am very close to implement this. Please help to resolve the above issue.
Please read help about FUNCTION syntax - function parameter have to be enclosed by %:
*FUNCTION BPCGACCOUNT(%PAR%)
IIF(%PAR%=0,NULL,([BPCGACCOUNT].[COCF] +[BPCGACCOUNT].[OT_DIS_ENVPPE] +[BPCGACCOUNT].[OT_DIS_PROFIT_PPE] + [BPCGACCOUNT].[CF_MAINTPPE] +[BPCGACCOUNT].[CF_REINSURANCE])/%PAR%)
*ENDFUNCTION
then:
BPCGACCOUNT([CFCF]), SOLVE_ORDER=10
But in your case I don't see long formula, the dimension prefix for account dimension can be removed:
=IIF([CFCF]=0,NULL,([COCF]+[OT_DIS_ENVPPE]+[OT_DIS_PROFIT_PPE]+[CF_MAINTPPE]+[CF_REINSURANCE])/[CFCF]),SOLVE_ORDER=10
Vadim
Vadim,
My formula is less than 250 characters but i am trying to use the options functionality with 1 formula first.
I tried exactly like above but i am getting the Nested formula error.
*FUNCTION BPCGACCOUNT(%CFCF%)
Vadim,
Same formula works when i manually enter in Member Formula but when i try through .LGF file it is not working.
Account Name : BPCGACCOUNT
Formula for Member : CFCF
Formula : [BPCGACCOUNT].[COCF]
+[BPCGACCOUNT].[OT_DIS_ENVPPE] +[BPCGACCOUNT].[OT_DIS_PROFIT_PPE] +
[BPCGACCOUNT].[CF_MAINTPPE] +[BPCGACCOUNT].[CF_REINSURANCE]
Function file name : BPCGACCOUNT in .LGF file
*FUNCTION BPCGACCOUNT(%CFCF%)
IIF(%CFCF%=0,NULL,([BPCGACCOUNT].[COCF]+[BPCGACCOUNT].[OT_DIS_ENVPPE] +[BPCGACCOUNT].[OT_DIS_PROFIT_PPE] +[BPCGACCOUNT].[CF_MAINTPPE] +[BPCGACCOUNT].[CF_REINSURANCE])/%CFCF%)
*ENDFUNCTION
In Member formula :
Formula for Member : CFCF
Formula : If i use this BPCGACCOUNT([CFCF]),SOLVE_ORDER=10 i am getting Nested formula issue.
If i use this BPCGACCOUNT([BPCGACCOUNT]),SOLVE_ORDER=10 I am getting the below error.
Bad request.
Server message:
code: UJO_READ_EXCEPTION_018
severity: error
description: MDX statement error: The argument ' IIF([/CPMB/AXDE8F5 PARENTH1]=0,NULL,([/CPMB/AXDE8F5 PARENTH1].[COCF] + [/CPMB/AXDE8F5
log id: 4eOpLjhK7kINxgf}7hfT1G
DATAVALUE:The argument ' IIF([/CPMB/AXDE8F5 PARENTH1]=0,NULL,([/CPMB/AXDE8F5 PARENTH1].[COCF] + [/CPMB/AXDE8F5
MDX:
V1:The argument ' IIF([/CPMB/AXDE8F5 PARENTH1]=0,NULL
V2:,([/CPMB/AXDE8F5 PARENTH1].[COCF] + [/CPMB/AXDE8F5
V3:
V4:
-----------------
Framework message:
The remote server returned an error: (400) Bad Request.
The remote server returned an error: (400) Bad Request.
Please Help.
Vadim,
I appreciate your patience. I hope the below explanation helps you to better understand.
All i want is CFCF = COCF+CF_REinsurance+etc.. (earlier i misunderstood you example and that is why i put BY)
I am just confused what to give in %Arg% and after IIF condition
and what is the reference to give in member formula.
All i need is
*Function BPCG (%???%) - do i need to give any member or its just a parameter for dynamic purposes?
IIF %???% =0, NULL, ([COCF]+[CF_REINSURANCE]+ [ETC])
*end function
Member for Formula CFCF
BPCGACCOUNT[(???)],SOLVE_ORDER=10
Do i need to mention account dimension name anywhere?
Hi Sivacharan,
I was getting same error "Unknown or Unimplemented Keyword error" due to authorization issue as I was not authorize to Upload .lgf formula file in Logic Library folder. So please confirm that you have authorization for the same.
.lgf file that need to upload in Logic Library folder:-
Best Regards,
Deepak
Hi,
Does this approach work in BPC on Hana? So far my simple tests using the instructions above have resulted in "Cannot find document/directory" errors.
Thanks,
Dave
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you very much Vadim,
I have just created a LGF file and uploaded to the route you proposed.
I have added the following formula and it is working for us: (the elements [PMCA_CTA_PAGAR] are the ones of the ACCOUNT dimension so as you mentioned before we did not reference it)
*FUNCTION FORMULA_YTD
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "1",
(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "2",
2*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "3",
3*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "4",
4*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "5",
5*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "6",
6*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "7",
7*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "8",
8*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "9",
9*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "10",
10*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "11",
11*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],
IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "12",
12*(365/12)*[PMCA_CTA_PAGAR]/[PMCA_EXP_OPEX],0))))))))))))
*ENDFUNCTION
I need to do further tests but it seem to be a correct approach to our issue.
Best regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vadim
I followed your example above and getting error as below.Did I miss anything?
Thanks
Ashok
Here is the function
*FUNCTION CEDT(%PE%)
IIF(%PE%=0,NULL,([A4003201]+[A4004201]+[A5008001]+[A5050201]+[A5060201]+[A5150201]+[A5155201]+[A4172150]+[A4800200]+[A5440050]+[A5440150]+[A5704350]+[A2400200]+[A3402100
]+[A4000200]+[A4000320]+[A4001200]+[A4003200]+[A4004200]+[A4004250]+[A4004320]+[A4004420]+[A4004520]+[A4015200]+[A4100200]+[A4101200]+[A4102200]+[A4110200]+[A4120200
]+[A4172250])/%PE%)
*ENDFUNCTION
Here is the Member formula
Here is the error message
Hi Lauro,
Yes, this limit is BPC internal limit and you can't change it. You can:
1. Remove the ACCOUNT dimension name from the formula.
2. Use short ID's.
MDX is the only language supported in the dimension formulas.
B.R. Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi Vadim
small query.
we are using BPC10 NW. In that we have almost 25 member formulas. Each member formula refers to function in script logic.
my query is
1. Is member formula advisable? as it may cause performance issue
2. Since the member formulas inturn referring to script logic, does it causes heavy performance issue? like when we input or refresh a single cell its taking long time.
Please advise.
If member formulas can cause this performance issues, what could be alternate way?
Can it be thru script logic for all the calculation mentioned in formula
?
thanks in advance.
User | Count |
---|---|
13 | |
2 | |
2 | |
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.