Searching vs Filtering

This is one of the questions that keeps coming up (usually in discussion featuring Microsoft SharePoint and equally usually starting with a complaint about not being able to find something they "know is there").

Mostly the issues come down to the differences between searching and filtering and I put together the little example below which helped explain things;

Bob lives in a house. He's got lot of books one of which is the Great Gatsby. His bookshelf is in his lounge and everything in his life is nicely indexed and searchable and all result sets include the location. 

Bob wants to find his book. 

He does a search "book=great gatsby AND location=lounge". He gets the result that the book he's looking for is in the bedroom (where he left it). 

Now if he'd used the same text as a filter then he'd have had no results as the book wasn't in the lounge and filters don't have the flexibility to, if no result matches exactly, to widen out to include "close enough"-matches that, in this case, give him the result he's looking for.

This worked quite well so I thought I'd share it.

As a side note this also demonstrates the effectiveness of a really good search engine. Take, for instance, the majority of users habit of "filing" their emails into folders. If everyone in an enterprise was reliably able to search for things just think of all the time that could be saved by everyone just archiving emails out of their inbox rather than filing them away.

SharePoint 2007: Implementing Simple Tagging

This blog post just covers a very simple "tagging" example that I've been using in our SharePoint 2007 Reporting Services server to allow the sharing of reports between different areas. For example in Finance we have a simple report that gets GL Journal Entry lines for a specific Account Code combination that is useful to many of the teams in Finance so they all want access to it. Using tags each team can have it's own view of reports that include the "shared" report.

Let's start with looking at how our current environment is configured. Basically we have a single Document Library (Reports LIVE) which contains all our reports and we've added various additional fields to the SharePoint library such that when you edit the document it looks like this;

Report Properties (SharePoint 2007)

As you can see we had a slight disagreement on how we should work. I championed the "tag everything, categorise nothing" position and sadly had to compromise on "Categorise on Business Process, and Tag Teams Within The Process" - of course we quickly had to turn the categories into a multi-select but otherwise it seems to work just fine (of course I still think I was right - but doesn't every software engineer?!).


The field I'm going to be storing tags in is the "Keyword(s)" field, as you can see it's not mandatory (as sometimes a Process is so distinct that after you've defined the process category then tagging is just irrelevant) and for the report specified above it's actually blank - we're going to change that.


I've picked an Expense report (from Oracle Internet Expenses) deliberately as I know that it is used by two teams; expenses, and accounts payable.  At the moment all the teams in Finance (General Ledger, Purchasing, Inventory, Audit, etc) all have the report visible in their lists.


The first step is to enter some keywords. I'm using ";" as a separator so I've entering "expenses;payables" (note the lack of a space);

Adding Keywords To The Report

Now that the changes have been saved go to the drop down at the top right of the document library and choose "Create View". I'm going to create a view called "Finance: Payables" that will contain any report that has been tagged with "payables";

New View for Finance (Accounts Payable)

Select the columns you'd like to display and then scroll down to the Filter section and choose to show the items when column "Keyword(s)" contains "payables";

Building a View Filter

Now save the new view and you should see the report you've tagged. To properly implement this you then need to create a new view to pick up the reports tagged with "expenses" - then you'll see the same report appearing in both views but not, if you continue the example further and create views for other groups, in those views.

Of course you're totally reliant on everyone spelling (and typing!) everything correctly - something that's far from guaranteed - but it does give you a way of dynamically categorising your items and sharing them between different groups. Something using folders won't let you do.

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: Using TS&R To Fix Report Builder 3 Issue with SharePoint Integrated Mode

This blog post describes a remedy for the issue which prevents Report Builder 3 from working with a Report in SharePoint integrated mode that has been migrated to SharePoint from a different server.

At the moment Microsoft has no solution for this issue (other that "don't use Report Builder 3).

NOTE: The exact problem that this blog post is showing you how to fix is detailed in another post here.

Background/ Setup
The first step in fixing this problem is gathering all the information. Each of your Reports will have embedded into it both the previous server URL and the location it was using on that server for it's Data Set. In this example we will assume the following;

The original server used the structure;

SSRS: Non-Sharepoint Integrated Mode Folder Structure

With the root folder being treated as "live" and the DEV and TST folders being hidden. The only location for data sets is in the "Shared Datasets" folders (highlighted in green).

This gives us the following list of "possible" sources for Data sets;

• /DEV/Order 2 Cash/Shared Datasets
• /DEV/Production/Shared Datasets
• /DEV/Finance/Shared Datasets
• /TST/Order 2 Cash/Shared Datasets
• /TST/Production/Shared Datasets
• /TST/Finance/Shared Datasets
• /Order 2 Cash/Shared Datasets
• /Production/Shared Datasets
• /Finance/Shared Datasets

Each of these will need to be mapped to the "new" folder on the SharePoint integrated mode server. For the purposes of this example (and to assume maximum change) I'm going to assume the structure of the new server is;

SharePoint Integrated Mode Structure

Assuming you're following Microsoft "guidance" (using properties rather than folders) it's quite possible you could end up with something like this.

One other difference between non-integrated mode and integrated mode is that the Data Sets now have .rsd as a suffix (which makes sense - but is annoying).

An the final difference is in the server name itself (well assuming you did a migration rather than an upgrade to the server!). In our example I'm going to assume the SharePoint server is called "ssrsintegrated" and the other server is called "ssrs".

And that's it - a complete list of all the changes we need to make.

Configuring The Application
The first step is to download and open the application (it's open-source and you can find the details of how to get hold of it on another blog post TS&R: Replacing Text in Multiple Files).

The Search Folder needs to be set to point to the SharePoint servers' published folder for the "Reports "document library;

\\ssrsintegrated\DavWWWRoot\Reports\

The File Extensions we are interested in need to be restricted to SSRS Reports;

|*.rdl|

The vertical lines either side will be added when you enter "*.rdl" into the entry box;

Next is the Replace Strings entry box. Copy/Paste the following lines;

/DEV/Finance/Shared Datasets/|/Data Sets/
/DEV/Production/Shared Datasets/|/Data Sets/
/DEV/Order 2 Cash/Shared Datasets/|/Data Sets/
/TST/Finance/Shared Datasets/|/Data Sets/
/TST/Production/Shared Datasets/|/Data Sets/
/TST/Order 2 Cash/Shared Datasets/|/Data Sets/
/Finance/Shared Datasets/|/Data Sets/
/Production/Shared Datasets/|/Data Sets/
/Order 2 Cash/Shared Datasets/|/Data Sets/
http://ssrs/ReportServer|http://ssrsintegrated
http://ssrs/reportserver|http://ssrsintegrated
|.rsd
.rsd.rsd|.rsd

NOTE: The first 9 lines are just mapping old data set directories to the new one, the next two lines map the old URL's to the new one (you'll need to add additional lines if your developers capitalisation is not consistent - thankfully ours just settled on two versions!), the next line adds the .rsd to the Data Set reference and the final line removes duplicates if it was already there!

Finally an Output Folder needs to be selected (i.e. C:\TEMP\Report Output\).

Click "Execute" and the changes will be made and updated files will be placed in the C:\TEMP\Report Output\ directory - reports which are not changed will not be included in the new directory (so you could repeatedly run this process as you migrate new reports and only change the new reports).

Summary
Not the easiest method I know but at least it works and if you have 2/300 files it's a lot quicker than manual updates. It's completely ludicrous that Microsoft haven't patched this problem - it does make "Report Builder 3" a completely useless product in some specific circumstances and "just use BIDS" is not really acceptable unless they're going to scrap Report Builder 3!

SSRS: Report Builder 3 Dataset Issues in SharePoint Integrated Mode

This blog post gives a brief overview of a dataset issue when attempting to work with a report which has been migrated from it's existing Reporting Server into a SharePoint Integrated Mode server.

The report works when you run it but when you open it in Report Builder 3 and attempt to edit the dataset you get an error.

1. Duplicating the Issue

Go to a report you have migrated from another document library;

Trigger the pop-up menu and select "Edit in Report Builder";

Expand and double-click a dataset in the menu on the left;

The error reads "The shared dataset cannot be loaded from the server". If you click on the Details button the text is;

The path of the item 'http:///Data Sets/FA Asset Budget Codes and Total Costs.rsd' 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 'http:///Data Sets/FA Asset Budget Codes and Total Costs.rsd' 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.

You'll also notice that the "Current report server" box at the bottom left of Report Builder has re-connected to the "old" server the Report used to live on prior to being migrated. If this server doesn't exist then you'll see a connection error along the lines of "can't find the server".

2. What's Causing It

Go to a report you have migrated from another document library;

Hover over it and on the pop-up menu select "Send To" and then "Download A Copy". Open the downloaded file in a text editor of your choice;

The problem is in two keys, both under the key at the top of the file;

• SharedDataSetReference, and
• rd:ReportServerUrl

Despite the migration and re-pointing the Datasets in SharePoint the XML for the Report has not been updated and these two keys (for each dataset) are still pointing at the wrong server.

Ironically if you scroll down to the bottom of the file you'll notice that the rd:ReportServerUrl key for the report is actually set correctly - it's just the ones for the datasets that are incorrect.

3. Fixing The Problem

We have raised this issue with Microsoft and hopefully a solution will be provided soon (and I'll update this). Until that time the only possible fix is to manually download the file and update the affected two keys with the correct values from the server and then upload the file back on top of the original.

Hardly ideal (especially if you have a large number of files). It's possible that you could do a database update and "fix" the XML where it's stored in the Reporting Services DB but it's unlikely Microsoft would support it, and equally likely if you have a quality team who approves/ rejects your changes to a production system you can get them to agree to the risk.

4. Update 06-OCT-2011 ** Fix now available **

I have now written a fix which doesn't involve doing anything unsupported by Microsoft - it's available in this Knol here;

SSRS: Using TS&R To Fix Report Builder 3 Issue with SharePoint Integrated Mode

Enjoy!

SSRS: Problems Linking To SharePoint Lists When Running in Sharepoint-Integrated Mode

This blog post documents a problem linking to List Items in SharePoint from a SSRS Report Running on a SharePoint integrated-mode server.

NOTE: There is currently no solution to this problem. However there is a quick and relatively painless work-around (see below)

Prerequisites;

• SQL Server Reporting Services 2008R2 with SP1 server running in Integrated Mode (with SharePoint 2007)
• A SharePoint website containing at least one list to use as a source for reporting
• Report Builder 3.0 for creating/ testing the report

Duplicating The Issue
Create a new report (using the "Blank report" option in the Wizard);

Report Builder 3: New Report (using Wizard)

Click on the "Click to add title" box and enter the text TEST. Right-click the text box and select "Text Box Properties" and then go down to "Action" in the list on the left;

Report Builder 3: Text Box Properties

Select "Go to URL" and then click on the Expression (fx) elipse;

Report Builder 3: Expression Dialog

Enter the URL to directly link to an item in the list adding in the current value of ID from the report as the parameter. For the example list I'm using this is;

="http://xxx/Reports%20List/DispForm.aspx?ID=4"

This will mean when you click on the link it will take you directly to the corresponding item. Click "OK" twice to get back to the main report and then click "Run" on the ribbon;

Report Builder 3: Report Preview

Click on "TEST" and it will open the URL in Internet Explorer (for example My Tasks);

Internet Explorer: Working Link

The important point to note is the URL which is;

Internet Explorer URL Bar

http://xxx/Reports%20List/DispForm.aspx?ID=4

The "4" is the ID for the "My Tasks" item (shown above).

This shows the report is working correctly when run from within Report Builder.

Save the Report to the Integrated-Mode Reporting Services server and re-run it;

Test Reporting Running from SharePoint Server

I've highlighted the same item we clicked on previously, click on it again;

Incorrect Data Displayed

Different data. Incorrect data. If you check the URL it has become;

http://xxx/Reports%20List/DispForm.aspx?ID=4&ID=4[1]

You'll notice that the ID variable on the end of the URL has been duplicated. This is no-where in the code that causes the link not to work. If you add the URL we are building as a displayed field you will find that it is displayed correctly (with a single ID) - something in SSRS seems to be causing this duplication to be introduced in the renderer/viewer rather than in the code of the report.

Workaround
If, rather than building the URL as described above, you use the expression;

="javascript:void(window.open('http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID=" & CStr(Fields!ID.Value) & "', '_self'))"

This solves the problem by "hiding" the URL in the expression so it doesn't get incorrectly updated.

(Thanks to Klaus Sobel from Microsoft Support for providing me with this work-around!)

References

1. The error seems very specific to SharePoint URL's. For example if you change the URL to "http://www.google.co.uk/?ID=" then the problem (duplication of parameters) does not appear. Equally if you were to remove the ?ID= then you don't get the problem. If you then add in the “?ID=” a bit at a time you get; Adding "?" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?4. Adding "?ID" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID4. Adding "?ID=" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID=83&ID=4. This suggests some amount of parsing is going on somewhere, and the parsing is incorrect.

SSRS: Migrating Reports from One SharePoint Server To Another

Sounds easy? Problem free? Think again ...

This blog post is a step-by-step guide to copying a Report (consisting of a Report, one (or more) Shared Datasets, and a Data Source) from an existing Microsoft SharePoint 2007 document library into another document library on a different server (such as you might want to do when taking a production report into development for enhancement).

You can probably thing of at least three ways to do it off the top of your head;

1. Save each item into a file on your local disk and then upload them to the new server using the standard "Upload" button on the document library

2. Highlight each item in turn and under the menu choose "Send to" and then "Other Location" and specify the location of the document library on the new server

3. As 1, except "hack" the report file so that it works.

Way 3 works so you might want to jump straight to that bit if you're in a rush. I'll go through 1) and 2) and let you know why they don't work and explain some of the errors you'll encountered if you try them (and what probably got you to google for a solution anyway!)

0. Just Create the Data Source

It's much easier than trying to copy it - just create a new one.

1. Save and Upload

Locate your dataset on the existing server;

The dataset I'm going to be working with is called "Parameter Default Values" and is highlighted in the image above.

Bring up the menu for the affected dataset and then choose "Send To" and then "Download a Copy". Then you should be prompted to save the downloaded file to local storage.

Next locate the dataset library on your new server;

Choose the "Upload" button (as highlighted above) and then "Upload Document";

Select the file you just saved the data set to and click "OK".

Click "OK".

The report file has now loaded, apparently correctly, into the Document Library. Unfortunately it's not actually worked and is in an unusable state. To show this highlight the new data set and choose "Manage Datasource" from the popup menu;

The text of the error is;

The item XXX cannot be found ---> Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: The Item XXX cannot be found

You'll notice that the URL's for the error message feature the OLD server. The file has been migrated with hard-coded links and unfortunately the new server does not allow you to update these links so what you're left with is a problem that you can't fix (well, within SharePoint anyway).

You have to question whether the "Upload" button should actually be enabled for importing SSRS items as it doesn't work.

2. Send To > Other Location

Locate the dataset on the original server;

Hover over the data set and choose "Send To" and then "Other Location";

Enter the URL for the new document library (click to test).

Click "OK"

And this is as far as it gets ...

And now you need to try and stop it "copying". The only way I've found to do this is to kill the process in Task Manager. 

It's possible that there could be some strange permissions problem but, to be frank, I've clicked "Test" and that worked so why wouldn't the copy??!! Very poor process design Microsoft ...

3. Export/ Edit/ Upload

And here is the "working" method for migrating items between two servers.

Migrating Data Sets

Locate the data set on the original server;

Bring up the menu for the affected dataset and then choose "Send To" and then "Download a Copy". Then you should be prompted to save the downloaded file to local storage.

Open the locally downloaded file using a text editor (I prefer Notepad++ but practically anything will do);

There are TWO places in the downloaded data set where the URL of the Datasource and the Reporting Server are stored. Thankfully they are located at the top and bottom of the file so don't require much searching!

Locate the two entries (search for DataSourceReference, and ReportServerUrl) and update the URL's to point to the new server. Save the edited file.

Next locate the dataset library on your new server;

Choose the "Upload" button (as highlighted above) and then "Upload Document";

Select the file you just edited and saved and click "OK".

Click "OK" (we don't want to change anything).

The report file has now loaded, apparently correctly, into the Document Library. To verify this select the data set and choose "Manage Data Sources";

Seems to be working (well if it's not you can at least change it!).

Migrating Reports

Ironically this works in exactly the same way as migrating a report from a non-SharePoint server (that I have documented here).

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).