ReportRange()

Estimated reading time: 8 minutes

Function Summary

Directs data into a single range of a spreadsheet with options for formatting, formulas, and columns. ReportRange() is designed to populate a specific area in a spreadsheet with a list of data returned from the DataPortal.

Function Arguments

Parameter Name Description Default Optional
DataPortal This is the name of the Interject DataPortal which has been set up to connect to data.   NO
TargetDataRange Designate the range to place the data on the worksheet. It must include at least two rows. Fetched rows are inserted in between.   NO
ColDefRange Column Definition Range defines which columns from the database will be used. It can span multiple rows. Best practice is to avoid selecting entire row because it reduces column quantity and conserves memory usage. The maximum number of columns is three hundred.   YES
FormatRange This Range defines the format (eg. font / color) or formula to be copied down to the TargetDataRange. If left blank, the first row of the TargetDataRange format is applied.When a ColDefRange spans multiple rows, a FormatRange must be used and must match the number of rows selected in the ColDefRange.   YES
Parameters Select cells which will be used as parameters for the Data Portal. The parameters must be selected in the same order as they are specified in the Data Portal setup. The order can be checked by using the validation report. A report builder will also list the parameters correctly to help jump start building a new report. The cells must use Interject’s Param() function.   YES
UseEntireRow If True, Interject will insert a row instead of a range in order to control displace of cells. FALSE YES
PutFieldNamesAtTop If the column headers are unknown, this can be used to insert them as the first row. This will only function if ColDefRange is not defined.   YES
RetainRowColumns This can either be a string noting the names of the columns in the ColDefRange or a range that points to the ColDefRange items. The noted columns will be considered the key for the inserted row. When using this feature rows are no longer cleared from the target range. But data is cleared still without deleting the rows. The columns noted as retained are not cleared and all other ColDefItems columns are cleared. The purpose of this feature is to allow a trend of numbers to lead into calculations for forecasting. On a repull of data, it is critical the calculation formulas are not removed, but only data that is shown in the trend columns are updated.   Yes
InsertNewRowsWithin Defaulted to false. If RetainRowColumns are specified, the system will automatically add new rows when the key (represented by the noted columns in RetainRowColumns) is not present in the target data range. These new rows will be added to the bottom of the data range to ensure the users calculated formulas are minimally affected. If InsertNewRowsWithin is true, the new rows will be inserted within the target range based on the alphabetical order of the key. At this time, the ability to further refine the order is not allowed. FALSE YES
UseTopSpacerRow For the TargetDataRange, adding a top spacer row inserts the data on the second row of the range and allows the first row to retain its formatting. This is used in cases where rows are inserted into the range and the user needs the ability to place the row at the top of the target range.   YES

Excel Formula Bar Example

=ReportRange("NorthwindCustomers",B14:H15,B2:H2,B4:H4,Param(C7,C8,C9),FALSE,FALSE,,FALSE,FALSE)

To see an example of this function in use, visit the Lab Create: Customer Aging

Function Composition

Argument Name Example Mapping Explanation
Function Name =ReportRange() The name of the report formula
DataPortal “NorthwindCustomers” The name of a DataPortal that is configured to connect to a Northwind demo database
TargetDataRange B14:H15 Data will be inserted between rows 14 and 15 and will only extend from column B to column H
ColDefRange B2:H2 The column names specified in this range will determine which data fields are returned from the data source
FormatRange B4:H4 The formatting specified in this range is copied down to the inserted rows in the target data range
Parameters Param(C7,C8,C9) Cells C7, C8, C9 are specified to pass information to or from the DataPortal.
UseEntireRow FALSE INTERJECT will insert a range into the sheet and will not insert full rows
PutFieldNamesAtTop FALSE Since this is false, the names of the data fields are not returned as the top row of the target data range by the data portal
RetainRowColumns ”” Optional Function argument left blank meaning no rows are retained upon a report clear
InsertNewRowsWithin FALSE Since there are no retained columns this is set to FALSE
UseTopSpacerRow FALSE Data is not inserted on the second row of the range, formatting is no longer defined by the first row of the target data range