ReportVariable()

Function Summary

The ReportVariable function pulls data from a data source and inserts it into a spreadsheet. It is similar to the ReportFixed function because it takes row names as input in addition to column names. Only data that is mapped to both these row and column names from the data source will be inserted. Consequently, the range where data is inserted is defined by the boundaries of the RowDefRange and the ColDefRange. Data returned by this function can be filtered, formatted, and customized for a specific desired report.

This function differs from the ReportFixed function in that it will group the data based on the values defined in the RowDefRange argument. The grouping feature allows the data to be collapsed and expanded by category.

For an example of this function, see Lab Create: Inventory Variable.

Function Arguments

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

RowDefRange
A single column range that contains the values that map to the RowDefName field of the data source. Each value in this range will be matched to the same value in the RowDefName of the data source. Only these values will be populated with data after a pull action.

If the data source does not have a column "RowDefName", this function will look for a column name defined in the column in the ColDefRange and the row defined here in RowDefRange. For example, if row 2 is defined in ColDefRange and column B is defined in RowDefRange, this function will look at the value in B2 to map the data to.

If there are records in the data source that are excluded in this range, the data from those columns will be included in a [leftover] section after a pull action.

The last row of this section must be blank.

ColDefRange
Column definition range: A single row designating the names of the columns of the data source. Only data from these columns will be inserted. Range cannot 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. Inserted data will be formated 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 Data Portal. The values in these cells will filter the data that is inserted from the data source.

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 clear or pull action. 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 or clear action.)

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

PopulateDuplicateRowDefs
True: Inserts data for each appearance of a RowDefItem in the sheet.

False: Will only populate the last instance of a duplicate RowDefItem.

Excel Formula Bar Example

=ReportVariable("NorthwindVariable",B18:B54,2:2,4:4,Param(I14,I15),,FALSE,FALSE)

Function Composition

Embeddable Helper Functions

• jDataPortal
• Param
• jCombine
• jCombineIF