Hi,

I have a data file and two dimension files. My requirement is to join the data file with the two dimension files, and then show the report such that in the BY clause I will have three fields of the 1st Dimension, and in the ACROSS I would have 1 fieldsof the 2nd dimension.

Unfortunately, when I try this, I am getting the following error message:

(FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH

Can anyone please assist?

The code is as below:

DIMENSION FILE 1:

FILENAME=LOCHIER, SUFFIX=SQLORA ,$
SEGNAME=LOCHIER, SEGTYPE=S1 ,$
FIELD=LOCATION_CD, ALIAS=LOCATION_CD, FORMAT=A6, INDEX=I, TITLE='Location, Cd', $
FIELD=LOCATION_CATEGORY, ALIAS=LOCATION_CATEGORY, FORMAT=A11, INDEX=I, TITLE='Location,Category', WITHIN='*Location Hierarchy', DESC='Location,Category', $
FIELD=LOCATION_GROUP, ALIAS=LOCATION_GROUP, FORMAT=A20, INDEX=I, TITLE='Location,Group', WITHIN='LOCATION_CATEGORY', DESC='Location,Group', $
FIELD=AREA, ALIAS=AREA_NAME, FORMAT=A32, INDEX=I, TITLE='Area', WITHIN='LOCATION_GROUP', DESC='Area',$
FIELD=REGION, ALIAS=REGION_NAME, FORMAT=A32, INDEX=I, TITLE='Region', WITHIN='AREA', DESC='Region',$
FIELD=DISTRICT, ALIAS=DISTRICT_NAME, FORMAT=A32, INDEX=I, TITLE='District', WITHIN='REGION', DESC='District',$
FIELD=EQSP, ALIAS=EQSP_NAME, FORMAT=A44, INDEX=I, TITLE='EQSP', WITHIN='DISTRICT', DESC='EQSP',$
FIELD=CITY, ALIAS=CITY_NAME, FORMAT=A39, INDEX=I, TITLE='City', WITHIN='EQSP', DESC='City', $



Dimesion File 2:

FILENAME=EQTDIM, SUFFIX=SQLORA ,$
SEGNAME=EQTDIM, SEGTYPE=S1 ,$
FIELD=EQUIPMENT_TYPE_SIZE_HT, ALIAS=EQUIPMENT_TYPE_SIZE_HT, FORMAT=A8, INDEX=I, TITLE='Eqt Type Size Ht', WITHIN='EQUIPMENT_TYPE_SIZE_SUBGROUP', DESC='Eqt Type Size Ht', $
FIELD=EQUIPMENT_TYPE_SIZE_SUBGROUP, ALIAS=EQUIPMENT_TYPE_SIZE_SUBGROUP, FORMAT=A9, INDEX=I, TITLE='Eqt Type Size Grp', WITHIN='EQUIPMENT_TYPE_SIZE_GROUP', DESC='Eqt Type Size Grp', $
FIELD=EQUIPMENT_TYPE_SIZE_GROUP, ALIAS=EQUIPMENT_TYPE_SIZE_GROUP, FORMAT=A6, INDEX=I, TITLE='Eqt Type Size SubGrp', WITHIN='EQUIPMENT_SUBCATEGORY', DESC='Eqt Type Size SubGrp', $
FIELD=EQUIPMENT_SUBCATEGORY, ALIAS=EQUIPMENT_SUBCATEGORY, FORMAT=A13, INDEX=I, TITLE='Eqt Sub-Category', WITHIN='EQUIPMENT_CATEGORY', DESC='Eqt Sub-Category', $
FIELD=EQUIPMENT_CATEGORY, ALIAS=EQUIPMENT_CATEGORY, FORMAT=A22, INDEX=I, TITLE='Eqt Category', WITHIN='*Equipment Dimension', DESC='Eqt Category', $


Data File:

FILENAME=EQTPERF, SUFFIX=SQLORA ,$
SEGNAME=EQTPERF, SEGTYPE=S3 ,$

FIELD=ACCOUNTING_YEAR_WK, ALIAS=ACCOUNTING_YEAR_WK, FORMAT=I6, INDEX=I, TITLE='Acct Year Wk', $
FIELD=EQUIPMENT_TYPE_SIZE_HT, ALIAS=EQUIPMENT_TYPE_SIZE_HT, FORMAT=A8, INDEX=I, TITLE='Eqt Type Size Ht', $
FIELD=LOCATION_CD, ALIAS=LOCATION_CD, FORMAT=A6, INDEX=I, TITLE='Location Cd', $
FIELD=VOLUME_CNT, ALIAS=VOLUME_COUNT_VALUE, FORMAT=I8, TITLE='Eqt Cnt', $



Fex Code:

JOIN
EQTPERF.EQTPERF.EQUIPMENT_TYPE_SIZE_HT IN EQTPERF TO MULTIPLE
EQTDIM.EQTDIM.EQUIPMENT_TYPE_SIZE_HT IN EQTDIM AS J0
END
JOIN
EQTPERF.EQTPERF.LOCATION_CD IN EQTPERF TO MULTIPLE LOCHIER.LOCHIER.LOCATION_CD
IN LOCHIER AS J1
END
TABLE FILE EQTPERF
SUM
VOLUME_CNT AS 'Container Count'
BY AREA
BY REGION
BY DISTRICT
BY ACCOUNTING_YEAR_WK
ACROSS EQUIPMENT_SUBCATEGORY
HEADING
"Performance Report"
FOOTING
"---- Generated on <+0>&DATE<+0> at <+0>&TOD<+0> ----"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET AUTODRILL ON
ON TABLE SET OLAPPANE HIDDEN
.
.
.
.
The rest of it is just Styling, and is irrelevant.

Any idea where I am going wrong?

Thanks,
Ved

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

 
The problem is that your joins are creating a multipath structure
like this

        *********             
        *       *             
        *********             
            *             
            *             
      *************             
      *           *             
      *           *             
  *********   **********             
  *       *   *        *             
  *********   **********      
  
What you actually need is a single path structure
Like this
  
        *********             
        *       *             
        *********             
            *             
            *  
        *********             
        *       *             
        *********             
            *             
            *                      
        *********             
        *       *             
        *********  
 

Join LOCHIER to EQTPERF to EQTDIM

Got it... I just removed the keyword 'MULTIPLE' from the JION clauses. Now it works like a charm.

I guess it indirectly does the same thing as you have mentioned here.