Highlighted

Report Builder keeps formatting numbers in Scientific/Text format

Avatar

Avatar

nataljaz4307690

Avatar

nataljaz4307690

nataljaz4307690

10-09-2018

Hello -

In my reports I have to report on confidence level that uses decimal number, which is not standardized and can get very long. In the report the number of decimals higher than 13 decimals gets converted to Scientific/Text format. I can manually change these types of numbers to number/generic format, and if I refresh report manually RB keeps the number/generic format. However, the scheduled report keeps reversing the numbers back to the Scientific/Text format. I spoke to Adobe client care they said that this is an excel limitation as the cell should display no more than 11 digits in the General format. I tried formatting, formulas and was not able to find any solution yet. Do you have any suggestions for me? Thanks in advance!

Replies

Highlighted

Avatar

Avatar

pabloc82923542

Avatar

pabloc82923542

pabloc82923542

11-09-2018

The challenge is indeed an excel one. The Adobe report builder data blocks when refreshed/or newly added automatically kill any previous custom formatting exel formatting, they also are indeed limited to excels limitations. In terms of report builder options there is no solution for scheduled reports.

What you may be able to do is use data warehouse for the formatting challenged sections. The challenge here is report builder can do many sectioned report-lets in one doc, while data warehouse is limited to one report per scheduled report-let. Its not a clean easy one doc solution, but in theory would work.

gltu

Highlighted

Avatar

Avatar

nataljaz4307690

Avatar

nataljaz4307690

nataljaz4307690

11-09-2018

Appreciate the suggestion!

This data is a part of a bigger automated report. I am trying to avoid any manual manipulations and am looking for possible ways to adjust the formatting automatically most likely by excel formulas.

Nat

Highlighted

Avatar

Avatar

pabloc82923542

Avatar

pabloc82923542

pabloc82923542

11-09-2018

Then I am sad to say you no real option, you could reduce the decimals to excels limitation...(but I am guessing that is not ideal)

Other than that there is no simple automatic solution.

regards

Pablo

Highlighted

Avatar

Avatar

nataljaz4307690

Avatar

nataljaz4307690

nataljaz4307690

11-09-2018

Thank you, Pablo!

Yes, I am working with IT to see, if they can change the digits number from the back end.

Thank you again for the feedback!

Nat

Highlighted

Avatar

Avatar

hyderziaee

Employee

Avatar

hyderziaee

Employee

hyderziaee
Employee

12-09-2018

You can try one more thing. Prepend the text with a single quote.

If this is an event, click on the None besides it

In the context menu select 'Prepend/Postpend Text'.

In the first box, enter a single quote.

1570614_pastedImage_3.png

1570613_pastedImage_1.png

This will make Excel not change the number to scientific format, and also Excel by default hides the single quote if starting with a single quote.

Highlighted

Avatar

Avatar

nataljaz4307690

Avatar

nataljaz4307690

nataljaz4307690

14-09-2018

Hello -

Thank you so much for looking into it. Per your suggestion I tried

'Prepend/Postpend

Text' in RB. For some reason it did the opposite. I originally set

formatting for this data block to remove scientific/text formatting. When I

added single quote and refreshed the data block manually all numbers turned

back into and scientific/text format. This is why I honestly think that

this is RB functionality issue and not Excel.

Thank you so much for your help! Also special thanks for showing me

the 'Prepend/Postpend

Text' in RB, I did not notice this functionality before.

Nat

After - this is how it looks like with the "prepended" single quote.

Before* - this is how the data looks like with the set formatting

(removing text/scientific formatting) refreshing *manually

On Thu, Sep 13, 2018 at 1:25 AM, hyderziaee <forums_noreply@adobe.com>

Highlighted

Avatar

Avatar

hyderziaee

Employee

Avatar

hyderziaee

Employee

hyderziaee
Employee

18-09-2018

I don't see the images if there were any in the response.

Can you try keeping the formatting to default, not text, and then try the single quote solution?

Highlighted

Avatar

Avatar

Gigazelle

Employee

Total Posts

1.9K

Likes

459

Correct Answer

734

Avatar

Gigazelle

Employee

Total Posts

1.9K

Likes

459

Correct Answer

734
Gigazelle
Employee

21-09-2018

I frequently find that it takes a couple tries for Excel formatting to work. A lot of times I'll get almost all my data requests in date format for some reason. Try this:

  1. Format the cells the way that you want them to
  2. Schedule it to be sent to yourself immediately
  3. When the workbook arrives, open it, and you might see that the formatted cells aren't correctly formatted anymore
  4. Using this emailed workbook, set the formatted cells to the way you want them again
  5. Reschedule the emailed workbook (and not the original one) to be sent to yourself again
  6. If your experience is the same as mine, the cell formatting should stick now

Let me know if this works for you!

Highlighted

Avatar

Avatar

Gigazelle

Employee

Total Posts

1.9K

Likes

459

Correct Answer

734

Avatar

Gigazelle

Employee

Total Posts

1.9K

Likes

459

Correct Answer

734
Gigazelle
Employee

26-09-2018

Hi Natalja, were you able to get this resolved?