SSRS: Poor Support For Troubleshooting Of Dataset Errors

With apologies this post is slightly more of a rant than an actual solution to a problem!

We have just completed our migration from Business objects (6.5 - ancient) to SSRS for reporting against our businesses most important system; Oracle e-Business Suite. As you can imagine this has been a major piece of work (300+ reports) and a major amount of time.

It's actually a tribute to SSRS that we have managed this in about 8 months with a team of FIVE people, one of which was junior, two consultants, and one full-time member of staff (me), plus another dropping in/out as needed.

What hasn't helped is Microsoft's attitude to error messages. Take this one for example;

Oracle (ORA-01427) Error Message in Report Builder 3

As you can see from the text it's a pretty simple error, a sub-query in DataSet1 is returning more than one row. Easy to fix (or at least debug) you'd think? No. Let's take a look at the datasets associated with the report;

Report Builder 3 Datasets

Now the first thing you'll notice is that there are a lot of Datasets associated with this report - it's a complex report bringing together data from multiple sources (a strength of SSRS).

The second thing you'll notice, when you look back at the error, is that *none* of the datasets are called "DataSet1".

Well that's easy, you'll be thinking, they're clearly numbered from the top down just look in the top one (or two, numbering might have started at zero!) and there you'll find your error.

Except no, neither of the top two datasets (PARAMETER_DEFAULTS or dsINVInventoryClasses) have sub-queries in the SQL and so it's impossible for them to be generating the ORA-01427 error that is being reported.

So now what? Well I need to go into each of the datasets, manually, and test them with the parameters the user is using until I find the error (and due to the poor nature of the reported error I have to open *all* of the datasets to make sure that the error isn't occurring in multiple datasets).

Now you'll also notice, if you look closely at the image above, that all of the datasets are shared datasets (i.e. are stored separately on the reporting server). Now in order to open each of these I first need to double-click the dataset to see where it is stored and what it's called;

Report Builder 3: Finding the Name/Location of a Shared Dataset

This gives me the location and name (NPL Parameter Default Values.rdl) of the first dataset.

Now to open it. Report Builder 3 doesn't allow you to have multiple items open in the same application so in order to look at a Shared Dataset I need to open a new instance of the application (which is annoying, but not really a big issue; think of it as a minor annoyance on the annoyance scale). So I open a new copy of Report Builder 3;

Report  Builder 3: Getting Started Wizard

That's handy isn't it? I've worked on this report fairly recently so surely Report Builder 3 will have the datasets I've been working on in my Recent files list? Surely? No. Of course not. The recent list contains only the reports I've been working on - Not the shared datasets (which if you think about it are actually the things that are substantially more likely to change that the reports). In fact if you look closely at the "Getting Started" dialog you'll see that while you can create New Datasets you have no way of opening existing ones as the "Open" option is for reports only as well.

So I click on the "Open" option and am presented with the root directly of the server I'm connected to;

Report Builder 3: Open Dialog

This is literally the one place on the server (if you're running in Sharepoint Integrated Mode - which we are) where there *can't* possibly be any reports or datasets (because it's impossible to save anything). It would be like when you open a document in Word it defaulting to the "My Computer" folder - sure it works, sure you can get to where you need, but it's "slightly harder than it needs to be" - a slogan I think should be adopted for Report Builder 3 in general!

Now I go into "Data Sets" folder and, after a few seconds wait, I'm presented with a blank dialog. Looking at the bottom you'll see that the "Items of type" field is set specifically to "Reports (*.rdl)" so datasets don't appear, if you want to see them you need to change it to "Datasets (*.rsd)"- and then all the reports disappear. Microsoft > Can we *please* have a "All Supported Files (*.rdl|*.rsd)" option?

So now we Open the dataset we are interested in and (NPL Parameter Default Values.rdl), run our test, and then move on to the next one. I'm going to skip a couple now, ones that work and I'm able to test in Report Builder and skip on to the next annoyance.

For those of you keeping track this is the dataset called "OE Item Type Demand Planning - Item Orders.rsd" (or dsOEItemTypeDemandPlanning_ItemOrders in the dataset list above).

I'm picking on this one because the report I'm testing allows you to enter multiple values into a parameter;

Report Builder 3: Editing SQL

This is configured as (looking at Parameters under Set Options);

Report Builder 3: Parameters

Now when you try and run the report you get the standard "Define Query Parameters" dialog;

Report Builder 3: Define Query Parameters dialog

Guess what? There is no ability to include multiple values for a single parameter so if (as in this specific case) your test conditions include a parameter having multiple values then you need to copy/paste the SQL from your dataset into a tool which does support it (I use PL/SQL Developer) and then run the SQL there.

Needless to say tracking down the specific cause of the error in this specific case took *hours* but it's not the amount of time taken that annoyed me enough to make me document it here; it's the sheer frustration of the process that needs to be followed and the need to use non-Microsoft tools (PL/SQL Developer) in order to do some testing.

So here are my recommendations (for Microsoft);

• The obvious one; provide enough detail in the error messages for the user to know where the problem is. Did this really need to be said?!
• I should be able to open Shared Datasets by right/double clicking them. I'm happy for it to spawn a new process if necessary but having me do it manually is just a time wasting step I don't need.
• Datasets should receive the same treatment as reports in Report Builder 3 "Recent" listing.
• The default open file-type should be both Reports and Datasets
• If I'm using SharePoint Integrated mode it should remember the Document library I'm working in (maybe a Preference?) and the open dialog should take me right there.
• If Reporting Services supports multiple-value parameters so should Report Builder 3.

Oh and just in case you're wondering I did go to the Connect website and did a search from SQL Server Reporting Services and had no results, then reporting services with also no results, and finally SQL Server with results - but no obvious way of reporting a Reporting Services issue.

Am I really alone in finding this just a frustrating?!

Footnote: *Please* refrain from telling me how much easier everything is in Visual Studio. We have around 50 non-IT users in the business who write reports who we are quite happy to give Report Builder to but would draw the line at Visual Studio and, in our experience, you either use Visual Studio or Report Builder - mixing them is just a recipe for Visual Studio users overwriting Report Builder users changes.