Qvinci's Report Styler allows Admins, Leaders and Managers to create templated reports which dynamically update across all clients / companies in the account. The purpose of this feature is to save accounting professionals, managers or corporate time by allowing them to create standard reports which can be styled and customized, yet still deployed to many clients / companies.
The columns macro allows for dynamic columns to be generated, based upon the client's / company's entities which are set up in Qvinci, so that a base report may be built once and deployed to all.
Video Tutorial: Creating a Dynamic Rollup Report
Start with a Base Report
- Choose a client / company by navigating to the desired client / company in the top left navigation bar.
- From the Reports tab, ensure the desired 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 base report. Choose a desired frequency. Qvinci Enterprise users will be able to swap the frequency easily after the report is built by using the "Column Chooser" feature.
- Select the desired date range. A rolling/relative date range is suggested so that this report may automatically roll forward through the year. Qvinci Enterprise users will be able to swap the date range easily after the report is built by using the "Column Chooser" feature.
- Choose whether to build the base report off the SCoA or native CoA by checking/unchecking the Account Map box, if it is present.
- 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 Last Month by Entity, etc)
- The report will open in the Report Styler feature and has just become available as a template.
Customize the Template
- Although the current client's/company'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 financial 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 dynamically insert between the header and total rows upon download, preview or refresh.
- After the %%accounts%% macro has replaced the accounts/subaccounts your template likely looks something like this:
- 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, Qvinci Enterprise users may utilize the Column Chooser to insert the Entities / Locations in the template. Otherwise, non-Enterprise users may 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 (or Column Chooser macro) only needs to be inserted one time into the template, into the column where you want the first financial data set to appear (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 for users who utilize the %%columns%% macro:
- Or the template might look something like this for users who utilize the Column Chooser feature:
- The above templates are ready for use at this time, or can be configured to show only rollups/total lines as well. You may wish to keep the existing template, for full reporting and simply use the "Save As" feature to clone a copy to be used for rollup/totals reporting.
- In the desired template simply right-click on all row headers except for the "Totals" and use the "Hide" option to hide all other rows. Using the above example you would want to hide "Income" and the accounts macro in the income section. This means that only "Total Income" should be left from the income section of the report. The purpose of hiding verses deleting the Income row and macro row is so that the data behind those accounts continue to update into the report. Should the delete option be utilized then the report will cease to update the Total Income line.
Test the Dynamic Template
- In the same template screen click the Preview button in the upper right corner.
- A second tab will populate the hydrated report, based upon the client/company loaded in the top left corner, the chart of accounts and the entities.
- Change the client / company in the top left corner and then migrate to the Report Library (where the templates are saved) and either open the template again or download it.
- If downloaded, the new client's data will automatically update into the downloaded report. Otherwise, if the template was opened click the Preview button in the upper right corner to see the new client's chart of accounts and entities appear.