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.

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.