How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

http://msdn2.microsoft.com/en-us/library/ms145486.aspx

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.