Category: Reporting Services

SoftArtisans OfficeWriter

You’ve probably realised by now that I’m eagerly awaiting the arrival of Excel Services (I’ve been asked to join the beta program so you’ll be hearing a lot more about it here soon), but for me the Holy Grail of reporting would be something that incorporated the best bits of Excel Services and Reporting Services, where users could design reports in any Office app, connect them live to different data sources, and then make them available over the web and have them rendered in many different formats. While this might be a pipe dream, I did come across a very interesting product that offers another subset of the functionality I want while looking at Brian Welcker’s blog today: OfficeWriter, from a company called SoftArtisans.
 
It’s a tool that allows you to automate the production of Excel and Word reports through Reporting Services. There’s a good Technet webcast which gives you a good overview of how it works here, as well as some online documentation here. You basically create your report in Excel or Word, add some references to data sources using a toolbar, and then publish the whole thing to RS; users can then go to RS, render the report and they get the spreadsheet or document you originally designed (with all the original features like charts, formatting, formulas etc) with the data dynamically injected into it. Definitely worth a look.
 

Report Builder support for Analysis Services

I’ve been meaning to blog about the quality of support for Analysis Services in Report Builder for some time now, but to be honest the thought of doing so depressed me – I prefer to blog about functionality that I like and works well (even though, looking at my usage stats it’s the negative posts on this blog that get the most attention). However Pablo Mugica has just brought to my attention the following KB article which details the ‘known issues that may occur’ when you try to use Report Builder with AS:
 
So, no support for parent/child attributes, ordering of members or KPIs amongst other things. And it doesn’t even mention the bizarre (although it makes sense for relational data sources) method of selection/navigation and the rules on where you can put attributes and measures on a report that I still haven’t worked out. Hohum. I hope they make more of an effort for the next version.

Rant: Reporting Services and Analysis Services

I’m just finishing off my presentation for PASS, in the course of which I’ve come up with a whole bunch of topics to blog about in the future, but now I’ve got some more spare time I’ve started to look at Reporting Services 2005 in more detail. Unfortunately this has also reminded me of something that has irritated me for a long time and which I need to get off my chest… 

Now, before I get all steamed up I should say that I have done quite a bit of work with Reporting Services and like it a lot – its extensibility and programmability mean you can do a lot of cool stuff with it. However in my opinion it has one big design flaw, a flaw that Yukon doesn’t really seem to be doing much to correct, and which makes me roll my eyes and tear my hair whenever I think about it: Reporting Services just doesn’t support Analysis Services properly.

Before the comments start, yes, I have seen all the new features in RS2005 to do with AS support, but in my mind the problem is a fundamental one. And it’s that RS2005 still expects to receive a flattened rowset when you’re querying AS. Why is this a problem? Well, MDX gives you all this rich functionality to decide how you want to lay out your resultset, crossjoining as many dimensions you want on rows and columns, slicing by what you want, and RS then expects you to ignore all this and make MDX behave like SQL. You have to put measures on columns (what happens if you want to put measures in the WHERE clause then?) and all the rest of your dimensions on rows or in the WHERE clause, and then use the matrix control to recreate what you used to be able to do with a simple crossjoin. In RS2000 it was only recommended that you write your queries this way, and you could ignore this advice if you liked; in RS2005 this rule is actually enforced! What about backwards compatibility? Argh! It’s clumsy, it’s restrictive, and it’s a waste of time.

All I want is to be able to enter any MDX statement I want, assign it to a matrix-like control that understands multidimensional resultsets, and be able to display the results in a report. Not difficult, surely? I know it might break the purity of the design, but why should every data provider be forced to dumb itself down to the level of SQL? After all, isn’t one of the unique selling points of AS that MDX allows you to get round exactly this kind of inherent limitation in SQL? To me it’s unbelievable that the two Microsoft flagship BI products don’t understand each other on this basic level.

OK, rant over. Is anyone listening?