Hide PerformancePoint “Show Details” Menu Item

One big issue we’ve found with the Show Details menu item in scorecards and analytic reports is that if you use it on a calculated measure, it’ll pop up a window with the error message, “Error Running Data Source Query. Contact the Adminstrator for Details.” If you look in the Application event log, you’ll find more information in a Warning event with the error message below.

Error running data source query.
You cannot drillthrough if the cell in a select clause is a calculated cell.

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: 
    You cannot drillthrough if the cell in a select clause is a calculated cell.

This is a limitation of SQL Server Analysis Services and drillthrough on a calculated cell is still not offered, at least not as of SQL Server 2008 R2.

For our dashboards we really didn’t need the Show Details option at all, so I wanted to find a way to get rid of that menu option entirely. Read more of this post

Calculating Quartiles in Analysis Services

Histogram with quartiles

Histogram with quartiles

In SQL Server Analysis Services 2008, there is a native Median function in MDX, but no native Quartile function. If you need to calculate quartiles, there are a few different ways to do this. You can either use native Excel functions, code the quartile algorithm in MDX or write the algorithm as an SSAS stored procedure in .NET. In this post, I cover each of these options and provide sample code for the MDX and stored procedure options.

Read more of this post