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.
   

Identifying POV Dimensions

You often use the Design Manager to pinpoint what dimension intersections are being used for the data cells. Design Manager clearly shows how POV cells are being used. In a report, select one or more data cells and then click Design Manager. 

Similarly, if a user is running the report and wants to see what dimensions are being used in the report, they can select Metadata Viewer from the More menu. Then use Preview Filter Member to see details.

Excluding Dimensions in Reports

You can select to exclude point of view (POV) dimensions in reports. For example, in a model, if the price is maintained at the region level (Region dimension) versus the customer level (Customer dimension), you can exclude the Customer dimension as a POV in the report.

How to Exclude Dimensions in Report?

To exclude dimensions in a report, do the following:

1. In SpotlightXL, go to Report > Design, select all the data in the report, then click Design Manager.
2. On the Design Manager window, click the Exclude POV checkbox.
3. Next to Customer, click the Exclude POV checkbox.
   
   
   
4. Click Update.
5. Save the report changes.
6. Run the report. When you change the POV for Customer and Refresh the data, no change is reflected. Only your changes to the Region or Scenario dimension affect the data.

Shared POV Dimensions Across Models in a Report

It is possible to design a report with a single Point of View (POV) cell that influences the data visible across multiple models represented by the data. This feature provides flexibility by reducing the need for multiple dimension selections when creating a consolidated report. A consolidated report amalgamates data from multiple models.

Consider two models, Sales Rep, and Sales Forecast, set up for an enterprise. The objective is to generate a consolidated report from both models. Some dimensions, such as Scenario and Region, are common between the reports. Despite the models sharing common dimensions, individual selections of dimensions from each model are necessary to create a consolidated report. Shared POVs enable the configuration of a single POV dimension for common dimensions within a report.

How to Set up Scenario as a Shared POV?

1. In SpotlightXL, go to Report > Design and select the first Scenario POV cell and click Design Manager. 
2. Use the horizontal scrollbar under Share POV and select the desired member.
   
   
   
3. Click Update. Next, delete the row containing the second Scenario POV cell.
4. Save the report design, then go to Report >Run. When you change the Scenario selection and click Refresh, the data for both reports is updated.

Refreshing the Report Data on a POV Change

With Refresh on POV change, you can specify if you want to refresh the report each time you change POV to see report data. Suppose you set Refresh on POV change to Yes. When you run a report, choose a POV to view the report, the report data contains refreshed data. By default, this property is set to No.

While running the report you can change the POV dimension member and if this property is set to Yes then the report will automatically refresh once you select any POV dimension member. In a sample report, if you have the Time dimension as a POV dimension and you select 2023 from the Time dimension then the report will be refreshed and the data for 2023 will be displayed in the report.