Scaling Data on report BPC 10.0

[Former Member]

Hi All,

I would like to know if anyone had solve the problem on ScalingData. On our latest version (7.5) we had a drop-down list (€, K€, M€) which changed my entire report. In the new version I can't find a similar way, the ScalingData function doesn't allow you to perform it on the entire report.

Is there a way to manage scaling data like the currency ?

Thanks a lot.

[Former Member]

Hi,

there is, but it is a bit hidden ....

step 1: use the SCALING property in the account dimension to identify the accounts you want to scale (Y) versus those you do not want to scale (N). For example you probably would not want to scale number of employees etc.

step 2: use the EPMSCALEDATA function in a report. The easiest way I have found to use this on the entire report is to use it via the formatting sheet.

on the formatting sheet, go to the use cell, the example uses the default colum format

double click and select & highlight content only

select the override option on the right and enter your empscale data function

confirm with the override button below.

the formatting sheet should now look something like this

cell c2 on the report sheet has the factor as a number such as 1, 1000, etc., cell d5 is the first column header and c6 the first row header. this is what the report for this example looks like

As you can see, the empscaledata function has been inserted into every data cell of the report.

Hope this helps.

BR,

Arnold

[former_member186338]

Hi Arnold,

A bit strange approach... I will try to explain:

1. EPMScaleData is the same as EPMRetrieveData, but with scaling option. It's the function that is retrieving data without EPM report.

2. You are using this function to override data cells of EPM report... The data retrieved by report itself is not used...

As a result performance issues can happen.

It's better to play with Excel conditional formatting with thousand scaling.

B.R. Vadim

[Former Member]

Hi Vadim,

it might be strange but so far it has worked very well for me. And it allows me to easily have a dropdown menu in my report header where I can change the scale (i.e. from 1 to 1000 or vice versa) and the numbers in the report immediately reflect this change, without needing the report to refresh.

BR,
Arnold

[former_member186338]

It depends on the report size...

With conditional formatting you can do the following:

1. Only thousand scaling is possible: 1000, 1000000...

2. You have to define number of conditional formatting rules (2 is enough) for 1000 and 1000000

3. Each rule have to check some cell for the value and apply format if the value in the cell match the value defined in the rule. Example: "K" for 1000, "M" for 1000000.

4. In the EPM Sheet options you have to set "Keep formulas Static that Reference Report Cells"

5. For each rule the Applies to area have to be defined wide enough to allow report expansion.

6. For the cell with condition values you can add data validation with the list of values to generate in-cell combobox.

Result - immediate scaling.

Vadim

[former_member186338]

Sample screenshots:

Cell with condition value contains 1, 1000, 1000000 and 2 rules are checking this value applying the respective format.

Vadim

[former_member671850]

Hi Arnoled,

When i use this function and after refreshing the sheet i get the following error

#Error - Invalid Member Name: #Not an EPMOlapMember formula

Please let me know the resolution of this issue.

Regards

[former_member671850]

Hi Vadim,

How can I use it in BPC 10 >> EPMRetrieveData

[former_member186338]

"How can I use it in BPC 10 >> EPMRetrieveData" - what?

[former_member671850]

Hi Vadim,

I mean to say I am also facing the same problem as Mr.Arnoled presented the idea to use scaling formula in formatting option. But I am getting an error

#Error - Invalid Member Name: #Not an EPMOlapMember formula

The explanation given by you is easy but how I use EPMRetrieveData function in a report. Currently I am using following way to show a report in thousands >> I go to Excel cell formatting option and in custom i put  #,##0, this convert all the amount in to thousands. Please elaborate how can i use EPMRetrieveData function.

Regards

[former_member222556]

Hi Sohaib,

you can use excel format function in EPM format sheet to acheive this.

format for #K is "#,"

1,10,000 = 110 - it removes zero after first comma

you can keep adding "," after "#" .

no need to complicate the things

Thanks

Obaid

[former_member671850]

Hi Obaid

Can you explain (format for #K is "#,") what is K

I am using following way to show a report in thousands >> I go to Excel cell formatting option and in custom i put  #,##0, this convert all the amount in to thousands. Please elaborate how can i use EPMRetrieveData function.

I know the conversion #,##0, but let me know how and where do i put this in formatting options.

Regards

Sohaib

[former_member186338]

Please read my explanation here:

The idea is to use Excel conditional formatting for the cells with data (cells of EPM report or cells containing EPMRetrieveData function)

Conditional formatting rule will check some fixed cell and depending on the results of this check will format cells with #,##0, or other format.

Vadim

[former_member222556]

Hi Sohaib,

K-Thousand

Formating option:

1) EPMformatsheet:

2) Data section

3) Apply the excel format