Date dimension is inevitably used in most dashboards. Due to its role playing nature, a specific date dimension used as slicer in one report is different from the date dimension used for another report on the same dashboard page. For example sales reports are based on order date whereas delivery reports are based on delivery dates. On a dashboard that displays both sales and delivery reports, it would not be good for navigation purposes to have both order date and deliver date filters on the same page. The choice of which date dimension to use as filter among the available dates is a tactical decision. Having chosen a particular date dimension, the challenge is to provide filter value to those reports that use a different date dimension. This is where PerformancePoint filter Connection Formula comes into play as explained in the example below.
The dashboard in Screen Capture 1 is created from AdventureWorks.
1. There are 3 sales reports using order date as slicer and one delivery report using delivery date as slicer.
2. Order date’s fiscal year has been chosen as dashboard filter and the following MDX expression is used as filter dataset.
ORDER((EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0).LAG(-4) : EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0))
, [Date].[Fiscal Year].CurrentMember.Properties('key'),DESC)
3. All sales reports are connected to the dashboard filter by member unique name.
Screen Capture 1 – Date Filter
4. The delivery report is connected to order date filter by member unique name on delivery date just like the sales reports (Screen Capture 2).
5. Then comes the Connection Formula. Using the following MDX expression, the connection formula takes the filter value passed to it (which is the order date’s fiscal year) and gets the equivalent member from the Delivery Date dimension that will be used by the report as filter value.
Linkmember(<<UniqueName>>,[Delivery Date].[Fiscal Year])
The LinkMember() MDX function returns a member from the [Delivery Date].[Fiscal Year] level that is equivalent to the Order date member that is identified by the <<UniqueName>> moniker.
Screen Capture 2 – Connection Formula
Connection Formula is extremely useful to customize filter values passed to reports in PerformancePoint dashboard page. A useful tip to test your connection formula MDX expression is to turn on the information bar which displays the filter member.
Benny Austin 
Hi Benny, this is a good post!. I was just asking a help regarding an issue.
I have two Three SSRS report, Rep1 Rep2 Rep3
Now in PPS these three are integrated. Now in Rep1 has a colume for machine type. The requirement is ‘machine type’ field is a drill through filter, so if the user click on any machine type in Rep1 then other report will be automatically filtered based on that ‘machine type’. I am using a Cube for the machine type filter in PPS. How can this be achived using connection formula. Please Help!
Shirsendu, I guess you can connect scorecard to a report in PPS. Not sure about connection between SSRS reports. The connection formula only works between filter and report and not between reports.
Hi Benny:
thanks for this. I have a slightly different problem and can’t get the connection formula to work. I have a dashboard with two reports (the same report one on left, one on right). I want the left to show for the period the user has chosen in the filter (I have that working fine), but I can’t get the right to show parallel period last year. I cant seem to find any documentation on the connection formula syntax other than “you can use MDX in the connection formula”.
I am passing a selected week to the left report successfully, but I want the right report to show year ago. So I used the connection formula: parallelperiod([time].[calendarweek].week,52,<>) and I get “Unable to get filter values – the filter connection formula is invalid”
It seems like this should be pretty common but I cant find any documentation on it. Any resources you can point me to would be greatly appreciated.
Mark
hi Mark, this technet link has some good examples http://technet.microsoft.com/en-us/library/ff535784%28v=office.14%29.aspx. Scroll down to section – “Use MDX in the Connection Formula dialog box”
Interesting.
Can one use this approach as an replacement for custom tables which contain the MDX queries in your opinion?
You could use any valid MDX expression that uses takes the supplied filter parameter value and converts to another valid member that is used in the report.