The post I did on implementing SSAS drilldown in SSRS back in February has been one of the most popular I’ve written this year. Its success immediately made me think of taking the idea one step further and building a full, ad-hoc cube browser in SSRS – something I’ve had a crack at doing several times in the past, but which has proved to be pretty much impossible with SSRS out of the box. However I knew that with the extra flexibility that Intelligencia Query (full disclosure – I have a financial stake in this product; see here for some background on my involvement with it) gives it was going to be feasible… the next problem was that I didn’t have the time to do the work.

Luckily, my friend Andrew Wiles (who owns the company that develops and sells Intelligencia Query) did have the time to do this and blogged about it here:
http://andrewwiles.spaces.live.com/blog/cns!43141EE7B38A8A7A!566.entry

He also uploaded a video demo of it to YouTube:

It’s now available as a sample app for the SQL 2008 version of IQ (it relies on DMVs, so it won’t work with 2005), and he’s made several improvements to it since. I thought I’d mention it here because it’s not only a fun demo, it shows just how flexible the combination of SSRS and IQ is: we traditionally think of SSRS as being suitable only for static or semi-static reports, but here it is working as a basic ad-hoc query tool. OK it’s not exactly elegant the way you have to rerun a report every time you click something, and of course the only form of interaction possible is clicking on links, but hopefully you get the point!

One of the new features that caught my eye in Internet Explorer 8 when it came out was Web Slices – the ability for a web developer to carve up a page into snippets that a user can then subscribe to. There’s a brief overview of what they are here:
http://www.microsoft.com/windows/internet-explorer/features/easier.aspx
and a really good guide to implementing them from Nick Belhomme here:
http://blog.nickbelhomme.com/web/webslice-for-ie8-tutorial_84

Being the BI geek that I am, my first thought was to see whether they could be used with Reporting Services reports. After all, wouldn’t it be cool if you could subscribe to a table in an SSRS report, or even better a cell within a table, and get notified when that value changed rather than have to keep pinging the report yourself? Of course it would! Here’s how to do it…

The challenge with implementing web slices is to get SSRS to generate the necessary html when it renders your report. I first looked at using the new rich formatting functionality that’s available in SSRS 2008 that Teo Lachev describes here, but it turns out that you can’t use this to create Web Slices because SSRS doesn’t support the necessary attributes (see here for details – at least I assume this is why, because I couldn’t get it to work). The only way I could get it to work was to render the report as XML and then use an XSLT file to give me complete control over the HTML that SSRS generates. I won’t go into too much detail about how this works; once again, Teo has an excellent explanation in his book “Applied Microsoft SQL Server 2008 Reporting Services” (reviewed here – it’s an excellent book) on pages 263-265. To be honest this isn’t a satisfying approach for me because it involves a lot more effort to get the report looking the way you want, and of course you have to have control over how the report is rendered. However, it still makes for a fun proof-of-concept :-)

The first thing I did was create a simple SSRS report in BIDS that brought back values for Internet Sales broken down by country:

I then rendered the report to XML, took a look at the XML generated, and created a simple XSLT file that would generate a HTML report from that XML. I then added the XSLT file to my project and associated my report with it using the report object’s DataTransform property, so that it was always used when the report was rendered to XML. I was then able to deploy the project and, by using URL access to the report get it to render to XML and get the result treated as html, was able to see the following in IE8:

Here’s an example SSRS URL that does this:
http://myserver/reportserver?/Webslices/WebsliceDemo&rs:Command=Render
&rs:Format=XML&rc:MIMEType=text/html&rc:FileExtension=htm

Then I went back to BIDS and altered the XSLT file to add the necessary tags for a Web Slice around the main table. When I went back to IE and reopened the report after deployment I could see two new things. First, the Web Slice button appeared in the IE toolbar:

And when I moved the mouse over the table in the report, it was highlighted with a green box as a Web Slice:

I could then click on either to subscribe to the Web Slice and have it added to my favourites. This then meant I could see the contents of the table in my Favourites bar whenever I wanted:

And whenever the data changes (you can control how often IE polls the original web page in the Web Slice’s properties, and also in the definition of the Web Slice itself) the text in the Favourites bar turns bold:

So there you are. Even with the limitations that having to render to XML imposes I can think of a few useful applications of this approach… maybe I’ll test them out in a future blog entry. Let me know if you have any ideas!

One last thing: I think it would great (and involve relatively little dev work) if SSRS supported the creation of Web Slices out of the box. If you agree, please vote:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=443857

You can download my proof-of-concept SSRS project here:

In a recent post I sketched out an idea I had about consuming data from SSRS data within Excel: rather than have SSRS push data to a new Excel spreadsheet by rendering a report to Excel, what I wanted to do was have an Excel spreadsheet that pulled data from an SSRS report. Why do this? Well, we all know that users always want their data in Excel rather than in any other format. If we take an SSRS report and render it to Excel, though, we have two problems:

1. Getting an SSRS report to render to Excel in exactly the way you want, at best, involves a lot of trial-and-error. Even then when you render to Excel there’s a whole load of Excel functionality that SSRS doesn’t support – for example, you can’t get native SSRS to render a workbook containing a macro.
2. Ever time we run the report, we are creating a new workbook. So once we’ve got the workbook there isn’t much point in doing any extra work in it, for example adding new formulas or charts, because it’s going to be superseded by a newer workbook the next time the report is run.

A tool like Softartisans Officewriter (which MS licensed a long time ago and possibly will appear with SSRS 2008 R2) will solve the first problem, because it allows you to upload an Excel workbook to SSRS which has data injected into it when the report is rendered, but not the second.

However, it is possible to pull data into Excel from SSRS and avoid these problems. Excel allows you to import data from an XML document into a worksheet; since you can get an SSRS report to render to an XML document, all you need to do is hook Excel directly up to the XML file generated by SSRS. Here’s how:

• The key to getting this to work is URL access to SSRS reports. Excel needs to know where the XML file it’s importing is – and you can give it the URL of an SSRS report, and in that URL specify that the report should be rendered to XML. Let’s take the Sales Order Detail report from the Adventure Works sample reports as an example:
  
  On my machine, the URL for rendering this report into XML is as follows:
  http://myserver/ReportServer?%2fAdventureWorks+2008+Sample+Reports%2fSales+Order+Detail+2008&rs:Command=Render&rs:Format=XML
  Paste this into your browser and you’ll automatically get an XML file downloaded; you can find more details on URL addressability of SSRS reports here.
• Now, open Excel 2007, click on the big round Office button in the top left corner, click the Excel Options button and on the Popular tab check the box “Show Developer tab in the Ribbon”. This will ensure you can see the functionality within Excel we’re going to be working with.
• Open the Developer tab and click on the Source button to open up the Source pane, then the XML Maps button in the Source pane, then Add on the XML Maps dialog, and then enter the URL of the SSRS report in the File Name box on the Select XML Source dialog and click Open.
  
• The XML Source dialog will now be populated. Click on a cell in the worksheet, and then right-click on a node in the XML Source pane and choose Map Element to map an element into a cell; click the Refresh Data button in the ribbon to actually bring the data into the worksheet. Here’s what the data from the report above looks like when mapped into Excel:
  
  

The point is that every time you hit the Refresh Data button in Excel the report is rendered, so you’re able to build your worksheet around live data from SSRS. You can of course pull data directly from data sources like SQL Server in Excel, but the benefit of doing it this way is that you can take advantage of SSRS features like caching and snapshots, and of course as an end user you may not have direct access to the source database anyway.

There are some obvious drawbacks to this approach:

• It’s a bit too technical to set up for end users, except perhaps for the most technical of Excel power-users.
• There isn’t an easy way to pass parameters to reports. You can of course pass parameters through the URL, but it would be great if it could be done from a dropdown box in the worksheet. I think with a little bit of coding you could create an Excel addin that would do this though.
  UPDATE: actually, I think some of the techniques discussed in this post on the Excel blog could be useful here
• Rendering to XML isn’t the ideal format for this task – although I’m not sure there is an ideal format (the RPL format used by Report Viewer might be a good candidate but it’s not documented). Books Online has details of how reports behave when rendered to XML; one obvious drawback is that there’s no pagination of data, so if you have a lot of data in your report spread across multiple pages, you’ll get all the data from every page in Excel.

That said, I think this this approach might be useful when you have a large number of existing Excel reports that currently have data copied-and-pasted into them manually and which can’t (for whatever reason) be turned into full SSRS reports.

For some reason I’ve never needed to implement Analysis Services drillthough (note: not the same thing as Reporting Services drillthrough; why can’t they use consistent terminology?) in a Reporting Services report. Of course, Reporting Services support for Analysis Services being what it is, it’s not a straightforward task and since I’ve recently come across a few good blog posts that discuss the different ways you can do it I thought I’d link to them.

The main problem is that you can’t execute an MDX Drillthrough statement using the MDX query designer and the Analysis Services data source. You have four options then:

1. You can execute the Drillthrough statement through an OLEDB data source instead. Gurvan Guyader shows how to do this in the following blog entry (in French, but with lots of screenshots):
   http://gurvang.blogspot.com/2009/05/drillthrough-ssas-dans-ssrs.html
   The problem with using an OLEDB data source is that you lose the ability to use parameters and have to use Reporting Services expressions to dynamically built your Drillthrough statement instead.
2. It turns out you can also execute a Drillthrough statement by pretending it’s DMX, and so use regular MDX parameters, as Francois Jehl describes here (also in French):
   http://fjehl.blogspot.com/2009/06/drillthrough-ssas-dans-ssrs-ajout-au.html
3. If you buy Intelligencia Query (which, as always, I need to state that I have a financial interest in) then Drillthrough statements now work with no tricks necessary:
   http://andrewwiles.spaces.live.com/Blog/cns!43141EE7B38A8A7A!562.entry
4. Last of all, you can try not using a Drillthrough statement at all and use an MDX query instead to get the same data. You will lose some functionality though by doing this, however, most notably the MAXROWS option.

Sean Boon recently blogged about an approach to implement drilldown on charts with Reporting Services when Analysis Services is used as the data source, and it got me thinking about ways to implement drilldown in Reporting Services in general. There are two standard methods used to do this that are widely known about:

• The first can be described as "fetch all the data you're ever going to need to display and then hide the stuff that hasn't been drilled down on yet" - this article describes it well, albeit for SQL data sources. It's easy to implement but has has big problem: if the amount of data your report could ever possibly display is massive then the report will be very slow to run, for example if your dataset query returns millions of rows.
• The second is more scalable, in that you have multiple reports for each level of granularity you want to display and when you drill down or drill up you click on a member in the Report and pass it as a parameter to another report. This also works well but has a different problem: you now have multiple copies of what is essentially the same report to maintain and keep in synch. This approach can also only display one level of granularity at a time, and sometimes it's nice to be able to see multiple granularities in the same report.

Wouldn't it be good to have drilldown capabilities in Reporting Services just like you have in any other Analysis Services client? That's to say, you'd see a list of members on rows in your report, you'd click on one and then see all its children, then click again and its children would disappear? Well, it is possible and it's a problem I've tackled numerous times myself. The last time was when I was writing the samples for Intelligencia Query, but I've just come up with an even better approach which I thought I'd blog about. I've implemented it for the standard Analysis Services data source although I'll be honest it took me a few goes to get it to work properly (there would have been much fewer hacky workarounds if I'd been using Intelligencia Query!) and I'm not sure it's 100% robust; hopefully someone will find this useful though.

What I've done is basically a variation on the second approach above, but instead of using multiple reports I've created a single report which calls itself when you drill down on a member. The really tricky part is how you manage the set of members you've drilled up and down on, and this is where I'd struggled in the past - the solution I've got here uses a hidden parameter to manage that set, which is then passed to the main dataset and used with the DrillDownMember function.

Here are the steps to get it working:

1. Create a new Reporting Services report with a data source pointing to Adventure Works.
2. Create three new report parameters in this order:
   1. MemberClicked - tick "Allow Blank Values" and set the default value to [Customer].[Customer Geography].[All Customers]. This parameter will hold the unique name of the member the user clicked on to drill down.
   2. PreviousDrillDowns - again tick "Allow Blank Values" and set the default value to [Customer].[Customer Geography].[All Customers], and tick "Allow Multiple Values". This parameter will hold the list of members the user drilled down on before the last drill down.
   3. DrillDowns - again tick "Allow Blank Values" and tick "Allow Multiple Values". This parameter will hold the complete list of members drilled down on for the current report.
3. Create a new Dataset in the report called DrillDowns. Use the following MDX for the query:

   WITH
   MEMBER MEASURES.CUSTUNAME AS
   [Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
   SET DRILLDOWNS AS
   UNION({[Customer].[Customer Geography].[All Customers]},
   IIF(
   //CLICKED MEMBER HAS NO CHILDREN, SO IGNORE IT
   ISLEAF(STRTOMEMBER(@MemberClicked)), STRTOSET(@PreviousDrillDowns),
   IIF(
   COUNT(INTERSECT(
   STRTOSET(@PreviousDrillDowns),STRTOSET(@MemberClicked)
   ))=0,
   //DRILL DOWN
   UNION(STRTOSET(@PreviousDrillDowns),STRTOSET(@MemberClicked)),
   //DRILL UP
   EXCEPT(STRTOSET(@PreviousDrillDowns),STRTOSET(@MemberClicked))))
   )
   SELECT {MEASURES.CUSTUNAME} ON 0,
   DRILLDOWNS ON 1
   FROM [Adventure Works]

   What this does is take the set of previously drilled down members, and if the member we've just drilled down on is not in there return the set of all previously drilled down members plus the new member (for drilling down); if it is present, return the set of all previously drilled down members except the new member (for drilling up). If the member we've clicked on is a leaf member, we can ignore the click and just return the set of all previously drilled down members.
   
   You'll need to hook up the two parameters @PreviousDrillDowns and @MemberClicked to the report parameters you've previously declared. To do this, first of all in the query designer declare the parameters but just fill in the names and a default, such as [Customer].[Customer Geography].[All Customers] (see here, towards the end, for more detailed steps). Then exit the query designer but stay in the Dataset Properties dialog and create two dataset parameters with the names PreviousDrillDowns and MemberClicked and hook them up to the appropriate report parameters.

4. Go to the report parameter called DrillDowns and set the default value to be the CUSTUNAME field from the dataset you've just created.
5. Create a second dataset called DisplayQuery with the following MDX:

   WITH
   MEMBER MEASURES.CUSTNAME AS
   Space([Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL) +
   [Customer].[Customer Geography].CURRENTMEMBER.NAME
   MEMBER MEASURES.CUSTUNAME AS
   [Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
   SELECT {MEASURES.CUSTNAME, MEASURES.CUSTUNAME, [Measures].[Internet Sales Amount] } ON COLUMNS,
   DRILLDOWNMEMBER({[Customer].[Customer Geography].[All Customers]},
   StrToSet(@DrillDowns, CONSTRAINED), RECURSIVE)
   DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
   FROM [Adventure Works]
   
   This query simply displays the measure Internet Sales Amount on columns, and on rows uses the DrillDownMember function to drilldown on the All Member on Customer Geography plus any other visible members that are present in the set returned from the DrillDowns parameter.
   
   Once again you'll have to hook up the @DrillDowns parameter to the DrillDowns report parameter.

6. Now, in the report, create a table and bind it to the DisplayQuery dataset. Only use the CUSTNAME field to display the members for the Customer Geography hierarchy on rows - this means you have a single field that can contain members from all levels of the hierarchy.
   
7. Finally, open the Textbox Properties dialog for the cell bound to the CUSTNAME field and set up an Action to jump to the report we're currently building. We also need to pass two parameters: one that sends the value of the CUSTUNAME field (note this is the unique name of the member clicked on, not the CUSTNAME field which is just the member name) to the MemberClicked parameter, and one that send the value of the DrillDowns parameter to the PreviousDrillDowns parameter. It's not actually obvious how to pass the values of a multi-value parameter through an Action, but I found the answer here; the expression you'll need to use for this report is:
   =Split(Join(Parameters!DrillDowns.Value, ","),",")

Here's what you'd expect to see when you first run the report:

Click on Australia and then South Australia and you get this:

Click on Australia again and you'd go back to what's shown in the first screenshot.

I realise these steps are pretty complex, so I've created a sample report in SSRS2008 format and uploaded it to my SkyDrive here:

I dream of the day when SSRS will do all this stuff for me automatically...

UPDATE: you can now view the sample report online (minus the indenting for members on different levels, for some reason) here -
http://reportsurfer.com/CustomContentRetrieve.aspx?ID=170467

Boyan Penev recently blogged about the virtues of setting default parameters and he's right: it's a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened the dialog when there was a default selection made, for example:

The query designer would run an MDX query like this:

Clearly it was running queries to check the parameters were valid but in this case (although not in other instances) it was forgetting to add an empty set on the opposing axis, as per Mosha's recommendations here, and therefore bringing back measure values by accident. Now this doesn't matter for most cubes but my customer had a very, very large cube, their default measure was on their biggest fact table, and hadn't built any aggregations that this query could use. And so, not surprisingly, the query ran very slowly and editing the reports was a nightmare.

There are two possible workarounds here:

1. Build an aggregation for the above query, or
2. Create a new, hidden calculated measure that returns null and make that the default measure on the cube. Here's what to add to the MDX Script to do this:

CREATE MEMBER CURRENTCUBE.MEASURES.UseAsDefaultMeasure AS
NULL, VISIBLE=FALSE;

ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures,
DEFAULT_MEMBER=[Measures].UseAsDefaultMeasure;

Interestingly the same dialog also seems to generate some errors from the queries it runs which I think reinforces the argument for someone to go back to this code and do some work for the next hotfix. The errors seem to happen when you have a parameter with no default value set - the MDX error message is:
Query (1, 9) Parser: The syntax for 'on' is incorrect.
Query Text:
SELECT  on 0 FROM [Adventure Works]

It doesn't really matter from a performance point of view, but it's not very tidy. Anyway, I've opened an item on Connect if you'd like to vote on this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=403736

Thinking more on the subject of Chrome and web-based BI, my lunchtime browsing today brought me to the topic of site-specific browsers, something I'd not come across before although it's been around for a while. Here's what Wikipedia has to say about them:
http://en.wikipedia.org/wiki/Site_Specific_Browser

There are quite a few site-specific browsers out there but the Bubbles web site does a good job of showing off the kind of thing that's possible:
http://bubbleshq.com/

Here's a thought: wouldn't it be cool to have a site-specific browser for Reporting Services? Just think of the ways you could improve the user experience: launch RS direct from the desktop, always have it available in the system tray, better support for parameter display and selection (this would be a key feature), make it easier to add corporate branding to the interface, the ability to save and then organise local copies of reports and then compare different versions. Maybe you could even have some basic report design capabilities? The list is endless. There's a business idea here for someone with a bit of JavaScript knowledge...

Can I be utterly shameless and use my blog to promote products I have a commercial interest in? Of course I can! But first, let me tell you a story...

Last December I spent a lot of time thinking about the problems of building Reporting Services reports on top of Analysis Services. To be honest, this issue has been a constant irritation for me ever since I first saw Reporting Services about five or six years ago; this blog entry, one of the most popular I've ever posted, sums up my thoughts on the matter:
http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
and I've blogged about specific workarounds and performance issues on a regular basis ever since. But anyway, as I was saying, last December I was working with a customer new to the MS BI stack who had announced their intention of using SSRS and SSAS together in the naive assumption that because these tools were produced by the same company they would work well together, and for the n-hundredth time I found myself having to manage their expectations and teach them the various tips and tricks necessary to get their reports working. I also wrote an article on this topic for SQL Server magazine that came out in December and I expanded the same material into a presentation for the last SQLBits (you can download the slide deck here); it was also around that time that I realised that nothing was going to change in Reporting Services 2008 (see this thread on the MSDN Forum for other people's reactions to this). All this got me thinking.

The way you'd want SSRS to work is fairly clear: you'd want a decent query builder and the option of using any MDX query you'd care to write, and you'd want SSRS to render the resulting cellset in a matrix-like control automatically. It should work like pretty much like every SSAS client tool on the market only the output should be a SSRS report. The reason you can't do this is also clear: SSRS needs to know the name and number of columns your query returns in advance, and you can't do that with MDX queries. So how can you get around this limitation?

Then it hit me. Instead of having to unpivot your MDX query manually so that it's in the format that SSRS likes, why couldn't you have a custom data extension that did the hard work for you? So I wrote my first attempt in an afternoon and after a lot more polish it now works really well. Here's an example of what it does:

Take the following query:

select {[Date].[Calendar Year].&[2003],[Date].[Calendar Year].&[2004]} on 0,
{[Product].[Category].&[4],[Product].[Category].&[1]} on 1
from [Adventure Works]
where([Measures].[Internet Sales Amount])

This returns the following SSRS-unfriendly results:

But when run through my custom data extension the data is returned as follows:

You'll see that all of the columns have become rows, and there are two columns containing the Value and Formatted_Value properties of the cells. The cool thing is now that you can put a matrix control in your report, map the 'column' fields onto the matrix column groups, the 'row' fields onto matrix row groups and one of the cell properties onto the data area like this:

And when you run your report, the matrix control recreates the original structure of your MDX query:
 

This is pretty simplistic but even more complex queries with multiple levels of crossjoining on rows and columns are handled just as easily; it gets around all the limitations with the built-in Analysis Services data source/query builder as well as those of the OLEDB data source. Some of the key advantages are:

• You can use any MDX query and display the results (see here for some of the problems associated with the built-in Analysis Services data source needing measures on columns)
• You can parameterise your reports by measures (see here)
• There's no messing around with 'aggregate rows', all rows are treated the same
• Because the field names (optionally) do not refer to the hierarchy names used in the query, you can write one report that displays the results of very different queries - the only requirement is that you have the same number of rows and columns in your query. For example you can create one report that displays either Years, Quarters or Months on rows and control which one gets displayed using a parameter. It also means that the report design is much more tolerant of changes to the query during development.
• There's full support for parameterisation, something you don't have with the OLEDB data source.
• It (optionally) indents member captions based on their level depths, so the captions of members on lower levels appear further to the right of captions of members on higher levels.

I was feeling quite pleased with myself at this point and was about to publish the code up to Codeplex when it occurred to me that I could make some money out of this - after all, I do have a wife, two kids and a mortgage to feed. There was also one big missing feature that I knew I didn't have the time to implement on my own and that was an MDX query builder: whereas I'd be quite happy to write my own MDX queries, I know most people aren't that comfortable doing so and need some kind of tool to do it for them. So I got in touch with Andrew Wiles of iT Workplace who I knew had an excellent MDX query control, signed a partnership agreement with him and over the last few months we (well, mostly him really) have worked to integrate his query builder with my custom data extension with BIDS to come up with what we think is a product that anyone using Reporting Services and Analysis Services will want to use, Intelligencia Query for Reporting Services. Yes, it's something you're going to have to pay for but in my opinion it's very modestly priced and more importantly it has been saved my work from the fate of being a cool idea implemented by an occasional C# coder and stuck in perpetual beta on Codeplex, and turned into a commercial-quality product that is safe to install on your servers and which will grow and improve over time.

The main selling point of the whole solution is that it gives you, for the first time with Analysis Services, the ability to build in a query builder exactly the resultset you want to see displayed in your Reporting Services report - it's truly WYSIWYG. Certainly in a lot of cases you can achieve what you want with the native query builder but as I said, with it you always have to try to imagine the end result you want and write a query that can be pivoted into that end result, and that's not easy for the majority of people. Combine this with the fact that the Intelligencia query builder allows you to build much more sophisticated queries than the native query builder and that, as mentioned above, the custom data extension solves many of the problems you run into with the built-in Analysis Services and OLEDB data sources, and I think you've got something that finally makes it easy to build Reporting Services reports using Analysis Services data.

I think Andrew's query builder is the equal of anything else on the market in terms of the kind of query it can create. It does all the stuff you'd expect like hierarchical selections, it does stuff that few other tools do such as allow for the creation of asymmetrical sets on an axis, and it has at least one outstanding feature that no-one else has in its ability to let users create MDX calculations with an Excel formula-like interface. It even supports SAP BW as a data source too. Here's a screenshot:

Anyway, enough of me going on. You can find out more, download an eval version, watch a video and buy some licences here:
http://www.it-workplace.co.uk/IQ.aspx

One last thing: we want to hear your feedback. This is a version 1.0 and while we've got a lot of ideas on new features that could be added, we want to be sure they are the features that our users actually want. So even if you only download the eval please drop me a mail to let me know what you like and what you think is missing. It's likely we'll be able to add new features pretty quickly.

Continuing the theme of free stuff I've received, the nice people at 90 Degree Software have been chatting with me on the phone, inviting me to webcasts and sending me eval versions of their Reporting Services report generation tool Radius90 for, oohhh, over a year and a half now. So why haven't I blogged about the tool yet (ok, I did mention it once but only briefly)? Because it didn't support Analysis Services as a data source. But now with the release of Radius90 Version 2 it does at last, so it's review time.

Radius90 is one of those tools that has appeared to meet the need for an end-user friendly means of creating Reporting Services reports. BIDS is all very well for developers but you wouldn't give it even to a power user; while Report Builder is a bit simplistic in terms of the reports it can generate, its UI is confusing and as far as its support for Analysis Services goes it's very poor (see this white paper for a complete list of the limitations of Report Builder on an AS data source). Radius90 gives you a nice Office 2007-style interface that combines the power of BIDS with the kind of ease-of-use that Report Builder was aiming at. I like it a lot, and to be honest it's hard to see how you could come up with a better tool for the job. It's so good, in fact, that anyone who's seen what Microsoft have got planned for Report Builder in RS2008 (see this entry on Brian Welcker's blog or this webcast if you haven't) will notice some very strong similarities with Radius90. I guess, in the long-term, this could cause problems for 90 Degree but they've got enough time before Katmai arrives and people start migrating to put in place some more distinguishing features, and as it is they already have some nice collaboration functionality whereby you can reuse pieces of your own and other people's reports via a peer-to-peer network plus a good extensibility story.

What about their Analysis Services support? I found the following video which demos this and also acts as a good basic introduction to the report design process:

As you'll see if you watch the video, MDX queries are generated with a wizard and while it's easy to use I'd have preferred to see a drag-and-drop interface where you can see the results your query returns as you build it - they've taken a much more relational database-type approach rather than an OLAPy one, and I guess it's down to what your users are comfortable with and not a major issue anyway. Once the wizard has completed you don't seem to be able to change the query except through editing the MDX (or at least I haven't worked out how to, I could be wrong) which is a bit of a pain, although it's pretty quick to delete a query and create a new one and binding a query and the fields in it to table is very easy to do. It doesn't enforce the whole 'only put measures on columns' nonsense when you supply your own MDX, and I got all excited when I used a query which had a non-measures dimension on columns and it not only worked but gave me comprehensible column names, but as soon as I tried to crossjoin more than one dimension on columns it soon broke down... hohum. Of course the users that this tool is aimed at aren't going to be writing their own MDX anyway, so again I'm not too bothered about this. I also noticed that query slicing has been implemented using subcubes rather than the WHERE clause so that any calculated members that rely on looking at the currentmember on a hierarchy that you're slicing by won't work properly - it's a fairly common mistake and one that Mosha mentions here, but I've reported it to them and I'm sure it'll get fixed pretty soon. It's the only real showstopper I found, though, and in general the MDX it produces is clean and efficient. UPDATE: it turns out I was using a pre-release version and this problem has now been fixed - sorry...

Overall, then, Radius90 is definitely recommended. Its AS support is still a bit immature but even in its present state it's still adequate for most tasks; perhaps going forward they can make sophisticated support for AS data sources one of their selling points over Report Builder?

Undoubtedly the best time to find out about an undocumented change in functionality in a product is halfway through a demo of said functionality to a large group of people. This happened to me last week: I was teaching my MDX course (written on the last CTP of SP2) and had just finished my speech on all the rubbish aspects of Reporting Services/Analysis Services integration and was trying to show how Reporting Services automatically filtered out all but the lowest level of granularity of data from an MDX query (see Teo Lachev's post here: http://prologika.com/CS/blogs/blog/archive/2006/02/08/853.aspx and Reed Jacobsen's posts here: http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/08/07/22359.aspx and here: http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/08/04/22346.aspx for details) when I found that it wasn't doing it any more. Later on I emailed Teo to ask if he knew anything about this change - he didn't but he asked the RS dev team and they gave him some details (he then blogged about it here: http://prologika.com/CS/blogs/blog/archive/2007/06/10/aggregate-rows-and-sp2.aspx). So, a small victory for the people - Reporting Services now no longer tries to force its own aggregation functionality on you and you always see the full results of your query, unless you're already using the RS Aggregate function in your reports. This is apparently in response to customer demand. Just be sure to check any existing RS/AS reports you've got in production to make sure they're not displaying extra rows now!

I'm now hopeful that the message will get through about the other stupid restrictions that RS places on AS data sources, such as only being able to put the measures dimension on columns. I've not kept up with the changes in RS2008 as much as I should (Teo again has a good overview of what's coming here: http://prologika.com/CS/blogs/blog/archive/2007/06/09/teched-2007-us-memoirs.aspx) but I've not heard that these restrictions will be lifted. The problem is of course that RS expects to do all the aggregation of data itself, but the RS dev team don't seem to understand that if I'm using AS then I will have designed all my aggregation business logic into my cubes and dimensions and I couldn't care less about what RS can do in this area (for an example of their mindset, see the somewhat patronising comments on this posting on Connect from last year: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125414).

While we're talking about Katmai I might as well mention why I've not blogged about the new AS features in it - it's because there aren't all that many. You've probably already read Mosha's post about the attribute relationship designer (http://sqlblog.com/blogs/mosha/archive/2007/06/07/katmai-june-ctp-attribute-relationships-tab.aspx); Vidas Matelis has covered the other cube design wizard changes here: http://www.ssas-info.com/VidasMatelisBlog/?p=22#more-22. More features will come in later CTPS but overall it's really going to be all about performance improvements and manageability, and while I know a bit about some of the planned features it's too early to blog about them because it's too early to say what's going to actually get included. Suffice to say that for the serious AS developer there are going to be some very welcome improvements but there won't be anything that excites the marketing people.

• Parameter as the name of your parameter, without the leading @. So in our example it would be MyMeasure
• Dimension needs to be left blank - the Measures dimension doesn't appear on the dropdown list
• Hierarchy needs to be blank too
• Multiple Values needs to be left unchecked
• Default must be entered, but you can use an expression such as MEASURES.DEFAULTMEMBER

Thanks go to my colleague Tony for working this last bit out.

You can then bind this query to a table in your report and select different measures to slice by. Hope this helps!