Automate reports distribution across the corporate network using Excel VBA

During the reporting process it's vital that not only reports contain the correct data, but also that the distribution of those reports reach the right destinees..... A challenging task if your manual distribution matrix contain a lot of people/folders.  Whereas the right execution sometimes gets little attention, any human errors surely create a lot of fuss.  In such a context, an automated solution can be considered.  An investment in a good, simple but powerful mechanism ensures a quick, stable and error free task execution.

As the finance person's most favorite tool is Excel, you quickly end up writing Excel VBA code to do so.  This blog's posting focusses on file distribution via the corporate network.  Another major  method for distributing information is via email. A later posting will cover the tips and tricks for this solution.

Target audience: finance readers with VBA for Excel experience


There are some advantages in automating the task:

• it also immediately serves as a documentation too 
• hand-over of the task can be easily done (even due to an ad-hoc event)
• error-free process

Required

• a overview/matrix of (source) files and (target) directories (we assume for simplicity sake that the filenames do not require renaming during the copying)
• intuitive way for the end-user to alter the setup without the need for any VBA code changes 
• visual indication in case of any errors encountered during the execution

Steps in general

• Create a sheet in Excel that lists the files to copy and the directories to copy to. Foresee as many target columns as copies of the file you expect to need.
• Run a check to see if the specified files and directories exist before kicking off the mechanism
• Add your VBA to the workbook to copy the source files to the target directories
• Check for any encountered errors

Step 1: setup a distribution matrix

Excel sheet showing files and directories

Each line on the screenshot shown above shows a single file, potentially going to multiple locations (in this example up to 3 possible copies have been foreseen).

e.g.  (row 2):  the Excel report for Belgium is copied into a folder for the European reporting team and another copy of the same report is put in the folder used by the team analyzing the Gross Margin. For this report the 3th option is not used.

The first column ("Include") is a column with a switch to activate of deactivate a certain line/file.

e.g.  (row 3) if we do not need to (re)copy the file for Germany, we could change the "Y" into a "N" and our code will skip this line from execution. For advanced use you could consider putting formulae that check the name of your running process, current date, reporting set, ..

Step 2: Foresee a graphical mechanism to easily add files and directories to the matrix

As we want to avoid that the end-user constantly needs to copy in paths from his Windows Explorer to his Excel while extending or altering his list, a file dialog to "point and pick" is needed.  A good way to tell the user what can be "done" at a certain sheet in the workbook is to have a message popup when he starts working with a specific worksheet tab.

User clicks on the Sheet tab (On worksheet activate event)

e.g. The above message explains that any double clicks in the columns between (B) and (E) will trigger a dialog to select either a file (B) or a directory (C to E).

**Code**
**End of Code**

Paste in above VBA code into the worksheet object to see following message box after the double-click:

File Dialog that pops up after double-clicking anywhere in columns B:E

The choosen file is not opened but it's name and full path are written down as text in the cell from where we started our double-click.

Result in the cell after clickin on "Open"

Step 3: Make copies

Then you will have to build in your VBA to loop through the files .
You might find some inspiration in below code.
The copy action is done using following command:

filecopy file1 file2 

See Microsoft site for more info on the command: link

** Code **

**End of Code**

Step 4: Checking files and folders.

When we launch our macro it will return errors when either (1) the source file or the (2) target directory does not exist. We need to add a way to check any of those objects for existence. In case of issues with a certain source file or target directory we are going to color the cell red instead of green. I inserted the full function below.

** Code **

**End of Code**

That's all folks!

Using Drill Through Reports in Hyperion Smartview add-in (for metadata)

Extra metadata information needed please

On a regular basis following question pops up with Essbase users:

"We want to show metadata linked to our clients (or suppliers, employees,..) in Essbase. How can this be done? But do we need to load all of this into Essbase to do so?"

The answer is yes, it's possible to lookup  - while analyzing data in your Smartview sheet - any additional information for (a) specific dimension(s) from external tables by means of  "drill through" report. 

Interested? You might want to check out this video to see how the end-user experience looks like in Smartview before you dive into the below specified technical details.

https://youtu.be/uP4DMyaDQt0

Essbase Studio

There are a couple of possibilities to link data residing in relational tables (e.g. Oracle Database, txt files,..)  with the data or metadata stored in Essbase. This posting explains the use of Essbase Studio Console for querying additional metadata residing outside of the Essbase cubes.

Essbase Studio is the updated version of the old Integration Services but combines this concept with the possibility to do more "classic" admin actions, as we know them from the Essbase Administration Console.   You might already have heard that Oracle positions the Studio as the future standard toolbox for the Essbase administrator, so it's worth spending some time on this.

A typical setup within Studio is to make a connection to a number of tables containing either data and/or metadata. Studio can then automatically propose a star schema that can be used to populate your dimensions and to load in the data.

To Drill-Through or not, that's the question

Cube Setup

In our example we would like to see the phone-number for Adam, one of our employees. We have following information available in a table of our sample HR system:

Content of the relational table from Oracle Database visualized in an Excel sheet

We only loaded in the Employee-id, using the combined First and Last Name field as the alias.  One could off-course decide to additionally include these fields into the Essbase database and query them natively, but there might be some advantages in not doing so:

• infrequent use of the data
• ownership of the data
• or you just don't want to make this part of the process

Essbase Studio - view of the setup of the hierarchy/dimension

In our outline editor (Essbase Administration Console) this looks like this:

Essbase Administration Console - Outline Editor

Drill Through Report Setup

One of the objects that can be added in the Studio Editor is the "Drill Through Report" option we are looking for.

Essbase Studio

On the 'Context' tab, you choose the dimension(s) the report should work with..

Essbase Studio - Content Tab

 .. and in the 'Report Contents'- tab, select the columns from the table that should be shown when the drill is executed.

Essbase Studio - Report Content

Voilà, this will make our example work. The result can be seen in the video on top of this posting (if  you haven't done so).
More advanced setups may require the tuning of the SQL query with the necessary variables to link table and dimension(s).

Essbase Studio - Report Contents - Template SQL