Setup Reporting Metadata
Table of Contents
Setup Reporting Metadata
The RIO Education - Reporting Engine uses the following reporting metadata to define the structure of a report:
- Export Data Field.
- Export File Standard.
- Export File Type.
- Export File Section.
- Export File Config.
- Export Data Field Xrefs.
- Export Data Holder Config.
Export Data Field
This is to define the type, format, default value and the processing behavior for a field. An Export Data Field can be reused in multiple Export File Types that belong to different Export File Standard.
The configuration is categorized as follows:
Field Setting
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for the Export Data Field record. |
Field Type |
This often refers to the type of a Salesforce field to be exported for a report. The supported types are:
|
| Field length | The maximum data length of a field for a report. The data will be trimmed to the maximum length if it is longer than the specified length. |
| Fill Character | The filling character to be pad on the exported data up to the Field Length specified. This often requires for Position or Position with Sections file structure. |
| Format | The format to be converted to. This is only applicable to Date, Datetime, Alphanumeric field type. For Date and Datetime, please refer to the supported format. For Alphanumeric, please refer to the string format function. |
| Default Value | The default value to be exported if the targeted Salesforce field is empty. The default value defined is the final value to be exported without any further processing e.g., formatting. |
| Leading/Trailing Value | The filling character to be pad on the exported data up to the Field Length specified. This is similar to the Fill Character. However, this will take precedence if defined. This often requires for Position or Position with Sections file structure. You can define one if the reporting requires a different filling characters if the data is not empty. Otherwise, this should be left blank. |
| Justification |
The justification of a data. The supported options are:
|
Number Field Options
| Field | Description |
|---|---|
| Number Multiplier |
A multiplier to convert a decimal value to a non decimal value. For example, if the multiplier is 100, the data 20.55 will be converted to 2055. |
| Number Rounding Mode |
The rounding mode for a number type field. The supported options are:
|
| Retain Leading Zero | This is to retain the leading 0 of the original decimal value after conversion, e.g. 0.125 = 0125. |
Picklist Field Options
| Field | Description |
|---|---|
| Data Delimiter | The delimiter of a multi-valued field, e.g., the delimiter for a multi-picklist value is semi-colon ";". It is important to define the delimiter for a multi-picklist field if you plan to configure the Export Data Field Xrefs to convert the data. |
| Split Data | This is to split the multi-valued field (e.g., multi-picklist field) as a separate data row. |
| Remove Delimiter | This is to remove delimiter from the exported value, e.g., "a;b;c" > "abc". Note that this option can't be used in conjunction with "Split Data" as the option will always take precedence. |
Export File Standard
This is to define the supported reporting.
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for a reporting standard. The field is used by the reporting engine to identify the reporting standard based on the Reporting Configuration's Report Type and Reporting Export's Report Version e.g., <report type>_<report version>. |
Export File Type
This is to define the file types to be exported for a supported reporting.
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for the Export File Type record. |
| Export File Standard | The Export File Standard that a file type belongs to. |
| File Type | The name of a file type. The field is used by the reporting engine to identify the reporting file type and the file structure based on the Reporting Query's Report File Type. |
| File Structure |
The file structure for a file type. The supported options are:
|
| File Delimiter | The data column delimiter for a file type. This is only applicable to Delimited file structure. |
| File Extension | The extension for the export file. The default is txt. |
Export File Section
This is to define the sections for a file type. This is only applicable to file type with Position With Section file structure.
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for the Export File Section record. |
| Export File Type | The Export File Type that a file section belongs to. |
| File Section | The name of a file section. The field is used by the reporting engine to identify the data query based on the Reporting Query's File Section. |
| Sequence | The sequence of a file section for a file type. |
Export File Config
This is to define the structure for a file type and file section if there is any. The file configs for a file type and file section are the combination of multiple Export Data Field with Data Field Start Position defined in sequence.
File Config Settings
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for the Export File Config record. |
| Export File Type | The Export File Type that a file config belongs to. |
| Export File Section | The Export File Section that a file config belongs to. When defining the first field of a file section for a file type, the Data Field Start Position should always be set to 0. |
| Export Data Field | The Export Data Field setting to be used for a file config. |
| Object Name | The object API name for the source data. This should be the same as the Reporting Query's Target Object Name. If the Export Data Field acts a filler, this can be left blank. |
| Field Name | The field API name for the source data. This is used by the reporting engine to construct the data query. If you plan to use the denormalization feature, you can define the value as <child relationship name>[sequence].<field name> e.g., Contacts[1].FirstName represents the first name for the first contact record of an account. |
| Variant | The variant for a file config. This is used by the reporting engine to construct the data based on the Reporting Configuration's Report Variant. By default, all file configs with "Default" variant are used to generate the file. If you have a different variant is defined in the Reporting Configuration, both the "Default" and additional variant will be used to generate the file. |
| Data Field Start Position | The sequence of a data field for a file type and file section if there is any. |
Field Summarization Options
The field summarization options allow you to exported the summarized data row. Similar to the way how Salesforce aggregation SOQL works, you can define at least 1 group by field. If there is no group by field defined, there will be only 1 data row exported with the summarized data.
Please note that the data summarization is done by utilizing the Reporting Data Holder where the original data source will be queried based on the Reporting Query's Data Holder Generation query and summarized onto the Reporting Data Holder object.
When using summarization feature, the Object Name and Field Name should be set to rio_edrept__Reporting_Data_Holder__c and related fields.
| Field | Description |
|---|---|
| Aggregation Method |
The aggregation method to be used. The supported options are:
The AVG method is reserved for future implementation purposes. |
| Aggregation/Group By Field | The field API name to be aggregated or act as group by. Please note that the field defined has to be a field from the object defined in the Reporting Query's Data Holder Generation Query. |
| Aggregation Matching Value | The criteria to be matched with the record when performing the aggregation. You can define the value as MailingCoutry = United States if you plan to aggregate the result for all contacts with mailing country equals to United States. For date and datetime field, you need to define the date/datetime value according to your Salesforce locale. Please note that the field defined has to be a field from the object defined in the Reporting Query's Data Holder Generation Query. |
Export Data Field Xrefs
This is to define the cross reference table to convert an original data value to a value required by a reporting.
Please note that the Export Data Field Xrefs is defined a data field for a specific file standard. If you need to convert the same data field for a different file standard, you need to create a different sets of Export Data Field Xrefs.
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for the Export Data Field Xrefs record. |
| Export File Standard | The Export File Standard that a cross reference config belongs to. |
| Export Data Field | The Export Data Field that a cross reference config belongs to. |
| Data Field Option | The original value for a data field. |
| Data Field Target Value | The value to be converted to for a data field. |
Export Data Holder Config
This is to define the alternate data holder object for the reporting engine. By default, the Reporting Data Holder is used.
| Field | Description |
|---|---|
| Name (DeveloperName) | A unique name for the Export Data Holder Config record. |
| Object Name | The object API name for the data holder object. |
| Reporting Export Field | The field API name that represents the reporting export lookup field. |
| Report Type Field | The field API name that represents the report type text field. |
| File Section Field | The field API name that represents file section text field. |
| Identifier Field | The field API name that represents the identifier/group by text field. |
| Record Count Field | The field API name that represents the record count number field. |
If you plan to use an alternate data holder object, please ensure that the following fields are created on the object:
| Field | Type | Description |
|---|---|---|
| Reporting Export | Lookup | Lookup to reporting export object. The generated record will be linked to the reporting export record. |
| Report Type | Text | The report type will be tracked in the generated record. |
| File Section | Text | The file section will be tracked in the generated record. |
| Identifier | Text | This acts as the group by key for a data row. |
| Record Count | Number | This stores the total record count for all records that are being grouped under the same group by key (identifier) regardless of the Aggregation Matching Value defined in the Export File Config. |