Jens Kröhnert

Lately I had the request to support end-users in commenting on charts in a parameterized dashboard. This alone is not easy because very few of the frontend tools allow such comment data entry. But the request furthermore said formatted comments are necessary as well as that you only see the comments related to the chosen year-parameter in the dashboard. Data source for the charts is an Analysis Services Cube.

I found 3 solutions with slightly different handling.

Common basis for the different solutions is a SharePoint list for the formatted comments:

clip_image002

You may wonder what the “Year_UN” – column is about as the user only has to input a year and his comment. Normally this column would be hidden from the enduser – I just displayed it here for explanation. Because we have an Analysis Services filter in the dashboard, the chosen value is filtering the connected elements with the so called “Unique Member Name” – a string which is read like:

[Dimension Name].[Hierarchy Name].&[Member Key]

The exact structure is based on settings in the SSAS Cube and can easily be derived from the MDX Query pane of the SQL Server Management Studio.

While only the year in this case is variable in the string, the column “Year_UN” can be computed:

clip_image004

With this prerequisite you can complete the 3 solutions:

1) SharePoint List and Reporting Services

Reporting Services are able to dislay HTML formatted text via a property of the placeholder which in this case resides in the table details textbox.

clip_image006

Pro: Only one frontend tool involved: Reporting Services. Possibility to adapt parameter behaviour

Con: No possibility to use the dashboard for data entry (commentaries)

2) SharePoint List and –Analysis Services Filter as well as Excel Services

In this case the connection from the filter to the chart and to the list has to be done in the sharepoint page itself.

clip_image008

Pro: Dashboard can directly be used for data entry (commentaries) in context of the filtered chart

Con: SharePoint Analysis Services Filter can’t be adapted to single-value usage (Multiselect of year won’t filter the SharePoint List correctly)

3) SharePoint List, Performance Point Dashboard Designer with the already known Excel Services Chart und Reporting Services Report for the commentaries

clip_image010

Pro: Performance Point Dashboard Filter can be adjusted to single value usage

Con: No possibility to use the dashboard for data entry (commentaries)