Member Formula. 255 characters limitation and Options file

[Former Member]

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:

1. Is it possible to change the configuration (some application or application set parameter) in order to allow longer formulas?
2. Is it possible to work with longer formulas is we use the Options in the right side of the Member Formula screen.
   1. The file supports MDX syntax or is another different programming language?
   2. Where do we need to store the file and what name should we use?
   3. Is this file somehow related to the script logic files?

Best regards and thanks in advance

[Former Member · Accepted solution]

Hi Lauro,

What is the version SAP BPC?

What is the version Excel?

Regards

Gustavo

[Former Member · Accepted solution]

Hi Gustavo,

We are working on SAP BPC 10 Netweaver and Excel 2010.

I have read in other forum posts and it is not a limitation related with Excel, it seems to be a limitation related to SAP BPC.

Best Regards

[Former Member · Accepted solution]

Hi Lauro,

Have you seen this IDEA?

Increase max length of Member Formulas : View Idea

Regards

Gustavo

[former_member186338 · Accepted solution]

Hi Gustavo,

Unfortunately the MDXLIB approach works very unstable - simple formulas OK, complex - not working...

May be you know some magic ?

Vadim

[Former Member · Accepted solution]

Hi Vadim,

We have created our formula removing the ACCOUNT dimension name from the formula and 255 characters is not enough for us.

Is there any possibility to use the Options in the formula member in order to create a formula linger than 255 and the reference it in the member formula?

Thanks

[former_member186338 · Accepted solution]

Please, provide the formula to test...

Vadim

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Vadim,

What is "PAGESEDT" in the above example? Is it a member?If so, How is it is related to the formula. All these "2020....." members relate to ""PAGESEDT"?

Is %PE% refer to "PAGESEDT"?

Please help.

[former_member228522]

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

[former_member186338]

Please, show the member formula! I don't see the arguments in your TEST function...

Vadim

[former_member228522]

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

[former_member186338]

Hi Deepak,

Please open a new discussion - it's hard to answer here.

Please ensure that you downloaded function file correctly: \environment_name\systemlibrary\logic library\function_file.lgf

Vadim

[former_member228522]

Thanks Vadim, I have created a new discussion for the same.

Deepak

[Former Member]

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.

[Former Member]

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.

[former_member186338]

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

[Former Member]

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%)

IIF(%CFCF%=0,NULL,([BPCGACCOUNT].[COCF]
+[BPCGACCOUNT].[OT_DIS_ENVPPE] +[BPCGACCOUNT].[OT_DIS_PROFIT_PPE] +
[BPCGACCOUNT].[CF_MAINTPPE] +[BPCGACCOUNT].[CF_REINSURANCE])/%CFCF%)

*ENDFUNCTION

then:

BPCGACCOUNT([CFCF]),SOLVE_ORDER=10

I am selecting formula for member as CFCF and then

BPCGACCOUNT([CFCF]),SOLVE_ORDER=10

I think that is why i am getting nested formula issue. what should be the member you will be selecting in member formulas section

Thans for all your support.

[former_member186338]

You can't use calculated member with dimension formula in the formula of another member. It's possible only on HANA.

Vadim

[Former Member]

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.

[former_member186338]

Sorry, but WHY???? are you creating formula for the member referencing the same member? Create a new member and use formula!

Formula for Member : CFCF

BPCGACCOUNT([CFCF]),SOLVE_ORDER=10   - ? ????

What are you trying to calculate?

[Former Member]

That is where i am confusing.

I just need to enter formula for member CFCF in BPCG Account.

Formula is CFCF = COCF + CF_REinsurance + etc... I just want to use this formula in .LGF file.

what should be function parameter and reference in the above case.

[former_member186338]

In you original post you are dividing this:

Formula is CFCF = COCF + CF_REinsurance + etc... by CFCF?????

What is EXACT formula you want to calculate???

[Former Member]

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?

[Former Member]

Hi Deepak,

Is your .LGF file issue resolved?

Where member formula calls .LGF file? I have same issue.

[former_member186338]

In %arg% you provide any member in the function call. PLEASE READ HELP ABOUT FUNCTION!!!

[former_member228522]

Hi Sivacharan,

Yeah, it got resolved. I applied steps suggested by Vadim and it's working fine.

Deepak

[Former Member]

Deepak,

Thanks for the reply, When i try the same steps it is giving me "Unknown or Unimplemented Keyword error".

Can you please share your function and reference formula in member formula screen?

[former_member228522]

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

[Former Member]

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

[former_member186338]

Hi David,

Can't test it on HANA, but I see no reasons why it will not work on HANA.

B.R. Vadim

[former_member399115]

This for the help.  This is my basic setup to test the functionality.  I've since moved the lgf to the logic library which resolved the "Cannot find directory" error.  However, now I am getting a cube not found error.  Does this look right?

[former_member186338]

Please look on  my original post where to store the function:

"I have uploaded this file using UJFS to the root\webfolders\sim\systemlibrary\logic library\cedt.lgf"

Vadim

[Former Member]

David,

Please copy the .LGF file in UJFS-System LIBrary->Logic library

and Go to Admin->Rules->Member Formulas-> Go to options and click on new and give the same name as .LGF file.

Then you will not get the no document error.

[Former Member]

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

[former_member186338]

Hi Lauro,

Oooo! I see an old issue - missing StrToValue function in BPC (7.5 & 10) NW

B.R. Vadim

[ashokkumar_narasimha]

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

[former_member186338]

Have you mentioned the formula file name in the Option field on the right of the formulas list? Have you properly stored this file in UJFS? Have you correctly pasted the formula text (see some suspicious line break!)?

Please, show screenshots to confirm!

Vadim

[ashokkumar_narasimha]

Vadim

Thanks for your immediate reply.  I did not have the file name in the Option section  but now i added it and getting different error but could not see the complete message.

Here is the screen shots of the details

Thanks a lot

Ashok

[former_member186338]

Do you have the function body: IIF(%PE%=0,NULL,([A4003201]+...+[A4172250])/%PE%) as a SINGLE line in the notepad?

CEDT.LGF need to have only 3 lines!

Vadim

[ashokkumar_narasimha]

The file has only 3 lines

*Function..

*IIF

*End Function..

The IIF statement is not broken into multiple lines.  I will keep trying.

Thanks for all your help

Ashok

[former_member186338]

Ups, you turned on word wrap in notepad

Anyway, in my system the mentioned dimension member formula with the long function is working absolutely fine.

I am on SP12 for BW 7.30

Vadim

[Former Member]

Hi Ashok,

Did your member formulas calling from .LGF file issue is resolved? I have a question about your formula.

What is "ASSETLIFE" in member formula? Is it a member of account number? and how ASSETLIFE is linked to formula?

Please Help

[former_member186338]

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

[former_member200880]

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.

[former_member186338]

New discussion, please!

Vadim

[former_member200880]

hi Vadim

started new thread in below link

please help