We have an ACROSS on a description field from a LEFT OUTER join, that results in an extra column labeled '' for the cases where the right-hand side of the join has no matching records.
This report prints to an XLSX output with a BYTOC on the first BY field.

I would like to get rid of the "empty" across-column, but not any other ones that do have a description (anywhere at all).

Normally, I would simply hide the offending across-column using HIDENULLACRS = ON, but in this case that removes all the rows and sheets from the workbook where the across value is missing. I really need to keep those.

It appears that HIDENULLACRS behaves differently in combination with BYTOC (or does it?). How do I make it behave?

Or is this a bug?

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

Wep,

Have you first just try to extract the data that you need and hold it (no ACROSS just pure extraction with BY & SUM/PRINT) then as second step format it as you wish with the ACROSS and BYTOC ?

When I have issue with "formatting" I extract and then format. Sometime doing the stuff in more than one step helps to accomplish the desired result.

Isn't that just a null value for your "Across" value? Can't you just filter that out? You might need to hold it first, and then filter on the second pass like Martin is talking about. But just seems like you need to add:

WHERE [ACROSS_FIELD] IS NOT MISSING

Well, there's the problem - I can't filter out null results because those are valid rows. I am required to include those.

I just don't want those rows to add an ACROSS column with a NULL acrossvalue, because that is rather useless.

Normally, HIDENULLACRS would take care of that, but in this case - perhaps because of the BYTOC - it also removes the valid rows with a NULL across value from the report.

Something isn't making sense here Wep... you say you can't filter out Nulls, but you don't want to show the null value.... I'm confused... Can you post a mockup of your output? what does it look like?

I think I understand where the confusion comes from; in across, we're dealing with the across verb values and the acrossvalue that's printed above the column.

The empty acrossvalue is the problem here, not the (also empty) verb values.

For example:

-*SET HIDENULLACRS = ON

TABLE FILE CAR
PRINT CAR SEATS
BY COUNTRY
ON TABLE HOLD AS CAR1 FORMAT FOCUS INDEX CAR
END

TABLE FILE CAR
PRINT MODEL
COMPUTE BODYTYPE2/A20 MISSING ON SOME = BODYTYPE;
BY CAR
WHERE COUNTRY NE 'W GERMANY';
ON TABLE HOLD AS CAR2 FORMAT FOCUS INDEX CAR
END

JOIN LEFT_OUTER
	CAR IN CAR1 TAG C1 TO
	CAR IN CAR2 TAG C2 AS J0
END
TABLE FILE CAR1
SUM SEATS

BY COUNTRY
BY CAR

SUM BODYTYPE2

BY COUNTRY
BY CAR

ACROSS MODEL

ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET HTMLENCODE ON
ON TABLE SET COMPOUND BYTOC
END

This output contains rows for W GERMANY, that lists the cars and their seats, but we don't have any models for W GERMAN cars in these tables, so the across column is empty. However, this also adds an empty across column with no useful acrossvalue for the column's title - and I do not want that. Neither do I want to lose the relevant data about W GERMAN cars though.

With SET HIDENULLACRS = ON, for this case I get the correct results. However, in my XLSX output with BYTOC to put each COUNTRY in a separate sheet in the workbook, I lose the data about W GERMANY.

With SET HIDENULLACRS = OFF, I do get the data about W GERMANY, but I also get an empty across column.

Ahh, Now I understand your problem.

When in doubt.. McGyver... what about this...

-*SET HIDENULLACRS = ON

DEFINE FILE CAR
BLANK/A1='';
INDX1/A26=COUNTRY || CAR ;
END

TABLE FILE CAR
SUM SEATS
BY BLANK
BY INDX1
BY COUNTRY
BY CAR
ON TABLE HOLD AS CAR1 FORMAT ALPHA
END

TABLE FILE CAR
SUM CAR
BY BLANK
BY MODEL
WHERE COUNTRY NE 'W GERMANY' 
ON TABLE HOLD AS CAR3 FORMAT FOCUS INDEX BLANK
END

JOIN BLANK IN CAR1 TO ALL BLANK IN CAR3

DEFINE FILE CAR1
INDX1/A26=COUNTRY || CAR ;
INDX2/A100=CAR || MODEL;
END

TABLE FILE CAR1
SUM BLANK
BY INDX1
BY INDX2
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CAR4 FORMAT FOCUS INDEX INDX1 INDX2
END

DEFINE FILE CAR
INDX2/A100=CAR || MODEL;
END

TABLE FILE CAR
SUM
COMPUTE BODYTYPE2/A20 MISSING ON SOME = BODYTYPE;
BY INDX2
BY CAR
BY MODEL
BY BODYTYPE
WHERE COUNTRY NE 'W GERMANY';
ON TABLE HOLD AS CAR2 FORMAT FOCUS INDEX INDX2
END

JOIN LEFT_OUTER INDX2 IN CAR4 TAG C4 TO ALL INDX2 IN CAR2 TAG C2 AS J0
JOIN LEFT_OUTER INDX1 IN CAR4 TO INDX1 IN CAR1 TAG C1 AS J1

TABLE FILE CAR4
SUM BODYTYPE2
BY COUNTRY 
BY CAR
BY SEATS
ACROSS MODEL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET HTMLENCODE ON
ON TABLE SET COMPOUND BYTOC
END  

BTW, your multi-verb approach is much cleaner and simpler... but this, while irritating, should get around your problem.

Ookaay... That's a bit hard to follow, but I think I get what you're doing.

Basically, you make sure that every COUNTRY / CAR combination gets a record for every available MODEL in the table. That's where you apply McGyver, to create a Carthesian product. NB: Dave recently posted a neat alternative approach using an SQL join on FOCUS tables.

We leave out MODELs from W GERMANY there because I excluded that country in my example and therefore MODELs from that country won't have matches (which matches the results of my original example).

I don't actually need to do that in my real situation, as the "MODELs" there are a fixed set of values independent of my measures.
That actually simplifies the problem, as now we can forego the join to CAR1; All that information is already available in CAR4 if we don't exclude 'W GERMANY' and if we add SEATS back in that TABLE.

Finally, we join the Carthesian product to the filtered table to combine our information.


Yeah, that should work. Thanks a lot.
I'd better add comments rigorously, or nobody (including me) will be able to follow what's going on there in a couple of months :P


The funny part is that it's quite possible that I don't need to use this approach. Just yesterday I had a meeting with the problem owner and we found out that with the currently proposed organisation of this data they are creating a lot of duplicate data, resulting in a maintenance nightmare and an unusable report as soon as any of those duplicates mismatch. They're basically storing this information in the wrong place and are now looking into better alternatives.

For the archives, the simplified version with added comments:

-* Enrich original data with some multi-key fields and a McGyver field,
-* because FOCUS format doesn't do multi-key JOINs
DEFINE FILE CAR
	BLANK/A1	= '';
	COUNTRYCAR/A26	= COUNTRY || CAR ;
	CARMODEL/A100	= CAR || MODEL;
END

TABLE FILE CAR
SUM SEATS
BY BLANK
BY COUNTRY
BY CAR
BY COUNTRYCAR
ON TABLE HOLD AS CAR1 FORMAT ALPHA
END

-* Prepare a list of MODELs for McGyver
TABLE FILE CAR
SUM BLANK
BY MODEL
ON TABLE HOLD AS CAR3 FORMAT FOCUS INDEX BLANK
END

-* Create a Carthesian product
JOIN BLANK IN CAR1 TO ALL BLANK IN CAR3

DEFINE FILE CAR1
	CARMODEL/A100	= CAR || MODEL;
END

TABLE FILE CAR1
SUM BLANK SEATS
BY COUNTRYCAR
BY CARMODEL
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CAR4 FORMAT FOCUS INDEX COUNTRYCAR CARMODEL
END

-* Create our contra-table (see original example), without W GERMAN cars
TABLE FILE CAR
SUM
	COMPUTE BODYTYPE2/A20 MISSING ON SOME = BODYTYPE;
BY CARMODEL
BY CAR
BY MODEL
BY BODYTYPE
WHERE COUNTRY NE 'W GERMANY';
ON TABLE HOLD AS CAR2 FORMAT FOCUS INDEX CARMODEL
END

-* Combine the Carthesian product and our contra-table into desired output
JOIN LEFT_OUTER CARMODEL IN CAR4 TAG C4 TO ALL CARMODEL IN CAR2 TAG C2 AS J0

TABLE FILE CAR4
SUM BODYTYPE2
BY COUNTRY 
BY CAR
BY SEATS
ACROSS MODEL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET HTMLENCODE ON
ON TABLE SET COMPOUND BYTOC
END