Limit Display of Across Columns but include all records

I have a report that list Customers and places an 'X' in an Across Column under each Order Type this customer uses.

To show an example, using the CAR file, this report will show each COUNTRY and what BODYTYPE it makes:

DEFINE FILE CAR
BODY_X/A1 = IF CAR.BODY.BODYTYPE EQ CAR.BODY.BODYTYPE THEN 'X' ELSE '';
END

TABLE FILE CAR
SUM BODY_X
BY CAR.ORIGIN.COUNTRY
ACROSS CAR.BODY.BODYTYPE
ON TABLE SET HTMLCSS ON
ON TABLE NOTOTAL
END

What I need, still using the CAR file as an example, is to only include some of the Across Values (ex. ACROSS CAR.BODY.BODYTYPE COLUMNS COUPE AND HARDTOP AND ROADSTER).

When I do this, it only shows the Countries that make those body types. Even though FRANCE, W GERMANY and JAPAN do not make one of those BODYTYPES, I would still like to display a country record, but an 'X' would not appear under any of the columns.

Any help would be appreciated!

Thanks,
Tim J

Posts: 57 | Registered: February 24, 2004

GinnyJakes

Expert

posted

Hide Post

This is really kludgy but ...

Run your fex and hold the output in a Focus file, index COUNTRY. Run another query just to get the countries. Join together with SET ALL=ON.

I'm sure someone else has a more clever idea.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Show Post

Alan B

Virtuoso

posted

Hide Post

Another (kludgy) take on this is:

APP HOLD myApp
SET ASNAMES=ON
DEFINE FILE CAR
BODY_X/A20 = IF CAR.BODY.BODYTYPE EQ CAR.BODY.BODYTYPE THEN CAR.BODY.BODYTYPE ELSE '';
END

TABLE FILE CAR
SUM BODY_X
BY CAR.ORIGIN.COUNTRY
ACROSS CAR.BODY.BODYTYPE
ON TABLE HOLD
END

TABLE FILE HOLD
SUM 
COMPUTE COUPE/A1 = IF BODCOUPE NE ' ' THEN 'X' ELSE ' ';
COMPUTE HARDTOP/A1 = IF BODHARDTOP NE ' ' THEN 'X' ELSE ' ';
COMPUTE ROADSTER/A1 = IF BODROADSTER NE ' ' THEN 'X' ELSE ' ';
BY COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE NOTOTAL
END

Alan.
WF 7.705/8.007

Posts: 1451 | Location: Portugal | Registered: February 07, 2007

Ignored post by Alan B posted

Show Post

GamP

Virtuoso

posted

Hide Post

And yet another, maybe even more clumsy, approach might be touse the BY ROWS OVER tactic.
Example with the car database:

DEFINE FILE CAR
BODY_X/A1 = IF CAR.BODY.BODYTYPE EQ CAR.BODY.BODYTYPE THEN 'X' ELSE '';
END

TABLE FILE CAR
SUM BODY_X
BY CAR.ORIGIN.COUNTRY ROWS 'ENGLAND' OVER 'FRANCE' OVER 'ITALY' OVER 'JAPAN' OVER 'W GERMANY'
ACROSS CAR.BODY.BODYTYPE COLUMNS COUPE AND HARDTOP AND ROADSTER
ON TABLE SET HTMLCSS ON
ON TABLE NOTOTAL
END

Hope this helps...

GamP