BPC 10.1 : Number Formatting for Report

[former_member419358]

Hello Everyone,

I am working on BPC 10.1 on HANA System. My requirement is below. I have gone through number of threads couldn't found exactly what I was looking for.

In My report, I am getting data say in Thousands, Lacs, Millions but I want to see all figures divide by 1,00,000 which is mentioned in Expected BPC Output column. I could get Number formats in other threads if someone wants to divide by 1,000 or 10,00,000 but not if I want to divide by 100000

Actual Figures	Expected BPC Output
40,00,000	40.00
4,00,000	4.00
40,000	0.40
4,000	0.04

Currently I am using  Number Format as  #0","##,;-#0","##,

This number format gives wrong result in case of if Actual figure is 4,000 then it gives 0.4 instead of 0.04.

Kindly provide your input on the same.

[former_member225135 · Accepted solution]

Hi,

PLease check below link:

Custom Number Formats (Multiply & Divide by any Power of 10) | Chandoo.org - Learn Microsoft Exc...

Regards,

Rahul

[former_member419358 · Accepted solution]

Hi Rahul,

Thanks for your reply but it's not helpful. I tried couple of formats but still not useful.

[former_member225135 · Accepted solution]

Hi,

The format is working fine for me.

Regards,

Rahul

[former_member419358 · Accepted solution]

Hi Rahul,

Thanks for your reply. I was able to get number format what I wanted as you had shown. When I apply same format in BPC Report formating sheet, I am getting %% behind the numbers. I had given Ctrl J which sends %% sign down in the next line to hide it but it's not working in BPC report when I refresh it.

Did you format correctly in BPC report ?

[former_member186338 · Accepted solution]

Do you use formatting sheet? The use this custom format in the formatting sheet:

Number format: 0.00,,<CtrlJ>%

Text alignment Vertical: Top

Text Control: Wrap Text

Vadim

[former_member186338 · Accepted solution]

P.S. Sample BPC report with Default Format set in the Formatting sheet as in previous message:

Vadim

[former_member225135 · Accepted solution]

Hi,

As vadim said, use WRAP TEXT. you may be required to adjust font size also.

Regards,

Rahul

[former_member186338 · Accepted solution]

Top alignment is also required, by default it's - Center

[former_member419358 · Accepted solution]

Hi Vadim,

Please find below screenshot for your reference. Still no luck.

BPC Report Output

One more problem here is... in BPC Formatting Sheet-> Format Cell -> I had given 0.00,,,Ctrl J%% but once i refresh it, I always miss one , and my format becomes  0.00,,Ctrl J%%. Does it create any problem?

[former_member186338 · Accepted solution]

Incorrect number format, has to be 0.00,,,<CtrlJ>%%

<CtrlJ> - Press Control and simultaneously press J

P.S. Corrected to you case

Result:

[former_member419358 · Accepted solution]

Hi Vadim,

Thanks for the update but I didn't keep any space between Ctrl & J but still not getting the result.

As I also mentioned above, Once I give cell format & save my report and  Next time when I open BPC report, the cell format gets change. Does it happen with you also ?

[former_member419358 · Accepted solution]

Hi Vadim,

Thanks for the update but I didn't keep any space between Ctrl & J but still not getting the result.

As I also mentioned above, Once I give cell format & save my report and  Next time when I open BPC report, the cell format gets change. Does it happen with you also ?

[former_member186338 · Accepted solution]

Sorry, but everything is working fine in my system!

[Former Member · Accepted solution]

Which EPM version are you using and which MS excel version ?

[former_member419358 · Accepted solution]

Hi Geeta,

I am using MS Office 2013 and EPM Addin version 10.0 SP 24 Patch 1 (Build 9385)

[former_member419358 · Accepted solution]

Hello Everyone,

The issue is resolved. After giving the Cell Format in DATA column, I had to select ALL instead it was selected only Font and Number Format. I changed it to ALL and I was able to get the Number format that I wanted to have.

Thanks once again for your help.

[former_member419358]

Hi Vadim / Rahul,

The only problem I am facing is... Once I give the number format and refresh the report it works fine. After that I saved report and closed it. After that I opened report once again and my format gets changed and I get result multiply by 10. Ex. if Revenue is 9 then it shows 90.

Appreciate your input  / help in advance.

.

[former_member186338]

I don't have the mentioned issues...

P.S. Create new, very simple report with the required formatting and test it!

[former_member419358]

Thanks for the quick reply Vadim.

I developed a new report, please find below screenshot of the same. Format shows 1.00 instead of 0.10 after saved and closed it 🙂

It should show

Gross Revenue  -   41.32, 73.61

Net Ticket Rev  -   41.32, 73.31

Thanks again for your help and support

[former_member186338]

I can't imagine what you are doing wrong

Sample:

On the formatting sheet only this changes:

Format cells with:

Custom format: 0.00,,<CtrlJ>%      will divide by 10000

Result:

Can be refreshed, saved, closed, reopened - working absolutely as expected!

Vadim

[former_member419358]

Hi Vadim,

The format what you have shown above is working absolutely fine for me but I wanted to have it divided by 100000 and not 10000 so my format should be 0.00,,,<CtrlJ>%%.

Once you give the format mentioned above and refresh then we can see desired result but immediately after refreshing also if you go back to Formatting sheet then it converts to the format 0.00,,<CtrlJ>%%.

Can you try my format in your report and share your result ?

Thanks once again.

[former_member186338]

If you want to divide by 100 000 then the format has to be:

0.00,,,<CtrlJ>%% - three ","!!!!

Each "," - divide by 1000

Each % - multiple by 100

1000*1000*1000/(100*100) = 100 000

Result:

Save, close, refresh - fine!

Vadim

[former_member419358]

Thanks Vadim,

That is what exactly I did all the time and added THREE "," but once I save it, close it and open it, My format gets converted to 0.00,,<CtrlJ>%% - TWO ","..

I tried this in 3 different systems and I got the same issue. Currently I am in MS Office 2013. I tried this in MS Office 2007 also and found the same issue. 🙂

Anyway thanks for all your help and support. I will continue try to find out if I get anything and update on this thread.

[former_member186338]

Ups, looks like I was able to reproduce the issue!

And the issue is not related to BPC - Just pure Excel file with single cell formatted with

0.00,,,<CtrlJ>%%

After saving and reopening will convert it to 0.00,,<CtrlJ>%%

The only workaround is to use VBA code at on open to restore format:

ThisWorkbook.Worksheets("Sheet1").Range("C4").NumberFormat = "0.00,,," & Chr(10) & "%%"

Vadim

[former_member419358]

Hi Vadim,

Thanks for your reply. I have written the VBA code as you have mentioned above. I was able to get the expected result in column C using VBA Code but it shows % sign behind. Please find below screenshot for your reference.

Excel Output

VBA Code

But once again after I saved it, closed, open it and got the same issue 🙂

Thanks for all your help and support once again.

[former_member186338]

You have to put this code in the proper function an also format wrap and vertical alignment:

Private Sub Workbook_Open()

Dim rngFormat As Range

Set rngFormat = ThisWorkbook.Worksheets("Sheet1").Range("B1")

rngFormat.NumberFormat = "0.00,,," & Chr(10) & "%%"

rngFormat.WrapText = True

rngFormat.VerticalAlignment = xlTop

End Sub

Vadim