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.

How to Enable or Disable Navigation Access for Contributor User Roles?

1. In SpotlightXL, select the Manage > Navigation Access.
2. For the Contributor role, under the Design Excel Report heading, select the Yes or No option to allow or disallow design access for Contributor users.
   
   
   
3. Click Save.

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.

Using the Design With, Excel Report subtask

How to Create an Excel Report Based On a View?

1. In SpotlightXL, navigate to Analyze > Data.
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 to convert the view into an Excel Report. This action creates a new worksheet named after the view. The menu will update to show Excel Report options, with all data items displaying #REFRESH and cell contents containing 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.

How to Use Capture Data?

1. In SpotlightXL, navigate to Analyze > Data.
2. Select a view from the folder structure; the tab displayed will be named Analyze.
3. Under the More menu on the ribbon, click the down arrow and select Capture Data as SpotlightXL Formulas.
   
   
   
4. All data items will show as #REFRESH, and cell contents will consist of SpotlightMetadata or SpotlightData formulas.
   
   
   Note:

   Clicking Get Data in the Analyze tab will trigger a warning. If you proceed, it will replace Spotlight formulas with data values from the model.
   

   
   

5. Copy the entire contents of the Analyze worksheet to a new blank worksheet.
6. Select Excel Report from the Select Task menu. The menu changes to reflect the options for Excel reports.
   
   
   
7. Click Refresh.
   Note:

   If your view uses Display Label, the resulting Excel Report does not display the label initially after conversion. After you click Refresh, the labels appear.

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.

Understanding Which Cells are Associated with Which Model

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

1. In SpotlightXL, open any excel report and select all cells in the worksheet by clicking above and to the left of cell A1, then click Design Manager.
   
   The Model is displayed at the top; use the drop-down menu to view other models utilized in the report.
   
   Note:

   Shared POVs between models are counted only within the model where the POV was initially created.
2. Identify cells containing a specific dimension member within their formula.
3. Select an Account member, click the Member Select icon, and then select Preview Filter Member.
4. This feature reveals cells containing that member in their Spotlight formula.
   
   
5. Click OK or Cancel to return to the Design Manager or Metadata Manager.
   
   
6. To change the formula to use a different member, once again, select all cells in the worksheet and click Design Manager or Metadata Manager.
7. Select the member you want to change, use the Member Select Icon, then select Filter Member. Isolated cells containing that member are shown, enabling changes specifically to those cells within the Design/Metadata Manager.
   Note:

   Only Contributor users with design access can modify member selections in the Design Manager.
8. In the example where cells containing "6112 – Salaries: Part Time" are filtered, any changes made in the Design Manager will affect only the filtered cells.
   Note:

   Reviewer users and Contributor users without design access won't have the "Select Member" and "Select Cell Reference" menu items, won't see the "New Value" column, and cannot modify member selections.
9. Click Select, then Update to finalize the change (available only to Contributor users with design access).
10. Click Refresh.

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.

How to Design an Excel Report?

1. Open the desired Excel Report and begin by placing your cursor on the required cell and then click Design Manager.
2. In the Design Manager interface, select a model from the Model drop-down.
3. Click on POV and select a dimension from the Dimension list box.
   
   
4. Click Update. You will see that the selected cell displays the dimension member along with a SpotlightMetadata formula. Additionally, the ribbon changes to reflect Design Manager and Refresh. The selected cell now contains a drop-down menu allowing the selection of a member from the Company dimension.
   
   
   
5. Select the desired cell and click on Design Manager. Then, click on Data to set up a Data intersection and proceed by clicking Update.
   
   
   
6. Observe the selected cell now displays #REFRESH, and contains a SpotlightData formula, as shown below.
   
   
   
7. Click Refresh, and the data will be retrieved as displayed below.
   
   
   
8. Change the cell format to a numeric format, such as the Accounting format. Click Refresh again. Your formatting selection will be retained.
   Warning:

   Avoid modifying the contents of SpotlightMetadata or SpotlightData formulas.
9. Select the required cell and click Design Manager
10. Select the required Dimension.
    
    
    
11. Click Update. You will notice that the selected cell now displays the member from the Account dimension, and its content is a SpotlightMetadata formula.
    
    
    
    Note:

    There is no concept of Design mode and Run mode when using Excel Reporting. You work within a worksheet, such as Sheet 1, or multiple worksheets, where you can insert or view text, numbers, formulas, formatting, and SpotlightXL items with a live connection to the application and model in the cloud.
12. Copy or move the contents of cell B1 to any other location within the worksheet. You can create multiple copies and format them as needed. The format remains intact upon clicking Refresh.
    Important:

    As it's easy to accidentally lose Spotlight formulas by typing over them and pressing Enter, Dynamic Planning safeguards cells containing them. The formula is not changed or deleted if you attempt to delete or overwrite a cell with a Spotlight formula. However, select two or more adjacent cells together to delete the Spotlight formula and press Delete. You can use Undo if a mistake occurs.

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()

How to Use the Metadata Viewer?

1. Click on a POV cell.
2. Select Metadata Viewer.
   
   
3. Click Close.
4. Proceed by clicking on a data cell.
   
   
5. From the ribbon, select Metadata Viewer.
6. Here, you will notice that the data cell was generated from the intersection of all listed dimensions and their respective members. Please note, that this list of members might differ from the current POV selections. The Metadata Manager showcases how the data cell was initially created. When you modify the POV in the worksheet and then click Refresh, the data dynamically updates from the model, while the original formula remains unchanged.
   Note:

   If the model includes Attributes, they will be indented under the associated dimension. Grayed-out Attributes are not currently in use.

7. Click Close.
8. If you have Publish privileges, you can click on Publish within the menu ribbon to upload the model, incorporating any changes you have made.

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

Publish the Report

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.

How to Create a Template?

1. In SpotlightXL, navigate to Manage > Office Reports > Setup. Here, you can create a template that acts as a placeholder for each report that will be published and made accessible to users. One template can be used for either an Excel Report, Word Report, or PowerPoint Report. Each template reserves a spot for just one report in the Modeling cloud.
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.

Publish the Report

After the Power user has created a template for the report, a user with Publish privilege can upload the report.

How to Publish the Report?

1. Open the workbook, document, or presentation containing the report with Spotlight formulas.
   
   
2. Click Publish. The Select Report to Publish box appears and you will see the report template that was created by the Power user.
3. Select the report to publish (in this case, click 2014 Operating Expense) and click Publish.
   
   Note:

   Before the report is uploaded, the Modeling data is cleared from the file. When the report is later downloaded, the user will see all metadata and data cells as #REFRESH. When the user clicks refresh, only those members and data cells they have access to will appear.
4. You will receive a message indicating that the report has been published. Click OK.

Saving a Published Report

Make any necessary changes to the workbook, document, or deck, and click "Publish" whenever you want to save and upload the latest version. Since you have already specified the name of the report in Modeling, you don’t need to select the report template name again; it is now embedded in the workbook, document, or deck. 

To publish the workbook, document, or deck under a different name, the Power user must first create a new template. Then, the user can click "Publish As" and select the new template name.

The Assumptions Worksheet

You can set up a series of Excel Reports in a workbook that refers to an Assumptions tab in your workbook. The Excel Reports can use absolute or relative cell addresses in the Design Manager to specify where to find one or more dimension members. Cell addresses can point to dimension or member names in the row axis, column axis, or point of view (POV).

For example, if you are designing reports for each department entity or company where each group’s data is available in a worksheet, you can create an assumptions worksheet where you can input global drivers like Scenario and Fiscal Year. Point the Excel Cell References to the assumptions worksheet from each department, entity or company worksheet. Then you can update the assumptions worksheet with the required scenario and fiscal year and refresh the data in all worksheets by using the Refresh All option.

Consider the following Assumptions tab in a workbook.

Each cell in the Assumption tab is a simple text label. Member names must be spelled correctly because the data retrieval will be based on the text in the cell. Additionally, member names should not include the display label; but only use the code for the member. Here is a Summary report in the workbook that uses the Assumption tab for the page heading, column headings, and row headings.

The data cells in the Summary report are pointing to the page, row, and column headings on the worksheet, as follows:

Example of Inserting a Relative Cell Address for the Row Axis

For the Department cell reference, select all the SpotlightXL data cells in row 6 then click Design Manager.

Select the Department dimension. Click the Member Select icon > Select Cell Reference > Relative.

A box appears, asking you to select the relative cell address. Click cell A6.

After you click OK, you will see cell address A6 as the new value for the Department dimension. If you make a mistake, you can double-click the New Value column and correct.

Click Update. Click Refresh to ensure that the data is retrieved correctly. A valid department name must be in cell A6.

Example of Inserting an Absolute Cell Address for the Column Axis

For the Month cell address, select all the SpotlightXL data cells in column C then click Design Manager.

1. Select the Time dimension. Click the Member Select icon, Select Cell Reference > Absolute.
   
   
2. A box appears, asking you to select the absolute cell address. Click cell C5.
3. After you click OK, you will see cell address $C$5 as the new value for the Time dimension. If you make a mistake, you can double-click the New Value column and correct.
4. Click Update. Then click Refresh to ensure that the data is retrieved correctly. A valid time dimension name must be in cell C5.

Example of Inserting a Custom Cell Address for the Point of View (POV)

For the Account cell address, select all the SpotlightXL data cells in the table then click Design Manager.

Select the Account dimension. Click the Member Select icon > Select Cell Reference > Custom.

The New Value area in the Design Manager displays a text box for you to type the cell address.

This text box gives you the flexibility to specify any cell in any worksheet, so long as you use the correct syntax. You can mix absolute and relative in the address, as you can in Excel. Here are several examples:

Type Assumptions!$B$2.

After typing in the new value, you must press Enter (or use the mouse or arrow keys to select a different row in the Design Manager box) before the Update button is enabled. You will see cell address Assumptions!$B$2 as the new value for the Account dimension. If you make a mistake, you can double-click the New Value column and make a correction.

Click Exclude POV. The SpotlightXL Cell Addressing feature is intended to be used for row and column axis members. If you use the Relative, Absolute, or Custom Cell Addresses to specify a POV dimension, you must click Exclude POV. This ensures that SpotlightXL will give precedence to the Excel Cell Address rather than the member that was originally selected for the data.

Click Update. Then click Refresh to ensure that the data is retrieved correctly. A valid account dimension name must be in cell B2 of the Assumptions worksheet.

Additional Information When Specifying Member Names for Excel Cell References

• When specifying an Excel cell address as a SpotlightXL dimension member, that cell can contain a SpotlightMetadata formula or plain text. SpotlightXL prevents you from accidentally erasing a Spotlight formula by disallowing you to delete one cell at a time. However, you can erase Spotlight formulas by selecting 2 or more cells and pressing Del. (Note that the drop-down arrow still appears on any cells that previously contained Spotlight formulas but the drop-down is not functional.)
• If an invalid member name is used, SpotlightXL displays the data as #INVALID. In the example below, the Sales department is spelled incorrectly.

Cross-Workbook Excel Cell References

If you have an Assumptions list that is in a separate workbook, you must use the Custom Cell Reference option. The separate workbook must be open, but you cannot click into it to insert the cell reference. You are responsible for typing the correct syntax of the cell address.

Excel Reporting Income Statement Example

This topic provides an example of creating an Income Statement based on a view using Excel Reporting.

1. Select a View that contains the pertinent accounting data for the income statement.
2. Click the required sheet in the workbook, and create the headings and look of the Income Statement that you want.
   
3. Click back to the Analyze tab, and select Design With > Excel Report.
   
   
4. Click Refresh.
5. Copy the appropriate data cells to the Income Statement tab to fill in the contents of the Income Statement. Note that Gross Profit is not a member in the model so it will need its own Excel formula.
   Here is the result.
   
   Now, if you want to display your numeric values on a smaller scale, such as in millions, here are the steps:
6. Insert a heading indicating values in millions.
7. Use custom Cell Formatting ($#,,;) to display the value of the cells in millions.
   
   
   Here is the resulting report.
   

Sharing Excel Reports with People without a Dynamic Planning License Using Snapshots

Companies often need to produce reports electronically that can be accessed by individuals without a Dynamic Planning license. This capability is provided in Excel Reports with Snapshots. The Snapshot format removes all Spotlight formulas from the report and saves all the data values as regular numbers, and dimension members as regular text, in Excel. There is no option to refresh the snapshot report because it is now static, and it is saved in a separate workbook from your Excel Report.

How to Create a Snapshot Report?

1. Open an Excel Report, click the More menu and select More > Snapshot.
   
   Note:

   Ensure that the data is refreshed and correctly reflects what you want to save.
2. Give the new snapshot report a file name because it will be saved separately.
   

After saving, the snapshot report will be on your screen so you can fine-tune it before sharing it.

Dynamic Commentary in Excel Reports

You can collaborate within Dynamic Reports, Spotlight Web (Analyze and Report), SpotlightXL (Analyze and Report), and Excel-based Reports using the Dynamic Commentary functionality.

You can perform the following:

• Add cell-level comments.
• Edit, Delete, Highlight, or Resolve comments.
• Post comments for a combination of dimensions at leaf level, rollup level, and calculated members.
• Add users, text, #tags, and hyperlinks in the comments.
• Assign tasks and track their status from the Task Manager.
• Mark the Task as Complete in the Comment(s) pane.
• View all comments in the application and perform actions such as Delete and Highlight in Comments Manager.
• Post comments on cells containing data from Alternative hierarchy.

You can view an indicator whenever a comment is posted in the report. You can include the following in a comment:

• Add a user - Type @ or + and select the required username to tag the user in a comment. When you type @ or +, username suggestions are provided automatically for ease of selection. A notification is sent to the tagged user. You can turn off the notifications if required.
• Add # tags - Type # to find the most used tags in a comment.
• Add URLs in a comment.

How to Post a Comment

Select the report, click the More list box, and select Show Properties. The location of the report is displayed in the Properties pane.

1. Navigate to the More drop-down menu in the SpotlightXL tab and click the Comments On option. By default, the Comments On option is displayed in all existing and new SpotlightXL Excel- based reports. A new Comments section with the Comments option is displayed in the SpotlightXL tab.
   
   
   
2. Select the required cell and click Comments. The Comment(s) pane is displayed.
3. Enter the required text and click Comment. The comment is saved and displayed in the Comment(s) pane.
   
   You can view an indicator whenever a comment is posted in the report.

How to Edit a Comment

1. Click the icon on the right-hand side of the comments pop-up window and select Edit from the menu list.
2. Make the required changes and click Save.

You can only edit the comments posted by you. The administrator can update comments posted by any user. You can edit comments from anywhere (Dynamic Reports, Spotlight Web (Analyze and Report), SpotlightXL (Analyze and Report), and Excel-Based Reports) within the application irrespective of where you posted the comment.

How to Highlight a Comment

To highlight a comment, click the three dots on the right side of the comment pop-up window and select Highlight from the menu. The Highlight icon will then appear next to the comment.

How to Delete a Comment

1. To delete a comment, click the three dots on the right side of the comment pop-up window and select Delete from the menu.
2. Click Confirm to delete the thread.

You can only delete the comments posted by you. The administrator can delete comments posted by any user. You can delete comments from anywhere (Dynamic Reports, Spotlight Web (Analyze and Report), SpotlightXL (Analyze and Report), and Excel-Based Reports) within the application irrespective of where you posted the comment.

How to Resolve a Comment

You can resolve a comment thread by clicking Resolve visible in the first comment of the thread. Resolved comments are not displayed in the reports or when the report is exported. Once you resolve a comment, the visual indicator is not displayed on the cells.

You can resolve the comments from anywhere (Dynamic Reports, Spotlight Web (Analyze and Report), SpotlightXL (Analyze and Report), and Excel-Based Reports) within the application irrespective of where you posted the comment.

You can view the resolved comments from the Comments Manager page. An icon is displayed next to resolved comments as shown in the image below.

Use the Filter icon to filter resolved comments in the grid. Also, you can delete resolved comments using the More option. The image below shows the filter icon in the upper right-hand corner of the screen.

Once a comment is resolved, you cannot reopen, edit, reply, or highlight that comment. However, a new thread can be started for the same cell.

How to Assign a Task

1. Right-click in the required cell and select Comment. The pop-up window appears.
2. Enter + and type the user name.
3. Select Assign.
4. Enter the following details:
   1. Add task title
   2. Due On - Select the required date.
   3. Priority- Select any of the following:
      1. Low
      2. Medium
      3. High
5. Click Comment. A notification is sent to the mentioned user.

The assignees and admin will have access to the assigned tasks. You can view the task details from Task Manager.

Mark Task as Completed in Comments Pop-up Window

You can change the status of any of your assigned tasks to Completed directly from the Comments pop-up window using the Mark as Completed toggle.

Only Assignee, Reporter, or Administrator can mark the status Completed using the Mark as Completed toggle. The Mark as Completed toggle is available in Dynamic Reports, Spotlight Web - Analyze and Reports, SpotlightXL - Analyze, Reports, and Excel-Based Reports. The following image shows the status of a task marked as completed using the toggle.

When you mark any task Completed from the Comments pop-up window, the same status is automatically updated for the corresponding task on the Task Manager page.

How to Email Notifications

You can configure email notifications for the Dynamic Commentary functionality from the My Setting page. This functionality helps you track actions in the Dynamic Commentary functionality and keeps you up to date by providing email notifications for the selected action based on your requirements. You will receive email notifications with hyperlinks to directly navigate to the respective product area. This helps you access reports and tasks quickly from emails.

The email hyperlink redirects you to the corresponding artifact (for example, a Dynamic Report, a SpotlightXL View, or a SpotlightXL Report) from which the comment was posted and also opens the corresponding Comment pop-up window.

If the email notification is regarding an assigned task, the email hyperlink redirects you to the corresponding task on the Task Manager page.

To configure email notifications for Dynamic Commentary, click your Profile Name, navigate to My Settings > General tab and then select or deselect the required check box under the Enable Email Notification for the Dynamic Commentary section.

The following actions are available for which you can enable email notifications:

• All Actions - Select this check box to enable email notifications for all actions available in Dynamic Commentary. By default, this check box is selected.
• Replied to My Comment - Select this check box if you want to receive email notification only when someone replies to your comment.
• Mentioned on Comment - Select this check box if you want to receive email notification only when someone mentions you in any comment.
• Assigned Task - Select this check box if you want to receive email notification only when someone assigns you a task.
• Marked Task As Complete -Select this check box if you want to mark the task as completed.
• Resolve Comment - Select this check box if you want to resolve the comment.

You will receive email notifications only for the selected actions. By default, all checkboxes are selected.

How to Edit a Task in Task Manager

The tasks created from a comment are editable by the Task Manager. This applies to the information mentioned in the Comment pop-up window. If you edit any of the task information from Task Manager, the updated information will be reflected in the Comments pop-up window.

If a task is completed, then a tick mark is visible on the Comments pop-up window against the respective task name.

If you change the assignee from Task Manager, the assignee details in the Comment pop-up window are not updated. If a task is deleted from Task Manager, it is removed from the Comment pop-up window, however, the user mentioned in the comment is retained.

How to Extract Comments with the Dynamic Commentary Option

You can extract comments from a range of cells in an Excel-Based report and save them in a new cell. This allows you to view comments from multiple selected cells in one place.

You can use the Dynamic Commentary option under the More drop-down menu in the SpotlightXL tab (as shown in the image below). When you click this option, the Dynamic Commentary pop-up window is displayed where you can select the required cells in the Select Cells field to extract comments.

The Dynamic Commentary pop-up window provides the following three checkboxes to define additional configuration settings:

• Display only Highlighted Comments - Display only the highlighted comments in the Dynamic Commentary cell. By default, this check box is not selected.
• Display Username and Date - Display the username and date for each comment in the Dynamic Commentary cell. The date and time are converted to the user’s timezone. By default, this check box is not selected.
• Wrap Text - Wrap or enclose the selected cell’s content if the text in the cell overflows the cell boundary.

You can select a range of cells from multiple rows and columns, and extract comments in single or multiple cells. You can add multiple Dynamic Commentary cells in a report. The #tags, hyperlinks, and the tagged username are displayed as plain text in the Dynamic Commentary cell.

1. Select the cell where you want to extract comments.
2. Navigate to the More drop-down menu in the SpotlightXL tab and click the Dynamic Commentary option. The Dynamic Commentary pop-up window is displayed.
   
   
   
3. Select the following checkboxes based on your requirements:
   1. strong>Display only Highlighted Comments
   2. Display Username and Date
   3. Wrap Text
4. Select the required cells in the Select Cells field. The SpotlightXL pop-up window is displayed.
   
   
   Note:

   You can only select up to 78 cells.
5. Select the required cells and click OK. The Dynamic Commentary pop-up window is displayed.
6. Click Create, and then click Refresh. The comments are extracted in the selected cell.
   
   If you select a row and extract all comments to the Dynamic Commentary cell, then you can quickly extract all comments in the adjacent rows by dragging down the Dynamic Commentary cell.

Follow these steps to drag the Dynamic Commentary cell:

1. Select the Dynamic Commentary cell.
2. Rest your cursor in the lower-right corner so that it turns into a plus sign (+).
3. Drag the fill handle down the cells that you want to fill as shown in the image below.

Using Substitution Variables and Expressions with Excel Reports

Substitution variables and substitution variable expressions are used in reports in place of dimension member names to make report maintenance easier. Both formatted reports and Excel Reports can use substitution variables and substitution variable expressions. Substitution variables and expressions are defined by a Power or Contributor user with a particular value, however the value can be dynamically modified at runtime by end users.

You can set up the substitution variables and expressions in a formatted report and then Capture it to convert to a Excel Report. Or you can set up the substitution variables and expressions directly in the Excel Report. A Substitution Variable menu item is available on the ribbon to make it possible for users to change the value of the variable while using the report.

Creating Substitution Variables In Excel Reports

The process is the same as for formatted reports.

1. Power or Contributor users create one or more substitution variables in the model.
   
   
2. Power or Contributor users create one or more Excel Reports and insert the substitution variables.
   
   
3. Power or Contributor users specify substitution variable expressions in the design of the report. See Example Substitution Variable Expressions for details about expressions.
4. Users can change the value of the substitution variable using the Substitution Variables menu item from the Excel Report ribbon.
   
   
5. With the cursor anywhere in the report, click Substitution Variables. The Substitution Variables box appears. It shows the name of the variable and the current value.
6. Click the Member Select icon.
7. From the member selection box, select the month that you want to see in the report and click Select.
   
   
8. Click Refresh to refresh the report.

Example Substitution Variable Expressions

This topic is intended for administrators and designers and provides an example of expressions, syntax, and expression chains.

Consider the following dimension hierarchy for a fiscal year that begins in July each year. Assume that the model contains only these three years.

Expression Syntax

The following expressions are available for use with substitution variables. Assume that @CurYear@ is FY2018 and @CurMth@ is Jul 2017.

Expression Chains

You can chain expressions together. Each entry in the expression builds on the results of the previous entry, reading from left to right.

@CurMth@.Parent.FirstChild = Sep 2023. CurMth’s Parent is Q4 2023 and the First Child of Q4 2023 is Sep 2023.

@CurMth@.Lag(3).Parent = Q2 2023. CurMth’s 3-month Lag is Jun 2023 and the Parent of Jun 2023 is Q2 2023.

@CurMth@.Parent.Lead(2) = Q1 2024. CurMth’s Parent is Q4 2023 and 2 quarters ahead is Q1 2024.

When adding functions to a substitution variable, you may find the need to change the order or to remove one of the expressions. Click the menu button next to the expression to make the changes.

If you want to cancel out from adding expressions, click inside the Design Manager or press Esc.

Best Practices

• Each workbook can contain up to 30 worksheets and 7000 data cells per worksheet. If you are creating large volumes of cell references per worksheet, then consider using Excel 64-bit.
• When you click Refresh or Refresh All, avoid doing anything else in the workbook or other workbooks until the Refresh operation is complete. In large workbooks, Refreshing is a lengthy operation, and you can view the status of the operation in the Progress Information box and use the More Detail or Less Detail button. But if you do anything else in Excel or in other applications on your computer before the Refresh is complete, SpotlightXL detects it as an interruption and may display some cells as #REFRESH when done.
  
• If you click Analyze or Report while in a sheet containing your Excel Report with Spotlight formulas, Dynamic Planning asks if you want to clear the Spotlight formulas. It is best to click the Analyze tab or open a new tab before selecting Analyze from the menu. Similarly, if you want to run a formatted report, click the Report tab or open a new tab before selecting Report from the menu.
• When working with Published reports, one person in your organization should be made responsible for making changes to the report structure. Other users can then open/download the reports and refresh the data.
• Publishing reports to the Dynamic Planning 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, although SpotlightXL supports up to 50 reports per application, of no more than 16MB each.
• In Excel Report, it is recommended not to combine Excel Report formulas with other Excel formulas. This approach worked fine in the past. However, there has been a change in behavior with the latest update of Microsoft Excel's autocomplete feature. Now, when data is entered or calculated, if the result doesn't match an item in the list, it will display the first value from the list, which is typically a GUID. Since our dropdowns typically start with GUIDs, as a result, users may see GUID.

You can also find installation instructions for the Office 365 application in the following resources:

• Microsoft Office Customization Tool Overview
• Change the Microsoft 365 Apps Update Channel for Devices in your Organization Deploy Office
• How to Revert to an Earlier Version of Office - Microsoft Support

If you need further assistance, please reach out to your IT team.

How to Check the Release Version of the Office 365 Application?

1. Open Excel and navigate to the File.
   
2. Click Account.
   
3. The Product Information, including the release version, will be displayed.