Reports in Filevine can easily be exported to Excel with a click of a button. Filevine also allows users to download a default Excel template, make changes, and upload a new, custom template. With a custom template, users can configure/create unique formatting, formulas, charts, graphs, PivotTables, and almost any other feature enabled by Excel.
Templated reports are useful for formatting commonly used reports like Auto-Reporting. By adding a custom template to a Collection Export, users can also export Collection Section data into a format that best suits their firm’s needs.
Filevine Fusion makes use of the same templating system, with a few changes to accommodate templating several reports simultaneously. See “Configuring for Filevine Fusion.”
Filevine report templates for normal reports, Collection Sections, and Filevine Fusion require a deep understanding of several Filevine features as well as Excel—in most cases, Filevine will simply export data into a sheet that will be used as a data source for Excel’s many functions. Read the following articles and consult Microsoft’s Excel help center for further support.
How to Set Up a Report Export
- Begin in the “Save Report” page of the Report Builder. Build and run a report, then click the “Save Export/Report” button to get to the “Save Report” page.
- Save your report. Save your report to bring up the “Export Options” button.
- Download the default template. Click on the “Export Options” button. In the “Export Options” window, click the “Excel” button under step 1. This will generate a default Excel template link that you can download, edit, and upload to use for this report. Click the generated link to download the template.
- Edit and upload. Make your changes, save the Excel workbook, and then click the “Upload Template” button from step 3 and upload your saved Excel workbook. Filevine will verify your template, so uploading may take some time!
Excel templates for fusion reports are downloaded, edited, and attached in the “Template” tab of the Filevine Fusion Advanced tool.
Configuring Excel Templates
Excel templates allow you to customize the way that data from a Filevine report is exported in Excel. The data can be exported to any number of sheets in the generated workbook, and can be used to power other Excel elements like tables, graphs, charts, PivotTables, and PowerQuery.
The downloaded default Excel template contains basic headers for every column in the report, along with an additional row containing the field replacement codes in a repeating range. The replacement codes may look similar (or be identical to) the replacement codes used for DocGen, but this is not always the case, so always download the default template to be sure.
In the first and last column of the default template, there will be a comment with a special code. These codes dictate the beginning and end range for the repeating data.
These comment codes can be moved to different cells in the template in order to specify a larger or smaller range of repeating data. The repeating data is also not restricted to a single row; you can expand the repeating range to several rows by moving the ##END_REPEAT code below the ##BEGIN_REPEAT code. The begin and end codes mark the top-left and bottom-right corners of a square that’s filled and repeated for every row in your report.
The replacement field codes can be repeated as many times as needed within the repeated range, and the entire range of repeated data, including ##BEGIN_REPEAT and ##END_REPEAT comment codes, can be repeated throughout the workbook, even on separate sheets. Field codes can also be used outside the repeating range of data, but in this case they will only be populated with data from the first row of the source report. This is especially useful for inputting data from your report export that repeats for every row, like for an OrgName column, or an amount total from a collections export.
Formulas and Formatting
Any formatting changes to the template and repeating range will be respected in the export. When Filevine exports data to the repeating range, it uses an insert function, so don’t be concerned about the repeating data overriding anything beneath it—that will be moved down.
Excel formulas in templates, both within the repeating range and outside of it, will function normally. Ensure that you’ve configured your formulas to adapt to varying row counts, with special attention to relative and absolute cell references. In most cases, formulas based on column references are the most appropriate.
Be sure to set the correct formatting for the field code. Filevine outputs data in a standard compatible with Excel—for example, instead of the test string “05/24/2019,” Filevine outputs the number “43609,” which will display as a date as long as the cell is formatted as a date. A cell’s display formatting is set in the Format Cells dialog in Excel. You can read more about cell formatting in Microsoft’s Excel documentation.
Tables
Filevine can export report data into an Excel table. Tables enable automatic filtering, a subtotal row, and a host of other benefits. You can read more about Excel tables here.
To output report data to a table, format the template’s repeating range into a single row with headers (or just use the default template), select the header row and repeating range, and format as a table.
Pivot Tables
Repeating ranges in Excel templates serve as excellent sources for PivotTables. From the pivot table menu, be sure to select the full columns of the repeating data in the template, since the number of rows could vary from case to case. Be sure to select “Refresh data when opening file” from the PivotTable options menu.
Collection Exports
Excel Templates can also be used for reports assigned to a collection for Collection Exports. If the “Export to PDF” option is used, only the first sheet listed in the Excel workbook will be exported, but all sheets and formulas will update prior to export.
Configuring for Filevine Fusion
Filevine Fusion Excel templates behave identically to Excel templates for reports, but add additional capabilities.
The default fusion template includes repeating ranges and field replacement codes for every report in the fusion, with a new sheet and repeating range for each report. These ranges and codes can be moved around in any orientation on any sheet, as long as the repeating ranges do not overlap.
Consolidating Data into a Single Table
In many cases, data that would normally be split between separate reports simply needs to be exported to a single table—like if you need to consolidate the data from several similar Collection Sections.
The repeating ranges from several reports can be stacked underneath each other like so:
Make sure that the equivalent field codes are all in the same column, and the exported data will appear just as if you had run a single report.
Referencing Data from one Repeating Range in Another
Data from one report can be exported to the repeating range of another report by using the following syntax:
{{reportID::fieldName}} → {{1234::fullname}}
Fields referenced in this way will only display data from the first row of the foreign report. To nest a repeating set of data within a repeating range, use a Word Template for your export.
Comments
0 comments
Article is closed for comments.