SQL Server: Installing BIDS 2008R2 (Developer Edition)

After inserting the CD and running the Setup.exe the following User Account Control (UAC) dialog will appear;

SQL Server 2008 R2 UAC Dialog

Click "Yes".

The SQL Server Installation Center dialog will now appear;

SQL Server Installation Center

Click on "Installation" on the left (highlighted above);

SQL Server Installation Center (Installation Page)

Click "New installation or add features to an existing installation" (the top option on the right);

SQL Server 2008 R2 Setup

The installation program will run some tests to make sure your system can run the software. I'm installing onto Windows 7 Enterprise (32-bit). If there any any issues you'll need to resolve them before it will allow you to continue. Click "OK";

SQL Server 2008 R2: Product Key Dialog

After entering your product key click "Next";

SQL Server 2008 R2: License Terms

Click on the "I accept the license terms" checkbox (after reading it of course!) and then click "Next";

SQL Server 2008 R2: Setup Support Files

Click "Install" (if the installation of Setup Support Files fails with 1605 or 1608 error message then you may be interested in this post here - thank you Sage!);

SQL Server 2008 R2: Setup Support Rules

Click "Next";

SQL Server 2008 R2: Setup Role

Click "Next";

SQL Server 2008 R2: Feature Selection

Select "Business Intelligence Development Studio" (I've also chosen the Client/ Management Tools as they're often quite useful as well). Click "Next";

SQL Server 2008 R2: Installation Rules

Click "Next";

SQL Server 2008 R2: Disk Space Requirements

Click "Next";

SQL Server 2008 R2: Error Reporting

Click "Next";

SQL Server 2008 R2: Installation Configuration Rules

Click "Next";

SQL Server 2008 R2: Ready to Install

Click "Install";

SQL Server 2008 R2: Installation Progress

Wait for the installation to complete (takes about 10 minutes);

SQL Server 2008 R2:Complete

Click "Close".

You can now find SQL Server Business Intelligence Development Studio on your start menu;

SQL Server Business Intelligence Development Studio

NOTE: One of the fantastic anomalies with BIDS is that unless you have Integration Services installed locally you don't seem to actually do deploy to a remote server from within BIDS - you need to deploy to the file system and then use management studio to deploy to the server.

Also unless you install Integration Services *locally* you won't be able to test the deployed package on your system.

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.

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: 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: Migrating Reports to SharePoint Integrated Mode

This blog post covers the process of migrating a report from Microsoft SQL Server Reporting Services 2008R2 running in non-integrated (i.e. non-SharePoint) mode to a different server also running 2008R2 but running in SharePoint integrated mode.


A Little Background
This is important as it will affect the steps you need to take!

In our environment we split the various parts of the report (Data Source, Data Set, and Report) into separate files. This allows multiple datasets to share a data source, and multiple reports to share a data set. It's a fairly useful design.

If you don't do this and just bundle everything into the Report - your maintenance must be a nightmare if you have a large number of reports! - then all you need to do is the single "Migrating the Main Report File" step below.

I suspect (hope!) that most large companies will do it our way.

Finding Items To Migrate
There are three parts to a report; Data Source, Dataset, and Report.

Steps to identify the bits to move are given below.

Identifying The Report
Find the existing report you wish to migrate on the non-integrated server. In this example I'll be migrating the "AP Separation Monitor" report;

Identifying Any Datasets
Hover over the report name until it's menu appears and then select "Manage";

Click on "Shared Datasets" in the list on the left side;

Make a note of these "Datasets".

Identifying Required Data Sources
Find each of the datasets identified above and "Manage" them;

Click on "Data Source" in the panel on the left;

Make a note of this data source.

Migrating The Main Report File
Hover over the report name until it's menu pops up and then select "Edit in Report Builder";

Choose "Save as" from the Ribbon menu;

Click on "Recent Sites and Servers" at the top left on the pane on the right;

This displays a list of all the recent sites and servers you have used. If your new SharePoint server is not in the list then you'll need to create a new instance of Report Builder and create a new report on your new server (which you can then delete) in order to get the Site/Server to appear in this list.

Select the new (Integrated mode) server you wish to use.

You are now presented with a list of all the document libraries in SharePoint and you need to pick the one in which you want to save the report. I'm going to use "Reports LIVE".

This is a list of existing reports in the document library. I'm not planning on changing the name so just click "Save" (you will get an error);

The key part of the error is "The report definition was saved ...". The errors are due to the data sources not being exactly identical on the new server with what they were on the old server. Clicking on "Details" for my report I can see the error text is;

The report 'http://xxx/Reports LIVE/AP Separation Monitor.rdl' contains a reference to a dataset that is not valid. Verify that the shared datasets that are required for this report are deployed to the report server.

As we haven't moved across the datasets yet this problem is expected.

Close down report builder.

Migrating a Dataset
The process for migrating a dataset is almost identical to the process for migrating a report (so I'm not going to reproduce the image).

Hover over the data set until it's menu appears and choose "Edit in Report Builder", use "Recent Sites and Servers" to point to the new server, select the document library in which you want to save the dataset (I'd recommend a different one to the report!) and click "Save". You get an error, but it's different;

Note the key difference that you're not seeing a "The report definition was saved ...". It has been saved, but the message is much more worrying - still just as irrelevant - but it looks more worrying.

If you click on "Details" then the message gets truly bizarre;

"The path of the item '/Data Sources/NOETIX_R12TEST' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidItemPathException: The path of the item '/Data Sources/NOETIX_R12TEST' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash."

This is a rubbish error message (Microsoft take note) but it doesn't matter as the dataset has been saved;

Close down Report Builder (you will be prompted to save again, just ignore that and click "No").

Migrating The Data Source
Don't. Just create a new one. It's genuinely not worth the hassle of migrating an existing data source - all your passwords will be cleared anyway so why bother?

On our new folder Data Sources live in the Data Sources document library;

You'll only need to do this once for each Data Source.

Connecting It all Together
Looking at the Report/ Dataset/ and Data Source information we have from the old server the structure we need to configure on the new server is;

Joining Dataset to Data Source
Go to each of the datasets on the new server hover over them until the menu appears and choose "Manage Data Sources";

This shows you (via the yellow triangle) that there is a problem with the Data Source. Click on the link (called DataSetDataSource) to get the properties;

As you can see from the error message (The linked data source could not be found) you need to specify the Data Source Link. Click on the elipse to locate it or just type the full name into the box. In the case of this example the Data Source we are after is;

http://xxx/Data Sources/NOETIX_R12TEST.rsds

(If I'm going to convert a lot of reports I tend to have strings like this in Notepad somewhere as, if you try the ellipse, you'll see that it's not exactly quick identifying the data source that way). Once you've found/ entered the Data Source click "OK and it will be verified.

Click on "Close"

Joining Report to Dataset(s)
Find the report, hover over it, and select "Managed Shared Datasets";

In this specific report we have two datasets that aren't configured correctly. Click on the link (we'll start with APSeparation Monitor);

Click on the ellipse;

As I have separated the datasets from the reports there are no datasets in the default directory (the reports directory) so click on "Up" and navigate into the datasets document library;

Select the "AP Separation Monitor" dataset (the one we recorded earlier);

The link is actually the URL of the Dataset;

http://xxx/Data Sets/AP Separation Monitor.rsd

Click on "OK" and the link will be verified.

The first dataset has now been fixed, repeat the process to fix the second dataset. You'll notice that the dataset links aren't obviously named so it's worth writing down the names from the old server (which you did earlier ... right?) rather than hoping to remember it when you get to this stage.

Click "Close" when you've completed the fix.

Test the report and you're done.

Tidying Up
Ah yes, now you've moved the report across you really should be getting rid of the old one. If it's a big migration job it really helps to not complicate things further by leaving items you've already migrated on the existing server. Equally though you don't want to break anything.

The easiest item to remove is the report - these can be deleted straight way as nothing can really be dependant on them (if you've separated everything correctly!). Find the report, hover over it, and select "Delete" from the menu.

As there is a possibility of a dataset being used in a different report you need to check. "Manage" the dataset;

Click on "Dependent Items" on the left;

As you can see the message;

"There are no items that use the "/R12/Finance/Shared Datasets/AP Separation Monitor" dataset."

Is being displayed meaning that there is nothing (now we've deleted the report) that used the dataset so we can safely delete it (go back to "Properties" on the left and click "Delete").

Now looking at the other dataset and doing the same check;

This shows that there are a large number of other reports that are using this dataset so it should be left alone rather than being deleted (why Microsoft doesn't do this check and give you a warning when you do a delete is beyond me!).

And that's it. We now have a report running on a different server. There is slightly more tidying up to do to ensure that the SharePoint details for the Report and Dataset are up to date (for example the Dataset description will not have been copied across).