Show TOC

Reports Based on BW QueriesLocate this document in the navigation structure

Applies to: SAP BW (INA Provider) connections.

Each time you connect to a SAP BW (INA Provider) connection, you need to select a query.

Query and Query Alias

A "query alias" is a version in a workbook, of the query you have selected.

You can have various query aliases for the same query selected.

If the query selected does not contain any variable, the query alias represents the query.

If the query selected contains variables, the query alias represents the query and the variables you will select or have selected. For example, if the technical name of a query is “Budget”, you can create a connection to this query using the alias “Budget – France” and the second alias “Budget – Germany”, both using the same query but with different sets of variables.

When selecting a query in the Query Selection dialog box, a query alias is automatically created and given a unique name in the Query Alias area.
Note An alias is saved along with the connection when you save a workbook.
Note A query alias name is unique for all the workbooks opened at the same time. When opening two workbooks containing the same alias, here are the two possible behaviors:
  • If the alias has the same set of variables and variable values: both connections use the same version of the query.
  • If the alias has a different set of variables and variable values: the first opened workbook keeps its alias name unchanged and the second workbook is suffixed with the name of the workbook. Both connections use a different version of the query.
Note Note that you can use the query alias name as the connection name parameter in EPM functions. In case of conflicts between aliases, do not change the original alias name to the suffixed name of the alias in the EPM functions. Leave the original alias name.
Note You can copy a query alias from the Connections tab of the EPM pane, and paste it for example in an EPM function.
Opening Queries and Reports
You select a query in the Query Selection dialog box. All the queries that you have defined as favorites are displayed for selection.
Note

The queries are sorted by their favorite dates.

  • Select a favorite query. You can define a query as a favorite query by clicking the icon in the Favorite column.
  • If the query you want to work with is not part of the favorite list, enter a text that corresponds to the query name you are searching for. You can enter a complete query name or just a part of it. The search is performed on both technical name and description of the queries. Then, select the query and click OK.
  • Alternatively, you can enter the name of the query in the Selected Query area.
    Note For example, to work with the query named MyQuery, enter what follows: query:[MyQuery]

    Once you have selected a query, a query alias is automatically created and given a unique name in the Query Alias area. You can modify the name of the alias. If the query selected contains variables, the alias takes into account the variables you will select as a next step.

  • Specify what you want to be displayed in the EPM plug-in worksheet:
    • The report that is automatically displayed in the EPM plug-in worksheet is the query definition such as it is defined in BEx Query Designer. To do so, select the Create Report From Query option. If you do not select this option at this stage, you will be able to display the report as it is defined in the BW query later on by clicking Display Query Report in the Report Editor.
    • Or no report is displayed in the EPM plug-in worksheet. To do so, uncheck the Create Report From Query option.
Note
  • You can work with the default query. The default query is named after the InfoProvider. For example, if the InfoProvider is named InfoPro, enter what follows: query:[!!AInfoPro]
  • If you work with SAP Business Planning and Consolidation 10.1, version for SAP NetWeaver or higher, you can choose to open a workbook containing already defined reports. If a folder on the server contains reports, the Workbook Selection view is displayed by default in the Query Selection dialog box. You can still select the Query Selection view. The selection is persistent, meaning that the next time you log in, the last view selected will be proposed by default.
Once a query alias is opened in the EPM plug-in, the query alias is then displayed and can be selected in the second dropdown list in the EPM pane. This dropdown list displays all the query aliases opened for the current workbook.
  • You can select another query alias by clicking Select Another Query from this list in the EPM pane.
  • The selected query alias is the one that is taken into account for any new report you add by clicking the New Report button from the Ribbon.
Report Layout
Once you have selected the query alias, and provided that you have selected the Create Report From Query option in the Query Selection dialog box, the report that is automatically displayed in the EPM plug-in is the query definition such as it is defined in BEx Query Designer.
Note The resulting report display only the rows and columns whose intersections contain data.
As a general rule, all the characteristics specified in the "Default Values" area in BEx Query Designer are automatically part of the resulting report in the EPM plug-in.
  • The characteristics specified in rows in BEx Query Designer are automatically placed as dimensions in the row axis in the EPM plug-in.
  • The characteristics specified in columns in BEx Query Designer are automatically placed as dimensions in the column axis in the EPM plug-in.
    Note The members displayed in the row and column axis are the ones defined as default values in BEx Query Designer. You can change the member selection by using the Member Selector.
  • The characteristics specified as "free characteristics" in BEx Query Designer are automatically placed as dimensions in the EPM plug-in the following way:
    • the characteristics for which dynamic filters are defined in the "default values" area of BEx Query Designer are placed as dimensions in the page axis. The members displayed in the page axis are the ones defined as default values in BEx Query Designer. You can then make your own member selection by using the Member Selector.
    • the dimensions for which no dynamic filters are defined are placed in the context. The default member selection is displayed as All members in the EPM Context bar since no specific selection has been made in the EPM plug-in. You can then make your own member selection by using the Member Selector.
Note In the Connections tab of the EPM pane, the static filters defined for the current query are displayed. The static filters, defined in the "Characteristic Restrictions" area of BEx Query Designer, specify the dimensions or members that are not available in the resulting reports in the EPM plug-in.
You can modify a query report as needed by using the standard EPM plug-in features.
Restriction if a same member with different parents is defined several times in a hierarchy, in the EPM plug-in the member is retrieved only once in the hierarchy.

At any time, you can display the report as it is defined in the BW query later on by clicking Display Query Report in the Report Editor.

Note the reports do not inherit the formatting defined in the queries. To apply a format to a report, use the the dynamic formatting worksheet. See Dynamic Formatting .
All members: Member Selection as Defined in the Query

When a report is created based on a query, the members that are taken into account by default in the report are the ones that are defined as default values in the query.

  • All members is displayed in the page axis. The sum of members is performed.
  • All members is displayed in all the dimension dropdown lists in the EPM Context bar. The sum of members is performed.

You can either keep the default member selection or make your own member selection.

When you open the Member Selector, a sentence indicates that members, as defined in the query, is used in the report. If you do not want to use this member selection, make your own selection of members. The sentence will no more be displayed and the members you select are displayed instead.

Working with Context Members

When you open a query, as the query default member selection is applied to the resulting report, context members are not available.

If you want to work with context members, do as follows:
  1. For a dimension placed in the row or column axis, select the dimension dropdown list in the EPM Context bar and select another member instead of All members. As a result, the member selection of the query is no more taken into account for this dimension and you are able to use the context member.
  2. From the row or column axis, open the Member Selector for the same dimension. The member you have just selected is displayed in the list of available members and is indicated with (Context). To use this member in the report, select the member and click Add Members.
"Total" Member

The "Total" member is taken into account and displayed in the row or column axis of a report and in the Member Selector, if it has been specified in BEx Query Designer.

In the Member Selector, the option Show Totals is automatically selected or not, depending on what has been defined in BEx Query Designer. You can then select or deselect the option.

The "Total" member performs a calculation, based on:
  • The members displayed in the report.
    Note The hierarchy levels are taken into account in the calculation.
  • The key figure selected for the report, and therefore, the calculation that is defined for the key figure in BEx Query Designer (sum, mean, etc).

The placement of the "Total" member is the one defined in BEx Query Designer: top or bottom of the row or column axis.

Key Figures

Measures are gathered in the EPM plug-in into a dimension called Key Figures.

Key figures, calculated key figures and restricted key figures are supported and the result of the key figures are displayed in the reports.

For input key figures, all types of disaggregation and inversion formulas are supported in the EPM plug-in.

All members is not available for the dimension Key Figures. The first key figure in the list defined in BEx Query Designer is taken into account in the context and the page axis.

Using Free Text Key Figures

Using input key figures that support character strings, you can retrieve, modify, enter, transfer and save free texts for commenting purposes, just like for input key figures that support numbers.

Restriction for free texts:
  • The size of the text is limited and this limit is specified on the server side. The overall maximum limit is 250 characters but it can be less, depending on the limit specified on the server side. If you exceed the limit in the EPM plug-in, a message is displayed when performing a transfer or save.
  • Alt+Enter (new line in a cell) is not authorized.
Note
  • The inheritance behavior (for example, all children inherit the free text entered on their parent) depends on the disaggregation that has been defined on the server side. The text NOP (meaning: no presentation) may be displayed in a cell .
  • When a key figure cell has no value (empty), the character # is automatically added to the corresponding cell.
Member Selector

For more details about the Member Selector that you use for SAP BW (INA Provider) connections, see Member Entry Using the Member Selector (on Large Volume Data Sources and Specific Connections).

Input Options

Whether a query is defined or not as an input-ready query in BEx Query Designer, in the Sheet Options, the Use as Input Form option, whether it is checked or not, it will have no impact on the query reports.

In the Options tab of the Report Editor, the input option is not inherited from the worksheet options but it is inherited from the query settings:
  • If the current query is defined as an input-ready query, the Use as Input Form option is ckecked by default and you cannot uncheck it.
  • If the current query is not defined as an input-ready query, the Use as Input Form option is not ckecked by default and you cannot check it.