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,

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

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

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

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.

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
 

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

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.

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'