Infoset Query: User Defined Fields _______________________________________________________________________

Author: Jogeswara Rao Kavala

1.Introduction
We know that Infoset Query is a report development tool given to Functional people by SAP. Using this, a Functional person can be independent of ABAP to a large extent in the area of Report programs. Infoset query  is cross-functional application. Though this document demos in Plant Maintenance environment, it is believed to be useful for other Functional people also.

This document is based on the assumption that the readers have basic working knowledge in the area of Infoset Queries which involve Tcodes SQ03, SQ02 and SQ01.  Points discussed here, take an Infoset query closer to an ABAP report program.

2. Customer defined fields

Most of us, especially beginners, usually work on standard fields available in the tables we work on, in the Infoset (SQ02).  But, sooner almost everyone gets the desire to have his/her own defined fields in the report as a function of standard fields. Such fields are also referred to as Derived fields, Local fields or Additional fields.

About the task chosen to Demo this

A client has a requirement to compute the duration of fields Required Start Date & Time and Required End Date & Time in a Notification (VIQMEL table). The same is taken for demo here. Hereon we will be referring to this field asmnthr ‘ .

(The above is an example of how  requirements arise for derived fields. In IW28 standard report,  we have computed duration for Malfunction Dates, but not for the fields referred above.)


Two ways to do this:


1.To define and compute this ‘mnthr’ field in the Infoset (SQ02)

Define:

SQ02 –> Click on Extras button

Click on Create icon in the Extras Tab.

1.JPG

In the pop-up, select Additional Field option and give the Additional field name as say MNTHR.

Continue and define the details and Continue .

2.JPG

After this step we see a node created for Additional fields in the left side.

Capture.JPG

Also in Extras tab on the right.

Now, place the cursor on the field name and click on ‘ CodingForAddition’  icon.

3.JPG

You are now taken to the Code tab.

Here, give the code to calculate the Duration (mnthr)

Note:

We have used a function module to easily calculate the duration between two Dates and Times.

This might  look complex initially for beginners, but if you try it is simple application to understand.

One data type ‘duration’ (type i) declared here for temporary use in this code, because we import the duration from FM in this form (seconds).

So, we got the value here for the additional field, we defined (in the form of seconds). To convert the same into Hrs the last line of coding used.

(In other situations, the additional-field value calculation might be much simpler like some arithmetic expression of few standard fields.  The coding will be done accordingly)

All done. Now, you can drag this mnthr into desired Field Group on the right, to have this field in SQ01 for reports.

2.Now let’s see how we do this in Infoset Query way (SQ01)

Reach the following field selection screen in SQ01.

Then,

/wp-content/uploads/2013/10/1_309293.jpg

We see this (Short Name, Local field indicator columns appear)

Now go to the fields which are the components of the formula to calculate your duration (mnthr)

We know they are Required Start Date & Time and Required End Date & Time.

(I need not mention that these fields have come from the VIQMEL table fields selected in Infoset, SQ02)

And identify them with some Short names like shown below.

Now let us create our Local field (Additional field)  mnthr, as under.

Now define the parameters of the field as under.

Now we need to give our code in the Calculation formula seen at the bottom.

If our code is simple we can give it in this screen itself. If it contains more if s then we need to click on Complex Calculation button and to give the full code. (Our present case needs to go here)

So now, are giving the following code in the complex calculation screen.

(Note that what we mention against ‘Condition’ fields here, are nothing but ‘IF’ in ABAP coding.)

You may try to understand the logic (Condition vs Formula) given above, so that the formula giving would be easier for your other requirements.

Select this field by ticking the Check-box in the Field selection screen to have it in the Basic List Fields.

(Also we need to select this field in Basic List screen too as we always do.)

Now this field will appear in the Local Fields node of the Basic list screen of SQ01.

All done.

Notes:

  • In an Infoset query, we can have user fields derived from both SQ02 and SQ01.
  • A user field created in SQ02 would appear in ‘Additional Fields’ node and a user field created in SQ01 would appear in ‘Local Fields’ node in the Basic List screen (SQ01).
  • The above narration mainly illustrates the procedure to have user defined fields. A little complex example has been demonstrated here, so that  simple requirements can be easily met.
  • The author wishes to compile some more useful points in the area of Infoset queries, into another document.

Thank you

KJogeswaraRao



PS: The link given here will be of use to Infoset Query lovers: Infoset Queries: Collection of important posts


To report this post you need to login first.

43 Comments

You must be Logged on to comment or reply to a post.

  1. Vamsi V

    Very useful document. Thanks  a lot for sharing.

    Would you mind to explain how we can concatenate 2 fields in a given table and check that value against another  table field so that only the matched records are displayed in the report output.

    Appreciate your time and  efforts.

    Thanks,

    vamsi

    (0) 
    1. K Jogeswara Rao Post author

      1. Create an Additional Field (say ADDL1) in Infoset (Extras) as explained.

      2. Give code below for this fields as

          CONCATENATE TABLE1-FIELD1 TABLE1-FIELD2 INTO ADDL1.

          CHECK TABLE2-FIELD1 = ADDL1.

      This should work for your query.

      I hope you know about selecting right data type while creating Additional field, depending upon the fields you are concatenating and checking. (Concatenate only works for Character data types)

      Hi Vamsi,

      Thank you for the appreciation.

      You may like to rate the document.

      Jogeswara Rao K

      (0) 
  2. Alan Woods

    Excellent document that covers off quite a few of the questions that are normally raised.

    I have a question that I haven’t seen answered completely before regarding the use of the standard date selection field when using HR ad-hoc queries.

    The problem we are trying to solve concerns employee service years and months.

    We have 2 dates held in IT0041. Datetype=01 holds the current start date and 02 holds the continuous service date which is always less than or equal to the 01 date.

    What we need to code is the difference, in years and months, between the continuous service date in datetype=02 and the date entered using the standard HR Ad-hoc query selection.

    What we are struggling with is how to reference the date, or dates, entered when running the query.

    Normally ad-hoc HR queries are run as of Today but can be run as of a key date in the past or future. In this case the contonuous service years and months will vary depending upon the date entered. Although a date range selection could be entered, this would not normally be done with the length of service query, only a single date.

    So, any idea how the standard date selection field(s) can be referenced in the code behind an additional field (this would most probably be done in the Infoset) ?

    Thanks

    Alan

    (0) 
    1. K Jogeswara Rao Post author

      Thank you Alan,

      Though not conversant with HR area, I tried to understand the issue. Broadly it is about taking a date field value from the selection screen into the code for duration calculation. If it is so this will be possible if the date fields are from tables you are using in the infoset.

      Now take both the date fields (DATE1 and DATE2)  into the selection screen in SQ02 (not from SQ01).

      If you have doubts about this please refer Tip4 the other document for this.

      Declare an additional field for duration (test in the following example)

      Use the following code in the code for this additional field :

      data: durn type i.
           CALL FUNCTION ‘SWI_DURATION_DETERMINE’
             EXPORTING
               START_DATE = DATE1-LOW
               END_DATE   = DATE2-LOW
               START_TIME = ‘000000’
               END_TIME   = ‘000000’
             IMPORTING
               DURATION   = durn.
      test = durn / 3600 .

      All this to be done in the Infoset Extras only.

      Now the duration (test) is calculated in Hours.

      If any of the date fields is not from the tables, then it might be difficult to address.

      Jogeswara Rao K

      (0) 
      1. Alan Woods

        Thanks for the pointers.

        I checked the Infoset Selections and found 62 entries, with a mix of SelCrit and Parameters, and chose one called PNPDISBD to test with.

        Created an additional field and set this field to PNPDISBD using additional code.

        Ran my test query with the standard HR selection option of Today and the correct date was populated in my field.

        Re-ran with the standard HR selection option of current month and the correct start of month date was populated.

        Re-ran with the standard HR selection option of Key Date of 31.03.2014 and this was the date that was populated.

        So many thanks for your help and your link to the 10 Useful Tips document, that is also very good.

        Regards

        Alan

        (0) 
  3. Se-Uk Moon

    Hi, Guru of infoset

    I am beginner of SAP. I created a AR aging reporting using SQ02 & SQ01.

    I have faced a issue that some of duration calculation reqired selection date in selcetion screen.

    this date is not included any tables.

    cf-

    Logical database : DDF

    selection date : 2014.02.28 (open at a key date-???)

    selected sales transaction : 2014.01.05 (SD billing date-from BSID-BUDAT)

    I want to get the this 2014.02.28 to calcuate 54 days.

    How can I get or add the date in infoset and Is it possible to use your documentation ?

    I am sorry in advance if my exxplanation is not enough.

    Thanks

    (0) 
    1. K Jogeswara Rao Post author

      Hi Se-Uk Moon,

      I worked for sometime on this.

      Situation is tricky, because

      -In infoset, Additional fields can not be taken into selections (obviously)

      -So I created an additional field, seldate (date format) and taken into field group.

      Then, in SQ01, I created a local field say dtdiff. (char8 ) and gave formula to it as

      seldate – budat

      (you know that these two names are shortnames given to the original fields)

      I have taken seldate into both selection screen and List screens

      And dtdiff  into List screen.

      I do not have test data, so could not have the result.

      But I doubt this works, due to one obvious constraint that how the value will pass to the sledate  selection field.

      Such are the limitations in Queries when I use to go for coding.

      You may also try in these lines.

      I’ll let you know if I get any clues. (Time constraint)

      Regards

      Jogeswara Rao K

      (0) 
    2. Alan Woods

      Hi Se-Uk Moon

      I had a similar issue (see reply on 20-Nov-2013) where I was able to use the standard selection fields.

      These are accessed from within SQ02 of your Infoset. Goto > Selections.

      In HR you then get something like/wp-content/uploads/2014/03/2014_03_13_105816_409683.jpg

      I was then able to use these fields, once I found the correct one, as part of a calculation and as a parameter to a FM.

      Hope this helps.

      Alan

      (0) 
    3. K Jogeswara Rao Post author

      Hi Se-Uk Moon,

      Good News, I got the solution. Though this is a confined space to answer. I’ll try.

      SQ02

      1. Create an Addition field ‘SELDATE’  as under

      1.JPG

      2. In the Extras – Selections, create a parameter PDATE  as under: (sel No. 10)

      (Do not get confused, I gave same descriptions for seldate and pdate)

      2.JPG

      3. Now click on the Code icon of the Additional field SELDATE

      3.JPG

      and Give this Code. Generate the Infoset.

      4.JPG


      SQ01

      1.I have taken the date BSID-BUDAT  for calculation.

      Give Short names for fields BUDAT and SELDATE as shown here.

      Create a Local field DATEDIFF as shown below.(see the Calculation formula given)

      5.JPG

      2. Showing our fields in the Basic List.

      6.JPG

      3. Execute and go to the Selection Screen

      7.JPG

      4. The desired ALV

      8.JPG

      Hope you will take benefit of this post.



      Best of Luck Moon,

      Jogeswara Rao K

      (0) 
      1. Se-Uk Moon

        Hi,Mr Jogeswara Rao Kavala .

        I am sorry for late reply .

        As your comment, I have set it up and added field.

        and I have coded some adjustment  as “SELDATE = DD_SSTIDA” (Standard parameter).

        As a result , It is extracted and dislplayed well even though Selction date was not key in selection screen(This field was dispressed in layout).

        Anyway,I was deeply impressed with your kindly instruction.

        It is so helpful and cheerful guide to a SAP beginner like me.

        Thanks you so much, Guru of Query. ^^

        (0) 
      2. SANTHOSH SREE

        Hi Jogeswara Rao,

        I have created the User Group , Infoset and  Query in standard area then copied to global area as per the requirement.Now I had  created the varinat for the query and trying to assign the same Variant for T.code in se93  but it is showing erro messgae as” there are no variants for screen 1000″.

        could you please help.

        Regards,

        San.

        (0) 
        1. K Jogeswara Rao Post author

          Santosh,

          Because, your query is not related to the document topic, request you to open a discussion thread (preferably in ABAP space).

          As far the query is concerned it is difficult to make-out the untold parts. Whether the screen number inside the variant is different? (Check it). Also cross check with a direct case to ascertain that the trouble is related to copy from one space to other.

          (0) 
  4. SANTHOSH SREE

    Hi jogeswara Rao,

    How to control multiple records in the query.We have created the query  for Bank details and It0009 is having only one  valid record. In this query we have added It000, It0001,it0041 and it0006.when we run the report for other period it is displaying more than one record but It0009 is having only one record.

    could you please on this query.

    many thanks in advance

    Regards,

    santhosh

    (0) 
    1. K Jogeswara Rao Post author

      This is one complex area in Queries, which at times forced me to go for ABAP report programs. The reason for multiple records is always the deficiency in joins. But it is not very easy to identify which is the join required to eliminate these duplicate records. At times it becomes manageable, when we identify a field value which differentiates these multiple lines. If this is the case, then you can use CHECK syntax to eliminate the unwanted lines. Like:

      CHECK VIQMEL-QMART = ‘M2’.

      This needs usage of syntax Delete Adjacent Duplicates’ which needs the specification of Internal table and that is the root problem. Accessing the internal table in Queries is not straight forward. You’ll find several threads in SCN on this topic, but I didn’t find any with a solution.

      (0) 
  5. Kiran Kumar Manikyam

    Sir,

    I want to add a refresh button in sq02/sq01 reports. Is it possible ?

    Can we add informative text in selection screen ?

    Can we add selection values to report header ?

    -Kiran

    (0) 
    1. K Jogeswara Rao Post author

      Point1 & 3 can not be detailed here. You should take help of other ABAPers to achieve these. I never tried these. Point2 is possible by putting code in Initialization tab of SQ02 Extras.

      1.JPG

      2.JPG

      Further queries if any, you should start fresh discussions.


      (0) 
  6. Bose GN

    Sir ji !!

    Wonder full.. Thanks a lot, for sharing such a detailed and valuable info !!

    I am looking for a way to get the report with ‘Company code+ customer+ year => Total of Open Items (single field with sum of all open items per customer; single row per customer)’

    Can you please guide me in this… have tried my best, but I was not successful

    Looking forward for any suggestion or way to get it 🙁

    (0) 
    1. K Jogeswara Rao Post author

      Your query falls apart from the present topic and is perhaps SD or PP related. One needs to be conversant with the related tables to understand and answer. So I suggest you to start a new discussion in the ABAP space. Your query should contain all details about tables/Join you were working and exact issue being faced.

      Goo luck

      (0) 
      1. Ravi K

        Thank you sir,excellent document . i have created a query for return document details, Here i have taken tables VBRK & VBRP in infoset,and added order,delivery,billing no from vbrp table,And i have written below code to get the return order ,return delivery and return invoice. But i am getting run time error in prod client,It is working fine in qty and dev client,Please check my logic and provide the solution. 1)Return order no : SELECT SINGLE VBELN FROM VBAP INTO ZRETURNORDER   WHERE VGBEL = VBRP-VBELN . 2)Return delivery no : SELECT SINGLE VBELN FROM LIPS INTO ZRETURNDELNO   WHERE VGBEL = ZRETURNORDER. 3)Return billing no: SELECT SINGLE VBELN FROM VBRP INTO ZRETURNBILNO   WHERE VGBEL = ZRETURNORDER.

        (0) 
        1. K Jogeswara Rao Post author

          Hello Ravi,

          Thanks

          1. Your tables are related to SD Module.

          2. When it is working in other clients and not working in PRD means, some reason like domain/data element of one or more of these Z-fields was not transported to PRD.

          3.This place is not a convenient OR right place for such discussions because of several constraints like difficulty in attaching images.

          4. Also, because of point no.2 this query does not relate to the subject of the present document.

          So it is advised to start a discussion with more details like error screen etc, preferably in ABAP space (or in the function space SD/PP).

          (0) 
  7. Yat Yee Lam

    Hi Jogeswara Rao Kavala,

    Nice document. Thanks for sharing!  🙂

    I have a problem about additional field. Could you please help to solve the below issue?!

    I have added a new additional fields in ME80FN and set this additional fields as a selection field as well. When I try to execute the report with “Max. no. of hits” and selection of the additional fields, the number of result records is incorrect.

    Case 1:

    Selection screen input:-

    Max. no. of hits = <blank>

    Additional field = “A”

    Result:-

    500 result records would be shown.

    Case 2:

    Selection screen input:-

    Max. no. of hits = 100

    Additional field = <blank>

    Result:-

    100 result records would be shown.

    Case 3:

    Selection screen input:-

    Max. no. of hits = 100

    Additional field = ‘A’

    Result:-

    5 result records would be shown.

    Seems the report extracted the first 100 records first, and then filter this first 100 records with the additional records.

    How can I extract the first 100 records with the “additional field” and “max no. of hits”?!

    Thanks a lot!

    Best Regards,

    Eva

    (0) 
    1. K Jogeswara Rao Post author

      There is a likelihood for this long query to run very longer. This and the nature of the issue (standard tcode using LDB) call for anew discussion in ABAP space.

      Thanks for compliments

      (0) 

Comments are closed.