what is adhoc query?can anyone explain it clearly ?why it
is used and how do u configure it?do they ask abt t-codes
infotypes tables and features,i mean do we need to remember
all of them?
- 1 Answers
- 8747 Views
- I also Faced
- E-Mail Answers
Answer Posted / chennakesavareddy
Adhoc Querry is used to create custom fieldsin infosets with
emphasis in SAP HR module.
We can create ADHOC QUESRRIES like below:
SAP Adhoc Query SQ01 SQ02 SQ03
This blog will focus on the capabilities of SAP Query
(a.k.a. Adhoc Query) and Creating Custom fields in infosets,
with emphasis on HR Module.
Most of the reporting needs of an organization can be
addressed through Adhoc Queries. For some specific
requirements we will need custom ABAP reports, and to create
custom fields ABAP knowledge is required.
First let’s refresh some concepts, processes and
transactions involved in the process.
Infoset (Tcode: SQ02)
Here we will include all of our tables or infotypes that
will provide us data for our queries. If you are trying to
create queries in HR Module then it is strongly recommended
to use a Logical Database to create your queries, because if
you are using HR Tables then joining them will be a problem
because of date fields (BEGDA/ENDDA).
Logical database will always use inner join and will skip
the rows that will not match in corresponding infotypes. For
example: You are trying to create a query with Pernr and
name from infotype 2 and Work Address from infotype 6. Then
the report will not include the persons that do not have a
work address. Only employees shown on the report will be
those that have a work address subtype of infotype 6
populated. This constraint is usually overlooked and it
results in wrong reports.
Steps to create a new infoset using logical database PNPCE:
1. Go to Transaction SQ02.
2. Give Infoset a description, give source of data for
infoset i.e. Logical Database or Tables etc.
3. If you have used logical database e.g. PNPCE then a
screen will pop-up to select the infotypes.
1. Select the infotypes and continue.
2. If you later want to add more infotypes then click on
menu item ‘Edit’ -> ‘Change Info-Type Selection’ and add or
remove the infotypes.
4. If you have used tables then only give one table name and
you will be brought to the screen where you can add more
tables and specify joins between tables.
1. To add more tables, click on ‘Join Tables’ and click on
‘Insert Tables’.
2. You can drag and drop the matching field from one table
to another to specify joins, you can also change the join
type from inner join to outer join.
5. There is an area ‘Field Group’ in infoset screen, only
the fields that appear here will be available while creating
queries. All fields from infotypes may not be available in
field groups, so you will have to manually drag and drop
them in appropriate field groups.
6. On the left hand side, under each node of infotype/table
you will see some fields in blank or blue text color. Blue
fields are those that are included in the infoset and hence
available for reporting in SQ01.
7. Save and generate your infoset. This is mandatory, as
when you generate the infoset, a program is generated with
the code reflecting your changes in infoset.
User Groups (Tcode: SQ03)
Once you have created the infoset you have to assign it to a
user group. To create a new user group go to transaction
SQ03 and create a new user group and assign users to that
user group. If you already have a user group then you do not
need to go to SQ03, on the main screen of SQ02 you can click
on button ‘Role/User Group Assignment’ to specify the user
groups tied to that infoset.
Ad-hoc Queries (Tcode: SQ01)
Ad-hoc queries (a.k.a. SAP Queries) are used for reporting
off the infoset created in previous steps. So go to
transaction SQ01 and create a new query:
1. Give name of the query you want to create and hit create
button.
2. You will be asked the name of the infoset that you want
to use.
NOTE: You cannot change the infoset later, so be sure which
infoset you want to choose, otherwise you will have to
create a new query to choose a different infoset.
1. There will be various options on next screen regarding
output format, title etc. which are pretty self explanatory
and we will not go into details of them.
2. A faster way will be to click on ‘Basic List’ button.
3. Expand the infotype in which you are interested.
4. You will see two check boxes in front of each field.
1. First box is to display the field on the report.
2. Second box is to display the field on the selection
screen to restrict the records based on some criteria.
5. Fields will by default have same order in which you
select them. It can be changed visa dragging and dropping on
appropriate location in right hand pane.
6. Each infotype will have ‘Additional Fields’ node which
contains the texts for codes like for Position, it will
contain a field text:position which will display the STEXT
for that position.
7. Save and Run your report.
Creating Custom Fields:
Now, we will come back from refreshing memories of creating
queries and jump into a little technical area.
We can create fields that will fetch data according to out
own logic, fields that are not provided by SAP. For that,
you will need ABAP access to the system, so if you are
working in Production or Quality system then most probably
you will not have authorization for that. If you need custom
development then best place is the Development environment.
We can then either transport the infoset from Development to
next systems or we can download and upload it in production
(which will again need production system to be opened for
some time).
To create a custom field:
1. Go to your infoset in SQ02 in change mode.
2. Right click on the ‘Additional fields’ node of the
infotype in which you want to create the new custom field
and click create.
3. Give it a name; this will be the name of the field used
inside the ABAP code. Select ‘Additional Field’ and click
continue.
4. On next screen, provide following information.
1. Long Text: This text will appear in the infoset for that
field, and inside the SQ01 query while developing.
2. Header text: This text will appear on top of the field as
header, when the report will be in run mode.
3. Format: Give the technical information about your field
or simply give the LIKE reference e.g. P0002-VORNA, it will
pick up the attributes from that field.
4. Sequence of code: This number will be sequence of the
code to be executed inside that field group. E.g. if you
have created 5 custom fields and you want one field’s code
to be executed before some other field so that some
variables are set-up then give the appropriate sequence.
5. Assign this newly created field to the field group, so
that it can be accessed in reports.
6. Right click the newly created field and click ‘Field Code’.
7. This is the place where you will put all your code.
8. If you have used Logical Database then appropriate
structures will be filled in automatically (e.g. P0002,
P0006 etc.) you can use same macros as you will use in your
normal HR Programs.
9. Once done with the logic, assign the value to the field
name, so that it appears in the query.
10. This code will be executed for each pernr.
11. Save and generate the infoset.
Tips:
1. If you are working on custom fields in infoset and have
opened a query in another session then the changes in custom
fields will not be seen in that query. You need to generate
the query again, go to tcode SQ01, select the query, go to
menu option ‘Query -> More Functions -> Generate Program’,
then go out of SQ01 transaction and then come back to SQ01
and select your query
2. If you have changed the data types of custom field in
infoset then you need to ‘Adjust’ the query, it is easy,
again come out of SQ01 and then go back, it will
automatically ask for updating the new changes. Click on
‘Automatically Adjust’ check box next to each field and
update the query. Otherwise you can go to menu option ‘Query
-> More Functions -> Adjust’.
Local Fields:
Local Fields are used in queries, if you already have your
infoset developed and want to include some simple logic then
Local Fields can be useful. They are usually a calculation
on another field, like for example if you want to add a
field for membership status depending on field Age
(Assuming you already have a field Age in your infoset, or
Age is another calculated local field).
We want a field that will return values based on following
logic:
If Age is less than 21, then return ‘Junior’.
If Age is between 21 and 65, then return ‘Adult’.
If Age is more than 65, then return ‘Senior’.
Go to menu item ‘Edit -> Local Field -> Create’
Field Label will be visible inside SQ01 query creation so we
can say it is for SAP HR Person who will be creating or
modifying the query, and Field Heading will be visible at
the top of the field when the query output is displayed i.e.
it is for end user.
You will find rest of the items on the above screen pretty
self explanatory, click on ‘Complex Calculation’ (there is
nothing complex here, do not worry).
Here is a screenshot on how to use the Complex Calculation
screen:
If you want to add more conditions then play with + and –
icons on the above screen. And, you are done with creating
local field.
Report Assignment:
A very very interesting area that is mostly ignored and has
a great potential for interactive reporting. To use this
feature, go to menu item ‘Goto -> Report Assignment’.
Lets say we are developing a simple report in HR, that
displays Personnal Number, First Name and Last Name. Your
report lists a lot of record and you want the user to have
power to double click on any employee row and then open up
the details in PA20. Isn’t that fascinating?
Another example would be that when a user double click a
query row, the data of that employee is passed to an ABAP
Program and that triggers some interface or whatever is
coded in that program.
Report Assignment is in fact making the reports interactive.
SAP provides various options for that. It will take many
pages to go through all of them, for the sake of
understanding we will go through the simplest of them.
Goto menu item ‘Goto -> Report Assignment’.
Click on ‘Other report type’ and you will see the available
options. Here are the actions that can be done when user
double clicks the row:
Select ‘Transaction’, on next screen you will be asked to
enter the TCODE.
Save the program, and run the report. And, double click on
any person’s record and you will be brought to transaction
PA20 with that person’s data populated on the screen. You
can also choose to go to PA30 so that user can change the
data quickly.
Shafiq Rehman is a techno-functional consultant in HCM module.
Add to: del.icio.us | Digg | Reddit
Please feel free to comment on my mistakes in this blog, I
will try to correct them. Thanks.
Comment on this weblog
Showing messages 1 through 6 of 6.
Titles Only
Main Topics
Oldest First
• Good work, how I can add additional structure.
2009-04-23 10:49:25 Daniel Gaytan Rodriguez Business Card
[Reply]
Hi, Good work in this blog, thanks a lot.
I had a question, how i can add additional structure.
And how can optimizer the custom fields, because, I added
some, and the report is very slow to generate the
information on screen, and the browser shown a time out
error; this reports are of e-Recruiting (web).
Thank you, good day.
• parameter with multiple selection
2008-10-19 08:02:39 Yossi Bencuya Business Card [Reply]
Hi
Thanks for the great explanation, i still have one question:
Do you know how to create parameter with multiple selection?
• Additional field in infoset
2008-07-07 06:36:45 Percy Carbella Business Card [Reply]
Hi Shafiq,
I don't know who turn to until I came across your blog.
Here's what I'm trying to achieve. We have defined custom
subtype in infotype communiction. We would then want to make
it appear in the same fashion as SYHR_A_P0105_AF_CELL.
Could you help ? Thanks ...
o Additional field in infoset
2008-07-07 07:26:09 Shafiq Rehman Business Card [Reply]
Hi Percey,
You need to create a custom field. Please see above in the
blog on how to create a custom field, and you will need to
put some code to pick the data from the IT0105 for your
custom subtype. Your ABAPer can help you in that, I can give
you starting code tips:
re_provide_from_last P0105 'CUST' PN-BEGDA PN-ENDDA.
Then assign P0105-USRID or P0105-USRID_LONG to the custom
field name that you created.
Other option is to put Communication Subtype on Selection
Screen and select only the subtype in which you are
interested, but I do not recommend this approach, why?, I
have discussed above that it may not give you all data and
your reports may be wrong.
 Additional field in infoset
2008-07-08 18:26:41 Muhammad Harie Muhammad Amir Chow
Business Card [Reply]
Hi again Shafiq,
The other day I was using someone else's PC, hence,
different name. Apparently, none of my ABAPers, not even the
senior ones, are used to SAP Query.
However, I managed to get some codings, very much like what
you provided above. Coding was done in Free Coding code
section. Upon generating, I would get error No code exists
for additional field ZSYHR_A_P0105_AF_XXXX.
| Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
What is a Client In Personnel Administration?
What is requesting additional headcount?
What is the difference between prebooking and booking?
What is the transaction code to create advertisements?
What time constraint 1 on an infotype ?
What is matchcode w used for in sap?
What are dynamic actions and how do you create dynamic actions in sap?
What is the use of payroll areas?
What are the basic processes in sap e-recruiting?
What is the use of logical database?
What is Career And Succession Planning?
At what level of the img. You can perform project documentation and status monitoring?
What is structural authorization?
What is report variant for off cycle activities, what do we do in this step ?
what elements make up the personnel structure?
