2D 3D

Last week was a titanic one for me to say the least. My ongoing battle shoe horning Analysis Services data into Reporting Services continues with the completion of another 40 reports to add to the others completed so far on the project I've been working on.

To try and make my life a little easier I decided to spend a little time working on a way of easily tweaking each report to minimise the amount of work necessary to complete the report suite.

Bit of background, the report I'm working on has 3 alternate hierarchies in a store dimension which need to be drillable (whilst displaying different subreports at each differing level of each alternate hierarchy). On another axis is a crossjoin of a subset of the measures and what could be best compared to as a product dimension. A better explanation maybe would be to try and visualise the report....

Standard matrix with the first row group displaying a list of the children of the current store hierarchy parameter value. Row group 2 is a list of the children of current product hierarchy parameter unioned with a list of measures which effectively gives you the sales value for product sub categories A B and C and then things like number of stores and number of units sold for the parent category of A B and C.

Finally the column group was driven from a named set containing the previous 14 fiscal periods. As I previously said the report was drillable by store but also by product. This meant that a single report to the user was in fact built up of 40 reports linked and passing parameters between themselves to cater for every level in every dimension multiplied by 3 due to the alternate hierarchies.

What I ended up doing was removing the 'database' fields from the matrix and replacing them with calculated fields meaning I didn't really have to touch the matrix for the changes but only the calculated field for each separate report.

This may seem like going the long way around but for this requirement and this set of data this was quite literally the only solution and meant I was producing reports in several minutes along with thorough testing. Anyway I just thought I would share that one.