Excel Reporting in SpotlightXL

Overview

With Excel Report, you can leverage the full set of formatting options natively available in Excel. You now have the best of both; the power of Dynamic Planning and the added flexibility of Excel. Use Excel Reporting when your model contains pertinent data that you want to use with a wide variety of Excel features to format an Excel Report, such as an Income Statement or Balance Sheet. With Excel Reporting, you can design and customize the worksheet as a report and freely place data and members from the model into the report. Data is updated from the cloud upon Refresh.

You can create an Excel Report from the Excel Reporting interface, but it can be tedious to add dimensions, members, and data items individually. To simplify the process, you can create a report based off:

• Views
• Reports
• Multiple Models

With Excel Reporting, users can:

• Reference the data from Dynamic Planning in Excel
• Design and run reports from Excel
  • Use a wide variety of Excel features for formatting the reports
  • Leverage Excel formulas, formatting, graphs, and other features
• Publish the Excel workbook to Modeling
  • Publish (or upload) the workbook for collaboration
  • Open (or download) the workbook
• Refresh data
  • Only data cells from Dynamic Planning are refreshed
  • Excel formulas and formatting options are retained as is during the refresh process

Excel Reporting Access

Power User Access: Power users have access to all Excel Reporting functionality.

Contributor User Access: For Contributor users, menu choices will depend on whether or not the Power user has been granted access to Design Excel Reports. By default, Contributor users have this access.

Contributors with design access have the following (initial) menu items:

Contributors without design access have the following (initial) menu items:

Reviewer User Access: For Reviewer users, menu choices are consistent because Reviewer users cannot design Excel Reports. The menu ribbon expands once you start working with a report.

The following list of tasks is available for Reviewers and Contributors without design access:

• Opening Excel Reports
• Refreshing Excel Reports
• Organizing Reports into Folders
• Working with the Metadata Manager
• Deleting SpotlightXL Formula Cells
• Discovering and Filtering the Data Selection
• Creating Snapshots
• User Access to Models and Dimensions

Model and Dimension security functions similarly to how it operates in Views and Reports. Power users have unrestricted access and can define permissions for all other users. Contributor and Reviewer users can only access the models and data for which they have been granted permissions.

If Contributor or Reviewer users do not have access to a member referenced in a cell, they will see #NO ACCESS instead of the data. If a member referenced in a cell is not available in the model, users will see #INVALID in the cell.

Provide Users With Navigation Access

Contributor users can design views, formatted reports, and Excel Reports. However, a Power user can disable design access for Contributors, affecting all models in the application.

To learn how to enable or disable Navigation Access for Contributor user roles, click here.

Provide Group Access to Office Reports

Office Reports can be created and published for other users to download and refresh report data. Publishing and storing reports in the cloud facilitates collaboration by centralizing common monthly reports. This feature is intended for your organization’s standard monthly reports, such as those in a board book. While we support up to 50 reports per application, each up to 16 MB, a general guideline is to limit this to fewer than 10 standard reports.

Power users can control access to this feature by user groups. You can specify whether user groups can only open or download reports, or also publish (which includes downloading) reports. Note that "download" provides read-only access, while "publish" allows read-write access.

Each report (Excel, Word, or PowerPoint) requires an Office Report in order to be published in the Dynamic Planning cloud. Therefore, the Office Report must be created first before attempting to publish the Excel, Word, or PowerPoint report.

1. In SpotlightXL, select Manage > Office Report >Setup. Create a template for each report you want to publish and make accessible to users.
2. Enter the report name.
3. Enter the report description.
4. Click Save.
5. In the Group field, specify user groups that should have access to this report.
6. For Access, specify whether user groups should be able to publish or download the report.
7. Click Save.

Model-Level and Dimension-Level Security

Model and Dimension security works the same way as it works in Views and Reports. Power users have access to everything. Contributor and Reviewer users can access only the models and data for which they have been granted access. Power users grant access using Model Permissions and Dimension Locks.

If Contributor or Reviewer users do not have access to a member that is being referenced in a cell, they will see #NO ACCESS instead of the data. If the member that is being referenced in a cell is not available in the model, #INVALID appears in the cell.

Application Settings

Power users can set up two options for Excel Reporting; Display Progress Information and Display Blank Cells as Zeros.

Display Progress Information

To monitor the progress of a current operation with a progress bar and supporting information, follow these steps:

1. In SpotlightXL, select Manage >Application Administration > Application Settings.
2. In the Display Progress Information After (in seconds) field, select the number of seconds after which a progress information box will appear for operations that take longer than the specified time to complete. The default setting is None. You can enter any number between 1 and 60. For example:
   1. If you select 1, the progress bar will appear after 1 second. 
   2. If you select Immediate, the progress box will appear immediately after certain user operations.
      

User Operations with Progress Information

The following operations will display a progress bar if display progress information is other than None.

• Capture Data as SpotlightXL Formulas
• Capture Entire Workbook
• Refresh
• Refresh All

The image below shows an example of the progress when capturing data as Spotlight formulas.

Displaying Blank Cells with Zeros

The Display Blank Cells with Zeros field (shown below) allows you to choose whether to display data cells with no value as zeros. The default setting is Yes. When this option is set to Yes, any Excel Report containing data cells with a null value will display those cells as zero.

Excel Reporting Comparison to Views and Reports

The table below highlights the similarities and differences between views, reports, and Excel Reports.

Creating an Excel Report from a View

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. To learn how to create an Excel Report based on a view, click here.

Using Capture Data

You can also convert views to Excel Reports with the Capture Data as SpotlightXL Formulas menu command. To learn how to use capture data, click here.

Creating an Excel Report from a Formatted Report

To convert existing formatted reports into Excel Reports, open the report and click Capture Data. This can be done in either Run mode or Design mode. This option allows you to convert reports without having to redesign them from scratch.

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

Creating an Excel Report with Multiple Models

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

In the following example, two models are combined into one Excel Report. The first worksheet is a view of the model Demonstrate Excel Report named Basic, while the second worksheet is a view of the model Demonstrate Excel Report 2, also named Basic. These views contain data for the same company across 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 will not be refreshed whenever the Department POV is changed.

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

To learn how cells are associated with which model, click here.