1.7.1 Modeling Release Notes, June

This document describes all the features and enhancements with this release of Spotlight/SpotlightXL, as well as any known and resolved issues.

Features and Enhancements

Shared POV dimension across Models in Report

Export Data

Navigation Access for all sub-tasks for both Designer & Reviewer

Support for Excel a

Freeze Panes and Grouping in Report Design

Support for Variables in Formula Reference

Additional Enhancements

• Extending audit log

• Default Value for Run In Background property

• Support for #SKIP# in Formula

• API Library

Known Issues

Resolved Issues

Shared POV dimension across Models in Report

We are adding an enhancement to share POV dimensions across models that are available in reports. This enhancement gives flexibility for you to minimize the number of dimension selections currently performed for creating a consolidated report. A consolidated report is a report that brings data from multiple models.

Consider two models, Income Statement and Balance Sheet configured for an enterprise. The requirement is to create a consolidated report from both models. Few dimensions like Time, Scenario, Company are common between the reports. Notice that, even though models contain common dimensions you select dimensions individually from each of models to create a consolidated report. Shared POVs allow you to configure one POV dimension for common dimensions in a report.

To generate a consolidated report below that contains the data from both models (1 – Income Statement & 2 – Balance Sheet), you select Time and Scenario POV dimensions from inside each of models to see a consolidated report.

Consolidated Report (Income Statement & Balance Sheet)	Common Dimensions (Time and Scenario) in the report.

Summary of Steps to Configure Shared POV

This section provides summary of steps with examples at each step to indicate how to configure a shared POV across models.

Step Number	Description	Example
1	Select the common POV dimension that you want to share across multiple models.	Let’s say you want to share Time dimension across both Income Statement and Balance Sheet models. Time dimension as shared POV then we need to delete Time POV dimension from one model from the report and then open design manager and select applicable model and then save the report.
2	Delete the POV from one of model.	Delete the Time POV dimension from the Income Statement model.
3	Navigate to the other model that contains the common POV.	In Report design, go the area that contains Balance Sheet model.
4	In another model, select the common POV, open Design Manager, select applicable model and save the report.	Select the Time dimension POV from Balance Sheet model and click Design Manager, it displays the list of models available in the report which also contain Time dimension. In this case, Design Manager displays Income Statement model in the Share POV section because the Income Statement model also contains the Time dimension.
5	Run report. Repeat steps 1 to 5 to configure another shared POV dimension.	The Time POV dimension is shared across Income Statement and Balance Sheet models.

Export Data

With Export Data you can export leaf data that is available in the model. You also can filter data (for example, specify filter settings for a specific scenario or a specific time period) and export the data to either a text or csv format. Select the Model task and the Export Data subtask to specify the export data settings. You can save the export data settings and choose to export data at a later point in time.

Navigation Access

Previously Navigation Access was available only for the Designer role. In this release, Navigation Access is extended for the Reviewer role.

In a previous release, only few subtasks were available for an administrator to enable/disable access to subtasks. In this release, all the Modeling subtasks in Navigation Access page are made available. From this page, an administrator can select what subtasks can a Reviewer/Designer users can view in their application.

• To configure navigation access for Reviewer, on the Navigation Access page, select the Reviewer.

• Select Yes to allow users assigned the Reviewer role to access the subtask. Select No if you do not want Reviewer to access the subtask. In the example below, Designer users will have access to Run and Design for Reports, Data and Design View for Analyze and Setup and Calculations for Model subtask.

1. If a user is granted access to Design View then that user will have access to Run View.

2. If a user is granted access to Design Report then that user will have access to Run Report.

By default Design Model, Design Formula sub tasks are not enabled for Designer users. If required, administrators can enable access to each of them.

Support for Excel a - Spotlight XL

Modeling supports Microsoft Excel a from this release onwards.

Freeze Panes & Grouping in Report Design

In this release, we are adding an enhancement to allow users to use freeze panes and grouping rows/columns in the report.

Users while designing the reports can use these settings in Excel which will be saved as part of the report definition and will be retained when they are running the reports.

Freeze Panes: Open a report in Design mode and select the cell and apply Freeze Panes and save the report. The Freeze Panes settings are saved as part of the report definition and will be retained when report is executed.

Freeze panes before Executing Report

Freeze Panes after Executing Report

Grouping: Open a report in Design mode and select the columns or rows which you would like to Group and save the report. The Grouping settings will be saved as part of the report definition and will be retained when the report is executed.

Group Panes before Executing the Report

Group Panes after Executing the Report

Support for Variables in Formula Reference

Variables can now be used in Formula Reference section. Variables will be very helpful to define the formulas for dynamic calculations like weekly trend. With this release we are allowing the usage of Variables in formula reference section as well. Variables are allowed in Formula scope, Formula section and also in Formula Reference section. Usage of Variables is formula reference section is same as how Variables are used in Maps, Formula and so on. Values for the variables can be provided in the Calculation.

Additional Enhancements

This section provides additional enhancements for this release.

Extending Audit Log

Audit log is now available in the below areas of the application as well. The following information is now available for audit:

• Model Permissions

• Model Map (Save + Delete),

• Model Setup (Generate, Clear, Save, Delete)

• Dimension Setup

• Application Settings

Default Value for Run in Background property

The Run in Background option is configured to Yes by default. It is suggested to run the Calculation in the background when running the Calculation.

Support for #SKIP# in Formula

With this release, we are supporting to skip lines from the formula scope section while executing the formula as part of the calculation. You can select #SKIP# as the value for the variable defined in the formula section to skip that line from the formula execution.

API Library

Page dimensions

Page dimensions are optional. If the user chooses not to have page dimensions, they may skip this tag completely. Alternately an empty tag can be specified as well.

Page-dimensions tag comprises of all dimensions that have page level scope. In order to have page level scope, the single unique dimension member of this dimension should apply to all data-records.

Column dimensions

Column-dimensions tag comprises of all other dimensions that are not identified as page-dimensions. Each data-record provides a dimension member for every dimension identified in this tag. These dimension members can be unique or non-unique across all data records.

For example: Consider the following records for loading the data:

Table 1

These records are to be loaded into a model with four dimensions:

• Account (with dimension members 1001, 1002, 1003, 1004, 1005)

• Scenario (with dimension member ‘Actual’)

• Department (with dimension members ‘Sales’ and ‘Marketing’)

• Time (with dimension member ‘Jan-13’)

Here, Scenario: Actual and Time: Jan-13 qualifies to be extracted to page level scope. But, since page-dimensions are entirely optional, below four examples are valid.

1a. Scenario as page dimension

<page-dimensions>
<entry>
<key>Scenario</key>
<value>Actual</key>
</entry>
</page-dimensions>
<column-dimensions>
<entry>
<key>1</key>
<value>Account</value>
</entry>
<entry>
<key>2</key>
<value>Department</value>
</entry>
<entry>
<key>3</key>
<value>Time</value>
</entry>
</column-dimensions>

1b. Time as page dimension

<page-dimensions>
<entry>
<key>Time</key>
<value>Jan-13</key>
</entry>
</page-dimensions>
<column-dimensions>
<entry>
<key>1</key>
<value>Account</value>
</entry>
<entry>
<key>2</key>
<value>Scenario</value>
</entry>
<entry>
<key>3</key>
<value>Department</value>
</entry>
</column-dimensions>

1c. Empty page dimensions

<pagedimensions/>

1d. No page dimensions: Skip this tag completely

Additional information with a sample Payload

Consider a model that has four dimensions:

1. Account (includes dimension members 1001 and 1002)

2. Scenario (includes dimension member Actual)

3. Department (includes dimension members Sales and Marketing

4. Time (includes dimension members Jan-13, Feb-13 and Mar-13)

To insert the following table of amounts into the model:

Table 2

Design a payload first creating column-dimensions as follows:

<column-dimensions>
<entry>
<key>1</key>
<value>Account</value>
</entry>
<entry>
<key>2</key>
<value>Scenario</value>
</entry>
<entry>
<key>3</key>
<value>Department</value>
</entry>
<entry>
<key>4</key>
<value>Time</value>
</entry>
</column-dimensions>

The order of dimensions above specifies their order in the data-records section below. For each entry in column-dimensions, ‘value’ identifies the Dimension name, and ‘key’ indicates the position number dedicated to that dimension in every data-record.

For example:

Value: Account

Key: 1

Indicates that the first column in data-record is dedicated to an Account dimension member

Value: Time

Key: 4

Indicates that the fourth column in data-records is dedicated to a Time dimension member

Next, Indicate delimiter used in data-records to separate columns

<delimiter>@@</delimiter>

column-members tag contains information about dimension members that are shared by all data-records in the payload. This is a mandatory tag. It can contain one or more entries. The number of entries in this tag determine the number of columns dedicated to values in data-records. Two entries in column-members signifies two amount columns.

In this example below, entry with key=2 and value=’Actual’ indicates the second column of the data row is associated with member: Actual. This allows us to enter the amount in the second column for every data record.

<column-members>
<entry>
<key>2</key>
<value>Actual</value>
</entry>
</column-members>

Every row in table 2 has an entry in data-records tag. column-dimensions entry key determines the order of the dimension members in every data-record. As an example Department:Sales and Marketing are in column 3 as noted in column-dimensions tag Note that the amount in every Table 2 row is input in the second column of every record.

<data-records>
<data-record>1001@@100@@Sales@@Jan-13</data-record>
<data-record>1001@@200@@Sales@@Feb-13</data-record>
<data-record>1001@@300@@Sales@@Mar-13</data-record>
<data-record>1002@@400@@Marketing@@Jan-13</data-record>
<data-record>1002@@500@@Marketing@@Feb-13</data-record>
<data-record>1002@@600@@Marketing@@Mar-13</data-record>
</data-records>

Additional information with multiple columns per dimension

Oftentimes, there is a need to enter data for multiple members of a dimension. Consider the following example:

Table 3

The following payload can be used to enter data in this format:

<page-dimensions>
<entry>
<key>Scenario</key>
<value>Actual</key>
</entry>
</page-dimensions>
Extract Scenario: Actual to page since all records in Table 3 relate to Scenario: Actual
<column-dimensions>
<entry>
<key>1</key>
<value>Account</value>
</entry>
<entry>
<key>2</key>
<value>Department</value>
</entry>
<entry>
<key>3</key>
<value>Time</value>
</entry>
<entry>
<key>4</key>
<value>Time</value>
</entry>
<entry>
<key>5</key>
<value>Time</value>
</entry>
</column-dimensions>

This says that column 1 of every data-record contains an Account member, column 2 contains a Department member and Columns 3 to 5 contain Time dimension members.

<column-members>
<entry>
<key>3</key>
<value>Jan-16</value>
</entry>
<entry>
<key>4</key>
<value>Feb-16</value>
</entry>
<entry>
<key>5</key>
<value>Mar-16</value>
</entry>
</column-members>

The above xml snippet indicates that column 3 of every data-record contains amount related to Time: Jan-16, column 4 of data-record contains amount related to Time: Feb-16 and column 5 contains amount related to Time: Mar-16.

Specify delimiter as follows:

<delimiter>@@</delimiter>

The below xml snippet adds data per Table 3:

<data-records>
<data-record>1001@@Sales@@100@@200@@300</data-record>
<data-record>1002@@Marketing@@400@@500@@600</data-record>
</data-records>

Known Issues

Resolved Issues