Hi Community,

I'm wondering if someone already had this situation and if a solution exist ?

I have the below code where I want to display 0.00 (I have tried several value and all result in the same) in Excel when no data exist.
It seems that the SET NODATA = "something" result in blank value when looking into the formula bar; so no more Excel formula can be accomplished.

SET NODATA = 0.00

TABLE FILE CAR
SUM COMPUTE COST /P8.2C MISSING ON = IF COUNTRY EQ 'ITALY' THEN MISSING ELSE DEALER_COST;
BY COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT XLSX
END
-RUN

To have Excel been able to perform formula, I need: SET NODATA = ''
But I have the empty cells (missing one) displayed as "blank" where I need 0.00

Any thought ?

This message has been edited. Last edited by: MartinY,

Martin

I am assuming in the real world you would check the field to see if the value is NULL then you could just assign the value to zero as below.


TABLE FILE CAR
SUM COMPUTE COST /P8.2C MISSING ON = IF COUNTRY EQ 'ITALY' THEN 0 ELSE DEALER_COST;
BY COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT XLSX
END
-RUN


Hi Chuck,

Thanks for the suggestion, but if it was that simple, I already have it done.

In my sample I made it simple just to illustrate and reproduce the case.

The point is that I cannot (and I don't want) to test (and redefines within a COMPUTE) all the fields for missing value since a simple SET NODATA can do it and which is the purpose.
Processing COMPUTE to all possible missing data field will increase the processing time, internal matrix usage and coding lines which I don't want.

As for another sample, use the below where I have no COMPUTE but where "missing" data exist.
The 0.00 is displayed in every cells where no data exist, but not possible to perform Excel formula using these cells.
Using SET NODATA = '' will allow to use Excel formula but blank is displayed in empty cells.

SET NODATA = 0.00

TABLE FILE CAR
-*SUM COMPUTE COST /P8.2C MISSING ON = IF COUNTRY EQ 'ITALY' THEN MISSING ELSE DEALER_COST;
SUM DEALER_COST
BY COUNTRY
BY CAR
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT XLSX
END
-RUN

Martin

I must admit I felt silly providing you that answer when I knew you would have tried 30 variations. I'm sorry I don't have the answer...

Not a problem Chuck. It's good to remind simplest things, sometime the solution is right in front of our nose and we don't see it.

I'll wait a little more to let other contributors share their solution and if none comes up, I will open a case.

Hi Martin

I don't have a great answer for you, but an observation to pass along that maybe will spur you or one of the other members to come up with an idea.

I took your second set of code and added a ROW-TOTAL and COLUMN-TOTAL and then changed the PCHOLD to do XLSX FORMULA just so I could see how the formula part would work.

SET NODATA = 0.00

TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
BY CAR
ACROSS COUNTRY
ON TABLE ROW-TOTAL AS 'ROWTOTAL'
ON TABLE COLUMN-TOTAL AS 'COLTOTAL'
ON TABLE PCHOLD FORMAT XLSX FORMULA
END
-RUN

Interestingly, the COLUMN totals all work. And they look good too. The way Excel shows those to me when I click on a Column total is =SUM(C4:C13) for example. the place where we get the errors in the formulas are when we ROW-TOTAL which uses =cell+cell+cell instead of =SUM.

For grins, I changed the first formula ROW-TOTAL to =SUM(C4,D4,E4,F4,G4) by just replacing the + signs with ,'s and added the SUM in the front. That makes it work.

So - one workaround of sorts might be to figure out how to force in a function of some sort (like SUM) and try to avoid simple col+col+col kind of formulas.

You've been posting here for so long and with so many answers, my thinking is that FOCUS (even with one of the Walters) is likely not going to help you. Maybe the answer lies more in the Excel world.

The error I see when I get #VALUE! is: a value used in the formula is of the wrong data type. That matches what you see where the cell looks okay with 0.00 but the actual value appears blank.

For SUM reason (sorry - couldn't resist) the SUM function seems okay with the data type being different.

Only thing I can think of is some kind of NFR to clearly set the Column data type to match the numeric format of the data that should go in there. Then maybe Excel will automatically know how to handle the other data type (or maybe we have to set some preference in Excel that says to treat missing numbers as zero).

In the day I worked premium tech support, this was Kathy Kendall's area but I think someone else is managing this now.

To me, this is core FOCUS more than WebFOCUS so I'd attempt to open my case under the Reporting Server Group.

You could go ahead and collect a savediag for them when you open the case if you decide to go that route along with the resulting Excel file that gets created. This way they have everything already in hand to move forward.

Hope the =SUM part is a clue maybe someone can help with. If anybody already knows a setting in Excel that says to treat missing data as a zero, let us know.

Thanks!
Toby Mills, CISSP

This seems like an Excel issue. If I understand correctly, you are trying to set a formula in Excel on the cells that are marked 0.00? Those cells have a format of Custom, I tried to change it to Number but it is still a no go. I can create a formula in a blank cell using two cells that have numbers. Those have a format of number.

Thanks Toby for your input.

Yes it seems that if we use the Excel =SUM(C4:G4) feature and not the manual =C4+D4+E4+F4+G4 it does work.
But hard to tell users not the use the second method and only the first one.
Especially when you may have to select specific cells and not a whole row/column or perform other function than a SUM such as =D4-C4.

I don't think that if I end with opening a case, I would have to send a savediag. Only with the sample code it can easily be reproduced.

quote:

This seems like an Excel issue...
Those cells have a format of Custom

I don't think that it's an Excel issue because FOCUS is probably "sending" the format of the 0.00 as sort of text.
In other word it seems that SET NODATA = "something" result as sort of text format which become "custom" in Excel and not a numeric or general one that allows to perform calculation.

It's all about the SET NODATA = "something" for the missing values.
I can display 0.00 such as below.
Since I haven't assigned anything to missing values, they are displayed with the default dot (.) and those cells cannot be used in a custom Excel formula due to the format of the cell send by FOCUS.

SET CENT-ZERO = ON
TABLE FILE CAR
SUM COMPUTE COST /P8.2C MISSING ON = IF COUNTRY EQ 'ITALY' THEN 0.00 ELSE DEALER_COST;
BY CAR
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT XLSX
END
-RUN
-EXIT

Hi Martin

Sounds like you understand the problem the same as I do. There should be something that lets IBI dictate the type of column. The . for missing maybe is not an ideal thing, so using the 0.00 is good.

Your ACROSS example with ROW-TOTAL and COLUMN-TOTAL really with holding XLSX FORMULA blatantly shows the #VALUE! issue so the IBIers can see whats going on.

Just send them the example. Surely they can get a savediag themselves with a CAR file example.

You can put the letters NFR in your title so they know you're asking for a New Feature Request. Part of any new NFR is to explain a business reason for the NFR. Doesn't matter if it's a great looking reason, just part of the flow of an NFR. Just put something like "this NFR will improve the adoption of XLSX Formula within our company and save us valuable time".

Good luck!
Toby

quote:

I don't think that it's an Excel issue because FOCUS is probably "sending" the format of the 0.00 as sort of text.

I totally agree with you and Toby.

This is FOCUS passing a display rather than replacing the underlying value which is NULL.

I think your only real solution would be to change MISSING values to 0 within your code.

T

I have open a case regarding this issue
I will post the answer when receive it

Here is the explanation from
WebFOCUS Upgrade Considerations v8.1.05M, page 29 :

XLSX Format
The following are upgrade considerations and product changes for XLSX format:

- As of WebFOCUS Reporting Server Release 7.7.06M Gen 853, the NODATA display in cells
  has been enhanced to display symbolic text, instead of values that can be aggregated. For
  MISSING values, the NODATA symbol will continue to be presented in the cell, but now the
  formula bar will show an empty cell, instead of a zero (0).

And the answer

The topic has been raised by several customers.
While you say it make is not possible to use the quick AutoSum feature, it has also been debated that the representation of null values is more accurate.
Here is some doc on Excel usage.

Technical memo

It explains...  Support for full Excel functionality requires that only valid numeric values are placed into cells that will be used for formula references.
I don't really have a solution for you other than a .CSV file.
A COMT file is a .CSV file that commonly opens in Excel.
The behavior you are seeing for the XLSX driver is expected.

This message has been edited. Last edited by: MartinY,

We had this same issue when on 8105. However it was corrected when we updated the server to gen 921 and applied some different hot fixes that were sent to us. What's the version of your server?

One thing that we found is that it was only happening in XLSX outputs, but the EXL2K seemed to work okay. That could be a workaround for you.

I think in earlier versions the cell contents was the actual NODATA value as text and that causes errors in Excel formulas, but I can't remember exactly.

Try it in your 8201M environment, and see if it happens there. I tried it on my 8201M and it works. It gives the cell a number format of:

 #,##0;-#,##0;#,##0;@"0.00" 

Syntax explanation:

1. Format for positive numbers
2. Format for negative numbers
3. Format for zeros
4. Format for text

More Excel formatting info here: https://support.office.com/en-...cd-bf33-80f18b4eae68

So, for the NODATA cells it is formatting the cell as text, but the cell is empty (basically the same as typing a single quote in a cell and pressing enter). So this makes it a text cell and the format for the text (#4 above) will be what you put in the NODATA. Since the cell is empty, it doesn't mess up Excel formulas.

This message has been edited. Last edited by: Hallway,

Thanks for the suggestion Hallway.

EXL2K is not an option

The following code in WF8201M gen 240 have the NODATA cell customized to

0;-0,0;@"0.00"

and prevent the formula to work

Fex:

SET NODATA = 0.00

TABLE FILE CAR
SUM SALES
BY CAR
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT XLSX
END
-RUN