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     [CLOSED] XLSX NODATA FORMAT

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] XLSX NODATA FORMAT
 Login/Join
 
DLR
Member
posted
I am creating a report using FML and in the rows that have nodata I am setting NODATA = .00

This works but however when output is XLSX the data in the cells are not right justified.

The nodata rows end up with a custom format of #,###.00_);(#,###.00);.00_);@".00"
The rows with data end up with a custom format of #,333.00_);(#,###.00);.00_)

As a result the columns don't appear to be right justified.

Is works perfectly if I change the output to EXL2K.

Anyone have any ideas?

Diane

This message has been edited. Last edited by: FP Mod Chuck,


WF 8.1.04
 
Posts: 26 | Registered: February 25, 2005Report This Post
MartinY
Virtuoso
posted Hide Post
Can you try to reproduce with IBI sample file such as CAR file ?

I tried it on my side but unable to reproduce your error, everything look good but I'm using 8.1.05M


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
BabakNYC
Virtuoso
posted Hide Post
Hi Diane:
I just tried this and got expected results. Does this code work on your system?
SET NODATA=.00

TABLE FILE CAR
SUM DEALER_COST 
BY CAR
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT XLSX
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
DLR
Member
posted Hide Post
The code below works fine:

DEFINE FILE CAR
SORTCAR/A2=
IF CAR EQ 'FORD'
THEN
'10'
ELSE
IF CAR EQ 'AUDI'
THEN
'15'
ELSE
IF CAR EQ 'DATSUN'
THEN
'20'
ELSE
'99';
END
TABLE FILE CAR
SUM DEALER_COST
ACROSS COUNTRY
FOR SORTCAR
10 AS 'FORD (NO DATA IN FILE)' LABEL FORD OVER
15 AS 'AUDI' LABEL AUDI OVER
20 AS 'DATSUN' LABEL DATSUN OVER
99 AS 'ALL OTHER' LABEL ALLOTHER
ON TABLE PCHOLD FORMAT XLSX
END


However I have my amount fields defined as D12.2B, using the code below which redefines the dealer cost, the output does not align correctly in xlsx.

DEFINE FILE CAR
DEALERCOST/D12.2B=DEALER_COST;
SORTCAR/A2=
IF CAR EQ 'FORD'
THEN
'10'
ELSE
IF CAR EQ 'AUDI'
THEN
'15'
ELSE
IF CAR EQ 'DATSUN'
THEN
'20'
ELSE
'99';
END
TABLE FILE CAR
SUM DEALERCOST
ACROSS COUNTRY
FOR SORTCAR
10 AS 'FORD (NO DATA IN FILE)' LABEL FORD OVER
15 AS 'AUDI' LABEL AUDI OVER
20 AS 'DATSUN' LABEL DATSUN OVER
99 AS 'ALL OTHER' LABEL ALLOTHER
ON TABLE PCHOLD FORMAT XLSX
END

Diane


WF 8.1.04
 
Posts: 26 | Registered: February 25, 2005Report This Post
BabakNYC
Virtuoso
posted Hide Post
I just tested your code in 8.2 and the HTML looks exactly the same as xlsx and the cells align properly. Unfortunately, I don't have 8.104 to compare it with. IB Tech Support might be able to shed some light on this.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
tomatosauce
Gold member
posted Hide Post
I ran the code in 8105 and noticed that the amount is not properly right aligned. Adding SUM DEALERCOST/D12.2C in table file worked for me.

 
SET EXCELSERVURL=''
SET NODATA=.00

DEFINE FILE CAR
DEALERCOST/D12.2B=DEALER_COST;
SORTCAR/A2=
IF CAR EQ 'FORD'
THEN
'10'
ELSE
IF CAR EQ 'AUDI'
THEN
'15'
ELSE
IF CAR EQ 'DATSUN'
THEN
'20'
ELSE
'99';
END
TABLE FILE CAR
SUM DEALERCOST/D12.2C
ACROSS COUNTRY
FOR SORTCAR
10 AS 'FORD (NO DATA IN FILE)' LABEL FORD OVER
15 AS 'AUDI' LABEL AUDI OVER
20 AS 'DATSUN' LABEL DATSUN OVER
99 AS 'ALL OTHER' LABEL ALLOTHER
ON TABLE PCHOLD FORMAT XLSX
END


Tharun Katanguru
SBOX- 8205 DEV/TEST/PROD : 8105 8205
Linux, All Outputs
 
Posts: 52 | Location: BOWL OF PASTA | Registered: October 13, 2016Report This Post
DLR
Member
posted Hide Post
D12.2C works for me also.

It's the D12.2B that is not aligning. Finance folks like the negatives to be in brackets.

Diane


WF 8.1.04
 
Posts: 26 | Registered: February 25, 2005Report This Post
BabakNYC
Virtuoso
posted Hide Post
Hi Diane:
I just realized what you mean by not right justified. It looks like even in 8.201, the D12.2B leaves the extra space to the right of the decimals in order to account for possible negative numbers. When you use Brackets to show negative values, you have to have that extra space for positive numbers so all values line up properly. Otherwise, the right bracket will bump the value over and your numbers won't line up vertically. But the same isn't true with the NODATA = .00, which messes up the right justification.

So, no change in 8.201. You may want to discuss this with IB tech support.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Diane,
Maybe not exactly what you wanted but might mollify the financial folks.
Prefix your code with the following...
  
-SET &ND='.00' | HEXBYT(168,'A1');
SET NODATA='&ND'


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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     [CLOSED] XLSX NODATA FORMAT

Copyright © 1996-2020 Information Builders