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!
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.
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.
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.
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!
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.
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.
Thank you so much for looking into it. Per your suggestion I tried
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
Text' in RB, I did not notice this functionality before.
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 <email@example.com>
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?
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:
Let me know if this works for you!
Change the Scientific notation Format as text.
Prepend text single quote(‘).
Postpend text leave it as Blank.
Follow like as shown in the screen shot. you will get the complete numbers while receving reports as scheduled.