Creating a Book of Reports in Dodeca

blog main pic creating book reports | Applied OLAP

Creating a Book of Reports in Dodeca

A typical task for a financial analyst is to pull together a number of reports into a single workbook that tells a story. The story might show the progression from a higher level to a more detailed analysis or a number of different scenarios. In any case, putting together several reports into a single workbook can be done in Dodeca in a way that makes things easy for an analyst.

For this exercise we’ll follow these steps:

  1. Create a target, empty, workbook. This will be the view that holds the worksheets we want to compile together.
  2. Create a Workbook Script that adds a view to a workbook.
  3. Add buttons to an existing View’s Toolbar allowing a user to add the rendered View to a workbook.

 

Part One: Creating a Target View

This is the easy part. We need to create an Excel View with a blank workbook. This will be our target View for all of the worksheets we want to compile. We can do this using the Excel View Wizard.

  1. Select Admin->Add Excel View
creating book of reports 1 1 | Applied OLAP
  1. Name the View “Report Book”
creating book of reports 1 2 | Applied OLAP
  1. Create an Excel file
creating book of reports 1 3 | Applied OLAP
creating book of reports 1 3b | Applied OLAP
  1. Add the View to a hierarchy
creating book of reports 1 4 | Applied OLAP
  1. Drag the View to the Standard Hierarchy and commit the change.
creating book of reports 1 5 | Applied OLAP

Part Two: Create the Workbook Script

In this section we’ll create a generic Workbook Script. In general, when creating Workbook Script procedures that are executed by buttons on a Toolbar, it is best practice to put them into a Utility script. The reason behind this is that there can be issues when Views are copied. If you’re not completely careful, your new View could be calling the old View’s Workbook Script. Confusion inevitably ensues.

From a high level standpoint we’ll do the following in this part:

  1. Create a Workbook Script called “Utilities”.
  2. Add a Procedure called “New Book”. This procedure will:
    1. Delete the Binary Artifact from our “Report Book” View.
    2. Display a message letting the user know the “Report Book” View is cleared.
  3. Add a Procedure called “Add Sheet”. This procedure will:
    1. If the “Report Book” View is not empty, add the current worksheet to the end of the workbook.
    2. If the “Report Book” View is empty, create a new binary artifact using the current worksheet.
    3. Display a message letting the user know the worksheet has been added to the “Report Book” View.

Now the detailed instructions:

  1. Create a new Workbook Script called “Utilities”.
creating book of reports 2 1 | Applied OLAP
  1. Create a new Procedure called “New Book”.
  2. Add the “BinaryArtifactOperations” method to the “New Book” Procedure.
    1. Use the “Delete” overload.
    2. Set the MethodCondition to “@BinaryArtifactExists(Report Book)”. This will ensure that the binary artifact actually exists before executing the method.
    3. Set the ID to “Report Book”.
    4. Set the DeleteVersionPolicy to “Latest”.
creating book of reports 2 3 | Applied OLAP
  1. Add the “ShowMessageBox” method to the “New Book” Procedure.
    1. Set the Caption to “Dodeca”.
    2. Set the Message to “Report Book Cleared”.
    3. Set the Icon to “Information”.
creating book of reports 2 4 | Applied OLAP
  1. Create a second Procedure called “Add Sheet”.
  2. Add a second “BinaryArtifactOperations” method to the “Add Sheet” Procedure.
    1. Use the “AddWorkbook” overload.
    2. Set the MethodCondition to “@BinaryArtifactExists(Report Book)”. This will ensure that the binary artifact exists before executing the method.
    3. Set the ID to “Report Book”.
    4. Set the VersionPolicy to “Latest”.
    5. Set the ToPositionPolicy to “Last”.
creating book of reports 2 6 | Applied OLAP
  1. Add the “BinaryArtifactOperations” method to the “Add Sheet” Procedure.
    1. Use the “CreateFromWorkbook” overload.
    2. Set the MethodCondition to “=NOT(@BinaryArtifactExists(Report Book))”. This will ensure that the binary artifact does not exist before executing the method.
    3. Set the ID to “Report Book”.
    4. Set the VersionPolicy to “Latest”.
creating book of reports 2 7 | Applied OLAP
  1. Add the “ShowMessageBox” method to the “Add Book” Procedure.
    1. Set the Caption to “Dodeca”.
    2. Set the Message to “Worksheet Added to Report Book”.
    3. Set the Icon to “Information”.
creating book of reports 2 8 | Applied OLAP

Part Three: Add Buttons

In this section we will add two buttons to a Toolbar. One button will clear the Report Book and the other will add the current worksheet to the Report Book. When adding buttons to a Toolbar, best practice is to make a copy of the Toolbar you want to use since you don’t necessarily want to alter Toolbars used in existing Views.

In this part we’ll need to do the following:

  1. Create a copy of the Toolbar we want to use.
  2. Create two buttons.
  3. Add the two buttons to the Toolbar.
  4. Hook the buttons to the Utilities WBS.
  5. Change our View properties to use the new Toolbar.

These are the detailed instructions:

  1. In this case I’m using the Essbase View Standard Limited Toolbar. The View is called Market Reorganization. You should be able to alter almost any Toolbar for almost any View. First open the Toolbar you want to change. In the UI View Properties right click on the ViewToolbarsConfigurationID and select Edit “Essbase View Standard Limited”.
creating book of reports 3 1 | Applied OLAP
  1. In the Toolbars Configuration editor select Copy.
creating book of reports 3 2 | Applied OLAP
  1. Name the new Toolbar “Essbase View Standard Limited – Report Book”.
creating book of reports 3 3 | Applied OLAP
  1. Select Toolbars Designer.
creating book of reports 3 4 | Applied OLAP
  1. In the Toolbars Designer, select the Tools tab and press “New…”.
creating book of reports 3 5 | Applied OLAP
  1. Select a Button tool. Enter “New Book” for the Caption and Key. Then press “Add”. Then close the dialogue.
creating book of reports 3 6 | Applied OLAP
  1. Change the DisplayStyle to “TextOnlyAlways”. This indicates that our button will only have text and will not have an icon.
creating book of reports 3 7 | Applied OLAP
  1. Repeat that process for our next Button. Press “New…”.
  2. Select a Button tool. Enter “Add Worksheet” for the Caption and Key. Then press “Add”. Then close the dialogue.
creating book of reports 3 9 | Applied OLAP
  1. Change the DisplayStyle to “TextOnlyAlways”. This indicates that our button will only have text and will not have an icon.
  2. Select the “Toolbar Designer” tab. Select the “View” Toolbar from the dropdown. Then scroll down on the right to “New Book”. Next, drag “New Book” to the top left and place it on the Toolbar.
creating book of reports 3 11 | Applied OLAP
  1. Select “Add Worksheet” on the right. Drag “Add Worksheet” to the left and place it on the toolbar.
creating book of reports 3 12 | Applied OLAP
  1. Right click “New Book” and select “Begin a Group”. This will separate the buttons from others on the Toolbar. Now close the Toolbar Designer.
creating book of reports 3 13 | Applied OLAP
  1. Select “Configure Tools…”
creating book of reports 3 14 | Applied OLAP
  1. Select the “Add Worksheet” Tool Key on the left.
creating book of reports 3 15 | Applied OLAP
  1. Under the “Add Worksheet Tool Controller” dropdown, select “WorkbookScriptController”.
creating book of reports 3 16 | Applied OLAP
  1. For the WorkbookScriptID argument, select “Utilities” which is the WBS we created previously.
creating book of reports 3 17 | Applied OLAP
  1. For the ProcedureName argument, select “Add Sheet” which is the procedure in the Utilities WBS we created previously.
creating book of reports 3 18 | Applied OLAP
  1. Repeat the process for the “New Book” Tool Key by selecting it.
creating book of reports 3 19 | Applied OLAP
  1. Under the “Add Worksheet Tool Controller” dropdown, select “WorkbookScriptController”.
creating book of reports 3 20 | Applied OLAP
  1. For the WorkbookScriptID argument, select “Utilities” which is the WBS we created previously.
creating book of reports 3 21 | Applied OLAP
  1. For the ProcedureName argument, select “New Book” which is the procedure in the Utilities WBS we created previously.
creating book of reports 3 22 | Applied OLAP
  1. Press the “Commit Module…” button.
creating book of reports 3 23 | Applied OLAP
  1. Confirm that the changes will be committed.
creating book of reports 3 24 | Applied OLAP
  1. Notice that Toolbar configuration changes require a restart of the Dodeca application. Close the dialogue and restart Dodeca.
creating book of reports 3 25 | Applied OLAP
  1. Once Dodeca has restarted, edit the View properties for the View we want to enhance.
creating book of reports 3 26 | Applied OLAP
  1. Change the ViewToolbarsConfigurationID property to “Essbase View Standard Limited – Report Book” and press “Commit”.
creating book of reports 3 27 | Applied OLAP
  1. Build the View to test it out. After adding a couple of sheets to the Report Book, open the Report Book view.
creating book of reports 3 28 | Applied OLAP