How to generate a report with all material characteristic values using LSMW

Jimbo's picture
Submitted by Jimbo on Wed, 02/26/2014 - 13:57

http://www.animalspot.net/wp-content/uploads/2012/03/Nubian-Ibex.jpg|http://album.udn.com/community/img/PSN_PHOTO/debby927/f_3764285_1.jpg|https://upload.wikimedia.org/wikipedia/commons/thumb/e/ed/Capra_ibex_ibex_%E2%80%93_03.jpg/400px-Capra_ibex_ibex_%E2%80%93_03.jpg|https://upload.wikimedia.org/wikipedia/commons/thumb/6/6a/Bouquetin_adulte_couch%C3%A9.jpg/800px-Bouquetin_adulte_couch%C3%A9.jpg|https://pixabay.com/static/uploads/photo/2015/12/11/16/12/animals-1088374_960_720.jpgBecause of the labyrinthine table relationships between materials and characteristics it may be very cumbersome to knit together this data in a meaningful report. Fortunately, like anything else, it isn't impossible.

The characteristic values are stored in the AUSP table removed from MARA by four relational tables (or five in the case of batch classifications). Those layers are as follows.

Material classifications
MARA-MATNR → KSSK-OBJEK
KSSK-CLINT → KSML-CLINT
KSML-IMERK → AUSP-ATINN
AUSP-ATWRT (or AUSP-ATFLV for floating point values)
Batch classifications
MARA-MATNR → INOB-OBJEK
INOB-CUOBJ → KSSK-OBJEK
KSSK-CLINT → KSML-CLINT
KSML-IMERK → AUSP-ATINN
AUSP-ATWRT (or AUSP-ATFLV for floating point values)

Additionally, the AUSP-OBJEK is checked against the INOB-CUOBJ field. Everything is abstracted by relational tables and what is shown above does not supply the plain-text names of the classifications or the characteristics which are stored in the KLAH and CABN tables respectively.

This entire LSMW object is available for download at the bottom of this White Paper. The individual steps are provided for the purpose of edification.

Building a selection screen to allow for some filters is a great place to start. Using parameters during the conversion step allows for filtering and saves some time that would be spent later filtering the data in Excel™. This code goes in the Global Data section of the LSMW Mapping and Conversion Rules

selection-screen begin of block bclass with frame title btclass.
  selection-screen begin of line.
    selection-screen comment 3(35) comm2.
    PARAMETERS: p_WithB as CHECKBOX default 'X'.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 3(35) comm3.
    PARAMETERS: p_WithM as CHECKBOX default 'X'.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 3(35) comm8.
    PARAMETERS: p_MaDesc as CHECKBOX default 'X'.
    selection-screen comment 43(18) comm9.
    PARAMETERS: p_SPRAS like MAKT-SPRAS default 'E'.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 3(35) comm4.
    PARAMETERS: p_Char as CHECKBOX default 'X'.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 3(13) comm6.
    PARAMETERS: p_MTART like MARA-MTART.
    selection-screen comment 45(30) comm7.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 3(13) commc.
    PARAMETERS: p_ClassF like KLAH-CLASS.
    selection-screen comment 45(30) commlb.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 3(13) commch.
    PARAMETERS: p_CharF like CABN-ATNAM.
    selection-screen comment 50(30) commlbch.
  selection-screen end of line.
selection-screen end of block bclass.
selection-screen begin of block bfile with frame title bfile1.
  selection-screen begin of line.
    selection-screen comment 1(10) commf.
    PARAMETERS: p_filenm type string.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 1(60) commf2.
  selection-screen end of line.
selection-screen end of block bfile.
selection-screen begin of block btest with frame title btest1.
  selection-screen begin of line.
    PARAMETERS: p_limit as CHECKBOX default ' '.
    selection-screen comment 3(60) comm5.
  selection-screen end of line.
selection-screen end of block btest.
initialization.
btclass = 'Classification report'.
comm2 = 'Include Batch Classifications'.
comm3 = 'Include Material Classifications'.
comm4 = 'Include Characteristic values'.
bfile1 = 'Save to file'.
commf = 'Filename*:'.
commf2 = '* Leave the filename blank to see results as a report.
btest1 = 'Testing parameters'.
comm5 = 'Limit nIndex to 2000.'.
comm6 = 'Material type:'.
comm7 = '(leave blank for all types)'.
commc = 'Class name:'.
commlb = '(leave blank for all classes)'.
commch = 'Characteristic:'.
commlbch = '(leave blank for all characteristics)'.
comm8 = 'Include Material Description'.
comm9 = 'Preferred language'.
data: lMARA like MARA, lCABN like CABN, lCAWN like CAWN,
      lKSSK like KSSK, lKLAH like KLAH, lINOB like INOB,
      lKSML like KSML, lAUSP like AUSP, lT134T like T134T,
      lMAKT like MAKT.

data: nTotalRecords type i, nIndex type i.

data: fFloat type P decimals 4.

data: it_KSML type standard table of KSML initial size 0,
      it_CABN type standard table of CABN initial size 0,
      it_KSSK type standard table of KSSK initial size 0,
      it_KLAH type standard table of KLAH initial size 0.

types: begin of ClassReport,
        MATNR(18) type c,
        MAKTX like MAKT-MAKTX,
        MTART like MARA-MTART,
        XCHPF(5) type c,
        CLASS like KLAH-CLASS,
        KLART like KLAH-KLART,
        ATNAM like CABN-ATNAM,
        ATWRT like AUSP-ATWRT,
      end of ClassReport.
data: wa_CR type ClassReport, it_CR type standard table of ClassReport
      initial size 0.

The next step is to pre-filter some internal tables that will be used for filtering later. By populating these internal tables with desired subsets of the total data, the run time will be reduced significantly.

if p_CharF ne ''. "Time to filter by characteristic name
  translate p_CharF to UPPER CASE.
  select * from CABN into lCABN where ATNAM eq p_CharF.
    append lCABN to it_CABN.
  endselect.
  if sy-subrc ne 0. "Didn't find the char...
    write: 'Invalid Char:', p_CharF color col_negative. exit.
  endif.
else.
  select * from CABN into table it_CABN.
endif.
if p_ClassF ne ''. "Time to filter by class name....
  translate p_ClassF to UPPER CASE.
  select single * from KLAH into lKLAH where CLASS eq p_ClassF.
  if sy-subrc ne 0.
    write: 'Invalid Class name:', p_ClassF color col_negative. exit.
  else.
    append lKLAH to it_KLAH.  "Now there is only one class.
  endif.
else.  "No need to filter...
  select * from KLAH into table it_KLAH.
endif.

Now a header is added to the wa_CR internal table. This makes it easier to read when the report is generated. 

  move: 'MATNR' to wa_CR-MATNR, 'MAKTX' to wa_CR-MAKTX,
        'MTART' to wa_CR-MTART,
        'XCHPF' to wa_CR-XCHPF, 'CLASS' to wa_CR-CLASS,
        'ATNAM' to wa_CR-ATNAM, 'ATWRT' to wa_CR-ATWRT.
  append wa_CR to it_CR.

Now the report is fleshed out by using a series of nested loops. The loops are tighter if the internal tables have been filtered.

  nIndex = 0.
  select count(*) from MARA into nTotalRecords.
  select * from MARA into lMARA order by MATNR.
    add 1 to nIndex.
   if lMARA-MTART eq p_MTART or p_MTART eq ''.
    wa_CR-MATNR = lMARA-MATNR.
    wa_CR-MTART = lMARA-MTART. wa_CR-XCHPF = lMARA-XCHPF.
    if p_MaDesc eq 'X'.
      " Lookups for description--1st for *any* desc, 2nd for preferred
      select single MAKTX from MAKT into wa_CR-MAKTX
       where MATNR eq lMARA-MATNR.
      select single MAKTX from MAKT into wa_CR-MAKTX
       where MATNR eq lMARA-MATNR and SPRAS eq p_SPRAS.
    endif.
    if p_limit eq 'X' and nIndex gt 2000. exit. endif.
    perform ZPROGRESSTIME using nIndex nTotalRecords.
    if p_withb eq 'X'.
      select * from INOB into lINOB
       where objek eq lMARA-MATNR and KLART eq '023'.  "Batch Classes
        select * from KSSK into lKSSK where OBJEK eq lINOB-CUOBJ.
          loop at it_KLAH into lKLAH where CLINT eq lKSSK-CLINT.
            wa_CR-CLASS = lKLAH-CLASS. wa_CR-KLART = lKSSK-KLART.
            if p_Char ne 'X'. "No characteristics
              append wa_CR to it_CR.
            else. "Go crazy with the characterisitics!  Yeah, boyeee!
              select * from KSML into lKSML
               where CLINT eq lKSSK-CLINT order by POSNR.
                loop at it_CABN into lCABN where ATINN eq lKSML-IMERK.
                  wa_CR-ATNAM = lCABN-ATNAM.
                  select * from AUSP into lAUSP
                   where OBJEK eq lINOB-CUOBJ and ATINN eq lCABN-ATINN.
                    if lAUSP-ATWRT eq ''.
                      move lAUSP-ATFLV to fFloat.
                      wa_CR-ATWRT = fFloat.
                    else.
                      wa_CR-ATWRT = lAUSP-ATWRT.
                    endif.
                    append wa_CR to it_CR.
                  endselect.
                endloop.
              endselect.
            endif.
          endloop.
        endselect.
      endselect.
    endif.
    if p_withM eq 'X'.
        select * from KSSK into lKSSK
         where OBJEK eq lMARA-MATNR.
          loop at it_KLAH into lKLAH where CLINT eq lKSSK-CLINT.
            wa_CR-CLASS = lKLAH-CLASS. wa_CR-KLART = lKSSK-KLART.
            if p_Char ne 'X'. "No characteristics
              append wa_CR to it_CR.
            else. "Go crazy with the characterisitics!  Yeah, boyeee!
              select * from KSML into lKSML
               where CLINT eq lKSSK-CLINT order by POSNR.
                loop at it_CABN into lCABN where ATINN eq lKSML-IMERK.
                  wa_CR-ATNAM = lCABN-ATNAM.
                  select * from AUSP into lAUSP
                   where OBJEK eq lMARA-MATNR and ATINN eq lCABN-ATINN.
                    if lAUSP-ATWRT eq ''.
                      move lAUSP-ATFLV to fFloat.
                      wa_CR-ATWRT = fFloat.
                    else.
                       wa_CR-ATWRT = lAUSP-ATWRT.
                    endif.
                    append wa_CR to it_CR.
                  endselect.
                endloop.
              endselect.
            endif.
          endloop.
        endselect.
    endif.
   endif. "MTART eq p_MTART or p_MTART eq ''.
  endselect.

Finally, the report is exported or written to the screen in the END_OF_PROCESSING portion. If the filename is populated then the data will automatically be exported to a text-delimited file.

* Now start with the actual reporting...
if p_filenm ne ''.
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      FILENAME                        = p_filenm
      FILETYPE                        = 'ASC'
      APPEND                          = ''
*      CODEPAGE                        = 'IBM'
      WRITE_FIELD_SEPARATOR = 'X'
      HEADER = '00'
      TRUNC_TRAILING_BLANKS = 'X'
    TABLES
      DATA_TAB                        = it_CR
    EXCEPTIONS
      FILE_WRITE_ERROR                = 1
      OTHERS                          = 2.
else.
  loop at it_CR into wa_CR.
    write: / wa_CR-MATNR.
    if p_MaDesc eq 'X'. write: wa_CR-MAKTX. endif.
    write: wa_CR-MTART, wa_CR-XCHPF, wa_CR-CLASS, wa_CR-KLART,
     wa_CR-ATNAM, wa_CR-ATWRT.
  endloop.
endif.

Note: This code calls the ZPROGRESSTIME function. This is included in the User Defined Routines under DC_TOOLS. Also included is LSMW object that generates a report of Material Master Long Texts.

LSMW_Reports.txt


Using SQL to Achieve the Same Results

Many large organizations have SAP's Landscape Transformation Replication Server (SLT) running and if there is a SQL server receiving these tables then it's an easy enough task to tease these data into a cogent extract. The code below relies on the tables with descriptions of the Characteristics (CABNT) and Answers (CAWNT).

select inob.OBJEK,klah.CLASS,ausp.KLART,cabn.ATNAM,cabnt.ATBEZ,ausp.ATINN,ausp.ATWRT,ausp.ATFLV,
case
 when cawnt.atwtb is null then ''
 else cawnt.atwtb
end as ATWTB,
maktx
 from dgSAP_P01_100.dbo.ausp join dgSAP_P01_100.dbo.INOB on (ausp.objek = inob.cuobj and ausp.klart=inob.klart)
 join dgSAP_P01_100.dbo.makt on (inob.objek=makt.matnr and makt.spras='E')
 join dgSAP_P01_100.dbo.cabnt on (ausp.atinn=cabnt.atinn and cabnt.spras='E')
 join dgSAP_P01_100.dbo.cabn on (ausp.atinn=cabn.atinn)
 join dgSAP_P01_100.dbo.KSML on (ausp.atinn = ksml.imerk)
 join dgSAP_P01_100.dbo.KSSK on (ausp.objek=kssk.objek and ausp.klart=kssk.klart and ksml.clint=kssk.clint) --  and ausp.adzhl=kssk.adzhl)
 join dgSAP_P01_100.dbo.KLAH on (kssk.clint = klah.clint and kssk.klart = klah.klart)
 left outer join dgSAP_P01_100.dbo.cawn on (cawn.atwrt = ausp.atwrt and cawn.atinn = ausp.atinn) 
 left outer join dgSAP_P01_100.dbo.cawnt on (cawnt.atzhl = cawn.atzhl and cawnt.atinn = ausp.atinn and cawnt.spras = cabnt.spras)
 where 
-- Put your filters here.  It can be a list of Material Masters, a Class type or a nearly anything.
-- To pull the Classification and Characteristic data for other objects, be sure to remove the join to MAKT.
  inob.klart = 'XYZ'
order by inob.objek,class,atnam