Number Format in Excel for cells defaulting to 0

building reports webintelligence
1

What number format do I use to ensure that the cells displaying 0 counts do not convert to text and display the corner brackets in Excel. See attachment.

The number format I am now using in Webi is #,##0;;0;0: It is a custom format which defaults 0 or non-existent hours to 0. It is clearly not working.

Thanks,

Clare Marie :?:
Excel Number Formatting.xls (13.0 KB)

Clare Marie (BOB member since 2004-01-28)

2

Hi Clare Marie,

When I open your attached document on my pc, I don’t see your issue. I’m not seeing any brackets around the zeroes and I checked the format of each of the cells in the table you provided and they’re all number format - take a peek at my own attachment.

Since the file looks okay on my pc I assume that this isn’t BO formatting an excel document differently that you expect. Instead I’d guess that it’s your own excel install that’s treating the zeroes this way (as you describe). - Do you get the issue at any other time, maybe opening up something unformatted like a CSV into Excel?
my example.GIF

SteveD :uk: (BOB member since 2009-11-02)

3

Thanks. I had a hunch it was the Excel version. CSV is only an option in View mode, not Modify mode in our version of Webi. Using csv format creates an extra step for the client you runs this report: Run/Save/Select View Mode. So be it. It’s hard to sell customers on a new reporting environment when it creates more work than the old one (we are converting from full-client B.O. 5.1).

And no, I can’t schedule out the report. It relies on different end dates in a prompt each month and our Universe lacks dynamic date objects.

Clare Marie :wave:

Clare Marie (BOB member since 2004-01-28)

4

Actually when I was talking about CSV files, I meant what happens when you open up any CSV file into Excel - not specifically one from BO. My point being that CSV is unformatted so if you had one, or a text file and opened it up into Excel and saw the same thing, then you’d know it was Excel that was the issue :yesnod:

Wot no calendar tables? That’s bad full stop. :shock:

SteveD :uk: (BOB member since 2009-11-02)

5

The issue doesn’t occur when opening any csv file in Excel.

Clare Marie (BOB member since 2004-01-28)