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.

Design Manager

Design Manager provides advanced reporting capabilities, allowing for detailed data intersections and the selection of dimension members from multiple models. It also enables you to choose Point of View (POV) options. 

How to Use the Design Manager?

1. In SpotlightXL, Go to Report > Design.
2. Select the desired cell and click Design Manager to view the current dimension and member. 
3. You can change the dimension value by clicking the member select icon, selecting a new value, and then click Update. All row and column dimensions and members are treated as "member" cells. See Identifying POV Dimensions for more information.

The cell is referred to as a "data" cell. Data cells represent data intersections, which are formed from the intersection of all dimensions in the model. If you select a new value for any of the dimensions, the data cell is automatically updated based on selections once you click the Update button on the Design Manager interface.

Select a blank cell and click Design Manager. Notice how the interface is updated to give you the option to create a data cell, dimension member cell, or POV cell.

You can also report off of any one model or multiple models available to you in the Model list box. 

Description of Fields

• Data - data intersection
• Member - Dimension Member
• POV - Option to Update Report Data

Data - Data Intersection

Report on the combination of two or more dimension members. Select members from a dimension intersection to populate the value within the report. You can pull data from an intersection for any model to which you have access. When Data is selected, the Model list box is available along with the dimensions that belong to the selected model as shown below.

You can select to exclude point of view (POV) dimensions in reports. For example, if your report has a page-level member for Company (My Cycles Company) and you want to show one cell of Total Corporate (My Holding Group) as a comparison, you can add the Total Corporate cell and exclude (or override) the Company POV for that cell.

Member - Dimension Member

Display a specific member name from a dimension. This replaces the cell with the name of the selected dimension member. When Member is selected, the Model and Dimension list boxes are available. Select the model from which you want to populate the report with dimension member information. Select the dimension that belongs to the selected model to populate your report.

When using POV and substitution variables, the member names will change based on the selected context.

POV - Option to Update Report Data

Make POV (point of view) selections based on a model and dimension. A POV is a method to allows users to select a particular member from a list box and then see all data in the report updated for that member. For example, if you want the user to be able to view different accounts, then put the Account dimension on the page level of the report, and make it a POV cell. As a result, data displayed in the report is controlled to display the selected Account dimension member. When POV is selected, the Model and Dimension list boxes are available.

Attributes

Select this checkbox to display the attributes that are available in the selected model. Uncheck this checkbox to hide the attributes from the Design Manager.

Filtering Members

Filtering member options is helpful when updating an existing report, finding specific report dimension members, and previewing members you might filter. 

Filter Member

How to Use Filter Member Option in the Design Manager?

1. Open a report in design mode by selecting the Report > Design.
2. Select the entire spreadsheet and click Design Manager.
   
3. Select a dimension member and select Filter Member.
   

Preview Filter Member

How to Use Preview Filter Member Option in the Design Manager?

  Filter Member:

1. Open a report in design mode by selecting the Report > Design. 
2. Select the entire spreadsheet and click Design Manager. 
3. Select a dimension member you want to preview before filtering. 

The result is that the cell range is displayed as shown below. This way you know exactly where the member exists within the report.

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 POV 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 POV Dimensions in Report?

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.

Format Manager

Apply formatting options and lock cells. For example, display reports in various amount formats (such as thousands, millions, and billions). Select a cell or range of cells and click Format Manager to launch the Format Manager dialog page.

A description of each of the fields on the Format Manager dialog page is provided below:

• Locked - Select Yes to lock a selected cell or cell range to prevent modification. For example, you want users to input only certain portions of report data.
• Amount Format - Select to format numeric cells in Tens, Hundreds, Thousands, or Millions. Amount Format applies to reporting only.
• View/Select Range - Change the cell or cell range applied for the format you are changing.
  
• Update- Apply Format Manager selections to the cell or cell range selected. Save the report and then run it to display the selected cells in the appropriate format.
  Note:

  Locked and Amount Format properties are applied for reporting purposes only. When a report with locked and formatted cells is run, you will not be able to modify the data in these cells.

Locking and Unlocking Cells in a Report

You can lock data cells in a report to prevent modification by users. For example, you want users to input only certain portions of report data.

How to Lock Cells in a Report?

1. In SpotlightXL, go to Report > Design.
2. Select the data cells, then click Format Manager from the menu ribbon.
3. Specify Yes for Locked, and then click Update.
4. Alternatively, you can click the cell selector icon to specify the range of cells to lock.
5. Select the cells, then click the icon to Set as the New Range.
6. In the Format Manager box, click Update.

Applying Scale to Numbers

You can use scaling in SpotlightXL to see numeric values in the tens, hundreds, thousands, and so on. Displaying numbers in a smaller form can make a report appear cleaner and easier to understand. For example, on the Report Design page for the Annual Sales Revenue report, the Thousands scaling Amount Format is applied and then saved.

When you run the report, numbers are divided by 1000 and displayed in the worksheet. The source data is not changed.

Save / Save As

The Save option allows you to save the report. When you select 'Save As' from the Report Design page, all Report Properties are reset to default settings except for 'Created' and 'Modified' properties. Update the Report Properties accordingly and define group access for the new report. The 'Created On' and 'Created By' fields are automatically updated by the system based on the user initiating the 'Save As' action, while the 'Modified On' and 'Modified By' fields remain blank until modifications are made to the report.

If 'Save As' is chosen from the Report Design Properties page, most Report Properties, such as Design Manager, Format Manager, and Point of View (POV) settings, will remain the same as in the source report, except for group access and the 'Created' and 'Modified' properties.

How to Perform Save As for a Report in SpotlightXL with Default Selections?

1. Go to Report >Design.
2. Select Save As.
3. Enter a name for the report and select Save.

Report Properties

Clicking the Properties option allows you to enable save, provide groups with access to the report, define Point of View (POV) selections and filter criteria, and much more. Below is a description of each option within the Properties section:

Description

Enter a brief description of the report.

Display

Display dimension members by label (Display Label ) or Code. This functionality provides the flexibility to both analyze and report the data based on Code or based on Display Labels for the dimensions in the Model.

For models sourced from Structured Planning, the Display Label defaults to the dimension member code name. For example, if a dimension member is named Executive and the code is Exec, the Display Label would be Exec - Executive. If no name exists for the dimension member, the dimension member code is displayed. You can set the Display Label for each report or view.

See Also: Customizing the Display Label

Hide Gridlines

Yes - Hide gridlines from display in the report.

No - Show gridlines in the report which is the default.

Hide Headers

Yes - Hide header lines from display in the report.

No - Allow header lines to display in the report, which is the default.

Enable Comments

Add comments to the report.

Enable Save

Save data for all models available in the report.

None - This is the default option, when selected no data will be saved.

Calculation Model - This option saves the data of the model where the calculation is running. This means that when you save the report, the calculation is executed automatically as part of the save function.

All Models - This option saves the data for all models available in the report or a specific model in the report.

Click to view a drop-down that provides the possible selections:

Calculation on Save - Include a Calculation, which will be executed when you click Save. The calculation name should be provided in the available property. This property is dependent upon the Enable Save property when creating a model. Enable Save must be set to Yes to execute this calculation.

By default, Calculations run in the background, and users must wait until the calculation is complete before continuing to use the report. Power Users can set an application-wide setting to run calculations in the background for all views and reports that trigger calculations. Those calculations must have been created with the option Run in Background set to Yes. After the calculation has completed its processing, an email is automatically sent to the user that triggered it.

Refresh on POV Change

Yes - automatically update the report based on point-of-view changes.

No - the report will not be updated when point-of-view changes are made.

Suppress Rows

None - This is the default. Blank and Zero rows will appear in the report.

Zero - Rows with zero values will be hidden in the report.

Blank - Rows that contain no data (blank) will be hidden in the report.

Both - Zero & Blank - Both Blank and Zero rows will be hidden in the report.

Suppress Columns

None - This is the default. Blank and Zero columns will appear in the report.

Zero - Columns with zero values will be hidden in the report.

Blank - Columns that contain no data (blank) will be hidden in the report.

Both - Zero & Blank - Both Blank and Zero columns will be hidden in the report.

Created On

The date the view was created. The system populates this cell.

Created By

The username of the user who created the view. The system populates this cell.

Modified On

The date on which the view was last modified. The system populates this cell.

Modified By

The username of the user who last modified the view. The system populates this cell.

Group

The name of the user group(s) you want to have access to the report. You can assign more than one group access to the report. This field is optional. You do not need to specify a group for the newly created report. If no group is specified, the report can only be viewed by you.

You can add group access to only the group(s) for which you are a member. For example, if you are a member of Group “Finance” but not “Operations”, you can assign Group “Finance” and you will receive a validation error if you try to set access to “Operations”. The list of available groups is the complete list of groups to which you have access and also have access to the model for which you are trying to save a report.

POV

Select POV to define a dimension to control the data displayed in a report. POV is a user-specific method of modifying settings to desired dimension members. POVs apply to all objects on a report (for example, grids and charts).

Filter

Select a dimension member based on the POV dimension selected.

Variable Name

The name of the substitution variable applied to the report.

Model

The name of the model the report is associated with.

Dimension

The dimension member will be replaced with the entered/selected 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.