3 Simple Ways create Use AD-HOC QUERY in sap

The majority of an organization’s reporting requirements can be handled through Ad hoc Query.. Ad-hoc Query Queries are the actual reports that an end-user can run within SAP and configure in Ad-hoc Query. SAP offer three types of reports. The Fundamentals for straightforward reporting that include statistical functions like percentages, etc.- Ranked list: for easy report analysis

What is SAP ad hoc query?

SAP Ad-hoc Query is a comprehensive tool that generates reports based on the data input by QuickView. SAP Ad -hoc query can generate reports without ABAP programming. This means that SAP ad hoc Query hastens the process of creating reports. SAP already provides a custom-built environment for creating and running SAP queries as we are all aware. The principal purpose behind an SAP Ad hoc query is to create different kinds of reports, like basic lists and statistics, and ranked lists. Let’s take a look at the following aspects that are related to SAP Ad -hoc queries:

  • SAP Query
  • Query area
  • User Group
  • Infoset

Type of SAP Ad -hoc query

The SAP Ad hoc Query produces data in specific lists:

  • Basic List Like the order list, it provides a tidy list of elements.
  • Statistic-It is a special type of List that contains mathematical operations such as average or percentage information
  • Ranked List Provides lists that have been created for use in research.Lists like the ones below are examples of rank lists. The data is organized by customers’ ID, order numbers and ascending sales, just to only name just a few. or any other similar categories. The lists above are available in various formats like grids, tables, and spreadsheets that can be downloaded. Flat files are available for download.

Element of SAP Ad -hoc query

The elements that make up an SAP ad-hoc query work to take in and process the user’s query and create desired results. These parts are part of the process flow that is used to design and run the SAP Ad A-Clone query. It is an SAP Ad hoc Query is organized into the following four distinct elements:

  • Queries-Changes are defined. define and execute queries, and generate SAP lists
  • InfoSet Contains the data views utilized in the execution of SAP ad hoc Query InfoSet is a collection of views used to execute SAP ad hoc Query. A specific user Group is assigned an InfoSet. The importance of an InfoSet to a specific end-user is determined by a user’s role. If the user or role is relevant, then InfoSet will be used.If a group is associated with an InfoSet, the user can work with the InfoSet. In addition to InfoSet, InfoSet Query is also utilized to create reports.
  • User Group Helps to manage and manage user groups. Each user is assigned, one particular user group. This helps create and then execute and run the and execute the. Additionally, the user groups and roles may be added to an InfoSet with the help of an InfoSet’s Role/User Group/Role.
  • Translational/Query– Helps translate text created in an SAP ad hoc Query. It also allows us to compare the written text that is created in SAP.

How to create SAP Ad hoc query

Let’s go over the procedure for creating an ad query step by step. Please use the SAP Easy Access menu to get to the following menu path.

Menu SAP -> Tools >> Workbench ABAP>>Utilities> SAP Query -User Groups

Else, go to the tcode- SQ03.

Create a user group to specify which users will be allowed to run the Ad hoc query, here we have created a new users group as ZREPORT

ad hoc query-User Group
ad hoc query-User Group

Select Query Areas from the Environment menu, then the ‘Global Area’ radio option.

ad hoc query-Query Areas
Ad hoc query-Query Areas

SAP Query Area

Generally it is the place to keep the query. There are two types of query areas as below describe :

Global Area

Denotes that the query area global is designed and distributed throughout SAP. SAP system. In addition, it indicates the global query areas that are developed and distributed throughout the SAP system. In other words, global query areas are not specific to a particular Rather than being connected to the changing and transport organiser, the customer is connected to the changing and transit organiser. Global access and modification. Nowadays, the majority of fields used in SAP are global. Thus, queries that are made in the global SAP Ad area for ad-hoc queries are available to all customers.

Standard Area

 In a common query space, all query elements, including query, InfoSet, and User Groups, are designed and maintained for a specific customer. This means that the standardized and managed query area is created and maintained for a single client. The query objects aren’t associated with the workbench organiser.So, they’re not created and managed according to the most commonly used procedures for creating and run queries.The significant benefit of having a common query area is making queries tailored to specific customers.

how to build an ad hoc query
Work Areas

This will remain connected to an area that is functional. Save it with a brief description.

Infoset

User Group
User Group

SAP menu Path ->> Tools ->>ABAP Workbench ->> Utilities ->> SAP Query ->> InfoSet

Else use T-code SQ02 to create the infoset. Give the infoset a name and then click Create.

it is necessary to create the user group and designate specific users to the query. Each user group is assigned one InfoSet (or functional space), which is the basis for the database tables that SAP Query uses.When a query runs in the background, the ABAP programme also runs in the background, and the InfoSet acts as a template for the ABAP programme. When a user performs a query with an InfoSet, it is referred to as an InfoSet query. For instance, in SD modules, an InfoSet is used to perform ad-hoc reports; thus, the query it’s based on is Ad-Hoc Query. The InfoSet can also be utilized for a data sources to build and execute subqueries in the SAP query areas

infoset
Infoset

As we wish to report on sales data/credit data for the organisations in our example, a box comes with the option to pick all required information kinds. Provide a logical database or use multiple table. as shown below, provide the Infoset attributes. In this example, we’ll look at Zsale_info

Infoset : Data Source
Infoset: Data Source

By checking the boxes next to the information kinds, you can select them. The following screen of the produced infoset is given below.Then save your Infoset and then generate it.

 Infoset : Data Source selection
Adhoc Infoset: Data Source selection

The produced infoset next screen appears as shown below. Save the Infoset and then Generate it.

Infoset: Data Source selection
Ad hoc query Infoset: Data Source selection

Choose the previously created user group as ZSALE_INFO. Then save it.

infoset generation
ad hoc Infoset generation

Now we must associate an information set with the users group. From the Infoset start screen, select from the available list as shown below.

infoset role/ User group assignment
infoset role/ User group assignment

As ZSALE INFO, we’ve chosen Infoset roles and user groups in our examples. The new assignment has been saved

infoset role assignment
Infoset role assignment

Go into the operation SQ01 in order to make the Ad-hoc query display the data taken from the Infoset.

SAP menu >> Tools >> ABAP Workbench >> Utilities >>SAP >> Query >> Queries

Else ,T-code SQ01 to create Infoset Query

image 21
ad hoc query

Give the query the name (in this instance, ZSALE REP) and click Create.Select the necessary Infoset name and click on the next screen.

ad hoc query in sap
Infoset Query

As indicated below, enter the query title and other information. After saving, return to the main screen.

how to build an ad hoc query
Create ad hoc query

Now, go to the bottom of the page and click the Infoset query button.

image 16

The Infoset query window opens as below with Fields groups for output. In this section, we will define the report selection screen and the desired output.

ad-hoc query output
ad-hoc query output

When you expand the nodes to the left, select suitable fields for the selection criteria and presentation in report’s output.

ad-hoc query activate
Infoset Query report selection

Save the query with a name from the Query menu item.

ad-hoc query activate
ad-hoc query activate

Go back and Execute the query using F8.

how to run ad hoc query
how to run ad hoc query

After creating an SAP Ad hoc Query, the result of that query can be displayed in the following formats:

  • SAP List Viewer:Displays lists that have been sorted and filtered based on a specific property.
  • Graphics:Displays the results of a query as pie charts or Bar chart, in a graphic manner.
  • ABC Analysis:Displays the results of ABC analysis. ABC analysis is a concept that divides data into three categories: A, B, and C, each with its own set of characteristics. Category A holds the data with the highest priority or relevance (based on the stated criteria), category B contains the data with the second highest priority or relevance, and so on in ABC analysis. Consider the following scenario: you want to develop an ABC analysis report for the available materials in the stock depending on their worth.

Conclusion

Ad hoc queries are an efficient method of retrieving data at any time and whenever needed. Its versatility with real-time analytics capabilities and the ability to explore data in granular ways Ad hoc queries provide businesses with crucial data-driven insights that help improve decision-making in the current data-driven environment. If you use ad hoc queries, businesses can realize the potential of their data while retaining a an edge in today’s information-driven world.

Here are a few more articles you might enjoy reading.

Leave a Comment

error

Enjoy this blog? Please spread the word :)