6.2.4 Microsoft Excel - Lookup Adaptor
The Excel – Lookup adaptor is useful for interfacing with queries in Excel workbooks such as SAP Business Warehouse, Oracle Smartview or any other system with Excel interfaces. The adaptor can be set up to respond to the variable placing of data points due to changes in the query’s data point of view or the variable number of entities and accounts (rows and columns).
Automation panel
DPM Authority will list all the connections to spreadsheets defined in the Manage Connections dialog:
An Excel connection can be dragged from the automation panel and dropped on the report. If dropped onto an existing data object, its adaptor will change to reference the Excel connection. Once dropped, the Excel Lookup Data Adaptor form will appear.
Value Editor
An Excel – Lookup Table adaptor can be assigned or changed using the Adaptor control in the value panel:
The adaptor can be edited by clicking on the Configure Adaptor button:
Worksheet Name
DPM Authority will list all the worksheets found in the workbook defined by the connection under the Read In Values section of the dropdown list:
The worksheet can be either typed in manually or selected from the list of existing sheet names.
Column lookup row and text
Typical data queries often have entities or periods spread across different columns. The position of an entity or period may change when the data is refreshed due to new entities or periods, or a different point of view for the query itself. DPM Authority can handle this situation through the use of two values – the column lookup row and the column lookup value.
The column lookup row value tells DPM Authority which row to search to find the lookup value. In the example below, row 2 contains the entity IDs, and therefore the value for the Column Lookup Row will be 2 in this case.
The column lookup text value is the value which DPM Authority will look for in the column lookup row. This lookup value can be manually entered or selected from a list of values found in the column lookup row.
Row lookup row and text
Typical data queries often have accounts listed in the rows. The position of an account may change when the data is refreshed due to new accounts, values, or a different point of view for the query itself. DPM Authority can handle this situation through the use of two values – the row lookup column and the row lookup value.
The row lookup column value tells DPM Authority which column to search to find the lookup value. In the previous example, Column H contains the accounts, and therefore the value for the row lookup column will be “H” in this case.
The row lookup text value is the value which DPM Authority will look for in the row lookup column. This lookup value can be manually entered or selected from a list of values found in the row lookup column.
Auto-Apply parameters
In most cases, the only parameter that changes when automating data objects is the row lookup text. To prevent having to enter each and every parameter each time a new adaptor is created, use the Auto-Apply Parameters button:
When toggled, DPM Authority will remember each of the current values of each parameter and automatically apply them to each new adaptor created. To cancel, simply toggle the button again.
TIPS:
You can apply Excel data adaptors to the current selection with Ctrl + T. Use the context menu to select an entire table / row / column, then press Ctrl + T to apply data adaptors to all selected cells.
Drag and drop Excel automation nodes onto the table’s row/column headers to apply them to the entire row or column.
