Solved: ALV query and data structure issue

Former Member · ‎07-25-2012

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.

yogendra_bhaskar · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

udaykumar_kanike · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

udaykumar_kanike · ‎07-25-2012

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

Former Member · ‎07-25-2012

Hi Uday,

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

Regards

Former Member · ‎07-25-2012

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.

Former Member · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

hi ,

Provide me the data declaration done in first step.

I will help you out in for step 2.

regards ,

Yogendra Bhaskar

Former Member · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

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

Former Member · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

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

yogendra_bhaskar · ‎07-25-2012

if you are taking COSP as an internal table , do it as done before for it_proj a , b, c.

Regards ,

Yogendra Bhaskar

Former Member · ‎07-25-2012

cosp is a database table not an internal table

Former Member · ‎07-25-2012

and what about query, do i need to run a query for those files of COSP??

Former Member · ‎07-25-2012

should i replace "A" with my field name "B" with my field name?

Former Member · ‎07-25-2012

ok i checked and mistakenly write cosp table name, Now AFVV table name and fields are

UMREN

UMREZ

ZMERH

I want sum of these three fileds into another field which will be 4th field, Now please add them in query, structure and field catalogue too

Much thanks

Regards

Former Member · ‎07-25-2012

Check this image

i have four columns

1ex factory

2 deliver

3 erection

4 crane

5 total

Now i want sum of 4 columns in 5th column, Please guide me through test field name i wil replace them with my own

yogendra_bhaskar · ‎07-25-2012

hi ,

replace a b c with your own field :

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.

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.

regards ,

Yogendra Bhaskar

Former Member · ‎07-26-2012

Hi Yogendra,

Thanks for the answer, tell me one thing, should i add these fields in my already created structure or create a new structure for sum fields?

Below is my code I have edited this and add your sent fields in this, please approve this if this correct or make me correct 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,

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,

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.

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.

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.

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.

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

modify it_proj transporting total.

endloop.

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.

PLEASE LET ME KNOW

Former Member · ‎07-26-2012

Hi,

I would say, what every you did it was right....

Please go ahead....

Do let we know if you still face any problem....

Regards,

Rajesh Sadula.

yogendra_bhaskar · ‎07-27-2012

hi sarosh ,

if you r getting the correct output , then its the correct code .

regards ,

yogendra bhaskar

Former Member · ‎07-27-2012

Hello,

Can you please tell me what will be the query if i want to join 3tables, currently 2tables inner join query is executing,

If my table name is "afvv" and my fields are

PLIFZ

DAUNO

Do one thing, also guide me if i don't want to use inner join query then how i will get the desired result

Because i think its difficult for me to handle inner join query is it possible can i use simple query each qeury saperate from each other

for example

Select * from table1.....

Select DAUNO PLIFZ from table2 into.......

If i want to use likie this then how i will get them in my structure where to add etc etc

Regards

yogendra_bhaskar · ‎07-27-2012

hi sarosh ,

refer to following example for inner join on three tables :

SELECT c~carrname p~connid f~fldate
       INTO CORRESPONDING FIELDS OF TABLE itab
       FROM ( ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid
                              AND p~cityfrom = p_cityfr
                              AND p~cityto   = p_cityto )
         INNER JOIN sflight AS f ON f~carrid = p~carrid
                                AND f~connid = p~connid ).

here scarr , spfli and sflight are three tables .

yes , you can get the desired result without using JOIN .

but in that case , you have to select data from one table , then you have to use FOR ALL ENTRIES to get data from second table and finally you have to fetch data from third table same using for all entries :

see example below :

SELECT pspnr

pspid

FROM proj

INTO TABLE gt_proj

WHERE pspid IN s_pspid.

SELECT pspnr " WBS Element

posid " Work Breakdown Structure Element (WBS Element)

post1 " PS: Short description (1st text line)

objnr " Object number

psphi " Current number of the appropriate project

FROM prps

INTO TABLE gt_prps

FOR ALL ENTRIES IN gt_proj

WHERE posid IN s_posid AND

psphi EQ gt_proj-pspnr AND

werks IN s_werks.

** commitment data : COOI table

SELECT objnr

orgwth

FROM cooi

INTO TABLE gt_cooi

FOR ALL ENTRIES IN gt_prps

WHERE objnr = gt_prps-objnr.

I would say , you must be getting inner join as complicated scenario , but on performance basis it is much better. try to be familiar with it.

regards ,

Yogendra Bhaskar

Former Member · ‎07-28-2012

Hi,

Thanks i resolved my issue, now can you tell me how can i adjust width of my columns in ALV, right now its disturbed width column

attached is snapshop please guide me, I join 2images with each other just check and guide me width of columns in ALV

Regards

yogendra_bhaskar · ‎07-30-2012

hi sarosh ,

i understand your problem ,

what is the need of this in build_layout

gd_layout-expand_fieldname = 'EXPAND'.

gd_layout-window_titlebar = 'Hierarchical ALV list display'.

just comment this or remove it , you will get your desired result :

* gd_layout-expand_fieldname = 'EXPAND'.

* gd_layout-window_titlebar = 'Hierarchical ALV list display'.

regards ,

Yogendra Bhaskar

Former Member · ‎07-30-2012

thanks i got my error fixed

now check this post my new question

http://scn.sap.com/thread/3214694

yogendra_bhaskar · ‎07-30-2012

hi sarosh ,

try this and format accordingly the text in bold letters :

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

*& 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,

a type i,

b type i,

c type i,

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.

data : it_event TYPE slis_t_event ,

* wa_fld TYPE slis_fieldcat_alv ,

heading TYPE slis_t_listheader,

wa_event TYPE slis_alv_event .

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

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

*Start-of-selection.

START-OF-SELECTION.

perform data_retrieval.

PERFORM eventtab_build USING it_event.

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 = 5.

* fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-just = 'L'.

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

fieldcatalog-emphasize = 'C170'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.

fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'A'.

fieldcatalog-seltext_m = 'A'.

fieldcatalog-col_pos = 3.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'B'.

fieldcatalog-seltext_m = 'B'.

fieldcatalog-col_pos = 4.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'C'.

fieldcatalog-seltext_m = ' C = Sum a + b'.

fieldcatalog-col_pos = 5.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERNAM'.

fieldcatalog-seltext_m = 'Quantity'.

fieldcatalog-col_pos = 6.

fieldcatalog-outputlen = 20.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'STSPR'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 7.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PWHIE'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 8.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'BESTA'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 9.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SCOPE'.

fieldcatalog-seltext_m = 'Crane'.

fieldcatalog-col_pos = 10.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'XSTAT'.

fieldcatalog-seltext_m = 'Total'.

fieldcatalog-col_pos = 11.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'PSPID'.

fieldcatalog-seltext_m = 'Ex-Factory'.

fieldcatalog-col_pos = 12.

* fieldcatalog-outputlen = 15.

* fieldcatalog-datatype = 'CURR'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SMPRF'.

fieldcatalog-seltext_m = 'Delivery'.

fieldcatalog-col_pos = 13.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'ERDAT'.

fieldcatalog-seltext_m = 'Erection'.

fieldcatalog-col_pos = 14.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'POST1'.

fieldcatalog-seltext_m = 'Erection1'.

fieldcatalog-col_pos = 15.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SAKTO'.

fieldcatalog-seltext_m = 'Cost Element'.

fieldcatalog-col_pos = 16.

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

* gd_LAYOUT-no_colhead = 'X' .

endform. " BUILD_LAYOUT " 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 = it_event[]

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 ENTRIESIN it_proj WHERE PRPS~PSPHI = it_PROJ-PSPNR .

LOOP AT it_proj INTO wa_proj .

wa_proj-sno = sy-tabix.

wa_proj-a = sy-tabix.

wa_proj-b = sy-tabix * 3.

wa_proj-c = wa_proj-a + wa_proj-b.

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 a b c .

CLEAR wa_proj.

ENDLOOP.

endform. " DATA_RETRIEVAL

*up to 10 rows

*PARAMETER: CURRDATE TYPE EKPO-AEDAT.

*SELECT-OPTIONS: DOCNUM FOR EKKO-EBELN.

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

*& Form EVENTTAB_BUILD

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

* text

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

* -->LT_EVENTS text

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

FORM eventtab_build USING lt_events TYPE slis_t_event.

DATA : wa_event TYPE slis_alv_event .

CALL FUNCTION 'REUSE_ALV_EVENTS_GET'

IMPORTING

et_events = lt_events.

READ TABLE lt_events INTO wa_event

WITH KEY name = slis_ev_after_line_output .

wa_event-form = slis_ev_after_line_output .

MODIFY lt_events FROM wa_event INDEX sy-tabix .

READ TABLE lt_events INTO wa_event

WITH KEY name = slis_ev_top_of_page .

wa_event-form = slis_ev_top_of_page .

MODIFY lt_events FROM wa_event INDEX sy-tabix .

ENDFORM. "EVENTTAB_BUILD

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

*& Form top_of_page

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

* text

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

FORM top_of_page.

"Uline for creating a horizontal line

ULINE AT 1(199) .

"Format color for header background

FORMAT COLOR 4 .

WRITE: / sy-vline ,

15 SY-VLINE, 47 SY-VLINE , 68 sy-vline ,69 'Quantity' , 77 sy-vline , 94 'Unit Cost', 121 sy-vline, 154'Amount' , 199 sy-vline .

ENDFORM. "top_of_page

regards ,

yogendra bhaskar

Former Member · ‎08-01-2012

Hi,

Currently i have a query which is fetching data from two tables, now i want to fetch data form 3tables

Below is my query

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 .

New Field Name: usr05

new Table name from which field come: afvgd

Please guide me

Regards

yogendra_bhaskar · ‎08-01-2012

hi sarosh ,

afvgd is a structure , i guess the field USR05 can be obtained from table AFVU .

regards ,

Yogendra Bhaskar