In addition to the option that allows you to export grid results to a CSV file, Advantage allows you to export grid results to a spreadsheet using a pre-defined excel template. This feature comes handy if you want quick reports from a page that stores different types of data.
For example, on a page such as Fixed Asset Registry in Advantage Financial, someone wants a quick report from the page (one for vehicles and another one for buildings). The Export to CSV action gets all fields that belong to different types of data shown on the page. Alternatively, you can build templates applicable to each unique type of asset with relevant information, and then use the Spreadsheet option and choose the corresponding template to download filtered search results to a spreadsheet.
Although the Spreadsheet action is available on all Advantage grids, it is hidden by default. You need to activate the action using a file-based extension on an application page to use the functionality.
Advantage allows you to specify the Excel template for download in the following ways:
Using application metadata: In this approach, you can set download template name and download file name via the Spreadsheet action when activated for a specific grid. This option allows a single template per grid.
Using Reference Page: In this approach, excel template details for an application page are added on the Grid Download Template (PDTPL) page and metadata values not populated on the action. Refer to the “Grid Download Template Setup” topic in the CGI Advantage System Administration Guide for more information.
If template details are added in the metadata and on the reference page, the system uses setup defined on the reference page ignoring metadata values. This is the recommended approach and allows multiple download templates for the same grid to download unique types of data.
If the Spreadsheet action is used without specifying a template using either of the options, the system uses a default template style for the downloaded spreadsheet file.
To export records in the grid to a spreadsheet using reference page configuration, follow these steps:
Define excel template.
Prior to configuring an Excel template using available options in Advantage, the System Administrator must create the template as per site needs.
Save the file in the required version of XLSX. If the excel template contains macros, save it as an .XLSM file.
After finalizing the template, the System Administrator must place the template in the reserved directory location on the server.
The directory location (that is path) of the templates is specified in the server.conf file. The templates are placed under a sub-directory named exactly same as the UI Package value defined for the application page on the Application Page Registration (APGS) page.
Here is an example:
Template name created: Fixed_Asset_Vehicles.xlsx
Page Code: FARHDR
UI package for Page code FARHDR: Fixed_Asset
Directory location on server.conf file: pageResourcePath = apps/CGIADV/RTFiles/fin/PageResources
In the above example, template Fixed_Asset_Vehicles.xlsx must be placed under: apps/CGIADV/RTFiles/fin/PageResources/Fixed_Asset folder on the server.
Once a template is uploaded on the server, an entry must be made on the Grid Download Template (PDTPL) page.
You can define multiple templates for the same page to download unique types of data stored in the grid.
Navigate to the application page for which the template(s) is configured for and select the following from Grid Actions.
Export All > Spreadsheet to export all records in the grid using a template including rows displayed behind the pagination.
On selecting the action, the user is navigated to the Grid Templates selection page.
Select a desired template record in the grid and then select the Generate File row action.
Grid results are downloaded to the default download location on the local computer using the template selected. The downloaded file is named exactly as the download file name is specified on the selected record.
The MAX_DOWNLOAD_LINES (Max Download Lines for Download) parameter on the Application Parameters (APPCTRL) page sets the maximum number of lines that can be exported to the spreadsheet. If the total number of records exceed this limit, then a message (“Results are limited to Application Parameter value”) is displayed in the generated spreadsheet file. Narrow your search results and then select the Export All > Spreadsheet option again. Note that row and column limits are also set by Microsoft Excel and other similar products.