SAP Query, Step by Step Guide Part One

April 13th, 2010 | Categories: Consulting Tools, Document Type, How To, SAP, Technical | Tags: , ,

 

Introduction

SAP query is a great tool for developing reports for both end users and for the implementation team alike. Hopefully you have read my article about the uses of SAP query and why I think it is a must for every consultant to be conversant with this tool.

This  is part one of a three part step by step explanation of SAP query to get you up and running creating queries quickly and efficiently.

SAP query Concept

SAP query is a set of transactions that allow a user or at least a super user to design queries, showing different database fields along with custom formulas and calculations. These queries can be based on a single transparent table, a logical database or a set of table joins defined as part of the SAP query process. This allows a user to quickly and easily obtain a report covering multiple tables with a specific set of join criteria. In addition to basic joins SAP query supports the selection of individual fields by the use of ABAP statements, along with more complex data selections using custom code or existing function modules. SAP splits SAP query across multiple transaction codes so that authorizations can be split between different users. In this way one user might be authorized to define table joins and code in an infoset while another is free to use that infoset to generate different queries showing different information.

SAP query structure

The SAP query structure is split based on four transaction codes. To develop a query there are three main concepts;

· Infosets

· User Groups

· Queries

In addition to this there is an all in one transaction that combines these called Quickviewer for quick personal queries

Infoset [SQ02]

The infoset in SAP is the main underlying concept in developing queries in SAP. The infoset defines the tables, the join criteria and the pool of allowable fields within a query. It contains any ABAP code that also might be used in the processing of the query. From within the infoset set up you can define whether the query is based on a single table, a join of tables, a logical data base or if the query is based purely on a retrieval program. In addition to the table structure the pool of fields can be defined which can be accessed from a query. Custom fields can be added along with ABAP code.

Query [SQ01]

The query is the main report that a user will run to view the SAP query. Queries are tied to infosets that have been previously defined. Multiple queries can be developed on the same infoset selecting different selection criteria and display fields. It is also possible to add additional fields not in the infoset which are calculated based on formulas, i.e. if the infoset contains unit price and quantity but not the total value you could define a calculation based on those two fields. A user may be given access to SQ01 without having access to modify the underlying infoset as a security measure.

User group [SQ03]

Although infosets can be associated specifically with an authorization object, they are also associated with user groups. This enables users with query access to be limited to infosets within a certain user group. This can avoid a super user having access to sensitive information, such as HR related tables.

Quickviewer [SQVI]

Quickviewer is an all in one infoset query transaction that is generally intended for a user to create queries for their own consumption. Quickviews are only available to the user name that created them, however they can be converted into normal infosets and queries.

Query Areas

Queries can be defined in one of two areas, depending on whether they will be defined within the system they are to be used in or if the desire is to make them transportable to other systems and clients. From within any of the querying transactions you can change the query area from under environment> query areas

image

From there you will get a pop up where you can choose the query area desired

image

Global

Global queries (and infosets) are client independent and will generate a transport request (depending on the basis settings of your system). If you are creating a query in a development environment that you want to be available in the production system via a transport then this is the best type of query to use.

Local

Local queries are client dependant and the purpose is generally to write them in the system where they will be run. (However SAP does supply tools to move these as well). A local query will not generate a transport request.

Before you Develop a Query

One important step before you dive in and write a query is to make sure that the information you require isn’t freely available by some other method. If you think you will only need to run this query one time or the data comes from a single table then there may be faster ways of obtaining the data you need.

Standard reports

The first place to look is SAP’s standard reporting transactions. I’m the first person to dive into SE16N to pull out something that’s in a standard report, but often with the fact that most reports support ALV grid display it is quicker and often more effective to use standard reports. (Especially if you think end users will want to run these for themselves at some point.)

SE16N

If your data comes primarily from one table then the quickest way to query SAP is of course to use one of the table query transactions such as SE16N, SE17. Additionally if you want to get a quantitative only answer the SE16N “number of entries” button is all you need.

SE16N of course can be used to perform a single table query however if your data sets are small you can also query one table using the results of a previous query, effectively giving you the same results as a table join.

Say for example you want to get a list of the raw materials in a certain plant, normally you might think that this requires joining the tables MARA and MARC together as material type is held in MARA and plant is in MARC.

In the example below we enter our criteria and MARA returns 75 entries a manageable amount

image

Make sure the maximum number of hits is greater than the number found and execute

image

Select the column you will use as the ‘join’ to the MARC table, in this case MATNR use the control C keystroke to copy the data column in that column (in long lists you may need to page down to read the full list into memory before doing this)

You will get the following message if that needs to be done

image

Just page down and then control C to copy

Now exit back to the SE16N main screen and enter MARC as a table

image

For the ‘joining’ field (MATNR) select the multiple selection options

image

From the pop up select the paste from clipboard option to copy in the list from MARA.

image 

This will now query MARC only for the raw materials you are interested in. effectively giving you the results you are looking for. Obviously this works well for 75 entries but when the numbers get large this becomes more cumbersome, and if the query needs to be repeated multiple times then a SAP query might be a better option.

Views

Views are predefined table joins that SAP has provided or that have been created by a customer directly to speed up data retrieval for applications. For many common table joins there may already be views existing that can be queried directly from SE16N

To find a view that joins the tables you want you can use the SE15 transaction

image

From in the repository information system you can select views and additional information

This allows you to enter a primary and base table

image

The list may look something like below

image

You can either start with the descriptions or go through them one by one to see what the view definition is.

Double clicking on one of these views will show you the available fields and the join conditions

image

If you find a pre-existing view which has the correct join criteria and fields you need then you can go ahead and query that view directly in SE16N

image

This is the end of the first part of this SAP query Step by Step guide. Next time we’ll look in depth at infosets.

Post to Twitter

  1. Nicola McDonnell
    April 16th, 2010 at 01:07
    Reply | Quote | #1

    Hi Jerome
    Great SAP Query information! Your article is clear and concise and most of all very useful. I also highly recommend the use of SAP Query and SAP standard reports and views for experienced superusers as well. A lot of time and money can be saved by becoming familiar with all these tools.
    Thanks so much for sharing and I look forward to reading the subsequent parts.

    Nicola McDonnell

  2. Vichitra
    April 22nd, 2010 at 08:07
    Reply | Quote | #2

    Hi,
    I have gone through the article and found it very informative. I am also using SQ01 query but we got stuck at one place. i am using 3 tables – AUKF, AFVC & CATSDB but AFVC don’t have any common field so I have to fetch data from CATSDB and pass it to AFVC. I can’t join the fields with join, therefore I am writing code for all. But when I am executing the query, it is only giving one record. Whereas while debugging it is showing complete data. Could you please tell, how we can join the code with the additional fields, we have added into query.

  3. Roxx
    May 4th, 2011 at 14:52
    Reply | Quote | #3

    Built my infoset by joining tables mara, marc,mdma, mbew and selected only the fields (matnr, werks, mtart, maktx etc) I am interested it (did not join using all fields). Saved and generated infoset. Created the Query next using the infoset and those fields.

    Problem: When I test the query, the results are not as expected. I’m not seeing the material number, plant number, type etc but they were all include in the builds of the infoset and queries etc.

    Any ideas on what I may have missed?

  4. Krishna
    July 29th, 2011 at 09:47
    Reply | Quote | #4

    Hi,
    This is the most effective blog on SAP query I have ever found. Its very simple yet very informative. You have beatuifully covered all the concepts required to understand and build report using SAP Query. It was very helpful for me to design a new queries. I started without any knowledge on queries and now am able to build any report with SAP query. Thanks to your blog.

    Regards,
    Krishna

  5. prakash
    February 3rd, 2013 at 03:55
    Reply | Quote | #5

    I would like to know the transaction code for joining SAP table with structure. I want to create query joining botht in one.

    Kindly guide me.

    prakash