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])
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.
João Lopes (@SQLSniper) February 05, 2013
@ShebRehman might be possible if unique name is same in both. Otherwise have 2 construct unique member &use StrToMember.Can't use LinkMember—
Benny Austin (@BennyAustin) January 23, 2014