A client recently proposed an interesting challenge – when distributing a binder, is it possible to incorporate a dynamic password to open the file based on the intended recipient?
The scenario is as follows:
- A binder is distributed to a group of user emails.
- Password protect the report (to provide security if user’s email is compromised)
- Ensure different users have different passwords
What is a Binder?
Let’s say report to the CFO of a large firm and during month-end, you are to submit a P&L report for each individual office (New York, Toronto, Chicago etc.)
Instead of running report through the Xcelerate Excel interface for every office and then manually combining them into one Excel sheet (which would be tedious), the Xcelerate Binder feature automates this process for you.
An Xcelerate Binder is a Excel book where each sheet is the same report run for different user selections. It will take a specified report and run it for all the data sets specified for it – for example a report run of all North American Offices could create tabs for New York, Boston, Los Angeles and Washington DC.
Binders and Macros Integration
Unfortunately, Xcelerate Binders does not run any macros during report retrieval. Binders will generate the individual sheets using the report file and will copy any macro code into the final file distributed to users.
Therefore, any manipulations would have to be done either in report definition or in the macro code as those are the only elements Xcelerate interacts with prior to distribution.
One important to note is that the solution must already be in place prior to the macro running – this is because the user always has the option to open an Excel file with macros disabled.
Solution
Keeping in mind my last point that security must already be in place prior to the macro running, we decided that the optimal solution would be to retrieve the data on the reports encrypted. A simple way to do this would be to create a new Value Dimension member which would retrieve the data, but multiplied by a factor of x. Upon opening the report without the macro (the key), the data would all be meaningless as the values would not be correct.
As for the macro, we created input boxes for users to enter a username/password combination, which is then checked against authentication credentials.
A correct combination will allow the Excel file to open and decrypt the numbers, while an incorrect combination will cause the file to close.
I hope this provides some insight into some of possible security enhancements available with binder distribution or perhaps Xcelerate report retrievals in general. As always, if you have any questions, suggestions, or would like further clarification on any related topic, please feel free to reach out.
