Expand my Community achievements bar.

Report Builder keeps formatting numbers in Scientific/Text format

Avatar

Level 1

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!

10 Replies

Avatar

Community Advisor

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

Avatar

Level 1

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

Avatar

Community Advisor

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

Avatar

Level 1

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

Avatar

Employee Advisor

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.

Avatar

Level 1

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>

Avatar

Employee Advisor

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?

Avatar

Employee Advisor

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!

Avatar

Employee Advisor

Hi Natalja, were you able to get this resolved?

Avatar

Level 2

@nataljaz4307690 

  • Change the Scientific notation Format as text.

  • Prepend  text single quote(‘).

  • Postpend text leave it as Blank.

kirang29990284_1-1626672611184.png

 

kirang29990284_2-1626672715601.png

Follow like as shown in the screen shot. you will get the complete numbers while receving reports as scheduled.

Kiran