The last few days, my ego has been really, really bruised. I've been working on OLAP based reports in Reporting Services 2005 again. Being fairly experienced with RS / AS 2000, I ran into all sorts of problems when RS just didn't behave quite like I expected it to...
The thing that really messed with my head was the new way of parameterizing MDX queryies. Everything needs to be done through the graphic interface, and the code gets really messed up. The worst bit was not being able to create dynamic queries.
At one point I gave up on understanding the editor and see if there were other ways to create MDX queries. And after a while if found this article at Chris Webb's blog:
Rant: Reporting Services and Analysis Services
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!163.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
Or, actually, it was Aranda Morrison who gave me the solution in the comment section:
(...)
After a little tinkering I discovered that if you create a datasource in RS2005 of type OLE DB then choose the provider as "Microsoft OLE DB Provider for Analysis Services 9.0", the functionality from RS2000 is preserved.. Yay!
I tested this, and it works like a charm. Yay indeed! My confidence is improving.
And when I ran the old query against the new data source, I found out what the section about
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS does, it pads the data to make it behave... different. OK, if I'd bothered reading a bit of documentation on all the new and wonderfull things in RS, I'd probably know what it does. But for now I'm just happy I can create reports without wondering what is going on. So thanks to Chris Webb and Aranda Morrison.
