1.IntroductionWe 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 as ‘ mnthr ‘ .

(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 Extrasbutton

Click on Create icon in the Extras Tab.

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

Continue and define the details and Continue .

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

Also in Extras tab on the right.

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

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 intodesired 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,

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.

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) ?

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)

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

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.

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.

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.

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.

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.

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”?!