10.3.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 setup 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
Report 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
Report 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, selected from the list of existing sheet names, or linked to a report variable. Linking the worksheet name to a report variable can open up powerful connection possibilities where Report Authority could look (for example) for a sheet name that matches the current entity identifier or period.
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. Report 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 Report Authority which row to search to find the lookup value. In the example below, row 11 contains the entity IDs, and therefore the value for the Column Lookup Row will be 11 in this case.
The column lookup text value is the value which Report Authority will look for in the column lookup row. This lookup value can be manually entered, selected from a list of values found in the column lookup row, or linked to a report variable.
Linking the column lookup text to a report variable can open up powerful connection possibilities where Report Authority could look (for example) for a value that matches the current entity identifier or period in the 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. Report 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 Report 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 Report Authority will look for in the row lookup column. This lookup value can be manually entered, selected from a list of values found in the row lookup column, or linked to a report variable.
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, Report 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.