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
|
|---|---|---|---|
|
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. See example here . |
|
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 alphabatical 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 |
Function Composition
|
Formula
|
Example
|
Explanation
|
|---|---|---|
|
Formula
|
Example
|
Explanation
|
|
=ReportRang( DataPortal TargetDataRange ColDefRange FormatRange Parameters UseEntireRow PutFieldNamesAtTop RetainRowColumns InsertNewRowsWithin UseTopSpacerRow |
=ReportRange( "NorthwindCustomers" 12 : 13 1 : 1 Param ( C6 , C7 , C8 ) TRUE FALSE) |
← Data will be inserted between rows 12 and 13 ← The columns specified in that range will define what fields are returned ← First row of target data range will be used to format incoming data since this field is blank ← Cells C6 to C8 will be passed in order to the data portal to help filter data ← Entire rows will be displaced when inserting data ← The names of columns will not be added as the first row of data ← Optional field. Left Blank ← Optional field. Left Blank ← Optional field. Left Blank |
Example from:
|
From File
|
Worksheet
|
|---|---|
|
From File
|
Worksheet
|
| InterjectBasic | ReportRange |