The first step in generating a report is to identify the key spreadsheet cells that Publisher uses in creating the report. You input this spreadsheet information in a series of dialogs and you create a report markup. The report markup requires you to supply:
- The OLAPOpen Cell in your spreadsheet, which connects your spreadsheet and your database
- The locations of your dimension member loop cells
- The sheets of your workbook that you want to generate in your reports
- A conditional cell (optional)
- A macro to execute (optional)
The following topics explain how to make each markup selection and use the Acme Trading Company database to supply actual data for each step.
Create a new markup
To create a new markup from a spreadsheet:
- Start the markup tool from your Windows Start Menu by selecting Programs, proCube Publisher, proCube Publisher Markup. The proCube Publisher Markup dialogs opens and has two tabs:
- Create New: Select to create a new markup for an Excel spreadsheet
- Edit Existing: Select to edit an existing markup of an Excel spreadsheet
- Select the Create New tab and click Browse to find the spreadsheet.
- In the Open window that appears, browse to find the sample spreadsheet provided for the scenario. For example: C:\Program Files\proCube Publisher\Examples.
- Double-click ACME P&L.xls to open it. The file location now appears in the browse box:
Figure 1. Publisher Markup dialog
- Click Next. Your spreadsheet opens and the proCube Publisher Markup dialog opens in front of it.
Figure 2. Spreadsheet with Publisher Mark up dialog
When you open a spreadsheet in the markup tool, Publisher checks if proCube or Excel is running. If Publisher detects that proCube or Excel is running, a warning message appears asking you to save and exit all local proCube databases and Excel workbooks before proceeding.
- Click OK on this warning message, and then save and close Excel and/or proCube. When you go back into Publisher markup, the warning message will not appear, and you can proceed through the markup process.
Identify the OLAPOpen Cell (required)
Remember that you must know where the OLAPOpen Cell is located on your spreadsheet. OLAPOpen is a proCube function that creates a connection between Excel and proCube. The term “OLAPOpen Cell” is the proCube function name for a cell that stores the name of the database from which data is obtained and its location on your computer.
The default OLAPOpen cell is B1, but the creator of the spreadsheet can have placed the OLAPOpen cell anywhere in the spreadsheet. Be sure you know where the OLAPOpen cell is for this spreadsheet.
To select the OLAPOpen Cell:
- In the dialog, select OLAPOpen Cell.
- Move your curser to the Excel spreadsheet and click on the window title bar of the spreadsheet to re-connects to the spreadsheet.
- Now click on the OLAPOpen cell. In our example spreadsheet (shown below), the OLAPOpen cell is B6. Note that when you click cell B6, you can see the OLAPOpen Cell function in the Formula bar.
Figure 3. OLAPOpen Cell
Selecting a conditional cell
If you created a conditional cell in your spreadsheet, you can select it during the markup process. You can use a conditional cell to specify reports or spreadsheets that contain data you do not want sent out as reports. In our example, as CFO, you do not want spreadsheets sent out in reports if the total year revenue is less than $1000. So that Publisher enforces this “rule” when it generates reports, you need to create a conditional cell stating that:
“If the total year revenue is greater than 1000 (or total year Revenue>1000), then X=TRUE and send the report.”
The calculation for the cell is Cell=cell>1000, and when you click Enter, the cell will populate with either TRUE or FALSE.
At the same time, you want the conditional cell to prevent cells containing “#VALUE” from being included in your reports. (Recall that if you are using a secure database, and you send a report containing data that one or more recipients do not have permission to access, then those recipients will see “#VALUE” in the associated cells.). Our final conditional cell combines both rules, as indicated below:
Your conditional cell must be Boolean cell (i.e., true/false); an error will be generated if other values are used (e.g., 0, 1, Y, N, etc.), but only when you launch the report. In addition, the formula for the conditional cell must be written in such a way that when the conditional cell equals true, the reports will be sent.
If your spreadsheet has a conditional cell that is equal to False, the spreadsheet will not be sent. This means that if all of your reports have a conditional cell that equals False, no reports will be sent. (If you selected E-mail or server as your publication type, then you will receive an E-mail notification that no reports were sent.)
To select a conditional cell:
- In the dialog, select Conditional Cell.
- Move your curser to the Excel spreadsheet and click on the window title bar. The conditional cell can be located on any sheet in the workbook; you should know the location of the conditional cell before you open up the spreadsheet in markup.
- Next, click the Conditional cell on your spreadsheet. In our example, the conditional cell is located in cell B35 and is labeled “TRUE.”
Identify Loop Cells (required)
Loop cells indicate the dimension members containing Fact Data that will populate your spreadsheets—and change when cube data is refreshed and your spreadsheets are recalculated. Note that you should only loop through dimensions arranged as page members in your spreadsheet (i.e., that populate the X-Y axis of your spreadsheet with data).
In general, it is not advisable to loop through dimensions arranged as rows or columns in your spreadsheet. For this reason, before using Publisher, be sure to carefully arrange your column and row members in proCube as you intend them to appear in your reports.
In our example, we can only select the following dimensions as loop members: Version, Year, and Region.
The Month and Account dimensions on our spreadsheet are arranged as rows and columns, and as a result, we should not select them as loop members.
To select a loop cell:
- In the Loop Cells section of the markup dialog, click Add to open the Add New Loop dialog.
- Select the loop cell by clicking first in the title bar of the spreadsheet, then in the actual loop cell itself. Click in cell C8 of our example spreadsheet, which contains the Version dimension and whose value is “Actual”.
- Select the Cube Name (e.g., Financial Data).
- Select the Dimension name associated with the Loop Cell from the drop-down list in the Dimension Name field. The dimension name for our example is Version.
Figure 4. Add New Loop Dialog - Version Dimension
- To remove a Loop Cell, highlight it in the proCube dialog box and click Remove. To select additional Loop Cells, click the Add button and repeat the steps above.
- In our scenario, add additional loop cells for both the Year and Region dimensions, as shown:
Figure 5. Markup Dialog with Cell Values
- When you have finished selecting loop cells, click Next.
Publisher will not give you an error warning if you select a dimension that does not associate with the cell you selected. If this error occurs, reports will be sent to recipients, but they will contain no data.
Select Sheets to Generate in Report
You must select which of your workbook spreadsheets you want to include in your report. (For example, you may have used a separate sheet as a scenario spreadsheet, where you tested various assumptions used to develop the final spreadsheet to be used for the reports. In this case, you would not want your scenario spreadsheet sent out to recipients.)
To select which spreadsheets in your workbook to include in your reports:
- Select one or more spreadsheets from the Select Sheets to Generate list in the markup dialog box, as shown below. In our example, choose P&L Data and P&L Chart.
Publisher automatically detects the number of spreadsheets in your workbook and lists each spreadsheet by name in the dialog.
Figure 6. Markup Dialog - Select Sheets to Generate
- You can add a chart to a worksheet, and Publisher will include that sheet in the reports. However, if you create the chart on a Chart sheet, rather than on a regular spreadsheet, the chart sheet will not appear in the Select Sheets to Generate dialog and you won't be able to include that sheet.
Copy Workbook VBA (optional)
You have the option to copy over the VBA code from the source template to be included with any Excel workbooks that are generated. To have the workbook VBA copied over, select the checkbox.
Figure 7. Publisher Markup Dialog - Copy Workbook VBA
Identifying a macro to execute
You also have the option to execute one macro per report. Publisher will process the macro when you publish, or launch, the report. Our example spreadsheet contains a macro that hides any row of the report that contains no data. This macro is called “hide_zero_row.”
To select a macro to execute:
- Within the Mark up dialog, access the available macros from the Select Macro to Execute (Optional) drop-down menu.
- Select the name of the macro to execute in the report so that it appears in the text box. Our example spreadsheet only contains one macro.
- Click Next to display the Save a Markup dialog.
Save a markup
The last step in the markup process is to save your file. Filenames can be up to 40 characters long, are not case sensitive, and can have spaces.
To save a markup:
- In the Name field of the Save markup dialog, enter file name P&L Mark up.
Figure 8. Save Markup Dialog
If any of the following symbols are used in the report markup name, they cannot be used or carried over in the Configurator Report name: \ ? : * < > |.
- Click the Save button to open a message box.
- Click OK. The initial proCube Publisher markup dialog reappears. You can edit an existing markup, create a new markup, or close the dialog and proceed to Configuring a Report Template.
You can use one markup to create many different reports. In the Configuration tool, you simply select the markup name in the first Report Configuration page, configure the markup select the Edit Existing Report tab, select the report name, reconfigure the report by selecting different members to loop through and new recipients, and then save the new report configuration with a new file name.
The next step in the report generation process is to create a Report Template as described in Configuring a Report Template. But there are times that you may want to edit an existing markup.
Editing an existing markup
You can also edit a saved markup and save it with your changes (or edit a markup and save it with a different name), thus quickly creating similar reports that contain different data or dimensions.
To edit an existing markup:
- Open the markup tool by selecting Start, Programs, proCube Publisher, proCube Publisher Markup from your Windows start menu.
If you are already in proCube Publisher markup tool and have saved a markup, clicking Save will automatically bring you to the page shown in the following section, Delete a markup.
- The proCube Publisher markup dialog opens.
- Click the Edit Existing tab to display a list of available markups.
Figure 9. Markup Dialog - Edit Existing
- Select a markup from the list and click Next. The Publisher markup dialog opens.
- Go through each step in the multi-step dialog, changing the markup selections as needed.
- After all selections have been made, the final dialog will prompt you to Save Markup. Name the edited markup and click Save.
If you save a markup using an existing markup name, you replace the old markup with the new.
Deleting a markup
To delete a markup:
- Open up the configuration tool. From the Start menu, select Programs, proCube Publisher, proCube Publisher Configuration.
- From any page in the proCube Publisher Configuration tool, click Delete from the list of buttons on the left-hand side of the page. The Delete Markups or Reports page appears:
Figure 10. Delete Markups or Reports Dialog
- To delete a markup, click Markups and select the markup from the list of available markups.
- Click Delete.
If you create reports using a particular markup, and that markup is deleted, your reports will still run.