The Connection Formula setting for PerformancePoint Dashboard Designer is quite useful in many instances. For example, if we want to create a report with the top 10 US states by the revenue generated by employees in that state, then we can build the filter using a dimension hierarchy like Employees by State. The problem is that we cannot simply connect this filter to the chart by connecting it directly to one of the chart dimensions. The reason is that if we select "Arizona" from the hierarchy, the natural result that we would want to see in the chart is for it to show the top 10 Employees for Arizona.
To get the desired result, we somehow have to specify that when we select "Arizona" we want to pass all the Employees in Arizona as a parameter to the report. The report can then do all the filtering on this set which includes only Employees from the desired state.
This is where the Connection Formula property comes in very handy. When creating a connection between a filter and a report, it is possible to specify a Connection Formula so that you can actually specify exactly what custom values you want to pass to the report when this filter is used.
The formula has to be written in MDX where <<SourceValue>> refers to the value in the filter, that is selected by the user.
For instance, in the above mentioned case we can use something like the below MDX query (the syntax might not be exactly right). The query assumes that we are using a Employee by Location hierarchy that contains all the Employees as Children of the state members in the hierarchy.
IIF(<<SourceValue>>.Member_Name = 'Arizona', [Hierarchy].[Arizona].Children, "whatever else you want to do here")
Please let me know if I can be of any help,
Thanks,
Hermann
To get the desired result, we somehow have to specify that when we select "Arizona" we want to pass all the Employees in Arizona as a parameter to the report. The report can then do all the filtering on this set which includes only Employees from the desired state.
This is where the Connection Formula property comes in very handy. When creating a connection between a filter and a report, it is possible to specify a Connection Formula so that you can actually specify exactly what custom values you want to pass to the report when this filter is used.
The formula has to be written in MDX where <<SourceValue>> refers to the value in the filter, that is selected by the user.
For instance, in the above mentioned case we can use something like the below MDX query (the syntax might not be exactly right). The query assumes that we are using a Employee by Location hierarchy that contains all the Employees as Children of the state members in the hierarchy.
IIF(<<SourceValue>>.Member_Name = 'Arizona', [Hierarchy].[Arizona].Children, "whatever else you want to do here")
Please let me know if I can be of any help,
Thanks,
Hermann
Hi!
ReplyDeleteI have a grid with year,month hierarchy on column, hooked to a Post Formula Filter. How can I filter the calendar hierarchy in the grid from the first date to the month selected in the post formula filter?
Thanks
Hi, Giorgio!
ReplyDeleteI am not in front of a computer on which I can test it right now so I can't tell you with absolute certainty, but I will try nevertheless. You seem to be pretty familiar with connecting the filter to the chart. When you make the connection to a chart, you should see an option to "Specify Connection Formula" (or something very similar).
Here you can specify, through MDX, the values that you want to pass to the chart as filter values, based on what the user selects. The code that you specify in the connection formula should be written in MDX, using "<>" as the value that the user specifies.
For example, let's say that we want to select the twelve months of a year when a user selects a certain year, and let's say that, as you said, your hierarchy has a "year" level and beneath that the "month" level. In that case you would write something like the following when specifying the formula:
<>.Children
which will send all the children of the selected year to the chart.
As far as I understand, your users are going to select a month and you want the chart to show all the dates before and up to that month, maintaining your hierarchies.
If you do not need to keep the hierarchies, the MDX PeriodsToDate function should work perfectly for you.
http://msdn.microsoft.com/en-us/library/ms144925.aspx
I am assuming that you want to keep your hierarchies. If you have not found your answer by the time you read this, please let me know and I will dig into some code for you. There should be more than one way to accomplish this. Hope this points you at least in the right direction. Sorry I can't help you anymore right now, but feel free to get back to me.
Hermann