Designing Reports in SpotlightXL

Overview

In the Design subtask, you have the option to modify an existing report or create a new one. To initiate a new report based on an existing one, click here. If you want to create an entirely new report, go to Report > Design. 

With the New Report feature, you can consolidate data from multiple reports saved across different models. For example, if you have an Operating Expense report for both a corporate model and a foreign subsidiary model, you can merge these reports. Simply copy and paste the relevant data from each report into the New Report spreadsheet to create a comprehensive view of the Operating Expenses for both models.

How to Create a Report Using the New Report Option?

1. In SpotlightXL, go to Report > Design.
2. Click New Report.
3. Copy and paste report details from captured data intersections. 
4. Click Save.
5. Enter a name for the report in the Name field and click Save.
   
6. Optionally, click Properties.
   1. Select a model to save the report to in the Save on Model cell.
   2. Select the group or groups you want to have access to the report.
      Note:

      • By default, no user groups are selected, so only Power Users and the report creator will have access.
      • You can only assign access to groups you are a member of and have model access.
      • There must be a blank row between the Group section and the POV section. See: How to work with Folders in SpotlightXL
7. Click Save.
8. Select Report > Run to view the report.
   Note:

   The New Report is available for existing reports with captured data intersections.

Substitution Variables

Substitution variables are used in reports and calculations in place of dimension member names. Reports can be defined using substitution variables that can be dynamically modified at runtime by end users. Substitution variables can be used in any dimension, but are most commonly used in the Time and Scenario dimensions. Additionally, Power Users and Contributors can set up substitution variables with expressions. Expressions are added to a substitution variable to derive another member name from the same dimension.

Here is the overall process:

• Power User or Contributor creates one or more substitution variables in the model.
• Power User or Contributor creates reports and/or calculations that use the substitution variables.
• Any user with access to the report can run it and change the value of the substitution variable, provided they have security access to the dimension.
• Creating Substitution Variables for Reports

To set up formatted reports with substitution variables, see How to Add a New Substitution Variable.

How to Use Substitution Variables in Reports?

1. In SpotlightXL, go to Report > Design. Select all of the cells containing the dimension member to be replaced with a substitution variable.
   When a user generates a report that includes a substitution variable, a new icon will appear on the menu ribbon.
   
   
2. Click Design Manager.
3. Find the desired dimension member and click the Member Selection icon, then Select Member.
   
   
4. At the top of the member selection box, notice that Substitution Variables are listed first. Select the desired substitution variable and click Select.
   
5. Now the Design Manager reflects the change from the original value to the substitution variable. 
6. Click Update. The report now displays UPDATED in place of the data cells you originally selected.
7. Click Save to save your report.
   

How to Select a Different Value for a Substitution Variable at Runtime?

1. In SpotlightXL, go to Report > Run.
2. 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.
3. Click the Member Selector icon.
   
4. From the Member Selector box, select the desired value that you want to see in the report.
5. Click Select.
6. Click Refresh to refresh the report.
   
   The report now displays data for the selected value. Using this method, the user can easily move back and forth between other values, without updating the definition of the report and without the need for the Power User or Contributor to update the substitution variable value permanently.
7. To reset the substitution variable back to its initial value, click Substitution Variables. From the Member Selection icon, select Reset to Default.
   

Using Substitution Variables with Expressions

Dynamic Planning supports the ability to enhance substitution variables by incorporating expressions. These expressions refer to various members within the dimension associated with the predefined value of the substitution variable.This capability will facilitate easier maintenance of the report in the future.

How to Use Substitution Variable wit Expressions?

1. In SpotlightXL, go to Report > Design and select all the cells in the worksheet.
2. Click Design Manager.
3. Find the desired dimension member and click the Member Selector icon, then Select Member.
4. Select the desired substitution variable and click Select. Now the Design Manager reflects the change from the original value to the Substitution Variable. 
5. Click the Member Selector icon again, then Expression. The Expression box appears and variable is already selected.
   
   
6. Click Add Function and select the desired option.
   
   
7. The Expression box now shows the Offset option.
   
   
   Note:

   By default, the offset is 1. Offset refers to the number of members above or below the current member in the dimension hierarchy. Offset works within the current level and it skips over rollup members. For more details on Expression Syntax, click here.

8. Click Update then click Update again to save your changes and exit the Design Manager.
   
   The report now displays UPDATED and the expression shows in place of the data cells.
9. Save the report design by clicking Save or Save As. Consider changing the name to preserve the original report until you are confident that the new report is accurate. Now when you run the report, you can use the Substitution Variables box to change the starting value in your hierarchy.
10. After selecting any other value, click Refresh.

Converting Existing Reports with Substitution Variables to Use Expressions

Often times, many existing reports are set up with multiple substitution variables. For easier maintenance, these reports can be converted to use one substitution variable and many expressions.

Consider the model has a list of substitution variables that are used in all of the company reports to show data by month, quarter, or year. To convert reports to use a single substitution variable, start by selecting a member as the reference point for creating expressions.

For example, if you start with the first month of your fiscal year, you can use Sibling, Lead, or Lag to offset within the same level as other months. To move back one level, use Parent, then apply Lead or Lag to offset to quarters. Similarly, use Parent again to go back two levels and offset to years.  Or you can start with the year and use Child expressions to get to the quarters and months.

How to Convert Existing Reports with Substitution Variables to Use Expressions?

1. In SpotlightXL, go to Report > Design. Select all the cells in the worksheet, then click Design Manager. 
2. Find the desired dimension member. Click the Member Selection button and click Select Member.
3. Select the desired substitution variable that you created. Click Select. The New Value column now shows the new selected substitution variable.
   
   
4. Click Update. The report now displays UPDATED. With all the cells in the worksheet still selected, click Design Manager again.
5. Click the Member Select button and click Expression.
6. Click Add Function and select Lead. Keep the default Offset of 1, and click Update. The New Value column now shows the selected substitution variable plus expression.
   
   
   Note:

   Users should repeat this process as necessary to replace any additional values with substitution variables.
7. Click Update to apply the changes to the report. Add any additional substitution variables and expressions as needed.
   
   
   
8. Now save the report design by clicking Save or Save As. Consider changing the name to preserve the original report with multiple substitution variables until you are confident that the new report is accurate.
9. Now, go to Report > Run. When running the report, you can click the Substitution Variables button on the ribbon to change the substitution variable.

Adding Groupings to Reports

Rows and columns can be grouped and saved for a report from SpotlightXL. Multiple groupings can be defined for a single report. When the reports defined in SpotlightXL are accessed from Spotlight (web version), the grouping defined on the report in SpotlightXL is retained. This functionality is available for all reports created for any model.

How to Add a Group to a Report?

1. In SpotlightXL, navigate to the Report > Design.

2. Highlight the rows or columns you want to group as shown below.

3. Click the Data tab and select Group.
   
   

4. Return to the SpotlightXL tab and click Save.
   
   Run the report. Notice the grouping is applied.
   

How to Add a Chart to a Report in SpotlightXL?

To add a chart to a report in SpotlightXL, ensure you are logged in and have the necessary user access to design reports.

1. In SpotlightXL, go to Report > Design and select the desired report.
2. Click the Insert menu tab and select the desired Chart. For a full list of supported chart types, see Chart Formatting and Types 
   
   
   Note:

   You may have up to 4 charts per report.
3. Drag the chart to position it within the spreadsheet. Format the chart and report as desired.
   
   
4. Click the SpotlightXL tab and click Save. Select Report > Run to view the results of your work.