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. A necessary feature which enables the dynamic updating capability is Qvinci's "columns macro".
The columns macro allows for dynamic columns to be generated, based upon the entities which are set up in Qvinci for a particular client/company, so that a base report may be built once and utilized across all.
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: %%accounts%% (macros are case sensitive)
- 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 the accounts macro 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 the 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 the columns macro so that dynamically updating columns will appear. The columns macro brings in the Frequency option that was selected when the base report was created. The columns macro should be inserted in the first column where you would like the first financial data column to appear: %%columns%% (macros are case sensitive)
- The columns macro only needs to be inserted one time into the template, into the column where you want the first financial data set to apepar (do not insert the macro into each column).
- Be sure to click Save to save all edits to this template.
- The template might look something like this:
10. 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.
11. 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 500 rows and 500 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.