Most drawn lottery numbers ABAP program for SAP system

This abap report can be used to upload all previous Uk lottery results including draw number, date draw, ball set used, machine used, number of winners, jackpot into a SAP database. The structure of this file is based on the lottery data available from Lottery numbers and has the following structure:

Draw number, Day, DD, MMM, YYYY, Num1, Num2, Num3, Num4, Num5, Num6, Bonus, Jackpot, Wins, Machine, Ball set


Here is lottery numbers file containing all numbers upto draw 1538 made on the 23/01/2013

Once the data has successfully been uploaded the report can then be used to report on this data and display most drawn numbers, numbers drawn together, how often a set of 6 numbers would have won a prize and the different prizes it would have won.

See here for further information about this report including example screen shots

Creating this report

Step 1 - Creating a Ztable
In-order to create this abap report in your SAP system you first need to create a Ztable to store all the data. This needs to be called 'ZLOTRESULTS' otherwise the below abap code will not work and needs to be created with the following field names and data types.

Field name	Data element	data type	Length	Short desc
MANDT		MANDT		CLNT		3	Client
DRAWNO		ZDRAWNO		NUMC		6	Draw number
DRAWDATE	DATUM		DATS		8	Date
DRAWDAY		ZDRAWDAY	CHAR		3	Draw day
MACHINE		ZMACHINE	CHAR		30	Machine
BALLSET		ZBALLSET	CHAR		2	ball set
NUM1		NUM2		NUMC		2	2-Digit Numeric Value
NUM2		NUM2		NUMC		2	2-Digit Numeric Value
NUM3		NUM2		NUMC		2	2-Digit Numeric Value
NUM4		NUM2		NUMC		2	2-Digit Numeric Value
NUM5		NUM2		NUMC		2	2-Digit Numeric Value
NUM6		NUM2		NUMC		2	2-Digit Numeric Value
BNUM		NUM2		NUMC		2	2-Digit Numeric Value
JACKPOT		ZJACKPOT	NUMC		10	Jackpot
WINS		ZWINS		NUMC		3	No. Wins

Step 2 - Creating the ABAP report
Next you need to create a new z report ZLOTTO(can be any name you like) using the following code:

*...............................................................
*: Report:  ZLOTTO                                        :
*:                                                             :
*: Authur: SAP Development                                        :
*:                                                             :
*: Description: Displays report of lottery number statistics   :
*:              (i.e. most drawn numbers, numbers drawn        :
*:               together etc etc)                             :
*:                                                             :
*:.............................................................:

REPORT  ZLOTTO.
TABLES: zlotresults.
type-pools: slis.                                 "ALV Declarations
SELECTION-SCREEN begin of block block1 with frame title text-001.
PARAMETERS: p_infile  LIKE rlgrap-filename DEFAULT  'C:/',
            "upload data (overwrites existing data)
            p_upload TYPE c radiobutton group rad1,
            "numbers drawn the most
            p_dmost type c radiobutton group rad1,
            "check below nummbers against all draws
            p_tnums  type c RADIOBUTTON GROUP rad1.
*checks best results for the following 6 numbers
*        i.e. date, matched numbers, machine, ballset
PARAMETERS: p_num1 type zlotresults-num1.
PARAMETERS: p_num2 type zlotresults-num1.
PARAMETERS: p_num3 type zlotresults-num1.
PARAMETERS: p_num4 type zlotresults-num1.
PARAMETERS: p_num5 type zlotresults-num1.
PARAMETERS: p_num6 type zlotresults-num1.
SELECTION-SCREEN end of block block1.
SELECTION-SCREEN begin of block block2 with frame title text-002.
*only draws on this date
SELECT-OPTIONS: so_date for zlotresults-drawdate.
*only draws which use these machines
SELECT-OPTIONS: so_mach for zlotresults-machine.
*only draws using these ball sets
SELECT-OPTIONS: so_bset for zlotresults-ballset.
*only draws where these balls where drawn
SELECT-OPTIONS: so_BLINK for zlotresults-NUM1.
*day of draw (SAT or WED)
SELECT-OPTIONS: so_day for zlotresults-drawday.
SELECTION-SCREEN end of block block2.
*DATA: ld_file LIKE rlgrap-filename.
DATA: gd_file type string.
*Internal tabe to store upload data
TYPES: BEGIN OF t_record,
    DRAWNO type string,
    DRAWDAY type string,
    DD type string,
    MMM type string,
    YYYY type string,
    NUM1 type string,
    NUM2 type string,
    NUM3 type string,
    NUM4 type string,
    NUM5 type string,
    NUM6 type string,
    BNUM type string,
    JACKPOT type string,
    WINS type string,
    MACHINE type string,
    BALLSET type string,
    END OF t_record.
DATA: it_record TYPE STANDARD TABLE OF t_record INITIAL SIZE 0,
      wa_record TYPE t_record.
data: it_results type STANDARD TABLE OF zlotresults,
      wa_results type zlotresults.
TYPES: begin of t_datatab,
  row type string,
 end of t_datatab.
data: it_datatab type STANDARD TABLE OF t_datatab,
      wa_datatab like line of it_datatab.
TYPES: begin of t_dmost,
  bnum type i,
  timesdrawn type i,
 end of t_dmost.
data: it_dmost type STANDARD TABLE OF t_dmost,
      wa_dmost like LINE OF it_dmost.
TYPES: begin of t_tnums,
  drawdate type datum,
  machine type string,
  ballset type string,
  numcount type i,
 end of t_tnums.
data: it_tnums type STANDARD TABLE OF t_tnums,
      wa_tnums like LINE OF it_tnums.
*ALV data declarations
data: fieldcatalog type slis_t_fieldcat_alv with header line,
      gd_tab_group type slis_t_sp_group_alv,
      gd_layout    type slis_layout_alv,
      gd_repid     like sy-repid.
constants:
    con_tab  type c value cl_abap_char_utilities=>HORIZONTAL_TAB.
************************************************************************
*AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFILE.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_infile.
  CALL FUNCTION 'WS_FILENAME_GET'
       EXPORTING
            def_filename     = p_infile
            mask             = ',*.txt.'
            mode             = 'O'
            title            = 'Upload File'(078)
       IMPORTING
            filename         = p_infile
       EXCEPTIONS
            inv_winsys       = 1
            no_batch         = 2
            selection_cancel = 3
            selection_error  = 4
            OTHERS           = 5.
************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.
  select *
    from zlotresults
    into CORRESPONDING FIELDS OF TABLE it_results
   where machine in so_mach and
         ballset in so_bset AND
         DRAWDAY in so_day and
         drawdate in so_date and
         ( NUM1 IN so_BLINK OR
           NUM2 IN so_BLINK OR
           NUM3 IN so_BLINK OR
           NUM4 IN so_BLINK OR
           NUM5 IN so_BLINK OR
           NUM6 IN so_BLINK OR
           BNUM IN so_BLINK ).
case 'X'.
  when p_upload.
    perform upload_data.
    perform add_data_to_database.
  when p_dmost.
    perform balls_drawn_most.
    perform build_fieldcatalog.
    perform build_layout.
    perform display_alv_report.
  when p_tnums.
    perform test_number_combo.
    perform build_fieldcatalog_tnums.
    perform build_layout.
    perform display_alv_tnums.
endcase.
************************************************************************
*END-OF-SELECTION
END-OF-SELECTION.
*&---------------------------------------------------------------------*
*&      Form  UPLOAD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM UPLOAD_DATA .
  gd_file = p_infile.
  CALL FUNCTION 'GUI_UPLOAD'
       EXPORTING
            filename        = gd_file
            filetype        = 'ASC'
       TABLES
            data_tab        = it_datatab
       EXCEPTIONS
            file_open_error = 1
            OTHERS          = 2.
  IF sy-subrc EQ 0.
    LOOP AT it_datatab into wa_datatab.
      SPLIT wa_datatab-row AT ',' INTO wa_record-DRAWNO
                                   wa_record-DRAWDAY
                                   wa_record-DD
                                   wa_record-MMM
                                   wa_record-YYYY
                                   wa_record-NUM1
                                   wa_record-NUM2
                                   wa_record-NUM3
                                   wa_record-NUM4
                                   wa_record-NUM5
                                   wa_record-NUM6
                                   wa_record-BNUM
                                   wa_record-JACKPOT
                                   wa_record-WINS
                                   wa_record-MACHINE
                                   wa_record-BALLSET.
*      MOVE-CORRESPONDING wa_uploadtxt TO wa_record.
      APPEND wa_record TO it_record.
    ENDLOOP.
  ENDIF.
ENDFORM.                    " UPLOAD_DATA
*&---------------------------------------------------------------------*
*&      Form  ADD_DATA_TO_DATABASE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM ADD_DATA_TO_DATABASE .
*!! Text data is now contained within the internal table IT_RECORD
* Display report data for illustration purposes
LOOP AT it_record INTO wa_record.
  TRANSLATE wa_record-drawday to UPPER CASE.
  TRANSLATE wa_record-MMM to UPPER CASE.
  TRANSLATE wa_record-MACHINE to UPPER CASE.
  check wa_record-drawday ne 'DAY' and wa_record-MMM ne 'MMM'.
  wa_results-drawno  = wa_record-DRAWNO.
  case wa_record-mmm.
    when 'JAN'.
      wa_record-mmm = '01'.
    when 'FEB'.
      wa_record-mmm = '02'.
    when 'MAR'.
      wa_record-mmm = '03'.
    when 'APR'.
      wa_record-mmm = '04'.
    when 'MAY'.
      wa_record-mmm = '05'.
    when 'JUN'.
      wa_record-mmm = '06'.
    when 'JUL'.
      wa_record-mmm = '07'.
    when 'AUG'.
      wa_record-mmm = '08'.
    when 'SEP'.
      wa_record-mmm = '09'.
    when 'OCT'.
      wa_record-mmm = '10'.
    when 'NOV'.
      wa_record-mmm = '11'.
    when 'DEC'.
      wa_record-mmm = '12'.
  endcase.
  CONCATENATE wa_record-YYYY wa_record-MMM wa_record-DD
                                 into wa_results-drawdate.
  shift wa_record-drawday LEFT DELETING LEADING space.
  wa_results-drawday = wa_record-DRAWDAY.
  shift wa_record-machine LEFT DELETING LEADING space.
  wa_results-machine = wa_record-MACHINE.
  shift wa_record-ballset LEFT DELETING LEADING space.
  wa_results-ballset = wa_record-BALLSET.
  wa_results-NUM1    = wa_record-NUM1.
  wa_results-NUM2    = wa_record-NUM2.
  wa_results-NUM3    = wa_record-NUM3.
  wa_results-NUM4    = wa_record-NUM4.
  wa_results-NUM5    = wa_record-NUM5.
  wa_results-NUM6    = wa_record-NUM6.
  wa_results-BNUM    = wa_record-BNUM.
  wa_results-jackpot = wa_record-JACKPOT.
  wa_results-wins    = wa_record-WINS.
  modify  zlotresults from wa_results.
ENDLOOP.
ENDFORM.                    " ADD_DATA_TO_DATABASE
*&---------------------------------------------------------------------*
*&      Form  BALLS_DRAWN_MOST
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM BALLS_DRAWN_MOST .
data: ld_count type i.
  ld_count = 0.
  do.
   if ld_count gt 49.
     exit.
   endif.
   ld_count = ld_count + 1.
   wa_dmost-bnum = ld_count.
   loop at it_results into wa_results.
     if wa_results-num1 = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     elseif wa_results-num2 = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     elseif wa_results-num3 = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     elseif wa_results-num4 = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     elseif wa_results-num5 = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     elseif wa_results-num6 = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     elseif wa_results-bnum = wa_dmost-bnum.
       wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1.
     endif.
    endloop.
    append wa_dmost to it_dmost.
    clear: wa_dmost.
   enddo.
   sort it_dmost by timesdrawn DESCENDING.
ENDFORM.                    " BALLS_DRAWN_MOST
*&---------------------------------------------------------------------*
*&      Form  BUILD_FIELDCATALOG
*&---------------------------------------------------------------------*
*       Build Fieldcatalog for ALV Report
*----------------------------------------------------------------------*
form build_fieldcatalog.
  fieldcatalog-fieldname   = 'BNUM'.
  fieldcatalog-seltext_m   = 'Ball Number'.
  fieldcatalog-col_pos     = 0.
  fieldcatalog-outputlen   = 10.
  fieldcatalog-emphasize   = 'X'.
  fieldcatalog-key         = 'X'.
*  fieldcatalog-do_sum      = 'X'.
*  fieldcatalog-no_zero     = 'X'.
  append fieldcatalog to fieldcatalog.
  clear  fieldcatalog.
  fieldcatalog-fieldname   = 'TIMESDRAWN'.
  fieldcatalog-seltext_m   = 'Times Drawn'.
  fieldcatalog-col_pos     = 1.
  append fieldcatalog to fieldcatalog.
  clear  fieldcatalog.
endform.                    " BUILD_FIELDCATALOG
*&---------------------------------------------------------------------*
*&      Form  BUILD_LAYOUT
*&---------------------------------------------------------------------*
*       Build layout for ALV grid report
*----------------------------------------------------------------------*
form build_layout.
  gd_layout-no_input          = 'X'.
  gd_layout-colwidth_optimize = 'X'.
  gd_layout-totals_text       = 'Totals'(201).
*  gd_layout-totals_only        = 'X'.
*  gd_layout-f2code            = 'DISP'.  "Sets fcode for when double
*                                         "click(press f2)
*  gd_layout-zebra             = 'X'.
*  gd_layout-group_change_edit = 'X'.
*  gd_layout-header_text       = 'helllllo'.
endform.                    " BUILD_LAYOUT
*&---------------------------------------------------------------------*
*&      Form  DISPLAY_ALV_REPORT
*&---------------------------------------------------------------------*
*       Display report using ALV grid
*----------------------------------------------------------------------*
form display_alv_report.
  gd_repid = sy-repid.
  call function 'REUSE_ALV_GRID_DISPLAY'
       exporting
            i_callback_program      = gd_repid
*            i_callback_top_of_page   = 'TOP-OF-PAGE'  "see FORM
*            i_callback_user_command = 'USER_COMMAND'
*            i_grid_title           = outtext
            is_layout               = gd_layout
            it_fieldcat             = fieldcatalog[]
*            it_special_groups       = gd_tabgroup
*            IT_EVENTS                = GT_XEVENTS
            i_save                  = 'X'
*            is_variant              = z_template
       tables
            t_outtab                = it_dmost
       exceptions
            program_error           = 1
            others                  = 2.
  if sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  endif.
endform.                    " DISPLAY_ALV_REPORT
*&---------------------------------------------------------------------*
*&      Form  TEST_NUMBER_COMBO
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM TEST_NUMBER_COMBO .
   loop at it_results into wa_results.
     wa_tnums-drawdate = wa_results-drawdate.
     wa_tnums-machine  = wa_results-machine.
     wa_tnums-ballset  = wa_results-ballset.
     perform check_number using p_num1 wa_results
                          changing wa_tnums-numcount.
     perform check_number using p_num2 wa_results
                          changing wa_tnums-numcount.
     perform check_number using p_num3 wa_results
                          changing wa_tnums-numcount.
     perform check_number using p_num4 wa_results
                          changing wa_tnums-numcount.
     perform check_number using p_num5 wa_results
                          changing wa_tnums-numcount.
     perform check_number using p_num6 wa_results
                          changing wa_tnums-numcount.
     APPEND wa_tnums to it_tnums.
     clear: wa_tnums.
   endloop.
   SORT it_tnums BY numcount DESCENDING.
ENDFORM.                    " TEST_NUMBER_COMBO
*&---------------------------------------------------------------------*
*&      Form  CHECK_NUMBER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_P_NUM1  text
*      -->P_WA_RESULTS  text
*      <--P_WA_TNUMS_NUMCOUNT  text
*----------------------------------------------------------------------*
FORM CHECK_NUMBER  USING    P_NUM
                            P_RESULTS structure zlotresults
                   CHANGING P_NUMCOUNT.
    if p_num eq p_results-num1 or  p_num eq p_results-num2 or
       p_num eq p_results-num3 or  p_num eq p_results-num4 or
       p_num eq p_results-num5 or  p_num eq p_results-num6 or
       p_num eq p_results-bnum.
       p_numcount = p_numcount + 1.
     endif.
ENDFORM.                    " CHECK_NUMBER
*&---------------------------------------------------------------------*
*&      Form  BUILD_FIELDCATALOG_TNUMS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM BUILD_FIELDCATALOG_TNUMS .
  fieldcatalog-fieldname   = 'DRAWDATE'.
  fieldcatalog-seltext_m   = 'Drawdate'.
  fieldcatalog-col_pos     = 0.
  fieldcatalog-outputlen   = 10.
  fieldcatalog-emphasize   = 'X'.
  fieldcatalog-key         = 'X'.
*  fieldcatalog-do_sum      = 'X'.
*  fieldcatalog-no_zero     = 'X'.
  append fieldcatalog to fieldcatalog.
  clear  fieldcatalog.
  fieldcatalog-fieldname   = 'MACHINE'.
  fieldcatalog-seltext_m   = 'Machine'.
  fieldcatalog-col_pos     = 1.
  append fieldcatalog to fieldcatalog.
  clear  fieldcatalog.
  fieldcatalog-fieldname   = 'BALLSET'.
  fieldcatalog-seltext_m   = 'Ball Set'.
  fieldcatalog-col_pos     = 1.
  append fieldcatalog to fieldcatalog.
  clear  fieldcatalog.
    fieldcatalog-fieldname   = 'NUMCOUNT'.
  fieldcatalog-seltext_m   = 'Mached Numbers'.
  fieldcatalog-col_pos     = 1.
  append fieldcatalog to fieldcatalog.
  clear  fieldcatalog.
ENDFORM.                    " BUILD_FIELDCATALOG_TNUMS
*&---------------------------------------------------------------------*
*&      Form  DISPLAY_ALV_TNUMS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM DISPLAY_ALV_TNUMS .
  gd_repid = sy-repid.
  call function 'REUSE_ALV_GRID_DISPLAY'
       exporting
            i_callback_program      = gd_repid
*            i_callback_top_of_page   = 'TOP-OF-PAGE'  "see FORM
*            i_callback_user_command = 'USER_COMMAND'
*            i_grid_title           = outtext
            is_layout               = gd_layout
            it_fieldcat             = fieldcatalog[]
*            it_special_groups       = gd_tabgroup
*            IT_EVENTS                = GT_XEVENTS
            i_save                  = 'X'
*            is_variant              = z_template
       tables
            t_outtab                = it_tnums
       exceptions
            program_error           = 1
            others                  = 2.
  if sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  endif.
ENDFORM.                    " DISPLAY_ALV_TNUMS

Return to bespoke ABAP programs

Related Articles

Send instant message within SAP to a users PC desktop
Commented CALL FUNCTION Pattern
Improved ABAP Pretty Print program to beautify your code
Bespoke ABAP program (download employee data)
ABAP lottery numbers report for SAP system - User guide
Bespoke ABAP program (select staff by postcode)
Bespoke ABAP program (select staff by postcode)
Execute SQL statements on the fly in Open or Native SQL