ReportRange()

Function Summary

The ReportRange function pulls data from a data source and inserts it into a single range within a spreadsheet. Receives a list of columns for input that will be pulled from the data source. Only data that is mapped to these column names will be inserted. Data returned by this function can be filtered, formatted, and customized for a specific desired data set.

For an example of this function, see  Lab Create: Customer Aging.

Function Arguments

DataPortal
The name of the Interject DataPortal that will be used as the data source for this function.

TargetDataRange
The multi-row range where the data will be inserted. Data is inserted starting at the first row. The last row defined here will be the bottom row below the data.

ColDefRange
Column definition range: A range designating the names of the columns of the data source. Only data from these columns will be inserted. Range can span multiple rows. Best practice is to use a range instead of whole rows.

FormatRange
The range designating the formatting style (e.g. font, color) for the inserted data. Data will be formatted using this range as a template. Formulas can also be included, which will be used for the inserted data.

Parameters
The cells designating the parameters for the DataPortal. The values in these cells will filter the data that is inserted from the data source.

UseEntireRow
True: New rows will be added for the inserted data. Rows in TargetDataRange (besides bottom row) will be deleted first.

False: Data will be inserted into the range defined in TargetDataRange. Cells outside this range will be unaffected.

PutFieldNamesAtTop
True: Will insert the column names on a row above the inserted data if ColDefRange is blank.

False: Will not insert column names.

RetainRowColumns
The list of column names that will be used as a key for retaining information within the inserted data range. This can be a comma delimited string or a cell reference of a single column name defined in ColDefRange.

All data in the column(s) matching the names in this argument will be retained after a a Pull-Run or Pull-Clear event. Consequently, such rows are also retained and not deleted. However, all data in columns that are not defined in this argument are blanked out after a clear action or overridden after a pull action.

This feature makes forecasting calculations to remain in the report instead of being cleared. For an example of this feature, see Using the Retain Feature.

InsertNewRowsWithin
True: If RetainRowsColumns is used, will insert new data in the TargetDataRange on a pull action by alphabetical order of the first key listed in RetainRowColumns.

False: Will insert new data below the already present data in the TargetDataRange on a pull action if valid columns are defined in RetainRowColumns.

UseTopSpacerRow
True: Inserts data starting on the second row defined in TargetDataRange. (Retains top row on a Pull-Run or Pull-Clear event.)

False: Inserts data starting on the first row defined in TargetDataRange.

Excel Formula Bar Example

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

Function Composition

Embeddable Helper Functions

• jDataPortal
• Param
• jCombine
• jCombineIF