Formatting in EVDRE

[Former Member]

Hi all,

In an EVDRE report, we are able to simultaneously use both EVDRE formattting and also set the number format dynamically for the base level members .

When I try to implement formatting for CALC members in the EVDRE report, I am not able to dynamically use the number format defined in the FORMAT property of the dimemsion.

I would greatly appreciate suggestions as to how to accomplish this.

Regards,

Nithya

[Former Member · Accepted solution]

After more than 3 years of working with BPC I still don't understand the purpose of the FORMAT property in the account dimension.

If you want formatting to be different for base level members and calculated members, you can use two methods:

Method 1:

Use the conditional formatting from excel by retrieving the property value 'CALC' for an account and than create a conditional format that tests for this value (IE Y=bold, N=normal)

You cant use dynamic numberformat with this function because it is not supported by this function.

Method 2:

Use the advanced formatting range capabilities from BPC itself. How this works is explained in het online help and the office manual. You can use dynamic numberformats as well with this function.

Hope this helps,

Alwin

[Former Member]

Hi Garrett,

I have been working with EVDRE() for quiet few months but I want to know how to simultaneously use both EVDRE() formatting for the CALC members and FORMAT property dynamically.

[Former Member]

Hi Alwin,

As far as I have understood using the FORMAT property we can assign different formats for the members and use it dynamically in the EVDRE() report.

This works fine by having the parameter as NUMBERFORMAT=DIM.FORMAT and with some condition in CRITERIA for example DIM.CALC="Y" in EVDRE() formatting simply across all the data but when I try using different format for CALC members and base level members this does not work as expected.

[Former Member]

Hi Nithyapriya,

Been doing some work specifically with the "Advanced Formatting" in EvDRE recently and believe it or not, it's generally pretty easy to use.

It's also a lot more functional that using the regular Excel conditional formatting. Best way to get it going to is to start with the EvDRE builder by doing the standard entering of =EvDRE() in cell A1 and clicking the "Refresh" button.

You'll notice that the "Allow Formatting" option can then be selected, which will insert a starting Advanced Format range.

In the Advanced Format range, the columns allow you to specifically address (A.) the Default format, (B.) CALC formatted members, (C.) Inputs, and even specify certain members filtered by properties.

Take a look at the BPC for Office User Guide beginning on pg's 155 and 156 for some starters. But give it a try!

[Former Member]

I find more use with the Format property of the account dimension when using EVGTS in 4.2, but now that I'm starting to use EVDRE more I'm finding the advanced formatting much more user friendly. Granted I'm just picking it up, but a few tips that help me are:

leave blank rows in between rows of the advanced formatting so you can easier see where you are applying borders. If you set up the Format property or use properties like "calc" etc, then you can set up a ton of formatting options by setting the criteria such as account.format="3", then apply gray, no decimals, top border for example. Remember to set your default first and the order of the next operations do matter. For example if you are formatting a row, then a column, if the column property is similiar then you may end up inadvertantly changing the format for the entire row. Play around a bit and see what you can do. The more you use it the more comfort you'll have and if you have specific questions just post them up. Remember, you can basically use any cell property such as indent, font, etc.