Qvinci's Report Styler allows Admins, Leaders and Managers to create templated reports (for P&L, Balance Sheet and CashFlow) which can then dynamically update across all clients/companies/entities in the account. The purpose of this feature is to save accounting professionals or corporate users time by allowing them to create standard reports which can be styled and customized, yet still utilized across many clients/companies/entities. The "Columns Chooser" feature allows users on the Qvinci Enterprise subscription to easily create multi period reports.
Start with a Basic Report
- When building a template which may be used across multiple clients/companies choose a Client Company to build the template off of by navigating to their Client Company in the top left navigation bar.
- From the Reports tab, ensure all entities are selected in the "Filter Entities" tab at the top center of the screen.
- Choose the P&L report, for example.
- The Frequencies section is what defines the columns in the report. Choose the desired frequency.
- Select the desired date range. A rolling/relative date range is suggested so that this report may automatically roll forward through the year.
- Choose whether to build the templated report off the SCoA or native CoA by checking/unchecking the Account Map box if it appears under Report Options (Advisor subscriptions do not have SCoA features enabled).
- Select the Build Report button.
- After the report generates on the screen select "Memorize & Style" button in the top right corner.
- Save the report with a template name (ie: P&L YTD by Month, etc)
- The report will open in the Report Styler feature and has just become a template.
Customize the Template
- Although the current client's data appears in the template, the report is actually a template when it is loaded in the Report Styler. Proceed by customizing the template in the following steps:
- Start by deleting all columns which contain financial data. Click on the column headers and right-click to "delete".
- After deleting the entity columns then begin replacing the accounts/subaccounts from the chart of accounts in column A with a dynamic macro from the Account Sorter option. Click on a cell between a header and total, click the Account Sorter button, and apply an accounts macro into the cell between the header and total accounts.
- Only the account type headers and account type totals should remain in the chart of accounts. Keeping just the headers and totals, and replacing the accounts with a macro from Account Sorter will allow for each client/company chart of accounts to dynamically populate when the template is refreshed or downloaded for them. For example, keep just "Income" and "Total Income" and replace all the income accounts that fall between the header and total with an accounts macro. This allows the current client/company income accounts to dynamicall insert between the header and total rows upon download, preview or refresh.
- Insert rows at the top and create a title for the template. (View this article to learn how to create contextual titles with macros.)
- Perform other styling edits as desired, such as font changes, font sizes, alignment, etc.
- Once styled then add a macro to drive the financial data columns. A columns macro allows users to set the financial data and frequency desired in the report. The columns macro(s) should be inserted in the first column where you would like the first financial data column to appear. The Column Chooser macro should always appear in a cell above where the first account in the chart of accounts appears.
- For a multi period report the user should insert one macro from the Columns Chooser into the desired column (such as Frequency = By Month, Date = Last Month) and then in the next column add another macro (such as Frequency = By Month, Date = Last Month, plus an offset of -12). This combination will yeild "Last Month" data in the first column, and the same month from the prior year in the next column.
- A user could also add a Totals column via the Column Chooser in another column.
- Be sure to click Save to save all edits to this template.
- The template might look something like this:
12. This new template may be downloaded from the grid screen by using the "Export" icon (second from the left in the grid control panel) to be exported to PDF or Excel or may be accessed in the future from the Libraries > Report Library tab. Data updates into a report template upon download or by clicking the "Refresh" button.
13. The new template may also be added to a Package on the Libraries > Package Library tab so that it becomes part of a reporting package which might contain other template reports and/or documents.
NOTE: Report templates may contain up to 1,000 rows and 50 columns. If you have reached the maximum amount and you get an error when attempting to add a row or column, scroll over until you have blank rows or columns. Highlight as many blank rows or columns that you need to add, right-click on them and click "Delete". You should then be able to insert more.