Transcription

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesSDN Community Contribution(This is not an official SAP document.)Disclaimer & Liability NoticeThis document may discuss sample coding or other information that does not include SAP official interfacesand therefore is not supported by SAP. Changes made based on this information are not supported and canbe overwritten during an upgrade.SAP will not be held liable for any damages caused by using or misusing the information, code or methodssuggested in this document, and anyone using these methods does so at his/her own risk.SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content ofthis technical article or code sample, including any liability resulting from incompatibility between the contentwithin this document and the materials and services offered by SAP. You agree that you will not hold, or seekto hold, SAP responsible or liable with respect to the content of this document. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesApplies To:SAP R/3 SystemRelease 4.6C (SAP Web AS 6.10)SummarySAP Queries are also known as "Report Generating Tools." This primer describes various tool options forusing Query tools in the ABAP context: the “old” ABAP/4 Query tool is described in detail as it was theprototype for the newer query tools such as the SAP Query, the QuickViewer, and the InfoSet Query. Thetools will be described from two perspectives: the view of the end user who creates queries and generateslists and has little programming knowledge, and the ABAP developer who must handle certain administrativetasks of the Query environment.By: Marilyn Pratt, with ABAP/4 from Garry CarsonCompany: SAPDate: 01 Nov 2005Table of ContentsApplies To:.2Summary .2Table of Contents .2What is a Query?.3What is an ABAP/4 Query? .3Functional Areas in ABAP/4 Queries .4Additional Tables in ABAP/4 Functional Areas .8Steps to Add Additional Tables .9Additional Fields in ABAP/4 Functional Areas .10Selection Criteria and Parameters in ABAP/4 Functional Areas.12User Groups in ABAP/4 Queries .13ABAP/4 Queries .15Basic Lists in an ABAP/4 Query .21Executing the ABAP/4 Query .26Ranked Lists in ABAP/4 Queries.27Using Report/Report Interface in ABAP/4 Queries .29 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesWhat is an SAP Query? .32What is a QuickViewer Query? .33What is an InfoSet Query? .33Query Tools Summary Matrix.34Author Bio.34What is a Query?Queries in the SAP context are easy to use reporting tools that allow a user to interrogate data from one ormore database tables and report on the results as output. Query tools allow one to extract data fromapplications or database tables in the R/3 system and they enable the results of the extraction to be displayedin list format. Query tools generate ABAP programs which can run as “list reports” and output can bedisplayed in various modalities, depending on the query tool and the output selection of the end user. Theseoutputs can include lists on screens in table format, ALV grids, downloadable spreadsheets, anddownloadable flat files. The internal report generator creates an ABAP program corresponding to thedefinition of the list.Query tools all share these following elements: Queries are feed from a data source: depending on the tool, these feeds could be tables, joins,logical database contents (pre-defined joins for application areas), ABAP dictionary views,infosets and functional areas (also pre-defined data sources, administered and secured expresslyfor the purpose of generating query lists). Queries provide list definition including field sequence, sort order, selection criteria, ranked lists,summarized lists Queries provide output definition which, as mentioned above, can include simple lists onscreens in table format, ALV grids (end user configurable outputs), downloadable spreadsheets,and downloadable flat files.What is an ABAP/4 Query?The ABAP/4 Query was the precursor of the SAP Query (prior to version 4.6). Since it was used in earlierR/3 releases it originally was defined using a more alphanumeric environment than the SAP Query or theInfoSet Query. I have replicated that in this primer for historical reasons. The three main components ofABAP/4 Query are functional areas, user groups and queries. Functional areas determine the fields fromspecific tables which are available to be queried on. A user group contains a list of users, for example, theGL department or Sales department. Functional areas are assigned to a user group and the users within theuser group only have access to functional areas that have been assigned to their user group. Queries arealso created within a user group.The key to designing an ABAP/4 Query is knowledge of the fields that are needed and getting the functionalareas created correctly. The person responsible for creating functional areas needs to be familiar with thetable structures and fields. The ABAP/4 Query’s three components: a Functional Area, a User Group, anda Query, are each accessed by a separate transaction code, SQ02, SQ01, SQ03.The Data Source of the ABAP/4 Query was called a Functional Area. The functional area was a way to offera pre-defined set of data to the user, limiting the scope to a particular functional area of R/3, thus limiting the 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Querieskind and amount of the data to be queried. Since the system contains hundreds of thousands of data fields intables, views, and logical databases, the thinking went that not only could you restrict the user as to what dataand in which tables they could query against, but also who would be doing the querying. Therefore, anadditional administrative concept was created called the User Group which essentially defined those userswhose role would allow them to access the particular functional areas assigned to that user group.From an end user perspective, a Query is the way of creating different lists, or outputs using different layoutsfor each list. Each layout set is called a query and the query is specific to a user group and functional area.Each query has a one to one correspondence with a functional area.Generally speaking, it is a developer or administrator who would design the Functional Areas and UserGroups, while the user would design the layout of the queries and of course, execute them.In an ABAP/4 Query, the data source would need to be established in advance. Functional Areas couldinclude multiple tables, joins, views, or logical database utility programs, reading data from multiple datasources. In the context of a Functional Area, a developer or administrator could also define additional fields.ABAP/4 Query supports ranked lists, and summaries, as well as basic lists.As a general rule, non-technical users probably would find it difficult to work with the creation of additionalfields, table joins and logical databases, as well as coding additions. For this reason, functional areas andlogical databases are generally not the realm of end users.It is important to note that queries can create performance problems, so although the query tool is easy towork with, it is probably wise to control the usage of the tool in order to maintain optimal system performance.These are the main steps that need to be completed to create a query.1) Create a Functional Area2) Generate the Functional Area - Don’t forget this step!3) Create a User Group4) Attach the Functional Area to the User Group (This is done in the user group or the functional area)5) Create the QueryFunctional Areas in ABAP/4 Queries1) Under System Services ABAP/4 Query Environment Functional Areas, enter thefunctional area name and select a processing option. A functional area name is up to four charactersand may begin with a numeric or alpha character. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Queries2) Give the functional area a description.3) The authorization code is an optional field to work with authorizations.4) Fixed point arithmetic should be on! This field ensures that your decimal places are held in the correctplaces for calculations.5) Only one of the two following areas should be filled in:a. Functional area with logical database - If you are using a logical database, enter the twodigit database code and the application code. For a listing of logical databases, use thesearch available on the database field. This will list all of the delivered logical databases oryou may create your own logical database. Logical databases are groups of tables with therelationships determined within the logical database code. All tables related to general ledgeraccounts is an example.b. Functional area without logical database - In this section you have a choice to enter atable and additional table joins if necessary. Table joins are new in 3.0 and are consideredmore efficient in most cases than logical databases. You must include the main table for thefunctional area in the table field. If you are adding additional tables via the table joinfunctionality, select the table join radio button and hit the 'Define Join' button. Table joins aredescribed in detail below. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Queries6) Enter the tables to be joined to the main table and hit the enter key. Join the tables by selecting 2tables and hitting the 'Define condition' button. The conditions will appear on the right hand side of thescreen. Each condition must be further defined by placing your cursor on the join condition andselecting the down arrow that appears to the right of the line. When hitting the down arrow, amessage will appear to ask whether you would like to see proposals. Hit "yes" to this. The system willpropose what fields to join together in the two tables.7) Enter the tables to be joined to the main table and hit the enter key. Join the tables by selecting 2tables and hitting the "Define condition" button. The conditions will appear on the right hand side ofthe screen. Each condition must be further defined by placing your cursor on the join condition andselecting the down arrow that appears to the right of the line. When hitting the down arrow, amessage will appear to ask whether you would like to see proposals. Hit "yes" to this. The system willpropose what fields to join together in the two tables. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Queries8) To deselect one of the proposed join conditions, follow the menu path Edit Æ Join Æ Deselectrelation. To add field join conditions, enter the next set of corresponding numbers to the field fromeach table that should be matched.9) Once the conditions have all been defined, hit the green arrow key back to the join screen.10) Complete this for each set of tables to be joined.11) When finished, hit the save button.Note: Additional tables may be included in a functional area from the functional groups section. This is inaddition to logic database tables or table joins, whichever is being used in your functional area. This isdescribed later in this document.12) Hit the "Functional Groups" button to access the next screen. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesFields are not available for queries unless they are assigned to a functional group within your functional area.Functional Groups are logical groupings of fields for reporting.Create your functional groups by inputting a two digit code and a description in the functional groups sectionof the screen.The possible tables to select fields from are listed in the top right hand corner. These tables are either fromthe logical database or the table/table joins selected for the functional area. To select fields from a table,double click on the table and the fields for that table will appear in the bottom portion of the screen. To pagedown through the tables, your cursor must be in the table portion of the screen.In the space next to the fields in the bottom of the screen, put the two digit functional group code. This willassign the field to the group. To page down through the fields, the cursor must be in the Fields portion of thescreen. If you would like the entire screen to list the fields, tables, or functional groups instead of the defaultsplit screen, use Settings - Full Screen. To return to the split screen, use Settings - Split Screen.Additional Tables in ABAP/4 Functional AreasAdditional tables are added to a functional area if a field needed for queries is not available in the selectedlogical database or the specified single table/table joins section.Each additional table does create an additional “select” statement within the program for your query, so try notto add too many additional tables.The table the system will attempt to attach to the additional table is the highlighted table with the fields listedat the bottom of the functional group screen. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesTo attach a table, all of its key fields must exist on the table to which you are attaching. To determine whatthe key fields are, go to the data dictionary. The key fields are the fields with an "X" in the key column. Themenu path to get to the data dictionary is:Tools ABAP/4 Workbench Data DictionaryIn the example below, we are adding the account description table to the functional area. This table is notincluded in the logical database for general ledger accounts.Steps to Add Additional TablesHit the "SAP Table" button on the functional groups screen. Enter the table name and hit enter. The systemwill automatically fill in the matching fields for you. If the field names do not match, the system will leave thatfield blank and you will have to enter the field name or value in single quotes needed to make the connectionfor the two tables.A select single * statement goes to the table being attached when necessary and selects the one recordwhich matches the key fields. In the example, the system will go to table SKAT (or whatever table you areattaching) and search until it finds the correct chart of accounts, account number, and language. The fields inSKAT are then pulled into the query for that combination.The fields from the attached table can be found at the bottom of the field listing for the table to which it wasattached. These fields must also be assigned to a functional group. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesABAP Query does not access attached tables during the execution of the query unless fields from the tableare used within the query.Additional Fields in ABAP/4 Functional AreasAdditional fields may be added to a functional area for calculations or derived fields. For example, amountsare stored as positives and the posting key determines the sign of the amount. When reporting these amountsthey will show up positive unless you add additional fields for the sign to show with the amount.To add additional fields, hit the "Additional Field" button on the Functional Groups screen. Additional fieldsalso attach to the selected table in the functional area, so be sure you are on the correct table.Enter a name for the additional field (all one word or a string) and hit the "Enter" key. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesDescription of ScreenThe "Sequence of code" section specifies the order in which the tables are attached and the additional fieldsare added. If you attach a table and you need to add a field using fields from the attached table, the tablewould need to be sequence 1 and the additional field needs to be sequence 2. If additional fields and tablesare not dependent on each other, they can both be sequence 1.The next two fields are description fields. One is for the Query selection and the other is for the columnheading in the query."Format" can be entered in two ways. If the field format is known, it may be entered in directly. The type ischaracter, numeric, packed, etc. Length is the total length of the field. Output length is the display lengthwithin the query (including decimals and commas). Decimals are the decimal places. The second way to enterthe characteristics is to enter a field that is formatted like the field you are creating. The example above isBSEG-DMBTR, which is the local currency amount field on the FI line item table.The last section is for ABAP code. The five lines on the screen are the first five lines of code for theadditional field. To enter additional lines, hit the pencil and paper button.Additional fields also have to be added to a functional group. The additional field, just like the additionaltables, can be found at the bottom of the field list for the table it was attached to. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesSelection Criteria and Parameters in ABAP/4 Functional AreasAdditional parameters and selection statements can be added to the functional area if needed. Hit theappropriate button on the functional group screen to create them. If you are using a logical database, certainfields are included as selection criteria when executing a query.Suggestion: Execute a query on the functional area and determine if the selection field you want is standardwith the logical database. If not, add it to the functional area.Using Selection Criteria and parameters in the functional area can give you the following options: Search capability Make a field required for entry Set a default for the field Make a field highlighted in the selection screen Make a field display only in the selection screenThese options are entered in the "Additions" line of the selection criteria or parameters. The syntax is thesame as if entering selection criteria in an ABAP program. In the example below, the G/L account field isbeing setup as selection criteria for the functional area. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesThis is the last step of creating a functional area. Now the functional area must be generated! Select thegenerate button.User Groups in ABAP/4 QueriesUser groups are used to control access to functional areas and queries. Functional Areas and Users areassigned to user groups. When a user goes into ABAP/4 Query to create a query, the user group they are indetermines the functional areas they have access to for their query.System Services ABAP/4 Query Environment User Groups 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesThis screen is for maintenance of user groups and assigning functional areas to user groups.Note: Don't forget to assign functional areas to the user groups by hitting the "Assign Functional Area" button.To assign users to a user group, enter in their logon ID. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesABAP/4 QueriesTo access queries:System Services ABAP/4 QueryOn the initial screen, enter in the query name and select a processing option. If the wrong user group comesup, change it by Edit Other User Group or by hitting the "Other User Group" button (first button on the left). 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesIf the user group contained queries, they would be listed on the screen. A user can execute any query in hisuser group if he has the authorization. Query does work with SAP authorizations; for example, if a user doesnot have viewing access to cost center 1100 then he cannot access that information in a query.The following screens step through the creation of a query. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Queries1) A list of the functional areas available for your user group will appear. Select the functional area youwould like to use for the query. Only one functional area is allowed per query. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Queries2) The report title appears on the Windows title bar when the query is executed. This title does not printwhen the query is printed unless the Print list, "with standard title," button is on.Notes can contain any documentation for the query.The list format must match an existing print format in spool administration or the query will not print!3) To continue on to the next screen, hit the "Next Screen" button (the first button on the left). Once aperson is familiar with the screens, under the Goto menu option you may move to specific screenswithin the query.4) Select the functional groups that contain the fields needed for the query.5) Hit the "Next Screen" button. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet Queries6) Select the fields needed for the query. They are listed by functional group.The " /-" sign at the bottom of the screen pages down or up through the possible fields.7) Hit the "Next Screen" button to continue. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesNote: This screen is different then the previous screen! This screen only contains the fields selected for thequery on the previous screen.Select fields on this screen that should be run time options when executing the query. This means the querywill ask you to enter a Chart of Accounts or Account numbers when executing the query. These are notrequired to be entered at runtime, they are all optional. If the runtime fields are left blank at execution, all datais pulled.Remember: If the functional area is using a logical database, it will have certain selection fields. If you selectthe fields here and they are included in the logical database selections, the fields will appear twice atexecution.The selection text may be changed. For example if storing an old account number in the Group Account field,the text can be changed from "Group Account" to "Old Account Number." When the query is executed, itwould ask for "Old Account Number."The " /-" at the bottom of the screen pages up or down through the fields.This is the only screen that does not contain a "Next Screen" button. Hit the "Basic List," "Statistics," or"Ranked List" button to continue. All three may be created in one query. Basic list is the most common. This isa listing of the data selected for the query, sorted and totaled however specified. Ranked List will provide asummarized ranking of the selected fields based on whatever criteria is selected, normally an amount field.Statistics will provide a statistical listing based on an amount/quantity field. Basic List and Ranked List areshown in this document. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesBasic Lists in an ABAP/4 QueryThe line field specifies which line of the detail the field should be contained on. Based on the width specifiedon the first screen in the query, the fields may not fit on one line. Therefore, multiple lines may be specified. Ifa line is longer than the specified length for the query (on the header screen for the query), the lines will wrap.The sequence is the column the field will be placed in on the designated line.Sorting is done with the Sort field. There are subsequent screens to be filled in for each field a sort is done on.The total button determines that a total will be calculated for the field. This button is only valid for quantity andamount fields. If this button is turned on, a total will automatically be created for the whole query. Totals mayalso be calculated for the sort fields.The box button at the top of the screen works with the sort fields on the next screen. If you want a box drawnaround each new section for your sort fields, you must select the box button here as well as on the nextscreen.The compress data screen button at the top of the screen will compress the detail when executing the report.For example, if a G/L account history query is written, the compress button will suppress the detail on theinitial execution of the query. You have the option to show the detail by hitting the expand button on theexecuted query.Hit the "Next Screen" button to continue. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesThis screen is for setting up the sort fields defined on the previous screen.The "Desc" field is for descending order. The default is ascending.If the sort field should have text at the beginning of each new occurrence, select the Text button.The total button works with the total button on the previous screen. If a total button is turned on for an amountor quantity field on the previous screen, a total may be calculated for each entry of a sort field. The total is atthe end of each entry and serves as a subtotal to the report total. If the previous screen does not contain anytotaling fields, these entries are null.If a box should be drawn around each new section of the sort field, select the box field. The box field on theprior screen must also have been selected.A blank line or new page may occur between each occurrence of a sort field.Hit the ‘Next Screen’ button to continue. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesOn the sort levels, if the total or text button have been selected on the previous screen this screen will follow.This screen allows the total text and the top text to be modified if needed. The designates the size ofthe field. In the example above, the company's four digit code (0110) would appear. 2005 SAP AGThe SAP Developer Network: http://sdn.sap.com1

A Primer for Queries: ABAP, SAP,QuickViewer, and InfoSet QueriesEach line of detail created on a previous screen can be displayed differently. In this query we havedetermined two detail lines per record. The fields for each line are listed on the bottom half of the screen.The color for each line can be different. The descriptions for the colors may not make sense (headers in line 2does not mean line 2 is a header), it is a color scheme name only.A column header will be generated for the line if the "Header Line" button is marked. These headers can bemodified.The reference field creates a link between the lines for printing reasons. In this example, line 1 is a headerrecord for the account and line two is the detail. If the account doesn’t have any detail, the user does not wantto print the header line either. This creates a link between the two lines and line one would not print by itself.Line 1 is dependent on line 2.Blank lines may be inserted before or after each detail line. Enter the number of lines needed.The page header button will print the last detail line

using Query tools in the ABAP context: the “old” ABAP/4 Query tool is described in detail as it was the prototype for the newer query tools such as the SAP Query, the QuickViewer, and the InfoSet Query. The tools will be described from two perspectives: t