Skip to end of metadata
Go to start of metadata

Purpose

The following Wiki article explains the way that the EPM Add-In product makes use of the various forms of connection information to connect new and existing reports to known Environment and Model combinations. The article explains how this is done, the behaviour you may see when using connections and reports, and how report options may affect this behaviour.

Overview

EPM Add-In connections are a means of storing information to allow saved reports to connect the data and metadata (dimension information) in the report to a matching Environment and Model. Whilst these connections can be manually created and managed using the Manage Connections screen, the behaviour surrounding the way that these stored connections are used by new and existing reports can sometimes appear confusing, even contradictory. In fact, the behaviour is explicable, but it follows some rules that this article will make clearer for report creators and end users.

Terminology

Before we begin with an explanation of the connection methods and settings for the EPM Add-In there are several definitions that should be explained:-

  • Login Connection
    • The combination of Environment and Model explicitly chosen as part of the Log In routine for the EPM Add-In.
  • Active Connection
    • A selection made within a worksheet from the EPM Add-In Action Pane using a drop-down selection box.
  • Default Connection
    • The combination of Environment and Model set by the EPM Add-In based upon either selections made by the user, or from stored information that is saved with a worksheet.
  • Current Report connection
    • The combination of Environment and Model that is displayed in the EPM Add-In Action Pane, and which reflects the connection information that is being used by the currently active worksheet.
  • Report Name
    • A setting in a dropdown box displayed beneath the Current Report setting. The Report Name is the name given to the currently selected report (i.e. the report in which the currently selected cell resides).

NOTE: “Default Report” is the default name for your first report in the worksheet. This is a different setting to the Default Connection. Each report may have a default connection of its own.

  • Report Connection
    • A hidden field in a report created using the EPM Add-In. The Report Connection field stores the connection information that was current at the time when the report was created.

Worksheets and Default Connections

A worksheet is defined as a blank sheet within the Excel application. A report is a combination of rows, columns and/or page axis definitions that use the EPM Model’s dimensions to define a layout for reporting data from a Model.

A default connection is a connection string containing all the details that a worksheet needs in order to connect to a specific combination of Environment and Model. This Default Connection is stored within a worksheet.
The default connection can be seen via the Excel menu Formulas à Name Manager. This displays a screen showing any EPM Add-In default connection for the current open worksheet(s).

For example: 


Figure 1: The Excel name Manager shows Default Connections created by the EPM Add-In

When a worksheet is created it will not have a default connection set unless the Active Connection dropdown box is manually changed. Whenever the Active Connection is manually changed, then it will set the Default Connection for the current report using the combination of Environment and Model displayed in the Active Connection setting.

Figure 2: A new Excel worksheet with no report created. Although logged in to an Active Connection the blank worksheet does not have any Default Connection yet.

If there is a Default Connection associated with a worksheet then the EPM Add-In uses the default connection settings to set the Active Connection.

Reports and Report Connections

When a report is created and saved it stores the details of the Current Report connection inside its definition. These details are hidden from the user. If that report is re-opened it will attempt to connect the report to a known connection using the details that it has stored.

This behaviour can be affected by different Report Options. See ‘How the "Do not store environment" report option affects Connection behaviour’ below for more details.

The Order of Connection

When a report is trying to reconcile a set of connection details (Environment and Model) there is an order that it goes through to do this. The order is different depending on whether there is an active report open or not.

If a report is already open, a connection has already been established. If another report is opened the connection is made by trying to match the current Login Connection to:

  1. the Report Connection that is already saved with the report, and failing that…
  2. the Current Report connection

If the sequence fails to find a matching Model name between these connections then the report will remain disconnected from the source data.

If you are opening a previously saved report on its own after connecting to a Login Connection, then the order of connection is:

  1. the Report Connection that is already saved with the report, and failing that…
  2. the Active Connection

Again, if this sequence fails to find a matching Model name between these connections then the report will remain disconnected.

The Active Connection

By default, when you work with a new worksheet, the Active Connection is equal to the Login Connection and the Default Connection is not set (see Fig.2).

If you change the Active Connection manually by the drop-down box, then the worksheet’s Default Connection is set to match the Active Connection and thereafter the Default Connection will change to match whatever is manually selected as the Active Connection in the drop-down box.

If you switch to another worksheet and this worksheet is new, the Active Connection for this worksheet will try to match to the Login Connection, because the new worksheet does not yet have a Default Connection set, or a Report Connection (because there is no report in the worksheet).

Then if you go back to the previous worksheet, the Active Connection will be set to the Default Connection of the current worksheet. The Active Connection recognized that the first worksheet now has a Default Connection contained within it, and it matched the connection settings accordingly. From this behaviour we can also see that Default Connection information is stored against each individual worksheet.

You can save this workbook and select the connection that you want to store against the worksheet using the Active Connection selection. If you re-open the first workbook, the Active Connection for the sheet will still be its Default Connection.

So, if you manually change the Active Connection you set the current worksheet’s Default Connection, and every time you switch to or open this worksheet the Active Connection will be the Default.

If you never change the Active Connection for a worksheet, every time you switch to or open this worksheet, the Active Connection will change to match the Login Connection.

If there is no worksheet open then changes to the Active Connection are not reflected in the Current Report setting, as there is no ‘current report’.

Creating a report without a Default Connection

If the worksheet which contains the report contains no default connection because the Active Connection has never been changed then the EPM Add-In begins to make some automatic choices about how to set the connection information when a worksheet is opened.

When you open a worksheet that has no Default Connection the EPM Add-In detects this and uses the Login Connection. The Login Connection is the connection that was chosen at the time when the user logged into the EPM Add-In.

If you perform a Log Off or restart Excel, and you decide to change the Login Connection to a different Environment, then a worksheet that does not have a Default Connection will again revert back to the Login Connection. In this case the Active Connection will match the Login Connection, and show your new Environment.

The Current Report connection

When a report is created the Current Report setting takes the Active Connection setting. From that point onwards the report always has this ‘Current Report’ setting, irrespective of changes to the Active Connection.

If a worksheet is saved then the current Active Connection is saved with the worksheet as its Default Connection. Next time the worksheet is opened the EPM Add-In will try to re-connect this combination of Environment and Model.

 NOTE: Connections and case sensitivity

 The Model Name for connections should be exactly the same if the comparison is to resolve correctly when attempting to compare connections. This check is currently case sensitive, however this is being changed in future releases (from SP 13 Patch 3 onwards).

How the "Do not store environment" report option affects Connection behaviour

The ‘Do Not Store Environment’ report option offers a ‘last chance’ setting for a report to attempt to connect its information to a live data connection. The option does not literally mean that the report has no ability to discover an environment to connect to. Instead, it means that the sequence of connection behavior is different to the normal process.

If the option ‘Do Not Store Environment’ was saved with the report then the EPM Add-In attempts firstly to connect to the Report Connection (as if the ‘Do Not Store Environment’ option was not selected). Only if the report could not find any matching connection information is the ‘Do Not Store Environment’ option utilized.

If the report connection is not connected or unreachable (no matching Model name between the Report Connection and the Login Connection) then the report tries the Current Report connection, if there was a current report setting saved in the active worksheet before the report was opened.

If this information cannot be determined, then it is the Active Connection in the current worksheet that is used to make the connection.

If the Model name doesn't match between the Report Connection and the Active Connection, then the report is not connected.

How to tell if your report is connected

A connected report will have the following characteristics:-

  • The report name dropdown box has a setting (is not blank). By default this will be showing ‘Default Report’ if only one report exists and is selected in the current worksheet.
  • Dimensions that are used in the definition of the column or row axes will be greyed out and not selectable
  • Dimensions will exist in either the row, column, page axis or any combination of those.


NOTE: The Current Report setting cannot be relied upon to determine whether a report is currently connected. It will display the last setting for a report that had a connection.

Some example scenarios

In order to illustrate the way the connection information changes here are a couple of example scenarios.

Example 1

  1. Create a report with the report option “Do not store environment with this connection” set, using ‘Environment 1’ and the model ‘Planning’.
  2. Save the worksheet and log off.
  3. Open a new worksheet and create a report on ‘Environment 2’ and the model ‘Planning’... so the current report connection is ‘Environment 2’.
  4. Open the first worksheet again.

The EPM Add-In will try to get the current report connection (that means ‘Environment 2’) and try to map the report on this connection if both models are the same (i.e. ‘Planning’ = ‘Planning’). So in this case, the report will open and be connected to ‘Environment 2’ because the model names match.

However, if ‘Environment 1’ was connected, or if the EPM Add-In was able to connect to ‘Environment 1’ using the same login credentials as ‘Environment 2’ was using, then the report will still be connected to ‘Environment 1’. Therefore, you can see that sometimes the connectivity of a report is determined by the most ‘suitable’ connection.

Example 2:

  1. Create a report on a connection ‘Environment 1 – Planning’ (this connection is stored in the report, even if you check the option "Do not store environment").
  2. Now close Excel and log on to the connection ‘Environment 2 – Planning’ (so the connection on ‘Environment 1’ is no longer connected).
  3. Open the worksheet which contains the report. EPM Add-In tries to connect the report with its own Report Connection (‘Environment 1’) EVEN IF THE CONNECTION IS NOT CONNECTED.

I hope this article goes some way towards helping you understand the behaviour of EPM Add-In Connections. There are some simple principles at work - the Add-In is trying to help you make connections to known information - and now you can understand how it is trying to make these decisions when presented with new, saved and changing reports.

Related Content

1642291  - EPM Add-in: Changing report connection issue

EPM Add-in for Microsoft Office User Guide - Section 4.2 Connection Management