XFCell number format issue in People Planning Solution

AndreaF
Contributor III

Hi,

I have setup the Global drivers in the People Planning module to be XFCell formulas, so that users can input drivers by Period/Scenario via cube views.

 

The formulas look like this one:

(XFCell(Cb#People_Planning:E#RU0451:P#GL1151:C#Local:S#Plan:T#|WFTime|:V#Periodic:A#PP_Acc_Driver_DE007:F#None:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None))

 

The formula works fine as long as the value retrieved is lower than 1000. The reason is that the retrieved value is by default retrieved in the #,###.000 format, which the People Planning module doesn't like. This is an example of error message:

Summary: Error processing Data Management Step 'CalculatePlan_PLP'. Unable to execute Business Rule 'PLP_DataMgmt'. Global Driver [DE Max health insurance wage] Expression Error
Non-Numeric value found after substitution: [Expression Before Substitution =(XFCell(Cb#People_Planning:E#RU0451:P#GL1151:C#Local:S#Plan:T#|WFTime|:V#Periodic:A#PP_Acc_Driver_DE007:F#None:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None)), Expression After Substitution=(56,250.000)]. Syntax error in the expression.

 

I have therefore tried to include a NumberFormat option in the XFCell formula, like here:

(XFCell(Cb#People_Planning:E#RU0451:P#GL1151:C#Local:S#Plan:T#|WFTime|:V#Periodic:A#PP_Acc_Driver_DE007:F#None:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, NumberFormat=####0.00))

but the People Planning seems to not like the fact that there is a coma in the string, because this is the message it gives when trying to save the formula in the Global drivers page:

"String or binary data would be truncated. The statement has been terminated."

 

Anybody else had the same issue? How did you solve it?

Thank you

 

1 ACCEPTED SOLUTION

AndreaF
Contributor III

The workaround to the DriverValue column of the Global drivers table having insufficient space for the full XFCell command has been creating an XFBR rule which returns the full XFCell command as a string and to have the XFBR command called in the Global drivers table.

View solution in original post

2 REPLIES 2

AndreaF
Contributor III

Quick update on this, it seems the issue was that I've simply hit the limit of number of available characters for the DriverValue column of the Global drivers table, so the

XFCell(Cb#People_Planning:E#RU0451:P#GL1151:C#Local:S#Plan:T#|WFTime|:V#Periodic:A#PP_Acc_Driver_DE007:F#None:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None)

string can fit in the column of the table, while the

XFCell(Cb#People_Planning:E#RU0451:P#GL1151:C#Local:S#Plan:T#|WFTime|:V#Periodic:A#PP_Acc_Driver_DE007:F#None:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None, NumberFormat=####0.00)

string cannot fit in the same column.

I'll need to figure out an alternative way to use the XFCell in the second case. Any suggestion is appreciated.

Thank you

AndreaF
Contributor III

The workaround to the DriverValue column of the Global drivers table having insufficient space for the full XFCell command has been creating an XFBR rule which returns the full XFCell command as a string and to have the XFBR command called in the Global drivers table.