As an example, lets say you have a sales measure called average transaction value, against a date hierarchy of year,week,day. By default SSRS will create a SUM aggregation of the total at the Year and Week level. The first solution that I decided upon was to bring in two additional measures, namely total number of sales ,and total sales value then create an expression on the rows containing the totals which would do something like this sum(total sales value) / sum( total number of transactions). This would have sufficed, but I actually had a whole bunch of calculated measures that I needed to do this for.
After some googling, found that there is a function within SSRS that will query the cube for the totals ,called the Aggregate() function. In theory, it magically will adjust the MDX and bring in the correct aggregation based on the underlying level in the dimension and use the cube calculation.
Initially it did not work ,but after reading this post http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/021bf54a-0707-415d-aedb-285a4d15d80d/ , I found out how to fix it.
When you want to use the aggregate() function, you need to bring in all the levels of the hierarchy into the tablix, and add them in as groups above the group you want to use the function on. You can simply just hide the parent levels after that.
