Create a Dynamic "by Entity" Report

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. A necessary feature which enables this capability is Qvinci's "columns macro". 

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. 

Start with a Basic Report

1. Choose a client / company who has more than one entity by navigating to the desired client / company in the top left navigation bar.
2. From the Reports tab, ensure all entities are selected in the "Filter Entities" tab at the top center of the screen.
3. Choose the P&L report, for example.
4. The Frequencies section is what defines the columns in the report. Choose "by entity".
5. Select the desired date range. A rolling/relative date range is suggested so that this report may automatically roll forward through the year.
6. Choose whether to build the templated report off the SCoA or native CoA by checking/unchecking the Account Map box.
7. Select the Build Report button.
8. After the report generates on the screen select "Memorize & Style" button in the top right corner.
9. Save the report with a template name (ie: P&L Last Month by Entity, etc)
10. The report will open in the Report Styler feature and has just become a template.

Customize the Template

1.  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:
2. Start by deleting all columns which contain financial data. Click on the column headers and right-click to "delete".
3. 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)
4. 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.
5. Insert rows at the top and create a title for the template. (View this article to learn how to create contextual titles with macros.)
6. Perform other styling edits as desired, such as font changes, font sizes, alignment, etc.
7. 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)
8. 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).
9. Be sure to click Save to save all edits to this template.
10. The template might look something like this:

Test the Dynamic Template

1. In the same template screen click the Preview button in the upper right corner. The template will update, based upon the client loaded in the top left corner, the chart of accounts and the entities.
2. 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.
3. 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. 
4. The previewed template or downloaded report might look something like this:

Have Questions?
We're More Than Happy To Help

Schedule a Call with Customer Success below, email us at support@qvinci.com or call us at 1-512-637-7337 Ext. 1 Available M-F, 7:30am-6:30pm CT and it is always FREE!

Schedule an Appointment with Customer Success