Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SET NODATA = '' in IA; calculations bring back #value error...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SET NODATA = '' in IA; calculations bring back #value error...
 Login/Join
 
CoolGuy
Virtuoso
posted
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.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
BabakNYC
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
CoolGuy
Virtuoso
posted Hide Post
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.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Hallway
Master
posted Hide Post
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Hallway
Master
posted Hide Post
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
BabakNYC
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Hallway
Master
posted Hide Post
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
BabakNYC
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Hallway
Master
posted Hide Post
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Tamra
Guru
posted Hide Post
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
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
CoolGuy
Virtuoso
posted Hide Post
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.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Hallway
Master
posted Hide Post
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
CoolGuy
Virtuoso
posted Hide Post
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.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Tamra
Guru
posted Hide Post
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
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report This Post
CoolGuy
Virtuoso
posted Hide Post
Thanks Tamra!

Appreciate the help!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SET NODATA = '' in IA; calculations bring back #value error...

Copyright © 1996-2020 Information Builders