Quick inquiry:

Where in the IA tool can one do this?:

SET NODATA = '';

Any help is appreciated! Thanks!

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

I don't think this SET command is available in IA. Could you put it in a user profile or edasprof?

Are Reporting Objects an option?

Thanks BabakNYC for the ideas!

We are utilizing ROs for these reports that export to XLSX. We put it in the preprocessing like you suggested and it seems to do the trick partially. Once in Excel, it saves the NODATA = '' as a blank space (string). So when you try to add a cell with a value with a cell that has no data, you get an error. But if you try to SUM a bunch of them, it works without issue. ???

It works fine in EXL2K. It just won't work as an XLSX. I guess we just need to hop in the time machine and go back 16 years for it to work correctly... *smh*

In EXL2K, the number format is set as #,###.00






in XLSX, the number format is set as #,###.00;-#,###.00;#,###.00;@" "





The last part @" " signifies what to use if it is a blank value and totally messes up any ability to use that cell in any formulas in Excel.

So it appears once again we have to stay in the dark ages of Excel with WebFocus.

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

I just tried this as below:

SET NODATA=''
TABLE FILE CAR
SUM RCOST
BY CAR
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT EXL07
END

Then I opened the xlsx file it generated. Opened the Excel file and in column outside the output range place =D10+F10 and got an answer. I also got a value for =SUM(C4:C13) I also tried the same with FORMAT EXL2K and got the same results. This is all in 8105 which I believe you're running.

Interesting. I tried your code, and it worked on my end as well. So I added to your code the default style sheet that IA is set to use and it breaks Excel

 
SET NODATA=''
TABLE FILE CAR
SUM RCOST
BY CAR
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
ENDSTYLE
END 

In fact, it seems that any style sheet that I use breaks Excel

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

Very odd, I just ran your code with the STY and my formulas work in Excel 2010. I'm out of ideas.

Odd... I'm using Excel 2013. I wonder if that has anything to do with it.

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

Hello,

Here is the IBI Roadmap for Excel with information of supported options within WebFOCUS.

WebFOCUS EXL07/XLSX Format Supported Features Release Roadmap

The following link within the TechSupport site may be of assistance as well.

How to get SET NODATA=N/A to show up in EXL2K output.

SET EMPTYCELLS=OFF / ON

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator - Information Builders Inc.
* Summit 2016 – June 13-17 in Reno, Nevada  - http://www.informationbuilders.com/events/summit

Thanks Tamra!

Appreciate your help! I've let my coworker know of your updates and suggestions.

quote:

Originally posted by Tamra:
Hello,

Here is the IBI Roadmap for Excel with information of supported options within WebFOCUS.

WebFOCUS EXL07/XLSX Format Supported Features Release Roadmap

The following link within the TechSupport site may be of assistance as well.

How to get SET NODATA=N/A to show up in EXL2K output.

SET EMPTYCELLS=OFF / ON

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator - Information Builders Inc.
* Summit 2016 – June 13-17 in Reno, Nevada  - http://www.informationbuilders.com/events/summit

Tamra,

How would an InfoAssist user implement this?

The SET EMPTYCELLS = OFF doesn't fix the issue either. Any other ideas?

Just thought I should reply . The "Roadmap" provided in a previous link was for informational purposes only.

At this point for InfoAssist a SET command can be added to the edasprof for general use. There is not a way for Users to add a SET into InfoAssist.

There are various New Feature Request asking for this type of option for InfoAssist.

I have found the following comment from a project manager as of Jan 2016:
We do not currently support EMPTYCELL=OFF in XLSX. This is targeted for a future release.

Hope this helps.

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator - Information Builders Inc.
* Summit 2016 – June 13-17 in Reno, Nevada  - http://www.informationbuilders.com/events/summit

Thanks Tamra!

Appreciate the help!