Use the Connection Formula dialog box in Dashboard Designer to configure a filter connection
Applies to: SharePoint Server 2010 Enterprise
When you use PerformancePoint Dashboard Designer to connect a filter to a view, you can use the Connection Formula dialog box to specify a formula. The formula that you specify causes the view to display particular information. For example, you can specify a formula to show data for dynamic time periods, such as the last six months. (This is called Time Intelligence.) Read this article for an overview of how the Connection Formula dialog box works and how you can use it with your dashboard filters.
The Connection Formula dialog box
You use the Connection Formula dialog box after you have connected a dashboard filter to a view and when you are connecting a Time Intelligence formula to a view. Depending on the formula that you specify, you can use the Connection Formula dialog box to display information in a view for dynamic time periods, such as the last year or the last six months. Or, you can use it to display more detailed results in a view than the filter would otherwise provide.
For example, suppose that you have created a dashboard filter that is called Products. That filter enables dashboard users to select a product category in a list, and then view information about that product category in the views that are connected to the filter. Suppose also that you want one view to show only the five best-selling products for each product category when the filter is applied. You can use the Connection Formula dialog box to specify a query to display that information in the view.
When you use the Connection Formula dialog box, you specify a Time Intelligence formula or a Multidimensional Expressions (MDX) query, depending on the information that you want to display.
Example: Specifying a Time Intelligence formula
Suppose that you create dashboards for the managers in your organization. A marketing manager wants to know how many leads a recent marketing campaign generated for the sales team. The manager also wants to see how many of those leads turned into actual sales over specific time periods, such as the last three months.
The current dashboard includes views that show the number of leads that were generated by each marketing campaign and how many leads were converted to sales. The marketing manager tells you that the dashboard provides good high-level information, but not enough data about specific periods of time.
Your solution is to create a Time Intelligence filter and connect it to your views. In the Connection Formula dialog box, you specify a formula to display information for the last three months. Then, you redeploy the dashboard.
The managers use that Time Intelligence filter to specify the time periods they want to examine, and their views display the appropriate results.
Example: Specifying an MDX query
Suppose that you create dashboards for the managers in your organization. One sales manager has a team that is responsible for multiple geographical regions. Some regional sales representatives are performing much better than other sales representatives are. In addition, the sales representatives in the major cities of each region all have varying sales amounts. The manager wants to know which cities have the highest sales in the individual regions, and how the sales amounts compare among all the cities in a particular region. The manager wants to see all this information in a single dashboard.
Your solution is to create a Geography filter that contains a list of regions. You connect the filter to the analytic views, and then you use the Connection Formula dialog box to specify an MDX query. That query causes the views that are connected to the filter to display information at the city level. Then you link the filter to the analytic charts, and redeploy the dashboard.
The managers use the Geography filter to specify a sales region, and the views automatically display information that is specific to that region. One view shows the top performing cities in the region, and another view shows the sales amounts for all the cities in the region.
Syntax of Connection Formula expressions
You can enter two kinds of expressions in the Connection Formula dialog box: a Time Intelligence expression or an MDX query.
A Time Intelligence expression uses the Simple Time Period Specification (STPS) syntax.
STPS expressions take the form
(Period + n)or(Period - n), where "n" is a positive integer that represents the number of time periods to include in a filter. Depending on the data sources that are used by the filter and the items connected to it, you can use time increments such as Year, Month, and Day. For example,(Year - 6)is an STPS expression that you can use to configure your filter to retrieve information for the last six years.An MDX query can include one or more monikers, such as
<<SourceValue>>or<<UniqueName>>. These monikers can be used alone or as part of a larger MDX query.
Important
The specific formula that you use will vary depending on the data sources that are used for your filter and the item that you are connecting to the filter. Not all formulas work with all kinds of reports and scorecards. For example, let's say that you are connecting a filter to a report or scorecard that uses a data source that is not configured to work with Time Intelligence. In that case, the STPS expressions will not work for your filter connection. In addition, when you use some kinds of filters, such as a Time Intelligence Connection Formula filter, you must specify a formula in the Connection Formula dialog box.
Below are several examples of formulas to give you an overview of the kinds of expressions that you can use in the Connection Formula dialog box.
Examples of Time Intelligence expressions
| Description | Syntax |
|---|---|
Period-to-date functions to show results for a current period up to the current date |
|
Function to show a set of time periods, such as the most recent six months |
|
Examples of MDX expressions
| Description | Syntax |
|---|---|
Query that displays the children of a selected member |
|
Function to show the top 10 items that are two levels under a selected member |
|
See Also
Other Resources
Use MDX in the Connection Formula dialog box
Multidimensional Expressions (MDX) Reference (https://go.microsoft.com/fwlink/p/?LinkId=186166)