Spreadsheet Report Template Functions
- 5 minutes to read
The Report Designer control allows you to use the built-in functions and operators available in the Spreadsheet control for calculation and data analysis in report template documents. The Report Designer also provides dedicated report-related functions that you use only in report templates. The FIELD and FIELDPICTURE functions are designed to create placeholder template fields that are replaced with actual data and images from the bound data source in the resulting report.
The RANGE function specifies a template cell expanded to a range in the generated report as a result of repeating a single template section for each data record in a bound dataset.
If the displayed template document is saved as an XLSX or XLS file, all formula expressions with the report template functions (template placeholder fields) are saved in the corresponding cells. Note that all placeholder fields outside of the template sections are not included in the resulting report document.
FIELD
A value obtained from a data field whose name is passed as the FIELD function’s parameter in a report template, replaces that function in the resulting report. Use the following syntax in a formula expression to add a placeholder data field to one of the template sections:
=FIELD(“Data_Field_Name”), where Data_Field_Name is the name of the data source field from which the value is obtained.
The Report Designer displays the entered placeholder field in a template cell as the data source field name enclosed in square brackets by default, with the database icon at the cell’s upper-right corner:
All placeholder fields in template cells are displayed as formula expressions if the Report Designer’s OptionsView.ShowFormulas property is set to True:
Additionally, you can hide both database icons and template section rectangles by setting the Report Designer’s Options.DesignView property to False:
FIELDPICTURE
The FIELDPICTURE function entered into a cell within a report template is replaced by a floating picture container that hosts an image obtained from a data field whose name is passed as the function’s Data_Field_Name parameter. This picture placeholder function uses the following syntax:
=FIELDPICTURE(“Data_Field_Name”, “Picture_Placement”, Target_Range, [Ignore_Aspect_Ratio], [Offset_X], [Offset_Y], [Width], [Height])
Picture_Placement specifies how an inserted image is positioned within the target cell range passed as the Target_Range parameter. The Report Designer control provides two picture placement options that can be passed as a case-insensitive string:
“Range” indicates that the inserted image is scaled to fit the target range. To localize this option, use the sRange resource string.
“TopLeft” indicates that the inserted image’s top-left corner matches the top-left corner of the target range. To localize this option, use the sTopLeft resource string.
The FIELDPICTURE function can accept up to five optional parameters, based on its syntax:
Ignore_Aspect_Ratio is a Boolean parameter that indicates if the original aspect ratio of the inserted image is ignored. If the parameter is unspecified, the image retains its original height-to-width ratio;
Offset_X and Offset_Y specifiy the absolute distances (in pixels) from the left and top borders of the target range, respectively. Both of these parameters are ignored if the Picture_Placement parameter is set to “TopLeft” or the Ignore_Aspect_Ratio parameter is set to FALSE.
The Width and Height parameters specify the absolute dimensions (in pixels) of the inserted image.
The following table lists multiple examples of the FIELDPICTURE function used in formula expressions:
Function Parameters Example | Description |
|---|---|
=FIELDPICTURE(“PictureFieldName”, “range”, C2) =FIELDPICTURE(“PictureFieldName”, “range”, A1:B2, FALSE) | Inserts a picture and scales it to fit in the specified cell range, locking the aspect ratio. |
=FIELDPICTURE(“PictureFieldName”, “range”, A1:B2, TRUE) | Inserts a picture to fit in the specified cell range without locking the aspect ratio. |
=FIELDPICTURE(“PictureFieldName”, “topleft”, A1) =FIELDPICTURE(“PictureFieldName”, “topleft”, A1:B2) | Inserts a picture, so that its top-left corner matches the top-left corner of the specified cell or cell range. |
=FIELDPICTURE(“PictureFieldName”, “topleft”, A1, 100) =FIELDPICTURE(“PictureFieldName”, “topleft”, A1:B2, 100) | Inserts a picture, so that its top-left corner matches the top-left corner of the specified cell or cell range without retaining the aspect ratio. |
=FIELDPICTURE(“PictureFieldName”, “topleft”, A1, 0, 100) =FIELDPICTURE(“PictureFieldName”, “topleft”, A1:B2, 0, 100) | Inserts a picture, so its top-left corner matches the top-left corner of the specified cell or cell range, and sets the image height to 100 pixels without retaining the aspect ratio. |
=FIELDPICTURE(“PictureFieldName”, “topleft”, A1, 120, 100) =FIELDPICTURE(“PictureFieldName”, “topleft”, A1:B2, 120, 100) | Inserts a picture, so its top-left corner matches the top-left corner of the specified cell or cell range, and sets the image height and width to 120 and 100 pixels, respectively. |
RANGE
The RANGE function allows you to specify the range into which the template cell is expanded in the generated report document as a result of repeating a template section for each data record. Use the following syntax to refer to the expanded cell range in a formula expression:
=RANGE(Reference), where the Reference parameter is used to pass the absolute reference to the template cell that is copied for each data source record in the resulting report document.
The RANGE function is particularly useful if you need to sum field values obtained from multiple records from the bound data source:
