Focal Point
[SOLVED] SET NODATA = '' in IA; calculations bring back #value error...

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4047048086

February 18, 2016, 11:40 AM
CoolGuy
[SOLVED] SET NODATA = '' in IA; calculations bring back #value error...
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,


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
February 18, 2016, 11:54 AM
BabakNYC
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?


WebFOCUS 8206, Unix, Windows
February 18, 2016, 12:36 PM
CoolGuy
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. ???


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
February 18, 2016, 01:20 PM
Hallway
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*


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
February 18, 2016, 01:37 PM
Hallway
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,


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
February 18, 2016, 01:49 PM
BabakNYC
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.


WebFOCUS 8206, Unix, Windows
February 18, 2016, 02:11 PM
Hallway
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,


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
February 18, 2016, 02:29 PM
BabakNYC
Very odd, I just ran your code with the STY and my formulas work in Excel 2010. I'm out of ideas.


WebFOCUS 8206, Unix, Windows
February 18, 2016, 02:35 PM
Hallway
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,


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
February 26, 2016, 09:40 AM
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


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
February 29, 2016, 06:09 PM
CoolGuy
Thanks Tamra!

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
March 08, 2016, 03:30 PM
Hallway
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?


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
March 08, 2016, 03:40 PM
CoolGuy
The SET EMPTYCELLS = OFF doesn't fix the issue either. Any other ideas?


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
April 15, 2016, 11:21 AM
Tamra
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


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
April 15, 2016, 11:39 AM
CoolGuy
Thanks Tamra!

Appreciate the help!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.