Example:Professional Services for MS Excel

From prevero Competence Center
Jump to: navigation, search
Last update: 11/2011

How to create a detail list query?[edit]

You can insert detail list queries in Excel with the help of a PP table. For creating the query, use the PP table register tab in the PP control bar. The detail list query consists of two detail queries which are saved in the properties of a field reference. The result of the first detail query is inserted in the column/row of the field reference names and the result of the second detail list in the column/row of the field reference values.


PPServicesDetaillistetabell.gif


Inserting the detail query 1[edit]

Select the field reference for the first detail query (the result will be displayed in column 1) in the field reference list and add it to the field reference selection of the PP table. Select the detail list in the field reference property type. The properties of the field reference are adjusted; you can insert the query for the first detail query using the property detail Query. In the example above the field reference "Element Description (FR4760)" was inserted. Since the expense details are to be displayed, use the following query:

(FB2049 = 985601 OR FB2049 = 983042 OR FB2049 = 983043) AND FB4751 = -1 group by FB4760 order by FB4760

Tip: Click on the arrow icon to the right of the field and a big input field for entering the query will be displayed. Press the key combination CTRL+ENTER to confirm and close the input window.

PPServicesDetaillisteEingab.gif


Inserting the detail query 2[edit]

The second detail query is also saved on the selected field reference. Right-click the field reference in the field reference selection and select the menu item List query.

PPServicesDetailliste2.gif


In the following dialog you can define the field reference for the second detail query (the result will be displayed in the column 2) as well as the corresponding query. Please make sure that you use the key word SORTPOSITION(1) for sorting and grouping the second query. For this example select the field reference "Expense/Income Net Profit (2002)" and enter the following query in the properties:

(FB2049 = 985601 OR FB2049 = 983042 OR FB2049 = 983043) AND FB4751 = -1 group by FB4760 SORTPOSITION(1)


Activating the detail list[edit]

To display the details of a detail list query, use the button Detail list in the Control Group. Press this button to activate/deactivate detail lists.

Before drilling, deactivate detail lists; otherwise areas under a PP table may be moved unintentionally.

PPServicesSymbolleisteSteue.gif


How to create a calculated column/row in a PP table?[edit]

If you want to use calculated values (e.g. values that are not read out from the database), you can choose from two options:


Inserting a formula[edit]

If you want to use Excel formulas in a PP table, you need to work with a field reference. This field reference serves as placeholder for the formula; you can insert it using the button Insert formula.


PPServicesFormeleinfuegen.gif


The formula is to be added in the properties of the formula field reference. The calculated values are not saved in the dataset; they only display the results of the calculations.


Input form[edit]

If you want to calculate database values with the help of an Excel formula, use the field reference type InputFormula. Using this field reference type, the result of an Excel formula is written to a field reference and thus saved in the dataset.

To do this, add the field reference from the field reference list in the field reference selection to a PP table and choose the field reference type InputFormula. You can enter the Excel formula in the field reference property Formula.

Field references of the type InputFormula always return the result of an Excel formula, instead of the value saved in the dataset.


Formulas in PP tables follow the Excel rules and start with an equal sign "=". The following formula calculates the difference of two columns:

=Table1[[#This row],[Column1]]-Table1[[#This row],[Column2]]


How to access information of defined names?[edit]

There are certain display modes (year-to-date, display in thousands, currency conversion) that cannot be queried through field references. To display these modes, defined names (keywords) are available in Excel. This facilitates the readability of printouts.

To use these defined names, just enter the name in any Excel formula.


The following formula checks, if the function "Display in thousands" has been activated:

=IF(PP_Thousand="true","in thousands","")


The following formula returns the date of the last data update:

=PP_LastRefresh


Also see[edit]