ABAP program to Fetch roles of two users from AGR_USERS and display in ALV

[former_member704823]

Hello All,

I have written below ABAP program which can fetch roles of two users from AGR_USERS.

User id Input will be given by the user. Output screen will have User id as well as roles assigned to the user.

Program is working well for one user but cannot fetch the roles of second user.

Input screen:

Output screen :

REPORT Z_REFERNCE_USER_PROG.
TYPE-POOLS: slis.  " SLIS contains all the ALV data types

*&---------------------------------------------------------------------*
*& Data Declaration
*&---------------------------------------------------------------------*

TYPE-POOLS: SLIS,KCDE.
DATA: username(20) TYPE C,
      G_REPID TYPE SY-REPID,
      IT_EVENTS TYPE SLIS_T_EVENT,
      IT_FIELD TYPE SLIS_T_FIELDCAT_ALV,
      WA_FIELD TYPE SLIS_FIELDCAT_ALV,
      IT_SORT TYPE SLIS_T_SORTINFO_ALV.
INITIALIZATION.
G_REPID = SY-REPID.

DATA:BEGIN OF itab OCCURS 0,
       UNAME type AGR_USERS-UNAME,
       AGR_NAME  type AGR_USERS-AGR_NAME,
      UNAME1 type AGR_USERS-UNAME,
       AGR_NAME1  type AGR_USERS-AGR_NAME,
      END of itab.

start-of-selection.
     SELECT-OPTIONS AFF_USER FOR USERNAME NO INTERVALS.
     SELECT-OPTIONS REF_USER FOR USERNAME NO INTERVALS.
end-of-selection.

perform tosql.
perform listshow.
*--------------------------------
* form tosql
*--------------------------------
form tosql.
SELECT DISTINCT AGR_USERS~AGR_NAME    AGR_USERS~UNAME
         INTO corresponding fields of table itab
        FROM AGR_USERS
        where AGR_USERS~UNAME in AFF_USER.

SELECT DISTINCT AGR_USERS~AGR_NAME    AGR_USERS~UNAME
         INTO corresponding fields of table itab
        FROM AGR_USERS
        where AGR_USERS~UNAME in AFF_USER.
endform.
*--------------------------------
* form listshow
*--------------------------------
form listshow.
  DEFINE ADD_FIELD.
    WA_FIELD-FIELDNAME = &1.
    WA_FIELD-REPTEXT_DDIC = &2.
    WA_FIELD-NO_ZERO = 'X'.
    APPEND WA_FIELD TO IT_FIELD.
  END-OF-DEFINITION.
     ADD_FIELD 'UNAME' 'Affected user'.
     ADD_FIELD 'AGR_NAME' 'Role'.
     ADD_FIELD 'UNAME1' 'Reference user'.
     ADD_FIELD 'AGR_NAME1' 'Role'.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'"
       EXPORTING
            I_CALLBACK_PROGRAM = G_REPID
            I_BACKGROUND_ID   = 'ALV_BACKGROUND'
            IT_FIELDCAT        = IT_FIELD
*            IS_LAYOUT          = GS_LAYOUT
*            IT_SORT            = IT_SORT
            I_SAVE             = 'A'
            IT_EVENTS          = IT_EVENTS[]
       TABLES
            T_OUTTAB           = itab
       EXCEPTIONS
            PROGRAM_ERROR = 1
            OTHERS        = 2.
endform.

[abo]

Use "SELECTION-SCREEN BEGIN OF BLOCK..." and "SELECTION-SCREEN END OF BLOCK ..".

You only need ONE event "start-of-selection" and ONE "end-of-selection", if you wish.

[former_member704823]

Thanks Andrea for the answer.
I have updated my code above with your suggestion.

However, this still doesn't solve the issue. Roles are not showing up for the second user id

[abo]

Read the docs first, the selection screen is still missing and you're using the events in place of the screen delimitations.

[MateuszAdamus]

Hello 100788862604

There can be only one START-OF-SELECTION and one END-OF-SELECTION event in the report.

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abapstart-of-selection.htm

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapend-of-selection.htm

And your TOSQL procedure contains a DB SELECT for AFF_USER only.

Kind regards,
Mateusz

[former_member704823]

Thanks for the answer.
I have updated my code above with your suggestion.

However, this still doesn't solve the issue. Roles are not showing up for the second user id

[MateuszAdamus]

Hello

That's because you only copied the code for AFF_USER without thinking about it...

You need to change the AFF_USER to REF_USER - in the first place. However, this will not solve your issue, as you're using INTO CORRESPONDING FIELDS OF TABLE in the second query, which will overwrite the results from the first one.

What if there was a way to make one query for both users at the same time?

SELECT DISTINCT AGR_USERS~AGR_NAME    AGR_USERS~UNAME
   INTO corresponding fields of table itab
   FROM AGR_USERS
   WHERE AGR_USERS~UNAME in AFF_USER
      OR AGR_USERS~UNAME in REF_USER.

Kind regards,
Mateusz

[former_member704823]

mateuszadamus : I have thought about using OR statement. But that will just give the second user id results in the same column.
I want to have it in the 3rd column.

So the format should be

U1 R1 U2 R1

U1 R2 U2 R2

U2 R3

[MateuszAdamus]

You're right, I overlooked this requirement.

Try something like this:

SELECT DISTINCT u1~uname AS uname u1~agr_name AS agr_name u2~uname AS uname1 u2~agr_name AS agr_name1
  INTO TABLE itab
  FROM agr_users AS u1
  LEFT JOIN agr_users AS u2
    ON u2~agr_name = u1~agr_name
  WHERE u1~uname IN aff_user
    AND u2~uname IN ref_user.

Kind regards,
Mateusz

[VXLozano]

Buff... First of all, welcome.

And now, the bad news:

1. your code looks like it was written in the 80s, and the only good thing from the 80s is the music
2. you are confusing SELECTION-SCREEN with START-OF-SELECTION
3. you must work your algorythm skills

About your coding:

• OCCURS should be banned from SAP since the invention of TYPE TABLE OF, not just deprecated
• global variables are a weak point for any development, try to encapsulate
• following the last one: try to learn something about basic OOP

About the selection screen:

• if you want your parameters into a "box", use SELECTION-SCREEN BEGIN OF BLOCK, and use just a single one, don't put both your parameters into a box for each one, the boxes are intended to group parameters by concept
• if you want data from just a pair of users, change the SELECT-OPTIONS for PARAMETERS (and the IN for = in the SQL)

About the algorythm:

you can approach your requirements from many points, but the easier I can think of is to read the data for each user separately (one SELECT for each one, into their corresponding internal table) and then merge the data into a third table, something like

select user, role into table  @data(tab1) from agr where user = user1.
select user, role into table  @data(tab2) from agr where user = user2.
loop at tab1 into data(line1).
  insert initial line into tab3 assigning field-symbol(<line3>).
  <line3>-user1 = line1-user.
  <line3>-role1 = line1-role.
  if line_exists( tab2[ role = line1-role ] ).
    <line3>-user2 = tab2[ role = line1-role ] )-user.
    <line3>-role2 = tab2[ role = line1-role ] )-role.
  endif.
endloop.
" and now a loop for tab2, checking there is NO role in tab1, 
" and (if not) add line to tab3 with user1 and role1 empty 
" and user2 and role2 the values of tab2

Have fun!

[abo]

vicen.lozano

this looks more like an answer than a comment 😉

[Sandra_Rossi]

Use the debug, you will see that one of the two SELECT gives unexpected result. And you should be able to see your error in that SELECT.