Formatting of EPMLink/ EPMPathlink

[Former Member]

Dear Experts,

I try to create a table with numbers (Sumifs Formula based on BPC data above) which should include a EPMPathLink.

This works sofar, but I am not able to format the "DisplayedText" in a Number Format.

Neither Formatting of the cells nor including Value() or Text(---;"0.00#,00) does result in a number format.

Does anyone knows something about formatting of "DisplayedText" in epmLink/ epmPathlink Formula?

Thanks

Jo

[Former Member]

My conclusion is that it is not possbile to format DisplayedText Part of EPMLink/ EPMPathLink Formula.

Might be a possbility using VBA, but this is not an option in my case.

Assumed Answered, but if anyone has got an idea, let me know!

Best regards

Jo

[former_member186338]

Hi Johannes,

If you simply want to have text string in the cell formatted as number, then TEXT will do the job:

=TEXT("12345,4732";"# ##0,00;-# ##0,00") in your case use SUMIFS(...) instead of "12345,4732"

The format string is language specific!

Vadim

[Former Member]

Hi Vadim,

thanks for this suggestion.

The Text formula result in a number-"like" format, but Excel does not interpret this to be a number, thus conditional formatting based on >1.000 and <-1.000 is not possible.

Next to this, the text string is always is aligned left, which is not really user friendly.

I solved this by using two reports below eachother, the upper one shows the value of sumifs and applies conditional formatting, the lower one is also based on conditional formatting, but shows "Check" as a text string.

Thanks

Jo

[former_member186338]

Ups, if you want to use text string formatted as number in conditional formatting you can simply use the following formula for conditional formatting:

=C6*1>10000 (or =VALUE(C6)>10000)

Left alignment - just apply right alignment to the column and to the conditional format.

Vadim

[former_member186338]

Hi Johannes,

Please, provide full formula in the cell and the result as a screenshot!

Vadim

[Former Member]

Hi Vadim,

I solved it currently by using a second report with an epmlink below, but it would be better if I could have only one report in which the value is shown and epmlink is behind.

It should be something like:

=EPMPathLink(;"REPORTS\BS\29-10 Intercompany Details.xltx";

VALUE(SUMIFS(I:I;$G:$G;EPMMemberProperty(;EPMMemberID($F105);"ID")&"A"));

<ENTITY>;<BUSINESSPARTNER>)

But the Number is formatted automatically in a Text Format... but I would like to have it as a number.

Thanks

Jo

[former_member186338]

P.S. look on this 2 Excel formulas:

="124.4" - will return text in the cell

="124.4"*1 - will return number

Vadim

[former_member186338]

Just:

=1*EPMPathLink(...)

[Former Member]

I do not completly understand what you mean with

="124.4" - will return text in the cell

="124.4"*1 - will return number

using =1*EPMPthLink is resulting in a number Format, but the Link does not work anymore...

[former_member186338]

Ups, if =1*EPMPthLink is not working, then - no chance

Multiplication of string value by 1 will convert the result to number if possible. The same effect with =VALUE("124.4")

What is your goal - to get a number in a cell or?

Vadim

[Former Member]

That's what I want to achieve.

But it seems like EPM Coding converts the DisplayedText Part of EPMLink Formula automatically into a Text Format, independently on the Formula string.

Value is not working, value()*1 also not, seems like the format is defined after the formula is executed.

Well, I think the current setup is delivering the functionality we need, but having the sumif and Link in one report would be more user friendly.

Thanks anyway for taking your time, even during weekend!

Cheers

Jo

[former_member186338]

EPMPathLink returns string - no questions. And you can't use any formula with the result of EPMPathLink in the same cell. But may be you can add another column with formula:

=VALUE(X5), where X5 is the cell with = EPMPathLink

Vadim