Dynamic Planning Creating Excel Reports

Overview

You can design and create an Excel Report from the Excel Reporting interface (as discussed in the Overview of Excel Reporting topic). However, it is cumbersome to place dimensions, members, and data items one at a time. Therefore, you can also create an Excel Report based off:

• Views

• Reports

• Multiple Models

Creating an Excel Report from a View

Just as you design a report from a view by selecting Design Report, you can also create an Excel Report from a view by selecting Design With, Excel Report. This action replaces all POV, member, and data cells with Spotlight formulas. Once you have the Spotlight formulas, you can Refresh the data and format your Excel Report however you want.

There are two ways to convert views to Excel Reports:

Using the Design With, Excel Report subtask

Using Capture Data

Using the Design With, Excel Report subtask

In Practice: Creating an Excel Report Based Off a View

1. Select the Analyze task and the Data subtask.

2. Select a view from the folder structure. Notice that the tab displayed is called Analyze.

   

3. Under the Data subtask, select Design With, Excel Report, which converts the view to an Excel Report and places it into a new worksheet that is named for view that was converted. The menu changes to reflect the options for Excel Reports. All data items show as #REFRESH, and cell contents consist of SpotlightMetadata or SpotlightData formulas.

   

   

4. Format and lay out your Excel Report.

Using Capture Data

You can also convert views to Excel Reports with the Capture Data as SpotlightXL Formulas menu command.

In Practice

1. Select the Analyze task and the Data subtask.

2. Select a view from the folder structure. Notice that the tab displayed is called Analyze.

3. Under the More menu on the ribbon, click the down-arrow and select Capture Data as SpotlightXL Formulas. Notice that all the data items show as #REFRESH, and cell contents consist of SpotlightMetadata or SpotlightData formulas.

   
   

   Note:

   If you click Get Data, a warning message appears because you are in the Analyze tab, the Get Data command places data values from the model into the cells, and this overwrites the Spotlight formulas if you answer Yes to the warning message.

   

4. Copy the entire contents of the Analyze worksheet to a new blank worksheet.

5. Select Excel Report from the Select Task menu. The menu changes to reflect the options for Excel reports.

   

6. Click Refresh.

Creating an Excel Report from a Formatted Report

If you have existing formatted reports and would like to convert them into Excel Reports, open the report and click Capture Data. You can Capture Data from either Run mode or Design mode.

With this option, you can convert your existing reports into Excel Reports and there is no need to redesign the reports from scratch.

Additionally, if you have existing formatted reports in a workbook with multiple worksheets, you can convert all tabs to Spotlight formulas with one action, Capture Entire Workbook.

Creating an Excel Report with Multiple Models

Excel Reporting supports access to multiple models within one report, including Exclude POV functionality. Because Spotlight formulas are created and modified using the Design Manager interface, the same features and functions available in Design Manager in formatted reports are also available in Excel Reports.

In the following example, two models are combined to appear in one Excel Report. The first worksheet is a View of model Demonstrate EBR called Basic. The second worksheet is a View of model Demonstrate EBR 2, also called Basic. The views contain data for the same company in different years.

Selecting Analyze Data, Design With, Excel Report on both sheets makes it possible to copy the data items needed to a new blank worksheet.

A new joint report shows data from both models. Each cell containing a POV, member name, or data item contains a Spotlight formula.

The two POVs are Company and Department. By checking the Share POV box next to the model name, each POV is set to affect both models upon Refresh.

Selecting a different company or department and clicking Refresh updates data for both years.

If you want to exclude one of the POV changes from one month of data, for example, you can set Exclude POV for those data cells. In the example below, data for March 2014 will not be refreshed whenever the Department POV is changed.

At the next Refresh, the data for March 2014 reverts to the summary-level data for All Departments.

Understanding Which Cells are Associated with Which Model

If you are working with a report that is unfamiliar to you, Excel Reporting provides a method to visually shows you which cells are associated with a particular model or dimension member.

In Practice

1. Select all cells in the worksheet by clicking above and to the left of cell A1, then click Design Manager.

   

   The Model is shown at the top; you can select the drop-down to see other models that are used in the report. In this example, Demonstrate EBR is using 29 cells in the report. This count includes both data cells and dimension member cells.

2. Select the other model in the report, and see that Demonstrate EBR 2 is using 27 cells in the report.

   Note:

   If you have POVs that are Shared between models, those POV cells are counted only with the model that the POV was first created with.

   Further, you can discover which cells are have a particular dimension member as part of their formula.

3. Select an Account member, click the Member Select icon, Preview Filter Member.

   If you are a Reviewer or a Contributor without design access, your menu will have fewer items than the image below.

   

   Preview Filter Member shows you which cells contain that member in their Spotlight formula.

   

4. Click OK or Cancel to return to the Design Manager or Metadata Manager.

5. If you want to change the formula to use a different member, again, select all cells in the worksheet and click Design Manager or Metadata Manager.

6. Select the member you want to change, Member Select Icon, then Filter Member. Those cells that contain that member are then isolated and you are returned to the Design/Metadata Manager so you can make changes to just those cells.

   Note:

   Only Contributor users with design access can make changes to the member selections in the Design Manager.

   In the following example, the cells containing 6112 – Salaries: Part Time are filtered. Now, if you make changes in the Design Manager, those changes affect just the filtered cells.

   Again, Reviewer users and Contributor users without design access will not have the Select Member and Select Cell Reference menu items, will not see the “New Value” column, and cannot make changes to the member selection.

7. Click Select then click Update to complete the change (available only to Contributor users with design access).

8. Click Refresh.