July 18 Release Notes

Dashboards: Pre-defined Custom Members for Scenario and Time

In the Spring18 release, Custom Members functionality was delivered. We are now delivering frequently used Custom Members for Time and Scenario dimensions for ease of use and quicker setup and deployment. Now, by default for all existing and new applications, the Custom Members below are pre-defined in each reporting area (cube) where the Dashboards module is enabled. Reporting areas are; Financial and Workforce.

Scenario:

• Actual vs Budget Variance

• Actual vs Prior Year Variance

• Actual vs Forecast Variance

• Actual vs Budget Variance %

Time:

• Periods To Date

• Compare Last Month

• Compare Last Qtr Same Period

• Compare Last Year Same Period

• Trailing 12 Months

• Rolling 6 Quarters

• Rolling 12 Months

• Trailing 3 Years

To access the Custom Members page, navigate to Maintenance > Reports > Cube Settings. Click the Custom Members tab. Notice that the same set of Custom Members listed above is pre-defined for each Reporting Area.

Custom Members are populated for the Time and Scenario dimensions using substitution variables. So, for example, if you click Edit for the Compare Last Month Custom Member, you will see that the @CURMTH@ substitution variable is used to build the formula.

Dashboards: Dimension Security Applied to Member Selector Pick List in Dashboards

When setting up a Dashboard, the pick list member selector (shown below) will now display the dimension hierarchy and members within the hierarchy based on user security access.

If a user does not have permissions to the default selected member or the previously selected and saved member, the system selects and applies the first valid member (to which the user has access to) from the pick list.

In Practice

Let’s say dimension security is applied to the Product dimension (on the Dimension Security page shown below). User XYZ has been provided with access to the Road Bikes product, but not the Mountain Bikes product. Therefore, the Mountain Bikes product member will not be displayed in the Dashboards run interface. Only those members for which user XYZ has access to will be displayed in Dashboards.

Dimension security access is setup on the Dimension Security Setup page. Navigate to Maintenance > Reports > Dimension Security.

Reports: HR Staffing Standard Report

This Standard Report called Staffing Summary is based on HR Attribute selected for time, scenario and budget entity. It displays the headcount for each month and is calculated based on the number of positions starting each month.

In this example, there is 1 total Mr. X position starting in May for the 2017 year.

In this example, in California, there is a total of two positions available in 2017. One position is available in May and the other in November of 2017.

Standard Reports must be deployed so that users can run them. Deploy this report by navigating to Maintenance > Reports > Standard Reports. Select the report, click More, and select Deploy.

Selections for report:

• Scenario - select one scenario to display data.

• Budget Entity - select one or more budget entities to display data.

• Time - select one or more years for time.

• HR Attribute - select the attribute and then choose the value you want to apply. For example, if you select Work State, select the CA value.

Budget Entity and Template security are retained across all standard reports. All amounts are displayed in local currency for the Budget Entity. The default format of the report is applied based on the user location. Default formats include number format, thousand separator and decimals.

Reports: Variance Option in Subline Data Standard Report

Variance is now enabled as an option in the Subline Data Standard Report. If selected, both Scenario and Time dimensions are enabled in the filters in addition to Budget Entity. Up to 2 scenarios can be selected and multiple members can be selected for the time dimension.

Access and Deploy the Subline Data Standard Report by navigating to Maintenance > Reports > Standard Reports.

Columns displayed in the report are:

• Template

• Line

• Subline

• Entity

• COA Segments

• Scenario - two scenarios are selected to calculate variance (displayed same order as they are selected by the user from the hierarchy)

• Time

• Variance (for each period)

• Total Variance - variance is calculated as the difference between the 2 scenarios (scenario 1 - scenario 2) for each period applied in the report

• Capital Budget by Scenario

• Budgeted Data

• Subline Data

Modeling 2.5.2: Spotlight for Office - Spotlight Add-In for Microsoft Word and Microsoft PowerPoint

In this release, we are extending our reporting capabilities into Microsoft PowerPoint and Microsoft Word in addition to Microsoft Excel. Spotlight for Office offers reporting capabilities in Microsoft Excel, Microsoft PowerPoint, and Microsoft Word. You can use Excel, PowerPoint, and Word to retrieve data from Planful Modeling or Planning, Consolidation, and Reporting (PCR) applications. Within the Office applications, you can take advantage of all the native formatting capabilities that Microsoft offers for formatting and presenting the data and preparing board-ready reporting packages.

Spotlight for Office is available for all Spotlight and Modeling users.

MS Office versions 2010, 2013, and 2016 are supported.

Key Benefits

• Ability to access both Modeling and PCR data from Excel, PowerPoint, and Word.

• Ability to use the full power of Microsoft Office for reporting.

• Ability to create board books and presentations with complete control over the format.

• Ability to refresh the data points in Excel, PowerPoint, or Word with one click.

• Eliminates the need to copy and paste data points into your presentations and reports.

• Ability to place the Spotlight formula once and then Refresh the data as needed.

• Reduces financial reporting processing time.

Spotlight for Office uses the power of user-defined functions to connect Excel, PowerPoint, and Word to Modeling. All data and metadata from Modeling are represented in formulas for each cell copied into the Office application. This is similar to the way Excel-based Reporting uses SpotlightXL formulas for metadata and data. With a live connection to the model, Office users can just click Refresh to update the data.

For a primer on how to use Spotlight for Office, see the following topics:

New Installation Options for Spotlight for Office

One Spotlight Login for all Office Products

Copying Spotlight Formulas from an Excel View or Report to PowerPoint

Copying Spotlight Formulas from an Excel View or Report to Word

Using Spotlight for Office in PowerPoint

Using Spotlight for Office in Word

Creating and Formatting a Chart in PowerPoint or Word with Spotlight for Office

Modeling 2.5.2: Navigation Access Expanded to User Level

In this release, administrators can now manage security settings at the user level using the Navigation Access page. Previously, the Navigation Access page allowed security to be defined only at the role level: designer or reviewer. It is now being extended to the user level along with the role level. Based on your needs, any combination of user- and role-based navigation access can be defined.

User-level settings are given higher priority than role settings. For example, if a user is defined as a Designer and the Designer level has No access to Model Setup but the User has Yes access to Model Setup, the user access takes priority. All other Designer users will have No access to Model Setup, but the specific user who was given Yes access will be able to use Model Setup.

For organizations with several users with Designer privilege, some may specialize in maintaining the model and some may specialize in managing reports. In this case, the admin can selectively provide access to Model or Report features by user name.

1. Login to SpotlightXL.

2. Select Manage, Navigation Access.

3. Select the Role drop-down. Notice that you have the option to select Designer, Reviewer, or specific usernames of each type.

4. Select a user that to be given access to edit models.

5. Set Enable Access to Yes for the specific model capabilities this user should have.

6. Click Save.

7. Select a user that to be given access to edit reports.

8. Set Enable Access to Yes for the specific report capabilities this user should have.

9. Click Save.

Modeling 2.5.2: Calculation Drop-Downs Added to Name Column for Calculation Type

In this release, creating calculations is made easier with the addition of drop-down selection lists in the Name column. For each type of calculation entry, the Name column shows all the available options. This feature is available only in SpotlightXL.

Designers and Admins can perform this task.

1. Login to SpotlightXL.

2. Select Model, Calculation.

3. Select an existing calculation or type a name for a new calculation.

4. On the first row under the Type column, select a calculation step from the drop-down, such as Aggregation.

5. Under the Name column, select the drop-down to see the available options corresponding to the Type column.

In the example below, for the Aggregation type, the options available under Name include None (which aggregates the whole model), or three different scopes.

6. Select the appropriate value for Name.

Note:

For second and following rows of the calculation, if there is no drop-down in column A, then copy the drop-down from the first row, column A in the Type section to the rows beneath.

7. Click Save when you are done editing the calculation.

Name Options Available for each Calculation Type

Modeling 2.5.2: Model Permissions Drop-Down Added for Value Column

In this release, editing Model Permissions is made easier for admins with the addition of a drop-down selection list on the Value column. This feature is available only in SpotlightXL.

Admins can perform this task.

1. Login to SpotlightXL.

2. Select Manage, Application Administration, Model Permissions.

3. Select a model.

4. Select the user group(s) that you want to apply permissions to under Groups.

5. In the first row of the Groups section, select the user group in column A.

6. Select the dimension to apply permissions to.

7. Select the filter to apply.

8. In the Value column, the drop-down shows the available options.

9. Select an option for the Value column.

10. Click Save when you are done editing the model permissions.

Modeling 2.5.2: Scope Definitions LeafMembers Option

In this release, model scopes can be defined with a Filter Type called LeafMembers. This option filters only the leaf members of the selected dimension or parent-level dimension members.

This feature is available only in SpotlightXL.

Designers and Admins can perform this task.

1. Login to SpotlightXL.

2. Select Model, Scope.

3. Select an existing scope or type a name for a new scope.

4. On the first row in the Dimension section, select the dimension to filter.

5. Under Filter Type, select the drop-down and look for the option, LeafMembers.

6. Under Filter Value, select the dimension or any parent-level dimension member in the dimension.

7. Click Save when you are done editing the model scope.

Modeling 2.5.2: Calculation Variables Manager #SKIP# Option

In this release, admins and designers can use the SKIP option for variables in a calculation. Previously, this functionality was available only from SpotlightXL. Now it is available from Model Manager.

Use the SKIP option when you have a Map or Formula that uses variables... usually multiple variables. You can build one master map or formula to accommodate data loading or formulas for a whole series of data intersections. Then when the calculation is run that executes the formula or map, you can skip those data intersections that do not need to be loaded or calculated.

Consider the following formula with two variables and the calculation that runs it. We will set the calculation to skip the @NextQtr@ variable. 

1. Login to Model Manager.

2. Select the model and calculation that uses formula or map variable(s).

3. Click the pencil icon to open the Variables Manager.

4. Select the variable that you want to skip, and click the Skip option under the member selection box.

After you click Skip, you see #SKIP# as the Value of the variable. Select a value for other variables at this time also.

5. Click Save when you are done with your changes.

This calculation will now calculate Sales as ASP * Units for the member intersections defined in the formula. In the Time dimension, only Q3 2018 will be calculated.

Modeling 2.5.2: API Enhancement for Creating and Mapping Attributes

In this release, the API adds options for managing attribute metadata in your models.

• Create Attribute and Attribute hierarchies, with optional display labels (with the <attribute-members> section)

• Append Attributes to existing Attribute hierarchies, with optional display labels (with the <attribute-members> section)

• Map Attribute leaf members to dimension leaf members (with the <attribute-mapping> section)

• Modify attribute-to-dimension-member mappings (with the <attribute-mapping> section)

These options are all available within one API, Append Attributes. You can include both the <attribute-members> and <attribute-mapping> sections, or either section.

Append Attributes (Master and Analytic Models)

Use this API to append attributes to Master and Analytic models. Below is a sample of this API.

POST /modeling/model/attributes/append HTTP/1.1

Content-Type: application/xml

Accept: application/xml

X-ACCESS-TOKEN:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<attribute-payload>
<model-name>Sales</model-name>
<dimension-name>Products</dimension-name>
<attribute-name>Product_Type</attribute-name>
<delimiter>@</delimiter>
<attribute-members>
<!-- Define attribute: member code @ member parent @ member display label [optional] -->
<member>All_types@All_types</member>
<member>Android@All_types</member>
<member>iOS@All_types</member>
<member>Other@All_types</member>
<member>Windows@Other</member>
<member>Unknown@Other</member>
</attribute-members>
<!-- Define mapping: dimension leaf member code @ attribute leaf member code -->
<attribute-mapping>
<member>iPhoneX@iOS</member>
<member>S8@Android</member>
<member>DELL@Windows</member>
<member>HP@Windows</member>
</attribute-mapping>
</attribute-payload>