ReportVariable()

Estimated reading time: 12 minutes

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. Note: Entries for each group must contain an empty row above in order to populated with data correctly.

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

Function Arguments

Type String/jDataPortal()
Constraints Max 255 char
If Blank Function Error

Type Range
Constraints A single column range (not whole)
If Blank Function Error

Type Range
Constraints Max 500 columns. Must be a single row.
If Blank Will Error

Type Range
Constraints Must match the range dimensions defined in ColDefRange
If Blank Uses the formatting in the first row defined in RowDefRange

Type Param()
Constraints The values must match the order of the parameters in the data source (The order can be verified using the Validation Report)
If Blank Data is not filtered

Type String/jCombine()/jCombine_If()
Constraints Max 255 char
If Blank All data within the TargetDataRange is cleared on a clear action

Type Boolean
Constraints
If Blank False

Type Boolean
Constraints
If Blank False

Type Boolean
Constraints
If Blank True

Excel Formula Bar Example

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

Function Composition

Argument Name Example Mapping Explanation
Function Name =ReportVariable() The name of this function.
DataPortal "NorthwindVariable" This function will use the "NorthwindVariable" DataPortal for the data source.
RowDefRange B18:B54 The names found in B18:B54 will be used to map to the column names found in row 2 (ColDefRange). Only data that intersects these names will be inserted on these rows. Data will be grouped by the names found in this range.
ColDefRange 2:2 The names found in row 2 will be used to map to the row names found in row B14:B27 (RowDefRange). Only data that intersects these names will be inserted on these columns.
Format Range 4:4 The formatting used in row 4 will be used as a template for the inserted data.
Parameters Param(I14,I15) Cells I14 and I15 will correspond to the parameters in the data source to filter the inserted data.
RetainRowColumns "" Left blank to indicate not to retain any data in the TargetDataRange (B14:H15).
InsertNewRowsWithin FALSE This value is automatically set to false since RetainRowColumns is blank.
UseTopSpacerRow FALSE Data will be inserted on the first row (18) defined in RowDataRange.
PopulateDuplicateRowDefs "" Left blank to indicate to display duplicate RowDefItems

Embeddable Helper Functions