Tuesday, August 29, 2017

SAP Query creation

ABAP Query Creation & Usage

In this document, we are going to discuss about SAP Query creation using query components and also covering below concepts.
  1. what is sap query and advantages
  2. Tools/Components for query creation
  3. Sequence of steps to create query
  4. Transportation across clients
  5. TCode creation for queries
  6. Authorization checks


1.   What is SAP Query:

SAP Query is a Tool to create reports without knowing ABAP programming knowledge. Here just we need to join the tables and drag and drop of required fields. It is very useful for functional consultants whenever they want some quick reports. In this sap query, all features available like download to spread sheet, alv report, basic list report as like normal abap reports.
               

2.   Tools/components for query creation

a.       Query from user group -- SQ01
b.      Info set creation     -- SQ02
c.       User groups             -- SQ03
d.      Query areas             -- SQ03

Terminology/Definitions:

Query :

             The Queries component is used by end users to maintain queries.
             You can carry out the following tasks:
                 Execute Queries and Generate Lists 
                 Define Queries
                 Change Queries

Datasource: 

            Data source is nothing but tables which are joined using primary key fields or other key fields


Infoset:

It is a view of data sources,it shows all available fields from data source to show in queries.

User groups:

The User Groups component is used to maintain user groups. The system administrator uses it to set up the work environment for end-users. Every user assigned to the user group is able to execute the query.Users are not allowed to modify queries from other user groups, although they may, under certain circumstances, copy and execute

Query areas:

A query area contains a set of query objects (queries, InfoSets, and user groups) that are discrete and consistent.
There are the following query areas:
Standard Area
1. Client specific, we cannot move to other clients
2. Query objects are not attached to the Workbench transports
Global Area
1. Cross client, we can move to other clients
2. Query objects are attached to workbench transports




3.       Steps to create Query

a.       Create user groups -- SQ03
b.      Create info sets       -- SQ02
c.       Assign info set to User group – SQ02
d.      Create query – SQ01
e.      Arrange layout – SQ01

     a.       First we need to check in which area we are going to create queries.
                        Go to SQ03àEnvironmentàQuery areas











Here I am selecting standard area as I want to create query which is client specific.
Now in main screen , enter the name of user group,description and click on create


Now in the same screen click on ‘Assign users and Infosets’ Button to add sap user id’s to this user group.
b.      Create Info set
Go to SQ02 Transaction and check the query area is ‘Standard area’ as our user group available in that area.
Enter Infoset  name and click on create button and enter all required details as below.
Here I want to see the PO Header details, so I keep data source as EKKO.






































After click on Enter, it shows below screen.






























By using above ‘Insert table’ button, we can add more tables(EKPO) and join with EKKO table. Here at least one common key field should be there.






Click on ‘Infoset’ button to maintain fields




 



·         Expand the tree on the left hand side to view the fields in each table. As seen on the screen, the left side of the screen shows the tables and the fields. The right side displays the field groups.
·         Now we need to assign fields to the field groups. These field groups will display in the SAP Query tool during reporting. Please note that only the fields that are included here will be available for field selection in the SAP Query Tool that uses this InfoSet as a data source.
·         Select the field group on the right side and then drag and drop the fields from the left side to this field group.



















           Click on save and generate button.

c.       Assign infoset to query group/user group.
Go to SQ02
Enter infoset name created above and click on ‘user group assignment’ button.
Select your user group created in 1st step and save.




















Click on extras button and go to selections tab for maintain selection screen fields ebeln and ebelp as below by using create button.


For adding click on create button and fill as below.
















Save and click generate

d.      Create Query using SQ01.
Go to SQ01 and give query name and click on create.












Click on create and select fields which are required in selection screen and output screen as shown below.

Click on save and enter query name as below
















Now we can change the sequence of fields and selection screen,type of report,number of records to fetch as well by using SQ01 Transaction only.
SQ01àQuery nameà changeà




































Set all properties and click on next button(green arrow) .
In the below screen, we can display more fields by selecting.






































Click on next
Here we can change the sequence of selection screen



























Save.

Now check the Query output selection screen as below.
SQ01àQuery nameàexecute























Add query/infoset to transport.












































Authorization check/Add authorization object:

Go to SQ02àInfoset screenàclick on extras button àcode tabàmaintain authorization object check(Below one is added for reference)

Tcode creation for query:

Go to SE93 and enter name - > select parameter transaction -> Transaction – START_REPORT -> In Default values – 
a. D_SREPOVARI-REPORTTYPE = AQ
b. D_SREPOVARI-REPORT = User group
c. D_SREPOVARI-EXTDREPORT = Name of Query








































Note: This works when user group is created query area – Standard.

In case of global query area, add ‘G’ in, D_SREPOVARI-REPORT = User group.
Example
 User Group = ZTEST_GRP
In SE93,
D_SREPOVARI-REPORT = ZTEST_GRP (for standard query)
D_SREPOVARI-REPORT = ZTEST_GRP  G (for Global query)

Add ‘G’ at the 13th character infront of D_SREREPOVARI-REPORT = Name of user group (Which is max of 12 letters) + G (at 13th spot to show its global query area)



No comments:

Post a Comment

Custom routine creation in SAP

                              Sales order pricing   Requirement : In sales order, for one of the condition type, value is populating ...