Lot Genealogy (Part 2): Automating Testing of a Genealogy

If you are going to roll out any caching of your lot genealogy data the one clear thing you have to get right is testing. If, like us, you are working in a highly regulated environment it's necessary to ensure that you've got your testing right.

To this end I've created a new table called LOT_GENEALOGY_WHEREUSED_TESTS;

Lot_Genealogy_WhereUsed_Tests Table Description

The purpose of this new table is to hold copies of the lot genealogy data from Lot_Genealogy_WhereUsed. By holding these "snapshots" we can check after each rebuild of the cache that the data that was there and we had previously validated as correct is still there.

To create tests the following SQL will insert an existing Lot Genealogy into the testing table;

INSERT INTO LOT_GENEALOGY_WHEREUSED_TESTS
  SELECT 'ATR002', -- test_Ref
         'Product lot (166449) consists of two items (038003 and 038001)', -- test_description
         -- data from Lot Genealogy
         MASTER_LOT_NUMBER,
         INGRED_LOT_NUMBER,
         INGRED_ITEM_NUMBER,
         INGRED_ITEM_DESCRIPTION,
         INGRED_ITEM_TYPE_CODE,
         BATCH_NUMBER,
         PRODUCT_LOT_NUMBER,
         PRODUCT_ITEM_NUMBER,
         PRODUCT_ITEM_DESCRIPTION,
         PRODUCT_ITEM_TYPE_CODE
    FROM LOT_GENEALOGY_WHEREUSED LGW
   WHERE 1 = 1
     AND LGW.MASTER_LOT_NUMBER = '0490/0002';

This SQL is taking the lot genealogy for Master Lot Number 0490/0002 and copying it into the testing table, adding a test reference (ATR002) and a test description so we know what the test is supposed to be checking for.

That, as they say, was the easy bit. Now we need to create some SQL that is capable of running a test and returning a result. Our reporting tool (in case you can't tell from my other blog posts!) is SQL Server Reporting Services (SSRS) hence I'm going to split the SQL into two pieces, one to give me a list of all the tests and the other to run an individual test - SSRS will allow me to embed the latter as a sub-report into a report driven by the former.

List of Tests SQL

This was by far the easiest of the two;

SELECT DISTINCT LGWT.TEST_REF VALUE,
                LGWT.Test_Ref || ' (Lot ' || LGWT.MASTER_LOT_NUMBER || ')' LABEL
  FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT
 ORDER BY 1, 2

This returns something similar to;

List of Tests Generated Using SQL

I've added the bit of additional detail (for the Label) just so that in addition for being able to use this in the main report to get a list of tests I can also use it in the sub-report as a source for a picker on the "Test_Ref" parameter.

Run A Test SQL

This is slightly larger but here's the code then I'll try and explain it;

SELECT :Test_Ref "Test Ref",
       (SELECT TEST_DESCRIPTION
          FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
         WHERE LGWT1.TEST_REF = :Test_Ref
           AND ROWNUM = 1) "Test Description",
       TR.Test_Row_Count "Test Row Count",
       TR.Cache_Row_Count "Cache Row Count",
       TR.Union_Row_Count "Union Row Count",
       CASE
         WHEN TR.Test_Row_Count = TR.Cache_Row_Count AND
              TR.Cache_Row_Count = TR.Union_Row_Count THEN
          'PASS'
         ELSE
          'FAIL'
       END "Test Result"
  FROM (SELECT (SELECT COUNT(*)
                  FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
                 WHERE LGWT1.TEST_REF = :Test_Ref) Test_Row_Count,
               (SELECT COUNT(*)
                  FROM LOT_GENEALOGY_WHEREUSED LGW
                 WHERE LGW.MASTER_LOT_NUMBER =
                       (SELECT MASTER_LOT_NUMBER
                          FROM LOT_GENEALOGY_WHEREUSED_TESTS
                         WHERE TEST_REF = :Test_Ref
                           AND ROWNUM = 1)) Cache_Row_Count,
               (SELECT COUNT(*)
                  FROM (SELECT MASTER_LOT_NUMBER,
                               INGRED_LOT_NUMBER,
                               INGRED_ITEM_NUMBER,
                               INGRED_ITEM_DESCRIPTION,
                               INGRED_ITEM_TYPE_CODE,
                               BATCH_NUMBER,
                               PRODUCT_LOT_NUMBER,
                               PRODUCT_ITEM_NUMBER,
                               PRODUCT_ITEM_DESCRIPTION,
                               PRODUCT_ITEM_TYPE_CODE
                          FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
                         WHERE LGWT1.TEST_REF = :Test_Ref
                        UNION
                        SELECT MASTER_LOT_NUMBER,
                               INGRED_LOT_NUMBER,
                               INGRED_ITEM_NUMBER,
                               INGRED_ITEM_DESCRIPTION,
                               INGRED_ITEM_TYPE_CODE,
                               BATCH_NUMBER,
                               PRODUCT_LOT_NUMBER,
                               PRODUCT_ITEM_NUMBER,
                               PRODUCT_ITEM_DESCRIPTION,
                               PRODUCT_ITEM_TYPE_CODE
                          FROM LOT_GENEALOGY_WHEREUSED LGW
                         WHERE LGW.MASTER_LOT_NUMBER =
                               (SELECT MASTER_LOT_NUMBER
                                  FROM LOT_GENEALOGY_WHEREUSED_TESTS
                                 WHERE TEST_REF = :Test_Ref
                                   AND ROWNUM = 1))) Union_Row_Count
          FROM DUAL) TR

As you can see it takes a single parameter, the Test Reference Number. How it works is it counts the number of records in the cache, counts the number of records in the test table, and then does a select of all the records in the test table and, using a straight UNION, all the records in the cache. Because of the way UNIONs work (stripping out duplicates) the COUNT of the number of records returned by the UNION should be the same as the number of records in each of the other two queries. If they are all the same the TEST_RESULT is 'PASS' otherwise it's 'FAIL'.

NOTE: I'm sure this could be done a lot more efficiently but to be honest given the relative sizes of the tables I don't think you'll be sitting round too long for a result. On our system it takes less then .02 of a second. Your mileage will vary, but probably not by much!

Now that I've got the SQL I've setup a simple SSRS report to display the result for a single test back to me;

SSRS Report Showing A Single Test Result

You'll also notice that the test result includes a listing of all the records included in the test. This is a simple SELECT * ... WHERE TEST_REF = ... so I'm not going to give you the SQL for it.

The master report looks like this (in Report Builder 3);

SSRS Master Reporting Showing All Tests

I've included text boxes (on each page as part of the footer) to record who the Tester and Checker are and the Date. For our internal quality purposes we need this level of detail, you might not but what's the harm?

When executed the report appears like this;

SSRS Testing Report - Final Result

Hopefully you will find this useful.

SSRS: Scheduling A Report With 2008R2 (In SharePoint Integrated Mode)

In an ideal world you'd go to a report, enter your parameters, run the report, and a few seconds later you'd have all your data in the format you need. Unfortunately whilst going to the report and entering the parameters are (usually!) easy, the "few seconds" can quickly stretch to "a few minutes" and beyond before you get your data - particularly with some of the more complex GL-based reports.

As most reporting is clyclical (month end, year end, financial year, monthly sales, weekly orders, etc) it's possible to predict the reports you'll need for a particular period and it's possible, using Reporting Services and SharePoint to scheduled the reports to run when you know you'll need them.

The first step is to go to your report;

Simple SSRS Reporting Showing GL Segments

At the top-left of the web page is a "Actions" drop down. Select this and choose the "Subscribe" option. If you don't have a subscribe option then you probably need to contact whoever administers your server to see why not; there really isn't any reason why users who run reports shouldn't be able to schedule them to run out-of-office hours.

When you select "Subscribe" you'll be directed to the following web page to select the options for the report;

SSRS Subscription Properties

This page allows you to choose how you want to receive the report. By email is probably the simplest way (and is the default). The available options are;

• Email (default): You can choose to have a report sent to any email address (for example a distribution list) but you should already remember that only the person who sets up the Subscription will be able to edit it and if they leave and their Active Directory (AD) account is disabled the subscription will automatically stop
• Windows File Share: Select this to choose to have the report sent to a Windows File Share. You can specify your username and password to grant access. Useful under certain circumstances, but I'd recommend switching to a ...
• SharePoint Document Library: We like this one. Again it requires some setting up by your system administrator but once it's there then you get all the nice features of SharePoint (sharing, versioning, security, etc) without the permission problems of Windows File Shares. This is our preferred option for in-house reporting
• Null Delivery Provider: Sometimes it's necessary just to run the report without worrying about the results (triggering caching automatically for example). If that's the case then this is the provider to select.

Once you've picked the "Delivery Extension" (how you want the report delivered) then the next steps are to expand on that by selecting the following;

• Output Format/ Report Contents/ Render Format (Depends on Delivery Extension selected): Here you can select the format you want the report to be saved in. You can (in Sharepoint 2007 with SQL SSRS 2008R2) only select a single format which means if you want the report in multiple formats (for example PDF for archiving, and Excel for working with) you need to create multiple schedules (if you'd like to show Microsoft you'd like to see this changed you can vote for the item on Connect - see here)
• Delivery Event: You can select either; a) When a report snapshot is created, b) On a shared schedule, or c) on a Custom Schedule. You need to decide when you want the report and whether it's important or not if it runs at the *exact* time specified or within a few minutes/ hours. If it absolutely has to run at a specific time then select Custom Schedule 

"Delivery Event" Subscription Options*

• Parameters: Here you select the parameters for your report. Here you'll definitely need the help of the report writers who need to have written the report with scheduling in mind in order for it to work. For example if you have a report which has a Start and End Date then the default values need to be default to, for example, the start and end of last month so that when it's scheduled to run on the 1st of every month the user can just select "defaults" rather than having to hard-code values. With a bit of planning this can work really well (NOTE: The list of parameters includes *all* parameters for the report including ones developers have marked as Hidden - I've raised this as a bug with Microsoft click here)

Now that you've selected all the options click "OK" (at the top) to create the subscription. It won't run immediately but when the Delivery Event comes around it will try, remember to check back for errors!

*- you'll notice, if you squint really hard at the "Shared Schedule" option that we (OK, I) made a mistake when setting these up. The point of shared schedules should be to increase the flexibility of system administrators to juggle the jobs around but make sure they are still available when needed. "Monthly (1st) @ 7am)" isn't exactly flexible, what I should have created is "Monthly (1st) before 8:30" so I'd be able to move the reports around as and when.

Generating a "My Reports" In SQL Server Reporting Services (SharePoint Integrated Mode)

The idea behind this report is that most, if not all, of our users will need to keep running the same reports over and over again every month and so rather than coming up with some fantastic way of categorising the reports why not just write a report that looks at the logs and see which reports the user has run and how often and provide the user with this list to pick the report from?

Here's the screen capture of the final report;

My Reports - Sample Output

As you can see we took the opportunity to provide a method for users to provide suggests to correct the (laughably inaccurate) descriptions we hold of the reports into something a lot more meaningful for what the report does for them.

Now the next thing to say is that Microsoft does not support direct access to tables in either SharePoint or Reporting Services EXCEPT for SSRS access to the ExecutionLog* views. It also helps if, like us, you have consolidated your SQL Servers so that same server hosts both SharePoint's WSS_Content_ database AND the ReportServer database for SSRS.

Here is the SQL;

SELECT A."Report Name",
       AUD.ntext2          "Description",
       AUD.nvarchar12      "Status",
       AUD.tp_Version      "Version",
       A."Execution Count"
  FROM (SELECT C.NAME "Report Name", COUNT(*) "Execution Count"
          FROM ReportServer.dbo.Catalog C
          JOIN ReportServer.dbo.ExecutionLogStorage ELS
            ON C.ItemID = ELS.ReportID
         WHERE 1 = 1
           AND ELS.UserName = @Username
           AND ELS.Format = 'RPL'
           AND ELS.TimeStart > '01-JAN-2012'
           AND C.Type = 2 -- Report
           AND UPPER(C.Path) LIKE UPPER('%Reports LIVE%')
         GROUP BY NAME
        HAVING COUNT(*) >= @Min_Execution_Count
        ) A
  JOIN WSS_Content_Reporting.dbo.AllUserData AUD
    ON A."Report Name" = AUD.tp_LeafName
   AND AUD.tp_DirName = 'Reports LIVE'
   AND AUD.nvarchar3 = 'rdl'
   AND AUD.tp_IsCurrentVersion = 'True'
 ORDER BY A."Execution Count" DESC

As you can see this SQL joins the data in both the Catalog and ExecutionLogStorage tables in SSRS and the AllUserData table in SharePoint. The Execution details are restricted to just reports (ELS.Format  = 'RPL' and C.Type = 2) and, as our live reports are in a single Document Library in SharePoint, we have also restricted it to where the reports path (C.Path) contains the words "Reports LIVE" (the name of the directory).

Additional the details from SharePoint (AUD.ntext2, AUD.nvarchar12, etc) will vary depending on your configuration. If you don't have additional details stored in SharePoint (like descriptions) you can just drop that part of the SQL.

Finally we have two parameters @Username and @Min_Execution_Count. We use @Username as the user whose report execution history we're interested in and @Min_Execution_Count as a way of restricting the report to only display reports that have been executed over a certain number of times.

We order the results by the Execution Count in descending order so the most frequently run report is at the top.

Now that we've got the dataset set-up (I've called it "My reports By Frequency Used.rsd" and it makes use of a dataset in the "Data Sets" document library) the report which accesses it is available here - please note that in order to use it you need to save it as a normal text file. I have made the following replacements;

• xxxxx - This is the servername
• yyyyyy - This is the email address that the people clicking on "[Suggest Update]" will have their messages sent to

SQL Server: Installing Microsoft SQL Server 2012 On a Development Windows 2008r2 (SP1) System

This is a simple step-by-step guide to installing Microsoft's newest version of SQL Server with Reporting Services in stand-alone mode (the alternative is SharePoint Integrated, I'll deal with all the steps necessary to do that installation separately in a few days/weeks time).

I'm using the developer edition as downloaded from Microsoft MSDN, the ISO name is;

en_sql_server_2012_developer_edition_x86_x64_dvd_813280.iso

I've highlighted the build version. If past experience is anything to go by later releases will follow pretty-much the same installation pattern.

The first step is to install your Windows 2008R2 box and get it fully upgraded to the latest patch levels (SP1). Then you can run the setup.exe on the installation DVD;

SQL Server 2012: Installation Centre

Select "Installation" from the list of options on the left;

SQL Server 2012: Installation Options

Select "New SQL Server stand-alone installation ..." which is the top option on the right. After a few seconds wait (but I guess that probably depends on the speed and power of your system!) the following dialog appears;

SQL Server 2012: Setup Support Rules

The installation program has checked your system and, on the system I'm using, found nothing that prevents the installation from proceeding. If you are interested in seeing the "Detailed Report" I've updated it to Google Docs here (with a slight bit of editing to remove machine names).

Once you've got a good set of passes click "OK". A "please wait" dialog will appear and after a few seconds the next dialog;

SQL Server 2012: Product Key

You now need to enter your Product Key. If you are using the MSDN edition then it will be pre-populated, if you don't have one when you can just select one of the "Free" editions. Once you've entered the details click "Next";

SQL Server 2012: License Terms

No installation process would be complete without a 20-page Licensing Agreement and this one is no different. After you've read the entire document (you do do that right? *cough*) click on the "I accept the license terms". I also select the "Send feature usage data to Microsoft" and if you don't you'll only have yourself to blame when the features I use and tell them about are prioritised for improvement and the features you use but keep secret about aren't ... ;-)

Click "Next";

SQL Server: Install Setup Files

This dialog will only appear for a few seconds, as soon as the installation files have been successfully installed you are presented with the next dialog;

SQL Server: Setup Support Rules

Again I've made the Detailed Report for this check available via Google Docs here. As you'll see there is one Warning related to the "Windows Firewall", I'm going to ignore this and just move on.

Click "Next";

SQL Server: Setup Role

The default option, "SQL Server Feature Installation" is the one I'm after so I'll just click "Next";

SQL Server: Feature Selection

And this is the part where things start to get complicated. What exactly do you want your SQL Server to be doing? I'm going to install pretty much everything except the SharePoint integrated features (the first two shared features; Reporting Services - SharePoint, and Reporting Services Add-in for SharePoint Products). Clearly I'm building a development box so it's usually better to have too much rather than too little so I can use it for many different purposes.

Clearly if you're building a test (or production) machine you need to be a lot more careful about what you select on this page. As you select features configuration pages appear on the left to allow you to configure them during the installation.

Once you're done click "Next";

SQL Server: Installation Rules

So we now have another check, the third, to make sure the system is capable of running the options I've selected. And now it "Failed" as "Microsoft .NET Framework 3.5 Service Pack 1 is required". The instructions for enabling this feature I have also blogged about, click here. When I did this no-reboot was required.

After you've enabled the feature click on "Re-run" in the dialog;

SQL Server: Installation Rules (Attempt 2)

As you can see the check that had previously failed has now passed. The Detailed log is available (via Google Docs) here.

Click "Next";

SQL Server: Instance Configuration

There isn't anything here I need to change so just click "Next". There will be a brief pause while the installation program checks to see if sufficient disk space exists to install the options you have selected. After a few seconds a report will be displayed;

SQL Server: Disk Space Requirements

Click "Next";

SQL Server: Server Configuration

It's pretty unlikely that you'll want to change anything here (all these are services you can always change later). The one thing you might want to check if you are deploying to non-English customers is that the correct options are selected under the "Collation" tab - this is especially true if your system will be used to store multi-byte data such as Chinese, Japanese, Korean, etc. characters.

Click "Next" when you're done;

SQL Server: Database Engine Configuration

I always use "Windows authentication" and (as it's a development machine which will spend 90% of it's time off!) I usually add in a few AD groups representing the entire company (we are an IT Service company) on the basis that I never know who I might want to share it with. This is your chance to secure the system either as tightly or loosely as you wish.

Click "Next";

SQL Server: Analysis Services Configuration

And the same again really. You need to enter the users who will have access to the Analysis Services. Once you're happy with this click "Next";

SQL Server: Reporting Services Configuration

The default option, "Install and Configure", is the option I'm interested in so just click "Next";

SQL Server: Distributed Replay Controller

Whilst appearing to be exactly the same as other "Pick the users" dialog box this one is subtly (invisibly!) different; you cannot select groups. If you try when you click "Next" you get an error;

The specified account 'XX\YYYY' for setting 'CTLRUSERS' is a group account. You can only use a user account.

Add in the users for this feature and click "Next";

SQL Server: Distributed Replay Chat

Enter the name of your controller (or leave blank) and then click "Next";

SQL Server: Error Reporting

I always check this check box (to send error reports to Microsoft) so that errors I encountered will hopefully be dealt with in future releases! Click "Next" when you're done and another round of checks will be executed and after a few seconds you will be presented with a report;

SQL Server: Installation Configuration Rules

Again I have uploaded the detailed report to Google Docs here. Click "Next";

SQL Server: Ready To Install

Click "Install" to begin the install process. The installation itself on the development system I was using took around 25/30 minutes. After the install is complete you will see the following dialog;

SQL Server: Computer Restart Required

Click "OK" and under this is the final installation report;

SQL Server: Complete (Install Report)

I've attached the (edited) log file generated by the installation here.

A restart is required, after the restart running Internet Explorer and pointing to the the SQL Server Reporting Services URL will (after logging in) take you to the standard webpage.

SSRS: Duplicating the "Dataset1" Error Message

This is a fairly simple blog post (requested by Microsoft) showing how to create the "Dataset1" error message when you are using a report which contains no dataset called "Dataset1". Just as a quick recap the error message is along the lines of;

Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)

Now to make this as simple as possible I'm going to create a dataset using the (Oracle) SQL;

SELECT SYSDATE

FROM DUAL

WHERE TO_NUMBER(:TEST) = 1

This just returns the current date/time when you pass in the value 1 in the "Test" parameter.

If you pass in a non-numeric value then you'll get an error (ORA-01722: invalid number) when Oracle tries to do the conversion to a number.

Save the dataset as a Shared Dataset then create a new report and add the dataset to it (Using the Table Wizard).

Running the report with parameter "q" gives;

DataSet1 Error Message

As you can see the lines in the error message are;

ORA-01722: invalid number
----------------------------
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
----------------------------
The execution failed for the shared data set 'SSRSTestShowingDataset1error'. (rsDataSetExecutionError)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

I've highlighted the interesting line above. This line is clearly identifying the dataset which caused the error. Something I missed when writing up my previous example (see here) because, amusingly, this time the error coming back from Oracle/ SSRS is "simple" and appears on a single line - my previous error appeared on two lines and thus caused that important identifying line to slip off the bottom of the expanded error dialog (and I never thought to use the scroll bar!).

So while "DataSet1" is still - incorrectly - identified as the source of the error additional details are available.

SSRS: Changing US-format Date/Time Pickers (SharePoint Integrated Mode)

If you have, like us, configured your servers and clients for a non-US locale and then been surprised to see that when running SQL Server Reporting Services (SSRS) all the date/time pickers seem still, somehow, to be stuck in US Date/Time format the fix is actually incredibly easy.

Basically the issue is that SharePoint uses neither the Locale of the Server or the Locale of the User but instead uses the SharePoint culture to determine the format that the Date/Time pickers will use.

In order to fix the issue you need to be able to administer the site in order to update the Regional Settings.

Go to your SharePoint website;

"Site Actions" on a SharePoint site

Using the "Site Actions" drop down select "Site Settings";

Site Settings in SharePoint

Under the "Site Administration" section select "Regional settings";

Site Settings > Regional Settings in SharePoint

Change the locale (which should be saying "English (United States)") to the correct locale.

Date/Time pickers will now be in UK-format (or whatever format you have selected);

SSRS Date/Time Pickers Showing UK-format

NOTE: It's worth noting that if you set your users settings to a different region the settings on the Site seem to take precedence to your personal settings.

SSRS: Scheduling/Subscribing To An Existing Report

This is a fairly simple blog post with details of how to schedule an existing report so that it will run automatically and send you the report. The process below describes using Reporting Services 2008R2 running in Sharepoint Integrated mode - standalone more is similar but not identical.

It's worth noting that in order to schedule a report to run it's vitally important that the critical parameters for the report (i.e. reporting period) have default values which automatically roll forward as the months pass; this way you only need to schedule the report to run one rather than having the schedule the report to run in each period (if you get the distinction). Your Report Writer/ System Administrator will be able to help you if you need parameter default values changing.

The first step is to go to the report URL;

SSRS "Specify Parameter Values"-default Report Web page

At the top-left is an "Actions" menu, from this drop down select "Subscribe";

Subscription Properties Web Page

On this page you can select how the subscription should be delivered (i.e. email, windows file share, Sharepoint document library) and the frequency that the report should run on and the delivery preferences (i.e. Excel file, web archive, etc).

Generally most of these you will want to leave as default except the "Delivery Event";

Subscription Options: Delivery Event

As you can see you have in essence three options;

• When a report snapshot is created: This option is only relevant if you are using reporting snapshots. If you aren't sure (or don't know what one is) then this isn't the option to pick. Usually snapshots will be managed by your system administrator
• On a shared schedule: Your administrator can set-up "shared schedules" on the server which allow multiple reports to be scheduled within a window and, in the event of scheduled downtime for instance, that window to be moved around. By attaching your report to a shared schedule it puts the execution of your report in the hands of the system administrator - this makes it a log easier for the System Administrator but if your report *must* run at a set time then this is not the option to pick
• On a custom schedule: You are most likely to choose this option, this gives you complete free reign on when your report executes and how often it executes

If you select "On a custom schedule" and then click "Configure" you will be presented with the following screen;

Subscription Options: Defining a Custom Schedule

Changing the frequency at the top allows you to adjust the schedule. If you need a more complex schedule (for example run every Tuesday and on the First of the month) then you will need to configure multiple subscriptions in order to handle your request - there is no limit to the number of subscriptions you can create so this shouldn't be a problem.

Once you've defined your schedule you now need to pick the parameters for your report;

Report Subscription Parameters

As a general rule unless there is a specific reason to not use "Use Report Default Value" then I'd recommend you stick with that.

When all your parameters have a value click "OK" at the bottom of the web page, you will then be presented with the "Manage Subscriptions" page for this report showing your new subscription;

Manage Subscriptions Page for Report

If you with to add other subscriptions (i.e. you have a complex schedule which needs them) then you can click "Add Subscription" otherwise click "Close".

NOTE: If you ever want to un-subscribe from a report find the report in the document library, hover over it for a few seconds until the drop down menu appears, and then select "Manage Subscriptions".

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.