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|
|Report Builder 3 Datasets|
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|
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|
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|
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|
|Report Builder 3: Parameters|
|Report Builder 3: Define Query Parameters dialog|
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.
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.