Adapter for Excel: Creating a Synonym for Crosstabbed Data

Synonyms can now be automatically created in a crosstab format for data in Excel Worksheets.

When you create a synonym using the Adapter for Excel, there is now a check box labeled Crosstab for each Worksheet, as shown in the following image.

When selected, the header rows, up to the number specified, are un-pivoted to become data values.

The following shows a partial Excel Spreadsheet open in the Data Management Console. The headers include Product Category, a value for Product Category, Product Subcategory, and a value for Product Subcategory.

With Crosstab not checked, the resulting Master File has a field for column on the Spreadsheet. The following is a partial list of fields for the Spreadsheet:

FIELDNAME=PRODUCT_CATEGORY_ACCESSORIES_PRODUCT_SUBCATEGORY, ALIAS='Product,Category_Accessories_Product,Subcategory', USAGE=A8V, ACTUAL=A8V,
      MISSING=ON,
      TITLE='Product,Category_Accessories_Product,Subcategory', $
    FIELDNAME=FIELD_3, ALIAS=FIELD_3, USAGE=A12V, ACTUAL=A12V,
      MISSING=ON,
      TITLE='FIELD_3', $
    FIELDNAME=FIELD_4, ALIAS=FIELD_4, USAGE=A31V, ACTUAL=A31V,
      MISSING=ON,
      TITLE='FIELD_4', $
    FIELDNAME=CAMCORDER, ALIAS=Camcorder, USAGE=A10V, ACTUAL=A10V,
      MISSING=ON,
      TITLE='Camcorder', $
    FIELDNAME=FIELD_6, ALIAS=FIELD_6, USAGE=A15V, ACTUAL=A15V,
      MISSING=ON,
      TITLE='FIELD_6', $
    FIELDNAME=FIELD_7, ALIAS=FIELD_7, USAGE=A10V, ACTUAL=A10V,
      MISSING=ON,
      TITLE='FIELD_7', $
    FIELDNAME=COMPUTERS, ALIAS=Computers, USAGE=A12V, ACTUAL=A12V,
      MISSING=ON,
      TITLE='Computers', $
    FIELDNAME=FIELD_9, ALIAS=FIELD_9, USAGE=A7V, ACTUAL=A7V,
      MISSING=ON,
      TITLE='FIELD_9', $
    FIELDNAME=MEDIA_PLAYER, ALIAS='Media Player', USAGE=A8V, ACTUAL=A8V,
      MISSING=ON,
      TITLE='Media Player', $

With Crosstab checked, multiple measures unchecked, and five header rows specified, PRODUCT_CATEGORY becomes one dimension field, PRODUCT_SUBCATEGORY becomes a second dimension field, and the numeric values in the Spreadsheet become a measure field. The first column (containing store names) becomes an alphanumeric field. For example:

The resulting Master File follows:

FILENAME=RETAIL_CROSS_CROSS_ON, SUFFIX=DIREXCEL,
 DATASET=ibisamp/retail_cross.xlsx, BV_NAMESPACE=OFF, $
  SEGMENT=RETAIL_CROSS_CROSS_ON, SEGTYPE=S0, $
    FIELDNAME=DHL1, ALIAS=DHL1, USAGE=A20V, ACTUAL=A20V,
      MISSING=ON,
      TITLE='DHL1', $
    FIELDNAME=DVL1_PRODUCT_CATEGORY, ALIAS='Product,Category',
      USAGE=A20V, ACTUAL=A20V,
      MISSING=ON,
      TITLE='Product,Category', $
    FIELDNAME=DVL2_PRODUCT_SUBCATEGORY, ALIAS='Product,Subcategory',
      USAGE=A31V, ACTUAL=A31V,
      MISSING=ON,
      TITLE='Product,Subcategory', $
    FIELDNAME=REPORT_RETAIL_WF_RETAIL_LITE_RPT_FEX,
      ALIAS='Report retail/wf_retail_lite_rpt.fex',
      USAGE=I9, ACTUAL=A11V,
      MISSING=ON,
      TITLE='Report retail/wf_retail_lite_rpt.fex', $
  FOLDER=MG_RETAIL_CROSS_CROSS_ON, PARENT=.,
    DV_ROLE=MEASURE, 
    DESCRIPTION='Measure Groups', $
  FOLDER=RETAIL_CROSS_CROSS_ON, PARENT=MG_RETAIL_CROSS_CROSS_ON,
    DESCRIPTION='Retail_cross_cross_on', $
    FIELDNAME=REPORT_RETAIL_WF_RETAIL_LITE_RPT_FEX,
      BELONGS_TO_SEGMENT=RETAIL_CROSS_CROSS_ON, $
  FOLDER=DIM_RETAIL_CROSS_CROSS_ON, PARENT=.,
    DV_ROLE=DIMENSION, 
    DESCRIPTION='Dimensions', $
  FOLDER=RETAIL_CROSS_CROSS_ON1, PARENT=DIM_RETAIL_CROSS_CROSS_ON,
    DESCRIPTION='Retail_cross_cross_on', $
    FIELDNAME=DHL1, BELONGS_TO_SEGMENT=RETAIL_CROSS_CROSS_ON, $
  FOLDER=DVL, PARENT=RETAIL_CROSS_CROSS_ON1,
    DESCRIPTION='DVL', $
    FIELDNAME=DVL1_PRODUCT_CATEGORY,
      BELONGS_TO_SEGMENT=RETAIL_CROSS_CROSS_ON,
      DV_ROLE=LEVEL,  $
    FIELDNAME=DVL2_PRODUCT_SUBCATEGORY,
      BELONGS_TO_SEGMENT=RETAIL_CROSS_CROSS_ON,
      DV_ROLE=LEVEL,  $

You can now issue a request similar to the following:

TABLE FILE RETAIL_CROSS_CROSS_ON
PRINT REPORT_RETAIL_WF_RETAIL_LITE_RPT_FEX AS Measure
BY DHL1 AS Store
BY DVL1_PRODUCT_CATEGORY AS Category
BY DVL2_PRODUCT_SUBCATEGORY AS Subcategory
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

Partial output is shown in the following image.