Working with the Excel Reporting Menu Items

The menu items you may have access to are based on the role and privileges described above.

Open

Open the folder structure to perform the following:

• Add a folder
• Rename a folder
• Delete a folder
• Download an Excel Report

After the Power user has created a template for the report, and a user with Publish privilege has published the report, a user with Publish or Download privilege can download the report.

1. After logging into SpotlightXL, SpotlightWord, or SpotlightPPT, start with a blank workbook, document, or deck. In Excel, click Excel Report from the left main menu.
2. Click Open. A search box appears and you see the reports that have been published and that you have access to.
   Reviewer and Contributor users without design access see the following screen:
   
3. Select the required report and click Download.
4. Modeling downloads the report and immediately asks you to save a local copy of the report. Select the folder on your computer where the report should be saved and click Save.
5. The report is opened separately from the blank report you started with. All Modeling metadata and data cells appear as #REFRESH for security purposes. Upon clicking Refresh, you can see only those members and data cells you have access to.
   1. If there are multiple pages in the report, click Refresh All.

Now you can use the report.

Users with publish privileges can click Publish whenever they want to upload the latest data. Because the name of this report was already embedded in the workbook, document, or deck when it was first published, they do not need to select the report template name again.

As described, when you download a report, you will save a local copy of the workbook. If you attempt to download the same report again and save the local copy to the same file name and location, Modeling first asks if you want to replace the file.

To avoid this error, you can save the second download to a different file name, or you can close the first download before downloading a second time.

Folder Structure

Both the Open and Publish buttons open a box with the reports listed in a folder structure.

Reports are listed or omitted from the two boxes based on their status.

• A report template created by a Power user but not yet published will be listed in the Publish box but not the Open download box.
• A report template that has been published will be listed in the Open box but not the Publish box.
• By default, reports are placed into the parent folder.
• You can click Add Folder to create a folder to organize your reports. Then drag and drop the reports into the folder.
• The same folder structure appears in the Publish box.
• To delete a folder, right-click it and select Delete.
• To rename a folder, right-click it and select Rename.
• If a folder contains a report, you cannot delete the folder. You will see an error message, “The folder has children and cannot be deleted.”

Design Manager

The Design Manager option allows you to design the Excel Report, where you can set up data intersections, members, substitution variables, and point-of-view (POV) drop-downs. To learn how to design an Excel Report, click here.

Metadata Manager

Reviewer and Contributor users without design access have Metadata Viewer on the menu ribbon. Metadata Manager allows you to view the dimensions and members that are used on the report.

Metadata Viewer provides information only on cells with Spotlight formulas. As you click through the worksheet, you will notice that some cells contain plain text, others have Excel formulas, and some include Spotlight formulas. Spotlight formulas have two variations:

• SpotlightMetadata()
• SpotlightData()

To learn how to use the Metadata Viewer, click here.

Refresh

In the worksheet containing the Excel Report, click Refresh. The data is retrieved. If you have more than one worksheet containing an Excel Report, you can select Refresh All.

POV Refresh

Click POV Refresh on the menu to select from the refresh options. POV Refresh controls whether or not the data is refreshed automatically when you select a different POV from the page axis. You can turn the option off, turn it on for the current worksheet, or turn it on for the whole document (workbook).

• Sheet or Document: These options are useful for small- to medium-sized models where you often change a POV and want a quick refresh.
  • When you set POV Refresh to Sheet, any change to a POV on the page axis will automatically refresh the data in the current tab.
  • When you set POV to refresh to Document, any change to a POV that is referenced by any other tab in the document (workbook) will automatically refresh all the data in those tabs.
• Off: If you have a large model and want to change more than one POV at a time, you may find it faster to turn this feature off and click Refresh less frequently. When you set POV Refresh to Off, any change to a POV on the page axis will not affect the data in the current tab. You must click Refresh to see the corresponding change.

For example, in the following workbook, each tab shows a different department's operating expense.

You can point the POV on each sub-department sheet to one master POV on the main sheet so that any changes to the POV on the main sheet also affect the sub-department sheets.

For example, on a sub-department sheet, select all cells then click Design Manager. For the Company, set the POV to point to the Company tab and COMPANY cell. Click OK.

After selecting the absolute cell reference, you will see the change in Design Manager. Click Update to complete the change on this tab. Repeat these steps for the other tabs.

When changing the company on the main tab, the data will automatically be refreshed in the other tabs that refer to the same company POV.

Publish

Dynamic Planning includes the ability to create Excel Reports with Excel Reporting and publish the reports to the Dynamic Planning cloud. Users can then download the report and click to refresh the data. The ability to publish and store reports in the cloud is a collaboration feature to make it easier to store your most common monthly reports in one place.

A user with Power-user privilege is required to set up reports that will be accessible to users. The Power user creates a template (or placeholder) for a report that will be published and made accessible to users. This template reserves a spot for the report in the Dynamic Planning cloud and defines which user groups have access to it. To publish reports:

• Create a Template: Power-user access is required to do this step. Publishing reports to the Modeling cloud is intended to be used for your organization’s standard monthly reports, such as those in a board book. A general guideline is to use it for fewer than 10 standard reports, even though we support up to 50 reports per application, with each report limited to 16 MB. To learn how to create a template in SpotlightXL, click here.
• Publish the Report: After the Power user has created a template for the report, a user with Publish privilege can upload the report. To learn how to publish the report, click here.