topic Re: ALV query and data structure issue in Application Development and Automation Discussions

ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 04:46:41 GMT

Hi,

I need to do subtotal in between ALV so please tell me how to do this, in the meantime kindly let me know how to get data from two tables, I have done but not getting desired result,

Firstly i get data from one table through query then from second table through query, Now i want to move data from second table to first internal table so i can populate it from a location to ALV,

Below is my program kindly tell me where to add second internal table in structure or data i think i am making mistake in making data or structure

My First Database table is "afvc" it_proj

My secod database tbale is "proj" mess up what to do here

after getting data io want to merge them in a single internal table

*&---------------------------------------------------------------------*

*& Report /BWN0/Z_PROJECT_SUMMERY

* created by sarosh durrani

*&---------------------------------------------------------------------*

REPORT /BWN0/Z_PROJECT_SUMMERY.

TABLES: proj, prps, afvc, afvv, afvu, cosp, coss.

type-pools: slis. "ALV Declarations

*Data Declaration

*----------------

TYPES: BEGIN OF p_proj,

SNO TYPE int4,

* v_sno(6) type c,

pspid TYPE proj-pspid,

ernam TYPE proj-ernam,

stspr TYPE proj-stspr,

pwhie TYPE proj-pwhie,

besta TYPE proj-besta,

scope TYPE proj-scope,

xstat TYPE proj-xstat,

postu TYPE proj-postu,

smprf TYPE proj-smprf,

erdat TYPE proj-erdat,

post1 TYPE proj-post1,

END OF p_proj.

TYPES: BEGIN OF pl_proj,

SNO TYPE int4,

sakto TYPE afvc-sakto,

END OF p_proj.

*DATA: IT_PRPS TYPE TABLE OF PRPS.

DATA: it_proj TYPE STANDARD TABLE OF p_proj,

wa_proj TYPE p_proj,

VSNO TYPE INT4.

*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.

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

************************************************************************

*Start-of-selection.

START-OF-SELECTION.

perform data_retrieval.

perform build_fieldcatalog.

perform build_layout.

perform display_alv_report.

*&---------------------------------------------------------------------*

*& Form BUILD_FIELDCATALOG

*&---------------------------------------------------------------------*

* Build Fieldcatalog for ALV Report

*----------------------------------------------------------------------*

VSNO = VSNO + 1.

* it_proj-SNO = VSNO.

form build_fieldcatalog.

fieldcatalog-fieldname = 'SNO'.

fieldcatalog-seltext_m = 'Serial number'.

fieldcatalog-col_pos = 1.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PSPID'.

fieldcatalog-seltext_m = 'Description'.

fieldcatalog-col_pos = 2.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERNAM'.

fieldcatalog-seltext_m = 'Quantity'.

fieldcatalog-col_pos = 3.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'STSPR'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 4.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PWHIE'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 5.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'BESTA'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 6.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SCOPE'.

fieldcatalog-seltext_m = 'Crane'.

fieldcatalog-col_pos = 7.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'XSTAT'.

fieldcatalog-seltext_m = 'Total'.

fieldcatalog-col_pos = 8.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POSTU'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 9.

* fieldcatalog-outputlen = 15.

* fieldcatalog-datatype = 'CURR'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SMPRF'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 10.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERDAT'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 11.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POST1'.

fieldcatalog-seltext_m = 'Erection1'.

fieldcatalog-col_pos = 12.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SAKTO'.

fieldcatalog-seltext_m = 'Cost Element'.

fieldcatalog-col_pos = 13.

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'.

gd_layout-expand_fieldname = 'EXPAND'.

gd_layout-window_titlebar = 'Hierarchical ALV list display'.

gd_layout-lights_tabname = 'it_proj'.

gd_layout-colwidth_optimize = 'X'.

endform. " BUILD_LAYOUT

*&---------------------------------------------------------------------*

*& Form DISPLAY_ALV_REPORT

*&---------------------------------------------------------------------*

* Display report using ALV grid

*----------------------------------------------------------------------*

form display_alv_report.

gd_repid = sy-repid.

call function 'REUSE_ALV_LIST_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_tab_group

* IT_EVENTS = GT_XEVENTS

i_save = 'X'

* is_variant = z_template

tables

t_outtab = it_proj

* t_outtabnew = it_prps

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 DATA_RETRIEVAL

*&---------------------------------------------------------------------*

* Retrieve data form EKPO table and populate itab it_ekko

*----------------------------------------------------------------------*

form data_retrieval.

*select PSPID ERNAM STSPR PWHIE BESTA SCOPE XSTAT POSTU SMPRF ERDAT from proj into corresponding fields of table it_proj.

select * from proj into corresponding fields of table it_proj.

select * from afvc into corresponding fields of table it_proj.

MOVE GTab-bukrs TO GLTab-bukrs.

MOVE GTab-gjahr TO GLTab-gjahr.

MOVE GTab-BELNR TO GLTab-BELNR.

MOVE GTab-BLART TO GLTab-BLART.

MOVE GTab-LTEXT TO GLTab-LTEXT.

MOVE GTab-BLDAT TO GLTab-BLDAT.

MOVE GTab-BUDAT TO GLTab-BUDAT.

MOVE GTab-XBLNR TO GLTab-XBLNR.

MOVE GTab-BKTXT TO GLTab-BKTXT.

MOVE GTab-USNAM TO GLTab-USNAM.

MOVE GTab-CPUDT TO GLTab-CPUDT.

MOVE GTab-STBLG TO GLTab-STBLG.

* SELECT ERDAT INTO proj-erdat "Cost Center Id and Description

* FROM PRPS. "AND SPRAS = 'E'.

*ENDSELECT.

LOOP AT it_proj INTO wa_proj .

wa_proj-sno = sy-tabix.

MODIFY it_proj FROM wa_proj TRANSPORTING SNO .

CLEAR wa_proj.

ENDLOOP.

*select ERDAT from prps into corresponding fields of table it_proj.

*select * from prps into corresponding fields of table it_PRPS.

endform. " DATA_RETRIEVAL

*up to 10 rows

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

*-------------------------------------------------------------------*

* Form TOP-OF-PAGE *

*-------------------------------------------------------------------*

* ALV Report Header *

*-------------------------------------------------------------------*

Form top_of_page.

*ALV Header declarations

data: t_header type slis_t_listheader,

wa_header type slis_listheader,

t_line like wa_header-info,

ld_lines type i,

ld_linesc(10) type c.

* Title

wa_header-typ = 'H'.

wa_header-info = 'EKKO Table Report'.

append wa_header to t_header.

clear wa_header.

* Date

wa_header-typ = 'S'.

wa_header-key = 'Date: '.

CONCATENATE sy-datum+6(2) '.'

sy-datum+4(2) '.'

sy-datum(4) INTO wa_header-info. "todays date

append wa_header to t_header.

clear: wa_header.

* Total No. of Records Selected

describe table it_proj lines ld_lines.

ld_linesc = ld_lines.

concatenate 'Total No. of Records Selected: ' ld_linesc

into t_line separated by space.

wa_header-typ = 'A'.

wa_header-info = t_line.

append wa_header to t_header.

clear: wa_header, t_line.

call function 'REUSE_ALV_COMMENTARY_WRITE'

exporting

it_list_commentary = t_header.

* i_logo = 'Z_LOGO'.

endform.

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 05:52:36 GMT

Hi,

For subtotal:

There are 2 ways to achieve this......

1) You can create default layout for subtotal.

2) declare the below table :

DATA: IT_SORT TYPE SLIS_T_SORTINFO_ALV,

WA_SORT TYPE SLIS_SORTINFO_ALV.

WA_SORT-FIELDNAME = 'EBELN'.

WA_SORT-SUBTOT = 'X'.

APPEND WA_SORT TO IT_SORT.

First, you have to create 3 internal table....

1) Is used to fetch data from table 1.

2) Is used to fetch data from table 2.

Once, you done with step

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 05:57:09 GMT

hi sarosh,

you can use modify statment to merge two internal tables data into one internal table. following are different syntaxs you can use

Modify with database table

•MODIFY <dbtab> FROM <wa>.

Here the database <dbtab> will adopt the new values from work area <wa>

•MODIFY <dbtab> FROM TABLE <itab>.

Here the database <dbtab> will adopt the new values from an internal table <itab>

Modify with Internal table

MODIFY TABLE <itab> FROM <wa> [TRANSPORTING <f1> <f 2>...][ASSIGNING <FS> | REFERENCE INTO <dref>].

Modify with Index table

MODIFY <itab> FROM <wa> [INDEX <idx>] [TRANSPORTING <f1> <f 2>...][ASSIGNING <FS> | REFERENCE INTO <dref>].

Hope it helps.

Regards

Uday

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:01:44 GMT

hi,

thanks for your response, Now can you please tell me i am going to make an inner join query and facing difficulties

I want to fetch data from 2 tables and fields are wrtitten below

Tabel 1 Name : PROJ

Table 2 Name: AFVC

Fields of table 1 : pspid, ernam, stspr, pwhie,besta,scope,xstat,postu,smprf,erdat,post1,sakto

Fields of table 2: sakto,

Now guide me what will be the query for this

Regards

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:04:09 GMT

Hi,

Can you write in my program so may help me better in understanding code, or if you think it can be achievable through inner join query in simple way then guide through inner join query

Regards

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:11:48 GMT

Hi,

For subtotal:

There are 2 ways to achieve this......

1) You can create default layout for subtotal.

2) declare the below table :

DATA: IT_SORT TYPE SLIS_T_SORTINFO_ALV,

WA_SORT TYPE SLIS_SORTINFO_ALV.

WA_SORT-FIELDNAME = 'EBELN'. "specify the field name for which you want subtotal

WA_SORT-SUBTOT = 'X'.

APPEND WA_SORT TO IT_SORT.

and also in your field catalog for summation field, add the below code.

FIELDCATALOG-FIELDNAME = 'MENGE'.

FIELDCATALOG-DO_SUM = 'X'.

First, you have to create 3 internal table....

1) Is used to fetch data from table 1. i.e. afvc

2) Is used to fetch data from table 2. i.e proj

Once, you done with step, then create another internal table ITAB3 which includes intended field from itab1 and itab2.

then.....

Loop at ITAB1.

move respective data from ITAB1 to ITAB3.

append ITAB3.

clear: ITAB1, ITAB3.

endloop.

Loop at ITAB2.

move respective data from ITAB2 to ITAB3.

append ITAB3.

clear: ITAB2, ITAB3.

endloop.

now, in ITAB3 you have all data.....

Hop this will help you....

Regards,

Rajesh Sadula.

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:12:28 GMT

Sarosh,

Inner joins are not recommeded as per latest ABAP programming recommendations as it set to decrease your program's performance.

However, I can definitely help you resolve this issue in a very simple way.

declare a it_final table with its structure containing both table1 and table 2 fields.

now when you need to fill first table by using append table and then modify it_final by transporting all the contents of table1.

similarly do it for second table.

finally pass only it_final fields to the field catalogue.

Hope it helps.

regards

Uday

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:21:33 GMT

Hi Uday,

can you give me a favor please, if possible for you please do it in my code if you can

Regards

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:22:59 GMT

Hi Rajesh,

I understand and i have tried but i think i am making some mistake can you please write using my code so i can undestand it better

Regards

Re: ALV query and data structure issue

yogendra_bhaskar — Wed, 25 Jul 2012 06:39:54 GMT

hi sarosh ,

Follow this steps :

1 > create four internal table .

it_proj : data for table proj

it_prps : use psphi and pspnr

it_afvc : data for table afvc

it_final : data you want to display in alv.

2 > use the following link to select data

PROJ : PROJ-PSPNR = PRPS-PSPHI

AFVC : PRPS-PSPNR = AFVC-PROJN

3> loop at table it_afvc and append data in it_final , read data from table proj .

4 > finally display it_final in alv.

and for sub total , you have to implement this:

DATA: IT_SORT TYPE SLIS_T_SORTINFO_ALV,

WA_SORT TYPE SLIS_SORTINFO_ALV.

WA_SORT-FIELDNAME = 'xxxxx'.

WA_SORT-SUBTOT = 'X'.

APPEND WA_SORT TO IT_SORT.

and in fielcatalog :

FIELDCATALOG-FIELDNAME = 'xxxxx'.

FIELDCATALOG-DO_SUM = 'X'.

regards ,

Yogendra Bhaskar

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:44:33 GMT

Hi,

I have implemented first step but facing difficulties in in other steps, If you can write in my program it will be highly appreciated

Regards

Re: ALV query and data structure issue

yogendra_bhaskar — Wed, 25 Jul 2012 06:47:55 GMT

hi ,

Provide me the data declaration done in first step.

I will help you out in for step 2.

regards ,

Yogendra Bhaskar

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 06:52:31 GMT

HI BELOW IS MY CODE

*&---------------------------------------------------------------------*

*& Report /BWN0/Z_PROJECT_SUMMERY

* created by sarosh durrani

*&---------------------------------------------------------------------*

REPORT /BWN0/Z_PROJECT_SUMMERY.

TABLES: proj, prps, afvc, afvv, afvu, cosp, coss.

type-pools: slis. "ALV Declarations

*Data Declaration

*----------------

TYPES: BEGIN OF p_proj,

SNO TYPE int4,

* v_sno(6) type c,

pspid TYPE proj-pspid,

ernam TYPE proj-ernam,

stspr TYPE proj-stspr,

pwhie TYPE proj-pwhie,

besta TYPE proj-besta,

scope TYPE proj-scope,

xstat TYPE proj-xstat,

postu TYPE proj-postu,

smprf TYPE proj-smprf,

erdat TYPE proj-erdat,

post1 TYPE proj-post1,

sakto TYPE afvc-sakto,

END OF p_proj.

TYPES: BEGIN OF pl_afvc,

* SNO TYPE int4,

sakto TYPE afvc-sakto,

END OF pl_afvc.

*DATA: IT_PRPS TYPE TABLE OF PRPS.

DATA: it_proj TYPE STANDARD TABLE OF p_proj WITH HEADER LINE,

it_afvc TYPE STANDARD TABLE OF pl_afvc WITH HEADER LINE,

wa_proj TYPE p_proj,

VSNO TYPE INT4.

*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.

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

************************************************************************

*Start-of-selection.

START-OF-SELECTION.

perform data_retrieval.

perform build_fieldcatalog.

perform build_layout.

perform display_alv_report.

*&---------------------------------------------------------------------*

*& Form BUILD_FIELDCATALOG

*&---------------------------------------------------------------------*

* Build Fieldcatalog for ALV Report

*----------------------------------------------------------------------*

VSNO = VSNO + 1.

* it_proj-SNO = VSNO.

form build_fieldcatalog.

fieldcatalog-fieldname = 'SNO'.

fieldcatalog-seltext_m = 'Serial number'.

fieldcatalog-col_pos = 1.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POSTU'.

fieldcatalog-seltext_m = 'Description'.

fieldcatalog-col_pos = 2.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERNAM'.

fieldcatalog-seltext_m = 'Quantity'.

fieldcatalog-col_pos = 3.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'STSPR'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 4.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PWHIE'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 5.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'BESTA'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 6.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SCOPE'.

fieldcatalog-seltext_m = 'Crane'.

fieldcatalog-col_pos = 7.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'XSTAT'.

fieldcatalog-seltext_m = 'Total'.

fieldcatalog-col_pos = 8.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PSPID'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 9.

* fieldcatalog-outputlen = 15.

* fieldcatalog-datatype = 'CURR'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SMPRF'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 10.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERDAT'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 11.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POST1'.

fieldcatalog-seltext_m = 'Erection1'.

fieldcatalog-col_pos = 12.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SAKTO'.

fieldcatalog-seltext_m = 'Cost Element'.

fieldcatalog-col_pos = 13.

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'.

gd_layout-expand_fieldname = 'EXPAND'.

gd_layout-window_titlebar = 'Hierarchical ALV list display'.

gd_layout-lights_tabname = 'it_proj'.

gd_layout-colwidth_optimize = 'X'.

endform. " BUILD_LAYOUT

*&---------------------------------------------------------------------*

*& Form DISPLAY_ALV_REPORT

*&---------------------------------------------------------------------*

* Display report using ALV grid

*----------------------------------------------------------------------*

form display_alv_report.

gd_repid = sy-repid.

call function 'REUSE_ALV_LIST_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_tab_group

* IT_EVENTS = GT_XEVENTS

i_save = 'X'

* is_variant = z_template

tables

t_outtab = it_proj

* t_outtabnew = it_prps

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 DATA_RETRIEVAL

*&---------------------------------------------------------------------*

* Retrieve data form EKPO table and populate itab it_ekko

*----------------------------------------------------------------------*

form data_retrieval.

*select PSPID ERNAM STSPR PWHIE BESTA SCOPE XSTAT POSTU SMPRF ERDAT from proj into corresponding fields of table it_proj.

select * from proj into corresponding fields of table it_proj.

select sakto from afvc into corresponding fields of table it_afvc.

MOVE it_afvc-sakto TO it_proj-sakto.

* MOVE p_proj-gjahr TO it_proj-gjahr.

* MOVE p_proj-BELNR TO it_proj-BELNR.

* MOVE p_proj-BLART TO it_proj-BLART.

* MOVE p_proj-LTEXT TO it_proj-LTEXT.

* MOVE p_proj-BLDAT TO it_proj-BLDAT.

* MOVE p_proj-BUDAT TO it_proj-BUDAT.

* MOVE p_proj-XBLNR TO it_proj-XBLNR.

* MOVE p_proj-BKTXT TO it_proj-BKTXT.

* MOVE p_proj-USNAM TO it_proj-USNAM.

* MOVE p_proj-CPUDT TO it_proj-CPUDT.

* MOVE p_proj-STBLG TO it_proj-STBLG.

* SELECT ERDAT INTO proj-erdat "Cost Center Id and Description

* FROM PRPS. "AND SPRAS = 'E'.

*ENDSELECT.

LOOP AT it_proj INTO wa_proj .

wa_proj-sno = sy-tabix.

MODIFY it_proj FROM wa_proj TRANSPORTING SNO .

CLEAR wa_proj.

ENDLOOP.

*select ERDAT from prps into corresponding fields of table it_proj.

*select * from prps into corresponding fields of table it_PRPS.

endform. " DATA_RETRIEVAL

*up to 10 rows

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

*-------------------------------------------------------------------*

* Form TOP-OF-PAGE *

*-------------------------------------------------------------------*

* ALV Report Header *

*-------------------------------------------------------------------*

Form top_of_page.

*ALV Header declarations

data: t_header type slis_t_listheader,

wa_header type slis_listheader,

t_line like wa_header-info,

ld_lines type i,

ld_linesc(10) type c.

* Title

wa_header-typ = 'H'.

wa_header-info = 'EKKO Table Report'.

append wa_header to t_header.

clear wa_header.

* Date

wa_header-typ = 'S'.

wa_header-key = 'Date: '.

CONCATENATE sy-datum+6(2) '.'

sy-datum+4(2) '.'

sy-datum(4) INTO wa_header-info. "todays date

append wa_header to t_header.

clear: wa_header.

* Total No. of Records Selected

describe table it_proj lines ld_lines.

ld_linesc = ld_lines.

concatenate 'Total No. of Records Selected: ' ld_linesc

into t_line separated by space.

wa_header-typ = 'A'.

wa_header-info = t_line.

append wa_header to t_header.

clear: wa_header, t_line.

call function 'REUSE_ALV_COMMENTARY_WRITE'

exporting

it_list_commentary = t_header.

* i_logo = 'Z_LOGO'.

endform.

Currently i need to get data from 2tables only, now tell me how to do

Re: ALV query and data structure issue

yogendra_bhaskar — Wed, 25 Jul 2012 07:32:20 GMT

hi sarosh ,

*&---------------------------------------------------------------------*

*& Report /BWN0/Z_PROJECT_SUMMERY

* created by sarosh durrani

*&---------------------------------------------------------------------*

REPORT /BWN0/Z_PROJECT_SUMMERY.

TABLES: proj, prps, afvc, afvv, afvu, cosp, coss.

type-pools: slis. "ALV Declarations

*Data Declaration

*----------------

TYPES: BEGIN OF p_proj,

SNO TYPE int4,

* v_sno(6) type c,

pspid TYPE proj-pspid,

pspnr TYPE proj-pspnr,

ernam TYPE proj-ernam,

stspr TYPE proj-stspr,

pwhie TYPE proj-pwhie,

besta TYPE proj-besta,

scope TYPE proj-scope,

xstat TYPE proj-xstat,

postu TYPE proj-postu,

smprf TYPE proj-smprf,

erdat TYPE proj-erdat,

post1 TYPE proj-post1,

sakto TYPE afvc-sakto,

END OF p_proj.

TYPES: BEGIN OF pl_afvc,

* SNO TYPE int4,

sakto TYPE afvc-sakto,

projn TYPE afvc-projn,

psphi TYPE prps-psphi,

END OF pl_afvc.

*DATA: IT_PRPS TYPE TABLE OF PRPS.

DATA: it_proj TYPE STANDARD TABLE OF p_proj WITH HEADER LINE,

it_afvc TYPE STANDARD TABLE OF pl_afvc WITH HEADER LINE,

wa_proj TYPE p_proj,

VSNO TYPE INT4.

*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.

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

************************************************************************

*Start-of-selection.

START-OF-SELECTION.

perform data_retrieval.

perform build_fieldcatalog.

perform build_layout.

perform display_alv_report.

*&---------------------------------------------------------------------*

*& Form BUILD_FIELDCATALOG

*&---------------------------------------------------------------------*

* Build Fieldcatalog for ALV Report

*----------------------------------------------------------------------*

VSNO = VSNO + 1.

* it_proj-SNO = VSNO.

form build_fieldcatalog.

fieldcatalog-fieldname = 'SNO'.

fieldcatalog-seltext_m = 'Serial number'.

fieldcatalog-col_pos = 1.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POSTU'.

fieldcatalog-seltext_m = 'Description'.

fieldcatalog-col_pos = 2.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERNAM'.

fieldcatalog-seltext_m = 'Quantity'.

fieldcatalog-col_pos = 3.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'STSPR'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 4.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PWHIE'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 5.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'BESTA'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 6.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SCOPE'.

fieldcatalog-seltext_m = 'Crane'.

fieldcatalog-col_pos = 7.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'XSTAT'.

fieldcatalog-seltext_m = 'Total'.

fieldcatalog-col_pos = 8.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PSPID'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 9.

* fieldcatalog-outputlen = 15.

* fieldcatalog-datatype = 'CURR'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SMPRF'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 10.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERDAT'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 11.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POST1'.

fieldcatalog-seltext_m = 'Erection1'.

fieldcatalog-col_pos = 12.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SAKTO'.

fieldcatalog-seltext_m = 'Cost Element'.

fieldcatalog-col_pos = 13.

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'.

gd_layout-expand_fieldname = 'EXPAND'.

gd_layout-window_titlebar = 'Hierarchical ALV list display'.

gd_layout-lights_tabname = 'it_proj'.

gd_layout-colwidth_optimize = 'X'.

endform. " BUILD_LAYOUT

*&---------------------------------------------------------------------*

*& Form DISPLAY_ALV_REPORT

*&---------------------------------------------------------------------*

* Display report using ALV grid

*----------------------------------------------------------------------*

form display_alv_report.

gd_repid = sy-repid.

call function 'REUSE_ALV_LIST_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_tab_group

* IT_EVENTS = GT_XEVENTS

i_save = 'X'

* is_variant = z_template

tables

t_outtab = it_proj

* t_outtabnew = it_prps

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 DATA_RETRIEVAL

*&---------------------------------------------------------------------*

* Retrieve data form EKPO table and populate itab it_ekko

*----------------------------------------------------------------------*

form data_retrieval.

*select PSPID ERNAM STSPR PWHIE BESTA SCOPE XSTAT POSTU SMPRF ERDAT from proj into corresponding fields of table it_proj.

select * from proj into corresponding fields of table it_proj.

select afvc~sakto afvc~projn prps~psphi FROM afvc INNER JOIN prps

ON AFVC~PROJN = PRPS~PSPNR into corresponding fields of table it_afvc FOR ALL ENTRIES IN it_proj WHERE PRPS~PSPHI = it_PROJ-PSPNR .

LOOP AT it_proj INTO wa_proj .

wa_proj-sno = sy-tabix.

READ TABLE it_afvc with key psphi = wa_proj-pspnr .

IF sy-SUBRC eq 0.

wa_proj-sakto = it_afvc-sakto.

ENDIF.

MODIFY it_proj FROM wa_proj TRANSPORTING SNO sakto .

CLEAR wa_proj.

ENDLOOP.

endform. " DATA_RETRIEVAL

*up to 10 rows

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

*-------------------------------------------------------------------*

* Form TOP-OF-PAGE *

*-------------------------------------------------------------------*

* ALV Report Header *

*-------------------------------------------------------------------*

Form top_of_page.

*ALV Header declarations

data: t_header type slis_t_listheader,

wa_header type slis_listheader,

t_line like wa_header-info,

ld_lines type i,

ld_linesc(10) type c.

* Title

wa_header-typ = 'H'.

wa_header-info = 'EKKO Table Report'.

append wa_header to t_header.

clear wa_header.

* Date

wa_header-typ = 'S'.

wa_header-key = 'Date: '.

CONCATENATE sy-datum+6(2) '.'

sy-datum+4(2) '.'

sy-datum(4) INTO wa_header-info. "todays date

append wa_header to t_header.

clear: wa_header.

* Total No. of Records Selected

describe table it_proj lines ld_lines.

ld_linesc = ld_lines.

concatenate 'Total No. of Records Selected: ' ld_linesc

into t_line separated by space.

wa_header-typ = 'A'.

wa_header-info = t_line.

append wa_header to t_header.

clear: wa_header, t_line.

call function 'REUSE_ALV_COMMENTARY_WRITE'

exporting

it_list_commentary = t_header.

* i_logo = 'Z_LOGO'.

endform.

regards ,

Yogendra Bhaskar

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 07:49:46 GMT

Hi,

many thanks for the sweet help, If i want to get data from other tables too, should i make seperate structure for them or just include in it_proj and add in inner join query??

Regards

Re: ALV query and data structure issue

yogendra_bhaskar — Wed, 25 Jul 2012 07:59:15 GMT

hi ,

as per your current program , you have to include in it_proj .

whereas if you implement another structure , it would be much appreciated.

regards ,

Yogendra Bhaskar

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 08:00:43 GMT

One thing more, If i want to insert 3fields more from table "proj"

and want to display in ALV, I want their sum to be displayed in ALV under the column total

for example

a b c

2 4 6

this is just an example, add these columns in ALV alongwith other columns in field catelogue

How to take int fields in structure and add them in query and add in field catelogue

Regards

Re: ALV query and data structure issue

yogendra_bhaskar — Wed, 25 Jul 2012 08:55:47 GMT

hi ,

TYPES: BEGIN OF p_proj,

a type proj-a,

b type proj-b,

c type proj-c,

total type int4, " data type should be as per sum of a b c

END OF p_proj.

DATA: it_proj TYPE STANDARD TABLE OF p_proj WITH HEADER LINE,

wa_proj TYPE p_proj,

select a b c from proj into table it_proj.

loop at it_proj.

it_proj-total = it_proj-a + it_proj-b + it_proj-c.

modify it_proj transporting total.

endloop.

regards,

Yogendra Bhaskar

Re: ALV query and data structure issue

Former Member — Wed, 25 Jul 2012 09:15:20 GMT

Ok i Got it, now tell me how can i print them in field catelogue, one more thing please

if my fields are placed in table "cosp" and my fields name are cosid, press,opss

then how will i add them and print in seperate column in ALV

Regards

Re: ALV query and data structure issue

yogendra_bhaskar — Wed, 25 Jul 2012 09:28:12 GMT

hi ,

just add them in field catalog :

fieldcatalog-fieldname = 'A'.

fieldcatalog-col_pos = 14.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'B'.

fieldcatalog-col_pos = 15.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'C'.

fieldcatalog-col_pos = 16.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'TOTAL'.

fieldcatalog-col_pos = 17.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

and table COSP doesn't contain field cosid, press,opss

regards ,

Yogendra Bhaskar