Hi Guys,

I am wondering if i can consolidate the following reports as 1,

1) Gender vs State
2) Gender vs Type
3) Gender vs Race

The common totalling field is Male and Female.

Anyone can enlighten me on how to consolidate the report into 1 so that user can have different view instead of going through 3 reports.

Thanks in advance.

This message has been edited. Last edited by: Kevin Tong,

What this looks like is a three dimensional report, or is it four.

Could you use a report that utilises ACROSS ?

Are these three reports large ?

How are they going to be used, could you utilise OLAP ?

Waz thanks for replying.

It should be a 3 dimensional report, have tried using OLAP, but result isn't what i wanted though. Maybe i am not too familiar with it as in dimension reports, i can't find the flexibility way to view it as dimension by dimension , e.g. during report run, default is by Gender vs State when i choose a different dimension will oni show Gender vs Type and so on...

The data would be quite large as it comes from a credit card database for a bank.

Any guidance appreciated...thx

Below is what i have tried...

-OLAP ON
-* File GenderTest.fex

JOIN
LEFT_OUTER CUST.CUST.IPKIPN IN CUST TO UNIQUE APPL.APPL.ANKIPN IN APPL TAG J0
AS J0
END

JOIN
LEFT_OUTER CUST.CUST.IPKIPN IN CUST TO UNIQUE EMP.EMP.IPKIPN IN EMP TAG J1
AS J1
END

DEFINE FILE CUST
MALE/I7 = IF IPSEX EQ 1 THEN + 1 ELSE + 0;
FEMALE/I7 = IF IPSEX EQ 2 THEN + 1 ELSE + 0;
APPTYPE/A20 = IF J0.APPL.ANATP1 EQ 'B' THEN 'BASIC' ELSE IF J0.APPL.ANATP1 EQ 'E' THEN 'EXTRA' ELSE ' ';
END

OLAP DIMENSIONS
-* DIMENSIONS FILE CUST
State: 'CUST.CUST.IPLSTE'
Race: CUST.CUST.IPRACE;
Application Type: J0.APPL.APPTYPE;
END

TABLE FILE CUST
SUM
'CUST.CUST.MALE' AS 'Male'
'CUST.CUST.FEMALE' AS 'Female'
BY 'CUST.CUST.IPLSTE' AS 'State'
BY 'CUST.CUST.IPRACE' AS 'Race'
BY 'J0.APPL.APPTYPE' AS 'Application Type'

This message has been edited. Last edited by: Kevin Tong,

So you have Sex, two values.
Application Type, three values (Assuming its the define)
State, Assumed this is the state in the country, not state of play.
And Race, ??

What if the last TABLE FILE is

TABLE FILE CUST
SUM
'CUST.CUST.MALE' AS 'Male'
'CUST.CUST.FEMALE' AS 'Female'
BY 'CUST.CUST.IPLSTE' AS 'State'
BY 'CUST.CUST.IPRACE' AS 'Race'
ACROSS 'J0.APPL.APPTYPE' AS 'Application Type'

Hi Waz,

Thanks for replying.

Yeah you are right, State as in states in a country and Race as in Chinese, Indian, Malay and etc on the ethnic group/tribe.

I have tried ACROSS but it looks abit weird and with too many Male and Female totalling columns and it looks abit scattered.

Will it be better to stick with all BY and educate users on to use the OLAP options to remove the Drill Down fields if they dun wish to be there?

Thanks.

The other thing, if you have it, would be active reports. Its similar to OLAP, not sure about high record numbers though. We don't have it here.

There are a couple of MacGyver technique examples that you might be able to use to accomplish your task.
MacGyver Examples
Look at the last two examples specifically. You'd have to create a phony sort field that would either have the state, the type, or the race in it depending on the value of the MacGyver counter. Then have counter fields for male and female. Your end report would look something like this:

DEFINE FILE ...
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/Ann=IF MAC_CNTR EQ 1 THEN RACE ELSE IF MAC_CNTR EQ 2 THEN APP_TYPE ELSE IF MAC_CNTR EQ 3 THEN STATE ELSE ' ';
MALE/I4=IF GENDER EQ 'M' THEN 1 ELSE 0; 
FEMALE/I4=IF GENDER EQ 'F' THEN 1 ELSE 0;
TABLE FILE ...
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3
END

You still have to create your MacGuyver file and add the join.

Just an idea.

TABLE FILE CUST
SUM CUSTOMER
BY RACE
ACROSS SEX
ACROSS APPTYPE
WHERE COUNTRY EQ '&COUNTRY' OR '&COUNTRY' EQ 'ALL'
WHERE STATE EQ '&STATE' OR '&STATE' EQ 'ALL'
END

Here, by giving only the basic info, you can control the report format while users dictate the scope of the view. You are not limited to one geographic location.

Hua

Thx Ginny for your suggestion, I am trying the MacGyver technique but I am stuck as it keeps saying (FOC1109) DATA IS NOT PRESENT FOR FOCUS FILE : MACGYVER

My codes might not be correct as I am playing around with it. Can you please have a look and enlighten me on how it should be...

MacGyver File DefinitionCodes

FILENAME=macgyver, SUFFIX=FOC , $
SEGMENT=SEG1, SEGTYPE=S0, $
FIELDNAME=CONTROL, ALIAS=BLANK, USAGE=A1, $
SEGMENT=SEG2, SEGTYPE=S0, PARENT=SEG1, $
FIELDNAME=FIELD, USAGE=A20, $
FIELDNAME=MAC_CNTR, ALIAS=ORDER, USAGE=I4, $


Codes

JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
JOIN BLANK WITH J1.IPKIPN IN CUST TO BLANK IN MACGYVER AS AJ

DEFINE FILE CUST
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/A20=IF MAC_CNTR EQ 1 THEN CUST.CUST.IPRACE ELSE IF MAC_CNTR EQ 2 THEN J0.APPL.ANATP1 ELSE IF MAC_CNTR EQ 3 THEN CUST.CUST.IPLSTE ELSE ' ';
MALE/I4=IF IPSEX EQ 1 THEN 1 ELSE 0;
FEMALE/I4=IF IPSEX EQ 2 THEN 1 ELSE 0;
END

TABLE FILE CUST
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3


Thank you guys for helping...

Did you create file Macgyver focus file ?

The link that Ginny gave you shows you how.

You can create your own with this.

EX -LINES 7 EDAPUT MASTER,fseq,CV,FILE
FILE=FSEQ, SUFFIX=FIX
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

-SET &CNT_MAX = 100 ;

FILEDEF FSEQ DISK fseq.mas (LRECL &CNT_MAX

TABLE FILE FSEQ 
PRINT COUNTER
      COMPUTE BLANK/A1 = ' ' ;
WHERE RECORDLIMIT EQ &CNT_MAX
ON TABLE HOLD AS FSEQ FORMAT FOCUS INDEX BLANK
END

Kevin, your code structure looks fine to me except for the non-existence of the "BLANK" field which is required for the JOIN to work (it's all part of the MacGyver technique).

Waz's code illustrates the concept very clearly:

quote:

COMPUTE BLANK/A1 = ' ' ;

If you still want to use your "macgyver" masterfile try adding the following:

BLANK/A1 = " ";

right below your DEFINE FILE CUST structure and see how it goes. It should behave similarly to the example Waz provided you with.

- Neftali.

Perhaps my "explanation" above was not very clear ... this is what you may need to do:

JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
JOIN BLANK WITH J1.IPKIPN IN CUST TO BLANK IN MACGYVER AS AJ
-*   ^^^^^ This field is required to join to the MG master

DEFINE FILE CUST
BLANK/A1 = " ";   <-- Definition of the field your JOIN needs
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/A20=IF MAC_CNTR EQ 1 THEN CUST.CUST.IPRACE ELSE IF MAC_CNTR EQ 2 THEN J0.APPL.ANATP1 ELSE IF MAC_CNTR EQ 3 THEN CUST.CUST.IPLSTE ELSE ' ';
MALE/I4=IF IPSEX EQ 1 THEN 1 ELSE 0;
FEMALE/I4=IF IPSEX EQ 2 THEN 1 ELSE 0;
END

TABLE FILE CUST
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3
END

- Neftali.

Hi Neftali,

Thanks for providing me the pointer. But i still get this message (FOC1109) DATA IS NOT PRESENT FOR FOCUS FILE : MACGYVER

MacGyver File Definition

FILENAME=macgyver, SUFFIX=FOC , $
SEGMENT=SEG1, SEGTYPE=S0, $
FIELDNAME=CONTROL, ALIAS=BLANK, USAGE=A1, $
SEGMENT=SEG2, SEGTYPE=S0, PARENT=SEG1, $
FIELDNAME=FIELD, USAGE=A20, $
FIELDNAME=MAC_CNTR, ALIAS=ORDER, USAGE=I4, $


Codes

JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
JOIN BLANK WITH J1.IPKIPN IN CUST TO BLANK IN MACGYVER AS AJ

DEFINE FILE CUST
BLANK/A1 = ' ';
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/A20=IF MAC_CNTR EQ 1 THEN CUST.CUST.IPRACE ELSE IF MAC_CNTR EQ 2 THEN J0.APPL.ANATP1 ELSE IF MAC_CNTR EQ 3 THEN CUST.CUST.IPLSTE ELSE ' ';
MALE/I4=IF IPSEX EQ 1 THEN 1 ELSE 0;
FEMALE/I4=IF IPSEX EQ 2 THEN 1 ELSE 0;
END

TABLE FILE CUST
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3

Seems like it is not able to join with the Macgyver file, is there anything i am missing? 1st time trying out with the Macgyver technique...

Kevin, the master file MACGYVER, requires a FOCUS file,MACGYVER, to be created.

Did you do this ?

If you use the code I posted, and change COUNTER to MAC_CNTR, and put this before your join, then it should work.

If not, then please post your code.

Thanks guys for the effort and help....I will try again and tag this as close.

Sorry for the late reply, but some variation of this code might work. I assume column IPSEX is in table EMP, and therefore requires the second JOIN?

SET ASNAMES = ON
-*
JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
-*
TABLEF FILE CUST
 COUNT IPKN/I9 AS 'CUST_CNT'
 BY IPLSTE
 BY IPRACE
 BY APPTYPE
 BY IPSEX
 ON TABLE HOLD AS DATAHOLD
END
-RUN
-*
DEFINE FILE DATAHOLD
 DIMENSION/A5 = 'STATE';
 DIM_SORT/I1  = 3 ;
END
-*
TABLE FILE DATAHOLD
 SUM CUST_CNT
 BY DIM_SORT
 BY DIMENSION
 BY IPLSTE AS 'CATEGORY'
 BY IPSEX
 ON TABLE HOLD AS DIMHOLD
END
-*
APP FILEDEF DIMHOLD DISK dimhold.ftm (APPEND
-*
DEFINE FILE DATAHOLD
 DIMENSION/A5 = 'RACE';
 DIM_SORT/I1  = 1 ;
END
-*
TABLE FILE DATAHOLD
 SUM CUST_CNT
 BY DIM_SORT
 BY DIMENSION
 BY IPRACE
 BY IPSEX
 ON TABLE SAVB AS DIMHOLD
END
-*
DEFINE FILE DATAHOLD
 DIMENSION/A5 = 'TYPE';
 DIM_SORT/I1  = 2 ;
END
-*
TABLE FILE DATAHOLD
 SUM CUST_CNT
 BY DIM_SORT
 BY DIMENSION
 BY APPTYPE
 BY IPSEX
 ON TABLE SAVB AS DIMHOLD
END
-RUN
-*
DEFINE FILE DIMHOLD
 GENDER/A6 = IF IPSEX EQ 1 THEN 'MALE' ELSE IF IPSEX EQ 2 THEN 'FEMALE' ELSE '???';
END
-*
TABLE FILE DIMHOLD
 SUM CUST_CNT AS ''
 BY DIM_SORT NOPRINT
 BY DIMENSION
 BY CATEGORY
 ACROSS GENDER AS ''
END

Thank you Dan for your help...