Ad-hoc Query Designer: Defining New Queries 
Use
You use the Ad-hoc Query Designer to create and change your Web application queries.
The Ad-hoc Query Designer enables you to:
· Create queries by assigning characteristics from an InfoProvider to rows, columns, filters, and free characteristics, and including key figures from the InfoProvider in the key figure structure of the query.
· Restrict or filter key figures and characteristics
· Use predefined key figure structures and restricted or calculated key figures in the query
· Set or change query properties and key figure/characteristic properties in the query
· Create or change conditions and exceptions
For further information about restrictions on the Ad-hoc Query Designer in comparison to the BEx Query Designer, see Ad-hoc Query Designer.
You can also define new queries with a wizard that takes you step-by-step through the process.
Prerequisites
You have created a Web application containing the Ad-hoc Query Designer Web item.
Procedure
...
1. Choose Create New Query. You get to the Choose an InfoProvider dialog box.
2. Choose the InfoProvider that you want to use from your history or from the complete list under InfoAreas and then choose Transfer.
3. Select the characteristics that you want to use
a. From the dropdown box, choose Characteristics. The system displays the dimensions of the InfoProvider.
b. Expand the required dimension directory and click on the characteristic that you want to include.
c. From the context menu, choose the area (row, column, free characteristics, filter) in which you want to place the characteristic. The characteristic appears in the chosen area.
You can filter characteristics using a number of characteristic values to restrict the data set. Select the characteristic and from the context menu, choose Select Filter Value.
If a hierarchy is defined for the characteristic, an additional dialog box appears automatically before the Select Filter Values dialog box. Here you can choose whether you want to restrict the characteristic according to the flat list of characteristic values or using a hierarchy. If you select a hierarchy, the Select Filter Values dialog appears with a hierarchically structured selection.
4. Select the key figures/structures
a. From the dropdown box, choose Structures. The system displays the available key figure structures for the InfoProvider.
b. Select the key figure structure that you want to use and assign this in the context menu to the rows or columns.
If you are not using one of the key figure structures, assign the key figures to the rows or columns:
a. From the dropdown box, choose Key Figure. The key figures from the InfoProvider, including existing calculated and restricted key figures, are displayed.
b. If necessary, expand the directories of calculated or restricted key figures.
c. Select the key figure that you want to use and assign this in the context menu to the rows or columns.
You can also restrict the key figure, if necessary. From the context menu, choose Create Restriction
a. Select the characteristic by which you want to restrict the key figure: Expand the relevant dimension and select the required characteristic. You get to the Select Filter Values dialog box.
If a hierarchy is defined for the characteristic, an additional dialog box appears automatically before the Select Filter Values dialog box. Here you can choose whether you want to restrict the characteristic according to the flat list of characteristic values or using a hierarchy. If you select a hierarchy, the Select Filter Values dialog appears with a hierarchically structured selection.
b. Select the filter value that you want to use to restrict the key figure and choose Transfer.
The can mark the restricted key
figure selection as constant. This means that the selection can no longer be
changed through navigation or filtering while the query is running and is used
as a reference size. From the context menu of the restriction, choose Selection of Constants. For more
information about this function, see 
Selecting
Constants.
You can also display existing characteristics for key figures. From the context menu, choose Display Restrictions. If the list of restricted characteristics for key figures is empty, you can include characteristics in this list.
a. In the left-hand display frame of the InfoProvider objects, from the dropdown box, choose Characteristics, expand the dimensions and select the characteristic that you want to use to restrict the key figure.
b. From the context menu of the characteristic, choose Restriction. The characteristic is included in the list of restrictions for the key figure.
c. From there, using the characteristic context menu, you can choose Select Filter Values.
The query can contain only one structure. This structure has to be the key figure structure and be in the rows or columns of the query.
You cannot use key figures or key figure structures in the filter.
You cannot define exception cells, since this requires two structures.
You cannot create reusable structures or restricted or calculated key figures
If you want to see the documents available for an InfoProvider object (characteristic, key figure, structure, and so on), in the left display frame of the InfoProvider objects, from the context menu of the relevant object, choose Documents. You can also display existing documents or create new documents for the InfoProvider and the query itself. Select the name of the InfoProvider or query in the title, and from the context menu choose Documents.
This takes you to a list of existing documents. From here, using Additional Functions you can create a new document.
5. You can make settings in the properties, if necessary.
Select the characteristic, key figure, structural component or structure in the assigned area (rows, columns, and so on) and from the context menu, choose Properties.
Select the query name and from the context menu, choose Properties.
If you have not yet saved your query and therefore have not yet given the query a name, the name of the query appears in the title as <New Query>.
For more information, see:
Properties for a Characteristic
6. You can also define exceptions and conditions.
Select the query name and from the context menu, choose Exceptions or Conditions.
For more information, see:
Defining and Changing Conditions
Defining and Changing Exceptions
7. If you have assigned, for example,
- The key figures to the columns
- The characteristics to the rows
- Characteristics that you want to be able to add when you navigate in the drilldown to the free characteristics
- As well as characteristics according to which you want to restrict the data set of the query to the filter
and have made additional settings as necessary, you can check the query definition for errors. Choose Check.
8. You can review the structure of the query without data in the preview window. Choose Preview.
9. Choose Save. A dialog box appears.
a. From the dropdown box, choose the folder, favorites or roles in which you want to save the query.
b. Enter a technical name and a description for the query.
c. Choose Transfer.
You can execute the query without saving it.
10. Choose Execute.
If you do not want to execute the query immediately, choose Close. The Ad-hoc Query Designer is hidden, collapsed or does not change, depending on the setting you made for the attribute Action On Close (ON_CLOSE). If it does not change, it is still visible in the Web application and is expanded. The individual fields (rows, columns, filters, free characteristics) are empty. The query that you defined or changed is no longer locked but can be changed by other users.
See also: Ad-hoc Query Designer
You can also use and execute the query in a tabular view.
a. Select the query name and from the context menu, choose Properties. You reach the Query Properties dialog box.
b. In the Presentation tabstrip and under Data Formatting, choose Tabular View.
c. Choose the Tabular View tab page.
d. You can now determine the sequence of the characteristics and key figures used in the query in the columns by selecting the placing (1,2,3...) from the dropdown box of the InfoObject that you are using.
e. Choose Transfer.
f. Choose Execute. The query is displayed in tabular form, that is, the InfoObjects are shown only in the columns in tabular reporting. By assigning columns how you want, you can, for example, display a characteristic between two key figures.
Result
The query is displayed in a different frame or in other Web items in the current template, depending on how the attributes for the Ad-hoc Query Designer Web item are set up.