2.0.2 Modeling Release Notes, April

Excel-based Reporting

With Excel-based Reporting you can leverage the full set of formatting options that are natively available in Excel. You now have the best of both; the power of Modeling and added flexibility of Excel.

Use Excel-based Reporting when your model contains pertinent data that you want to use with a wide variety of Excel features to format a custom report, such as an Income Statement or Balance Sheet. With Excel-based Reporting, you can design and customize the worksheet as a report and place data and members from the model into the report freely. Data is updated from the cloud upon Refresh.

With Excel-based Reporting, users can:

• Reference the data from Modeling into Excel

• Design and run reports from Excel

  • Use a wide variety of Excel features for formatting the reports

  • Leverage Excel formulas, formatting, graphs, and other features

• Publish the Excel workbook to Modeling

  • Publish (or upload) the workbook for collaboration

  • Open (or download) the workbook

• Refresh data

  • Only data cells from Modeling are refreshed

  • Excel formulas and formatting options are retained as is during the refresh process

Sample Report

Reports created with Excel-based Reporting are called "Custom Reports". The following example contains data and members from a model along with Excel formatting.

1. Table formatting is used

2. Conditional formatting is used

3. Font is changed to Arial Rounded Bold

4. Cell comments were added

5. Number formatting varies per quarter

6. Columns were inserted containing formulas and sub-totals

7. Totals were moved to the bottom and right

Required, Recommended, and Optional Topics with Tasks to Get Started

Each of the links to topics below are provided in sequential order so that you can quickly get started with Excel-based Reporting. Topics contain tasks that are required, optional or recommended.

• Setting Up Excel-based Reporting (required)

• Overview of Excel-based Reporting (required)

• Setting up Security (required)

• Publishing and Opening Excel-based Reports (required)

• Creating Excel-based Reports (required)

• Refreshing and Formatting Excel-based Reports (required)

• Setting Up an Assumptions Worksheet (optional)

• Excel-based Reporting Income Statement Example (optional)

• Excel-based Reporting Best Practices (recommended)

Description Field Updates

We’ve added additional Description fields to all model artifact pages as well as Reports' and Views' properties. In addition to these enhancements, we’ve enhanced the Model Setup page to display descriptions for all related artifacts.

Description for Model Artifact Pages

Description Update to Design View and Report Design

Model Setup Description

Description for Model Artifact Pages

We’ve added a “Description” field to each model artifact page for improved identification purposes. Now, you can provide relevant information for each artifact in the Description field. Like the “Description” field that already exists on the Model Calculation page (accessed by selecting the Model task and the Calculation subtask), the only difference is character limitation. The Description field on the Model Calculation page allows up to 5000 characters while all other artifact Description fields allow up to 1000 characters. If you exceed the 1000-character limit, characters will be truncated.

Model artifacts (aside from Calculation) include: Scope, Formula, Map, and Setup. Each of these pages are shown below with the Description field highlighted.

Description for Design View and Report Design

A Description field has been added to the Design View (accessed by selecting the Analyze task and the Design View subtask). To display and update the Description field (shown below), click the Properties action.

Similarly, the Description field has been added to the Report Design page as well (accessed by selecting the Report task and the Design subtask). Click the Properties action. The Description field is shown below.

Model Setup Description

The Model Setup page has been enhanced so that descriptions related to the artifact associated with the model are displayed. In the example below, if the 2015 Budget scope had a description, it would be displayed in the highlighted field. If the Seeding calculation, or any other calculation or view below, had a description then it too would display in the associated Description field highlight below.

Model Setup Audit Details

In past releases, the Model Setup page (accessed by selecting the Model task and the Setup subtask) had audit log details associated with each related View and Report. We’ve removed the audit log details from the Setup page as all audit information is centrally located and available in the Audit Log. You can access the Audit Log by selecting the Manage task and the Application Administration – Audit Log subtask.

Ability to Rename Views and Reports from Folder Structure

With this release, you can rename views and reports directly from the folder structure. This feature is available in SpotlightXL as well as the web front end.

Renaming Views

Renaming Reports

Best Practice

Renaming Views

To rename a view:

1. Select the Analyze task and the Data subtask.

2. Click on the folder structure, select the view and right-click.

3. Select Rename…

4. The Rename View dialog appears. Enter the new name of the view.

5. Click OK.

The folder structure automatically refreshes displaying the new view name as shown below.

Renaming Reports

To rename reports:

1. Select the Report task and the Run subtask.

2. Click on the folder structure, select the report and right-click.

3. Select Rename…

4. The Rename Report dialog appears. Enter the new name of the report.

5. Click OK.

Best Practice When Renaming Reports and Views

If you have a report book with multiple reports or views and one of those reports or views is renamed, an error will result when you perform a Refresh All as shown below.

For details on which report failed to refresh, click the More Details portion of the Refresh All log shown below.

The resolution is to reload the renamed view or report from the folder structure. As a best practice, use caution when renaming reports and views. Report and view owners may rename a report or view as can an Admin user or any user given access to edit the report or view.

SpotlightXL Add-In Installation Format using InstallShield MSI

The SpotlightXL Add-In installation is now available in a user-friendly form, using InstallShield. The installer will now be distributed in an MSI format. MSI is an installer package file format. Using InstallShield MSI will resolve installation issues and allow Admin users to install the SpotlightXL Add-In for their user base.

Users must still use Control Panel to uninstall the previous release before running the InstallShield MSI file.

After running the installer EXE file, click Next and follow the prompts to install SpotlightXL. When installation is complete, click Finish.

Reliability Improvements

We have implemented several enhancements to address reliability issues making the Modeling application even more robust and efficient. For example, we’ve changed the way models are processed so that model resources are more effectively utilized. Each calculation will be assigned with dedicated resource on the fly and the resources will be released after the processing is complete.

Data Load Enhancement to Master / Analytic Model API

The Master and Analytic Model Data Load API has been enhanced so that data will persist to load even when dimension members are missing.

It is an optional configuration and by default the data load API will not load the data if there are any missing dimension members. If this option is configured then data load API will continue to load the valid data and will notify users with the list of missing dimension members for which data has not been loaded.

The flag we introduced in the Data Load API will continue to load data even when an Invalid member or Invalid number is:

<continue-on-error>true</continue-on-error>

The sample payload is provided below:

<?xml version="1.0" encoding="UTF-8"?>

<load-data-payload>

<model-name>Base</model-name>

<delimiter>|_|</delimiter>

<aggregate-flag>true</aggregate-flag>

<continue-on-error>true</continue-on-error>

<page-dimensions>

<entry>

<key>Measures</key>

<value>MTD</value>

</entry>

<entry>

<key>Scenario</key>

<value>Actual</value>

</entry>

</page-dimensions>

<column-dimensions>

<entry>

<key>1</key>

<value>Products</value>

</entry>

<entry>

<key>2</key>

<value>Time</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>

<entry>

<key>6</key>

<value>Time</value>

</entry>

<entry>

<key>7</key>

<value>Time</value>

</entry>

<entry>

<key>8</key>

<value>Time</value>

</entry>

<entry>

<key>9</key>

<value>Time</value>

</entry>

<entry>

<key>10</key>

<value>Time</value>

</entry>

<entry>

<key>11</key>

<value>Time</value>

</entry>

<entry>

<key>12</key>

<value>Time</value>

</entry>

<entry>

<key>13</key>

<value>Time</value>

</entry>

</column-dimensions>

<column-members>

<entry>

<key>2</key>

<value>Jan</value>

</entry>

<entry>

<key>3</key>

<value>Feb</value>

</entry>

<entry>

<key>4</key>

<value>Mar</value>

</entry>

<entry>

<key>5</key>

<value>Apr</value>

</entry>

<entry>

<key>6</key>

<value>May</value>

</entry>

<entry>

<key>7</key>

<value>Jun</value>

</entry>

<entry>

<key>8</key>

<value>Jul</value>

</entry>

<entry>

<key>9</key>

<value>Aug</value>

</entry>

<entry>

<key>10</key>

<value>Sep</value>

</entry>

<entry>

<key>11</key>

<value>Oct</value>

</entry>

<entry>

<key>12</key>

<value>Nov</value>

</entry>

<entry>

<key>13</key>

<value>Dec</value>

</entry>

</column-members>

<notify-on-success>

<email>mhamed@hostanalytics.com</email>

<email>ryennam@hostanalytics.com</email>

</notify-on-success>

<notify-on-failure>

<email>mhamed@hostanalytics.com</email>

</notify-on-failure>

<data-records>

<data-record>HP1|_|10|_|10|_|10|_|10|_|10|_|10|_|10|_|10|_|10|_|10|_|10|_|10</data-record>

<data-record>Dell1|_|20|_|20|_|20|_|20|_|20|_|20|_|20|_|20|_|20|_|20|_|20|_|20</data-record>

<data-record>Nexus 6|_|30a|_|30|_|30|_|30|_|30|_|30|_|30|_|30|_|30|_|30|_|30|_|30</data-record>

<data-record>iPhone 7|_|40|_|40|_|40|_|40|_|40|_|40|_|40|_|40|_|40|_|40|_|40|_|40</data-record>

</data-records>

</load-data-payload>

Known Issues

The following Known Issues are specific to Excel-Based Reporting:

Resolved Issues

The following Resolved Issue is specific to Excel-Based Reporting: