Managing totals and sub totals in SSRS using SSAS cubes

Today I had one of those days where the hours spent researching a problem actually ended in a positive result. The issue I was having was creating correct sub totals for a report developed against a SSAS cube. The problem arises when you create totals for grouped dimensions.

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.

Sustainable software development

Having started in my new role as a BI Architect for a global fashion retailer, there was so much I wanted to do, so much that had to be done, and I was going to have so much fun doing it. I started delivering complete stories at double the velocity that I normally achieve, mostly because I would continue working when I got home at night and the kids were in bed. After about the third month, I started to get cranky, I felt tired, and when new features were being asked in the same timeframes as I had been delivering, I pushed myself even harder to meet them.

****BURNOUT****

I quickly reigned in my enthusiasm, and started to rather work on a more sustainable pace. Its all about long term thinking, and consistent achievable velocity that is actually the most important. It makes it easier to plan, not only for the developers, but for business too.

Definition of done

Prior to being converted to the Agile way of thinking, I didnt really have the concept of defining 'done'. We spoke about key project deliverables, constraints , dependencies and a host of other end game type objectives.

These days, I like to keep things simple. Any task that I intend doing now, I start at what I believe will be the criteria for it being 'DONE' . It is so important to have a clear focus on the end game, and to also have the ability to close off a project and move on to the next. It is also important to communicate this to others, so that they can keep you accountable.

I believe that the concept of a definition of DONE is very closely related to another Agile methodology which is the Minimum Viable Product {MVP}. More on this in my next post.

Sharepoint. It gives you just enough to keep loving it

With my first voyage into Sharepoint BI, I was both excited and apprehensive at the same time. But, I quickly fell in love with the first impressions, and the ability to really have a method of deployment of some of our BI capabilities. Scratch under the surface, and you find alot of "buts" . Connecting document libraries to Outlook are a great feature, but ..... Easy deployment from BIDS to sharepoint is easy but.... And for my latest investment in time, the missing default option for date parameters. Will keep you posted on how this pans out.

Quick follow up: Simply shifting the date parameter up the hierarchy sorted the issue out. Now you can select the "Use Default Value " checkbox.