Creating a Book of Reports in Dodeca

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

2. Name the View “Report Book”

3. Create an Excel file

4. Add the View to a hierarchy

5. Drag the View to the Standard Hierarchy and commit the change.

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”.

2. Create a new Procedure called “New Book”.
3. 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”.

4. 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”.

5. Create a second Procedure called “Add Sheet”.
6. 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”.

7. 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”.

8. 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”.

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”.

2. In the Toolbars Configuration editor select Copy.

3. Name the new Toolbar “Essbase View Standard Limited – Report Book”.

4. Select Toolbars Designer.

5. In the Toolbars Designer, select the Tools tab and press “New…”.

6. Select a Button tool. Enter “New Book” for the Caption and Key. Then press “Add”. Then close the dialogue.

7. Change the DisplayStyle to “TextOnlyAlways”. This indicates that our button will only have text and will not have an icon.

8. Repeat that process for our next Button. Press “New…”.
9. Select a Button tool. Enter “Add Worksheet” for the Caption and Key. Then press “Add”. Then close the dialogue.

10. Change the DisplayStyle to “TextOnlyAlways”. This indicates that our button will only have text and will not have an icon.
11. 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.

12. Select “Add Worksheet” on the right. Drag “Add Worksheet” to the left and place it on the toolbar.

13. Right click “New Book” and select “Begin a Group”. This will separate the buttons from others on the Toolbar. Now close the Toolbar Designer.

14. Select “Configure Tools…”

15. Select the “Add Worksheet” Tool Key on the left.

16. Under the “Add Worksheet Tool Controller” dropdown, select “WorkbookScriptController”.

17. For the WorkbookScriptID argument, select “Utilities” which is the WBS we created previously.

18. For the ProcedureName argument, select “Add Sheet” which is the procedure in the Utilities WBS we created previously.

19. Repeat the process for the “New Book” Tool Key by selecting it.

20. Under the “Add Worksheet Tool Controller” dropdown, select “WorkbookScriptController”.

21. For the WorkbookScriptID argument, select “Utilities” which is the WBS we created previously.

22. For the ProcedureName argument, select “New Book” which is the procedure in the Utilities WBS we created previously.

23. Press the “Commit Module…” button.

24. Confirm that the changes will be committed.

25. Notice that Toolbar configuration changes require a restart of the Dodeca application. Close the dialogue and restart Dodeca.

26. Once Dodeca has restarted, edit the View properties for the View we want to enhance.

27. Change the ViewToolbarsConfigurationID property to “Essbase View Standard Limited – Report Book” and press “Commit”.

28. Build the View to test it out. After adding a couple of sheets to the Report Book, open the Report Book view.