In this blog I would be discussing some basic concepts for Managing Queries in SAP. These queries are quite powerful and can be learnt by functional and business users which can give them pretty handy tool to cater to any reporting requirement they might have.
I have been using queries but often tend to get confused so I am including some easy steps which would help to understand
SQ01: It is the Query group which has got a lot of infoset Queries in it .You can also choose query area to be standard or Global;
Standard: Non transportable
Global: Associated with a transport
You can choose your Query Group from below and see the infoset queries associated with it
I choose Z_*
After you select the query group you would see the Query associated with it as below
My query name is Z_vendor . Select the query Z_vendor and click on infoset query to open the query
This now Opens the SQ01 Tcode which shows the additional logic for this query;
Or you can also go to SQ02 and open this query by clicking on change
Click On Join to add tables from which you need to read the data
Create a field group and drag and drop to add fields these are going to be either the Selection fields or will be the result column in your report.
Click on regenerate to finalize the query
If you click on ‘Extras’ button on the top it would take you to build any custom logic if needed for any of your fields. I added a Z field called ZNAME_ALL
Cliced on ‘CodingForAddition’ button
You can put the code here: This code is added to concatenate all the name 1 ,2 4 values into one field ZNAME_ALL.
Different Layout Options in SQ01 and SQ02
Lets now go back to SQ01 and click on ‘Infoset Query’
Here the system provides you an option to do below:
Now we will do remaining changes in SQ01 for this query:
You get the below 1st screen where you can put a variant .
There is also an option to ‘Execute only with Variant’
Screen2 :
Shows the filed group associated with the query:
You can choose which field you want to show up on the query
Finalize the layout
Click ON basic list and you find some more options to edit:
Linking to a Tcode
Once you are done with editing your query you can run now it but remember first to click on Query > More Functions > Generate program. The program gets generated and then click on Display report name to display the name of the program
Report Name is generated as AQZZZ_MM========Z_VENDOR =
Assign this now to a T code:
Using SE93 Either you can directly assign this to your Tcode but remember the program name can generate differently in different systems
The right way to do this would be to use SE93 option ‘Transaction with parameters’
Navigate below and enter these values;
D_SREPOVARI-REPORTTYPE AQ >> Queries
D_SREPOVARI-REPORT Z_MM G >> This should be 13 characters name of the query group with space and then G for Global query
D_SREPOVARI-EXTDREPORT Z_VENDOR_NAME >> Query Name
Execute the query to see the selection screen using the Tcode or running the program
Making changes in the layout using Variant
If you want to change the layout using variant then in SE38 choose the variant option
Select the variant and click on change
This screen determines default values for specific fields
Variant attributes decide the other mandatory field criteria. The field can hidden or make a field mandatory etc. There is a checkbox which says ‘Protect Variant’ this ing a variant means the variant can only be changed by the person who created it or last changed it.
Remember the layout you specify on the screen can also be managed using the layout button
Rearranged the column order;
Remember the settings which you apply on the Variant and layout for display overrides the settings done in SQ01
To remove the protected lock on variant run the program RSVARENT to remove protected variant
It will remove all variants in the program which are protected
Other useful information
Read more about queries in the help document as below
https://help.sap.com/saphelp_nw70ehp3/helpdata/en/4e/4c5be17e1f6d30e10000000a15822b/frameset.htm
Other Tocdes used are as below:
Transaction Target User Purpose
SQ01 End Users Editing and executing Queries and InfoSet Queries
SQVI End Users Editing and executing QuickViews.
SQ02 System administrators Editing InfoSets
SQ03 System administrators Editing User Groups
SQ07 Translators Language Comparison: Translation of text modules
Thanks for reading my blog. Hope you find the document useful . Do leave a comment if you think I can include something more or if I need to change something.
Regards
Vinita
Okumaya devam et...
I have been using queries but often tend to get confused so I am including some easy steps which would help to understand
- How to build a query
- What are the different options on queries like SQ01, SQ02 that can be used to modify queries
- Linking to a Tcode
- Making changes in the layout using Variant
- Other useful information
SQ01: It is the Query group which has got a lot of infoset Queries in it .You can also choose query area to be standard or Global;
Standard: Non transportable
Global: Associated with a transport
You can choose your Query Group from below and see the infoset queries associated with it
I choose Z_*
After you select the query group you would see the Query associated with it as below
My query name is Z_vendor . Select the query Z_vendor and click on infoset query to open the query
This now Opens the SQ01 Tcode which shows the additional logic for this query;
Or you can also go to SQ02 and open this query by clicking on change
Click On Join to add tables from which you need to read the data
Create a field group and drag and drop to add fields these are going to be either the Selection fields or will be the result column in your report.
Click on regenerate to finalize the query
If you click on ‘Extras’ button on the top it would take you to build any custom logic if needed for any of your fields. I added a Z field called ZNAME_ALL
Cliced on ‘CodingForAddition’ button
You can put the code here: This code is added to concatenate all the name 1 ,2 4 values into one field ZNAME_ALL.
Different Layout Options in SQ01 and SQ02
Lets now go back to SQ01 and click on ‘Infoset Query’
Here the system provides you an option to do below:
- choose the fields which you want to show for selection
- Choose the result column
- Provide default value for selection fields
Now we will do remaining changes in SQ01 for this query:
You get the below 1st screen where you can put a variant .
There is also an option to ‘Execute only with Variant’
Screen2 :
Shows the filed group associated with the query:
You can choose which field you want to show up on the query
Finalize the layout
- First checkbox makes the field available for selection
- ‘No’ Column decides the order of the field
- Selection text : You can provide the selection Text that appears on the screen
- ‘SV’: You can specify only a value on the selection screen — I have never used this
- ‘1L’: Specify a line only— I have never used this
Click ON basic list and you find some more options to edit:
Linking to a Tcode
Once you are done with editing your query you can run now it but remember first to click on Query > More Functions > Generate program. The program gets generated and then click on Display report name to display the name of the program
Report Name is generated as AQZZZ_MM========Z_VENDOR =
Assign this now to a T code:
Using SE93 Either you can directly assign this to your Tcode but remember the program name can generate differently in different systems
The right way to do this would be to use SE93 option ‘Transaction with parameters’
Navigate below and enter these values;
D_SREPOVARI-REPORTTYPE AQ >> Queries
D_SREPOVARI-REPORT Z_MM G >> This should be 13 characters name of the query group with space and then G for Global query
D_SREPOVARI-EXTDREPORT Z_VENDOR_NAME >> Query Name
Execute the query to see the selection screen using the Tcode or running the program
Making changes in the layout using Variant
If you want to change the layout using variant then in SE38 choose the variant option
Select the variant and click on change
This screen determines default values for specific fields
Variant attributes decide the other mandatory field criteria. The field can hidden or make a field mandatory etc. There is a checkbox which says ‘Protect Variant’ this ing a variant means the variant can only be changed by the person who created it or last changed it.
Remember the layout you specify on the screen can also be managed using the layout button
Rearranged the column order;
Remember the settings which you apply on the Variant and layout for display overrides the settings done in SQ01
To remove the protected lock on variant run the program RSVARENT to remove protected variant
It will remove all variants in the program which are protected
Other useful information
Read more about queries in the help document as below
https://help.sap.com/saphelp_nw70ehp3/helpdata/en/4e/4c5be17e1f6d30e10000000a15822b/frameset.htm
Other Tocdes used are as below:
Transaction Target User Purpose
SQ01 End Users Editing and executing Queries and InfoSet Queries
SQVI End Users Editing and executing QuickViews.
SQ02 System administrators Editing InfoSets
SQ03 System administrators Editing User Groups
SQ07 Translators Language Comparison: Translation of text modules
Thanks for reading my blog. Hope you find the document useful . Do leave a comment if you think I can include something more or if I need to change something.
Regards
Vinita
Okumaya devam et...