Velixo’s Report Distribution Blueprints for Multiple Dynamic Worksheets
One of recent improvements making ERP reporting with Velixo even more straightforward and convenient, Worksheet Blueprints, is a major addition to our customer-acclaimed Report Distribution Blueprints functionality.
Let’s take a look at a business case that inspired its onset!
Suppose your company has a P&L statement built with Velixo, parameterized by branch and financial period:
What would you do if you want to analyze this P&L sheet for every reporting period of the year and every single branch of the company?
Well, first of all, with Velixo’s lightning-fast Smart Refresh, it is always possible to tweak the branch / period parameters directly in the cell and review the result in near real time.
But what if you’d like to review them all side-by-side in one file?
It is quite easy to imagine such a request from a company executive, who may not even have access to operational data in Acumatica.
Previously, that meant manual work. If your company has two branches, and there are 12 reporting periods in a year, it means that 24 worksheets needed to be prepared somehow.
Previously, that left you with just two options, each with their own downsides.
On the one hand, you could leverage the custom column parameters in Velixo Distribution List:
In this option, you’d need to manually prepare all the parameter combinations line by line, as seen above. You would also need to accept the fact that multiple files / emails would be produced instead of the one desired file.
Alternatively, you could write some VBA, or go completely manual to:
- Copy the P&L worksheet 24 times in the source report
- Substitute every combination (by hand) into the branch / period parameter cells on every copied worksheet
- In Velixo Distribution List, combine the 24 worksheets you prepared in the Tab Selection column:
This option would give you just one report file, as desired, with 24 tabs side-by-side.
But the sheer number of manual operations you needed to perform to make this happen! It certainly screams “error-prone” and “boring”.
With the Velixo Blueprint feature, however, there are no more trade-offs!
Now, you can keep around just one source P&L tab that is going to serve as a “blueprint” of all the 24 tabs in the generated report file, in a fully automated manner!
Only a few simple steps are required to reach our goal.
First, on the P&L worksheet, for each of the two cells that contain the branch and financial period report parameters, define a named blueprint substitution range.
On Excel Ribbon, select Formulas -> Name Manager -> New — or simply Formulas -> Define Name.
(Heads-up: make sure that the range is scoped to the worksheet (P&L in this example, as displayed above) and not the workbook: this is very important for the blueprint feature to work)
Do the same for the branch – again, specifying the correct scope:
Second, on the distribution list, create two custom columns with the same names as the ranges you’ve just defined. In the value cells, specify a hash-delimited (#) sequence of all the values that need to be substituted into the range. To avoid tedious manual entry, you can use the TEXTJOIN function:
Finally, in the Tab Selection column, instead of just specifying the P&L tab, append a special square bracket syntax with the two substitution range names:
This will tell Velixo to treat the source tab as a multi-worksheet blueprint instead of just copying the tab over.
Now, when you click Acumatica -> Distribute All, the resulting report will look like this:
Whoa! Velixo has automatically looped through all 24 combinations for you, generating a separate worksheet for each of them in the resulting file!!!!
This means – no more tedious and time-consuming reporting, thanks to our new Report Distribution Blueprints.
Our team at Velixo wants all our customers to stay productive and focused on creative, value-adding business activities. With three simple steps outlined above, Velixo and its Distribution Worksheet Blueprints feature may become your biggest reporting time saver ever.
For more information about the features and enhancements in Velixo, register for our webinar