# SAP Query Tools - Link: https://www.lorenzo-datasolutions.com/sap-query-tools/ - Published: 2017-04-01T18:16:58+00:00 - Author: Anipe Abraham SAP Query Tools in its standard form, is designed so that an end user with no technical skills can create a report from scratch. It has five basic screens that an end user can go through to create a [report](https://www.linkedin.com/groups/8229784/). Ad Hoc Query, transaction code S_PH0_48000510, is very useful tool when producing reports from Personnel Administration and Organisational Management. Queries (or reports) in SAP are split into Query Areas, User Groups, Infosets, and Queries. Query Areas define whether the report is available cross client ([Global](https://www.facebook.com/LorenzoDataSolutions) Area) or only in the client that it is built (Standard Area). If SAP is set to Global Area, then it is not possible to see any reports built in the Standard Area and vice versa. SAP Query Tools User Groups define which groups of end users can see different infosets. Infosets define which infotypes and which fields within those infotypes you can report on. [Infosets](https://www.lorenzo-datasolutions.com/best-successfactors-online-training/) are generated via SAP Query, transaction code SQ01. Queries are the actual reports that an end-user can run within SAP, these can be set up in SAP Query or Ad Hoc Query. When you first enter Ad Hoc Query you need to select which Area, User Group and Infoset you want to use to run your report; ![SAP Query Tool00](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tool00") When running a report in Ad Hoc Query, you must specify which fields you would like to use as a selection and which fields you would like to output. The selection fields define which employees are [going](https://www.lorenzo-datasolutions.com/successfactors-online-training/) to appear in your report; for example, if you wanted to find a list of females above the age of 65 within a company you could use gender and date of birth as selection fields. SAP Query Tools ![SAP Query Too8](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF%21% F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Too8") The next step is to define what values of the gender field and date of birth are valid for the report. In the case of the above report, the gender key needs to be 2 – female and the date of birth must be less than the exact date [65 years ago](https://www.lorenzo-datasolutions.com/sap-successfactors-training-videos/). When a selection is made on the left of the screen the relevant fields will appear on the right of the screen, this is where the values are input. SAP Query Tools ![SAP Query To](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF%21% F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query To") Once you have selected your input values, you must define the time period that you would like to report on. SAP defaults to today’s date, but it is possible to define any period of time. This can be useful when you would like to report on who had an absence in April 2007, for example. To select the date parameters select the‘ Reporting period’ button at the top right of the screen. By clicking on the drop down box you can select the option relevant to your report. To select a certain period (April 2007 for example, select other period and input a start and end date. SAP Query Tools ![SAP Query](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF%21%F9% 04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query") When you have defined all the data and the date parameters required for your report, you need to select your output fields. For example, you might need to know the name, employee number and work place of every lady over the age of 65; the outputs to your report would be the fields with this information in them. ![sap](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF%21%F9%04%01% 00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "sap") Before you output your report it is good practice to check how many people have been selected by your selection fields, this normally gives a good indication as to whether your selections are correct – if you expect to have 10 people in your report and you have 150 then it is likely at least one of the selection fields is incorrect. To check how many people have been selected by your report, select the ‘Hit List’ button. ![SAP Query Tools1](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tools1") Here the report has selected one person, i.e. there is one female above the age of 65 in SAP. To see the employee’s name and number, select the glasses, to reset the hit list select the bin. When you are happy with the report you have built and want to see the output, select the output button at the top of the screen: ![SAP Query Tools2](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tools2") You will then see the results of your report. ![SAP Query Tools3](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tools3") You can now save this report to your PC by selecting the ‘Save to Local File button; ![SAP Query Tools4](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tools4") If the report you are building is likely to be run on a regular basis, for a headcount, then you should save it as a query, simply by selecting the save button at the top of the selection/output screen. You can then open the report and re-run it without needing to make all your selections again. If you need to run 2 separate reports and compare them to find out the intersection (employees in both reports), the union set (employees in either of the reports) or any employee who is one report and not the other then this can be done through SAP Query. For example you might need to know anyone who was sick in April 2007 and who isn’t a female over the age of 65, this could not be done simply by running one report. To compare 2 reports (report A and report B), you first must build report A and store the results and the build report B and store the results. To do this select the ‘Extras’ menu from the top of the screen and choose ‘Show set operations’. You will notice tabs appear in the right of the screen, and two extra buttons appear next to the ‘Hit list’ button. ![SAP Query Tools5](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tools5") These two buttons perform the same function as the ‘Hit list’ button, i.e. they work out which employees are selected by a report; but they also store the results of the hit list, meaning you can have a hit list for report A and a hit list for report B and compare them. The blue button stores employees into Set A and the yellow button stores employees into Set B. To store the 2 hit lists simply build report A and select the blue button, then build report B and select the yellow button. Now choose ‘Set operations’ ![SAP Query Tools6](data:image/gif,GIF89a%01%00%01%00%80%00%00%00%00%00%FF%FF%FF% 21%F9%04%01%00%00%00%00%2C%00%00%00%00%01%00%01%00%00%02%01D%00%3B "SAP Query Tools6") As you can see, report A (females over 65) has 1 person in it, and report B (anyone absent in April 2007) has 3 people in it. You can now choose one of the operations and select the Carry out operation button. The result of the operation will be stored in the ‘Resulting set’ – from here you can see that only 2 people were absent in April 2007 not including females over the age of 65. You can save the results to set A / set B again and do another iteration if required by selecting the ‘Copy resulting set’ button. SAP Ad Hoc Query: Additional Tips and Tricks You need to create User Groups and Infosets first (before you create any queries)– to which you will have to assign the relevant users. You can save an Ad Hoc Query and run it from SAP Query (transaction code SQ01) where you have SAP standard selection options. You can use the Ad Hoc Query to provide selected data for another specific report in SAP. Specify the report using the menu path Goto > Start Report. You can change the column order of the output by using the up and down keys of the Output tab. Select the relevant field and push it up or down. The old transaction code for Ad Hoc Query is PQAH. Don’t use in on versions 4.6b and above. Rather use the new transaction codes S_PH0_48000513. The new transaction code gives you an option to choose your user group and infoset and furthermore allows you to open and save different queries. In version 4.7 there is another functional area for PA. It is called PNPCE – you will recognise it. If you try and use the PNP InfoSet, the system prompts you to use the new one instead. You can use the Complex Operations which allow you to choose 2 different data selections and report on the differences/union etc. Complex Operations can be chosen by using the menu path View > Expert Mode within Ad Hoc Query. If you are using 4.5 you can create the “explorer” look and feel of 4.6 by changing the settings in menu path Help > Settings. Change the radio button from System Defaults to Active X. The changes take place from the next transaction call. If you are using version 4.6 and would prefer to use the old view – you can change the view format – under settings on the menu path. You will also notice, that any query set up in SAP Query, can be run from within Ad Hoc query. SAP Ad Hoc Query: Default Parameters The following items can be assigned to the User Parameters for Ad Hoc Query users to default certain parameters: AQB Default User Group AQS Default Functional Area 1. a. You need to create User Groups and Functional Areas first (before you create any queries) – to which you will have to assign the relevant users. b. Remember that Query Areas – whether Standard or Global – have an effect on the view of the reports that are with the User Group and Functional Area selected. c. You can save an Ad Hoc Query and run it from ABAP Query where you have SAP standard selection options. d. For ABAP Query – always set the list format columns to 200 to maximise the screen output. When executing the query, save a variant and then tick the checkbox “ execute only with a variant” to force the user to use the same parameters. e. Need to know the name of your query or run it with SA38, use the menu path Query More Functions Display Report Name. Generally the report name will be AQ50/SAPQUERY/ HRtest – where the AQ stands for ABAP Query, 50 is the client name, SAPQUERY/ HR is the user group and test is the report name. f. If you specify an ABAP Query to output in table format – then you will be able to sort, sum and find values in the resultant output. g. When creating a functional area which calls the PCH (org mgt) database, don’t populate the field “selection screen version”. Choosing either 900 or 901 from the drop-down options, will result in a different screen selection which does not allow the option of entering the evaluation path. h. When setting up a functional area which calls the PCH (org mgt) database, remember to include all the requiredPA and org mgt infotypes. i. When running a query in a functional area which uses the PCH database, ensure that you use the correct evaluation path which will offer the correct results– you can start off by using the evaluation path “o-s-p” which can be used in many queries. j. When selecting data in ABAP Query, it can happen that in order to be able to select on a particular field, you need to output it in the query. In other words, you have to stipulate the field as an output, in order to use it as a filter in your selection [criteria](https://www.lorenzo-datasolutions.com/payroll-control-center/). Seems weird but this applies to a few fields – one of which is the employee’s employment status. k. Having created a query using one of the above 2 methods, you can view the source code by looking at the saved report using transaction code SE38. You can modify and add to the code and then rerun the report in SE38 or SA38. It is a good way to start to learn how to write ABAP reports – although it is unlikely to be the most efficient way of writing the code. Remember that once you have added to the code, you might experience problems editing it in ABAP or Ad Hoc Query. l. You can change the authorisation of a user to allow them to create, modify or run ABAP queries. m. Users can be assigned to user groups which are in turn assigned to functional areas. It is possible to amend the profile assigned to a user – to allow them to use ABAP Query – without them having been assigned to a user group. n. When choosing the fields to be output in the report, modify the field widths. The SAP standard ones can be quite wide, which can cause the data to flow onto a second output line. Modify the field widths in your report. o. Once having saved the report, it is wise to execute it from the main ABAP query screen. You can execute it from within the edit mode, but this can cause “system dumps” in compiling on certain occasions. p. You can transfer the results from a SAP Query directly into MS Word using the mailmerge functionality within Word. This would allow you to create templates in Word with the fields stipulated in the query. When looking at the output result in ABAP Query, choose the download to MS Word and choose the mail merge option. Choose new file to set up your template in Word using the available fields and save the template in a specified directory. The next time you link to Word, choose the “existing file” option and point SAP to the directory and file where you saved the template. The mail merge functionality will then kick in. Excellent for employee specific forms etc. Useful Transaction Codes 1. a. SQ01 – ABAP Query b. SQ02 – Functional Areas c. SQ03 – User Groups Useful User Parameters 1. AQB – Default User Group 2. AQQ – Default Query 3. AQR – Default List 4. AQS – Default Functional Are This is probably one of the easiest reporting tools to use in SAP. The benefit of using Quick Viewer is that the user does not have to be attached to a particular user group and infoset. Quick Viewer is relatively easy to use. It can be accessed by using transaction code SQVI. **Report Developed in Quick Viewer and now required to be attached to a transaction code** You might have developed a report in Quick Viewer and now wish it to be brought up after entering a customer created transaction code. SAP Query Tools Queries in Quick Viewer are created uniquely for each user. You first have to convert the query to a SAP Query using transaction SQ01. For further information see OSS note 550764. Read the help for SQVI and SQ01 about the converting procedure. After having converted the report to a[ SAP Query](https://www.lorenzo-datasolutions.com/sap-successfactors-online-training-india/), you can attach your own transaction code to it. For more information look at OSS note 393160. 1. a. You need to create User Groups and Infosets first (before you create any queries)– to which you will have to assign the relevant users. b. Remember that Query Areas – whether Standard or Global – have an effect on the view of the reports that are selected with the User Group and Infoset. c. When creating an infoset which calls the PCH (org mgt) database, don’t populate the field “selection screen version”. Choosing either 900 or 901 from the drop- down options, will result in a different screen selection which does not allow the option of entering the evaluation path. SAP Query Tools d. When setting up an infoset which calls the PNP (Personnel Admin) or PCH (Org Mgt) database, remember to include all the required PA and Org Mgt infotypes. e. Remember when first creating an infoset, to check the fields that are defaulted by SAP. Delete the fields that are not used on your system and include ones that will be required when creating a query. f. Remember to change the “selected functional group” when switching fields “on” in the infoset. The fields are placed into the current “selected functional group” when turned “on”. SAP Query Tools g. Change the descriptions of the fields when setting up the infoset. The long text is used when choosing the field and the header is the field name that is output with the report data. h. If you wish to add more infotypes to your infoset, use the menu path: Edit > Add Info Category. i. In order to convert query objects (for example – after an upgrade), you need to run the program RSAQSUMM followed by RSAQUM40. SAP Query Tools j. To find all the ABAP Query reports in the system, use transaction code SE38. Type in the string “AQ*” (without the quotes) and change the maximum number of hits to include all entries. You will find all the ABAP queries have the notation AQ50/User GroupReport Name====== where 50 is the client number. k. You can use the report RSAQR3TR to transport user groups, infosets and queries. Use transaction code SE38 to run the report or look for the truck symbol (transport icon) on the relevant screen. SAP Query Tools Useful Transaction Codes 1. SQ01 – ABAP Query 2. SQ02 – Functional Areas 3. SQ03 – User Groups