Author: John White

Deploying Reporting Services Reports to SharePoint using Business Intelligence Development (Visual) Studio

Published May 19, 2010

If you are using BIDS to develop reports for Reporting Services in SharePoint Integrated mode, you may find some of the deployment options somewhat confusing. Paths in Native mode must be relative, white in integrated mode, they must be absolute. To get to the deployment options, you right click on the project from the Solution Explorer window, and select Properties.

The highlighted areas are the ones that we need to be concerned with. The TargetServerURL property is the most important of the bunch, as you are essentially telling Visual Studio where to find the Reporting Services Web Service. The value that you select here should be the root of the site collection where the report is contained. Basically, because the SharePoint front end is now the report server this makes sense, and the property makes sense in native mode, but for integrated mode this property should be called TargetSiteCollectionURL.

The xxxFolder parameters all behave the same way, and they should contain the complete path to the container for each one (which easily could be the same value). The path should include everything including http, the site collection,the path to the site,the library, and if used, the SharePoint folder. In integrated mode, if you replace folder with Library, or even better, path, this will make more sense.

5 Comments

Using Themes and CSS with SharePoint 2010

Published May 18, 2010

Branding SharePoint just got a lot easier with the release of 2010. I used to have a basic rule of thumb when it came to using themes with 2007. Don’t use them. And if you must use them, don’t do further customization down the road. This is no longer true with 2010. The themes in 2010 are quite literally Office theme files (.thmx) and can be edited and created with PowerPoint.

There are really two types of branding exercises with SharePoint. The first is for public facing, typically anonymous sites with a high degree of customization and complete control over all of the visual elements. The second is for authenticated, typically intranet type sites where some customization is necessary, but where appropriate, out of the box visual elements can be used. In either case, themes are a good place to start the branding exercise, but they are particularly compelling with the latter, which is what this article is focused on.

Themes are essentially a collection of colours, and default fonts. You can apply a theme to a site by going to Site Settings, and selecting Site Theme under the Look and Feel heading.

As can be partially seen here, there are two other major theme features in 2010 that were not available in 2007,the ability to inherit a theme from a parent site,and the ability to push a theme down to sub sites, much like the way that master pages work.

From here you can select a theme, and if you like modify it. You can change the base colour for the 12 colour categories (the colour scales are calculated automatically), and you can set the base font face for headings and body. If you do modify these colour, you should be aware that you are not modifying the theme files themselves, which live in the theme gallery, but the derived CSS classes that are automatically generated whenever a theme is applied to a site. As such, you will find yourself working with a “custom theme”. Unfortunately, there is no way to save the custom theme off to the gallery, or locally, and that severely limits its reusability options. If anyone from Microsoft is reading this, please consider it a feature request.

The best way to edit a theme is by using PowerPoint, and it’s exceedingly easy. You can create a theme from scratch easily enough, but if you wanted to use one of the themes that ship with 2010 as a starting point, you can simply navigate to Site Settings (from the root site), and select Themes under the gallery heading. This is a simple document library, so select the theme in question, and click “Download a Copy” from the ribbon.

Once the file is downloaded, just double click on it to open up PowerPoint. PowerPoint will open up to a blank presentation. Click on the Design tab in the ribbon, and you will see that your theme colours are currently selected. You will edit your theme using the “Colors”, “Fonts” and “Effects” buttons.

Here’s where it gets a little non-intuitive. To change your colours, click the colors button and select “Create New Theme Colors”. I know… they’re not new, but to PowerPoint they are.

Edit away to your heart’s content, and then give the theme a good descriptive name so that you can refer to it later. You will likely use this name when saving your theme in a moment, but for now, this name will be used by PowerPoint to identify this group of colours. When you’re done selecting colours, save them, and repeat the process as necessary for fonts and effects. Once done, it’s time to save your theme. Simply use File-Save As, and select the “Office Theme (*.thmx”)” file type, and give the file a good descriptive name. Once that’s done, navigate back to the theme gallery, upload it, and it will be available for selection now under site theme.

What happens if you want your customization to go a little further? Maybe you’d like to tweak some of the CSS classes a little? Should your start figuring out what the system classes are and start changing them? What’s the best approach?

Under no circumstances (OK, maybe a couple, but don’t) should you modify the core CSS files. There are many ways to accomplish your goal, I use several depending on the use case, but one of the easiest is to simply create a CSS Override file, and tell the system to use it. I start with a blank file called CoreOverrides.css (call it whatever you want) and I store it in the root of the style library. The next step is to tell the site to use it.

From the root site, Go into site settings, and select the Master Page link in the Look And Feel section. Then scroll down to the “Alternate CSS URL” section.

The description for this section is just as poorly written in 2010 as it was in 2007. It implies that by turning this on, you’re turning the internal classes off. This is not at all true. The file that you indicate here gets loaded into the CSS stack after all other system classes have loaded, and therefore any classes defined here will have precedence over any identically named system classes. That’s why I call it Overrides.

How do you know which classes need to be modified? That’s where hacking comes in, or a lot of patience. You can study the master pages and page layouts, use view source on a rendered page, or use the IE developer tools. I use all 3, but I’m beginning to like developer tools a lot.

Load the page in question into IE, and then press the F12 key (or select Tools-Developer Tools from the menu). You will get a child browser widow pop up and from here you can navigate the entire DOM of the page. However, we want to see what CSS is being applied to a particular element, and the easiest way to do that is to select the CSS tab, and then select Find-Select Element By Click from the menu. You can then hover over any element on your page which will be outlined, and when you have the one that you want, click it , and its CSS stack will be displayed.

Once you find the class who’s behaviour you wish to change, simply copy its definition over to your overrides file, and edit away. Keep in mind though that you’re overriding a pre-existing class definition, and therefore you need to be explicit. If you want to remove a background image, you cant simply remove its definition, you must set it to “none”. Also, because some properties can be very sticky, I find the use of the !important directive to be very helpful.

Happy branding!

8 Comments

Opening PDF Files in SharePoint 2010

Published May 14, 2010

If you’ve installed SharePoint 2010, you may have noticed a change in behaviour of any PDF files that you may have stored. Previously, they would open directly in the browser, but now the user is prompted to save the file to the disk. This is due to a new security feature in IE8 that SharePoint 2010 respects. In order to allow the old behaviour, you must set the browser file handling options to “permissive” as opposed to “strict”.

This change is done at the application level. First, navigate to Manage Web Application within Central Admin, select the application in question, and choose General Settings on the ribbon.

Once in the settings screen, scroll down and find the section for Browser File Handling, and change it to permissive.

It goes without saying that this is less secure, but if you trust your PDF files, you should be good to go. Of course,as always,no warranties, express or implied…

Thanks to Mike Hacker’s Blog where I originally came across this.

UPDATE – SEPT 7 2010

I just ran across a case where this does not fix the problem. PDFs and all file types including HTML were prompting the user for download. This was not consistent, as it was happening in some libraries and not others. As it turns out, it’s a bit of a bug, and document libraries will not always inherit the browser handling attribute.

You can test this by running the following Powershell:

$site = Get-SPSite(“https://mysitecollection")
$web = $site.OpenWeb("/MyWeb")
$list = $web.GetList("https://myweburl/LibraryName")
$list.browserfilehandling

If it returns “Strict”, then you have a problem. The good news is, you can set it:

$list.browserfilehandling = “Permissive” ;
$list.update();

You should probably loop through your entire site collection and set this value to be safe. The powershell to do this can be found on Nerdtastics Tips, which is where I found the fix in the first place.

UPDATE – SEPT 10 2010

As opposed to hunting through your sites to find the problems, I wrote the below PowerShell script that take the URL for the site collection as an argument, and sets the permissive flag on any lists set to strict.

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$siteURL = $args[0]
$webname = $args[1]
$site = Get-SPSite($siteURL)

foreach ($web in $site.AllWebs) {
    Write-Host "Inspecting " $web.Title
    foreach ($list in $web.Lists) {
        if($list.browserfilehandling -eq "Strict") {
            Write-Host "Changing " $list.Title
            $list.browserfilehandling = "Permissive";
            $list.update();
            $site.url,
            $list.title,
            $list.browserfilehandling
        }
    }
}

UPDATE – OCT 5 2010

I’ve run into more situations where this doesn’t solve the problem. I created a new post describing them here.

18 Comments

Format an HTML Safe String using .NET

Published May 6, 2010

I’m posting this as much as a reminder to myself as anything. I infrequently find myself needing to make sure that a string is safe to use on the web in any number of scenarios. The .Net framework contains a function to do just that, but they’ve cleverly hidden it away in the security assembly. That’s why I needed to write this – I use it infrequently enough that I can never remember where it is.

To use it, just make a call to:

System.Security.SecurityElement.Escape("text to encode")

And that will do the trick "&" becomes “&”, “<” becomes “&lt”;, etc.

Installation Considerations for Reporting Services in SharePoint Integrated Mode

Published May 6, 2010

Ever Since SQL Server 2005 R2, it has been possible (with varying degrees of difficulty) to tightly integrate Reporting Services with SharePoint. What this means is effectively discarding the management and user interfaces that come with a Reporting Services native mode installation, and managing and use all reporting through the SharePoint interface.

This has really nice benefits for those managing the Reporting Services environment, mainly because you can simply leverage your SharePoint storage and security infrastructure instead of having to create and maintain another one simply for reports. Users benefit from a consistent user experience, and are easily able to create filtered reports through the Report Viewer web part.

Integrated mode is however not without its complications, and with the release this week of SQL Server 2008 R2 (which includes some very nice Reporting enhancements), I thought that I’d go over them. Here are some of the more important moving parts:

Reporting Services Add-In must be installed on EACH front end web server in the farm
Reporting Services database must be created in SharePoint integrated mode
Anonymous access must be disabled for the target application
The machine hosting Reporting services must be a member of the SharePoint farm

If everything that you’re running is on a single server, you really don’t need to read any further. In that scenario, everything is straightforward. But if you’re not on a demo system, or you have more than 3 users, chances are that your farm is distributed, and least broken out into one Web Front End (WFE) and one SQL Server.

As I’ve mentioned previously,The Reporting Services add in now installs as a prerequisite with SharePoint 2010,which makes the first point a no brainer. However, if you have SharePoint 2007 and multiple web front ends (your query servers count), you need to install the add in. Also if you add a new Front End server down the road, don’t forget the add in.

If you’re currently have a Reporting Services server running in native mode, and you’re looking to upgrade to SharePoint Integrated mode, don’t expect an enjoyable experience. You can’t upgrade your existing native mode database to integrated mode, you’ll need to export everything, create a new database in integrated mode, and move everything into it. You can however switch back and forth on the server side if you need to, but as you do, it’s all or nothing.

One thing that I wanted to do was to take an election results analysis demo that I’ve recently done with SQL Server 2008 R2 mapping and expose it to the world. Unfortunately, that’s not going to be an option because you can’t run the application in anonymous mode with the plug in. If you have this requirement, then Integrated Mode is not for you.

Finally, the big requirement is that the machine running Reporting Services must be a member of the SharePoint farm. Typically you only install the SharePoint bits on the WFEs and the Index servers, and the SQL Server itself is left alone. Basic guidance from Microsoft indicates that now you need to install the SharePoint bits on the SQL server and then join the farm. This may be daunting to those that have yet to “enjoy” the challenges of running a multiple WFE farm. It also might be a particular challenge if you don’t “own” the SQL server itself.

My preference (typically, not always) is to take the other approach, and install Reporting Sevices itself (none of the other services, database engine, etc) on one of the SharePoint front end servers. In our case, we have a 3 server farm (one front end, one indexer/query/central admin, and one SQL, and Reporting Services is on the Indexer/query/central admin box (I need a better name for it).

In this scenario, the Reporting Services databases themselves still live on the main SQL server, but the services and rendering all happen from the WFE machine. This makes for a very low touch solution in terms of your SQL server, and is much easier to maintain. This model also also allows you to get going with the new features available in SSRS R2 without having to first upgrade your central SQL server.

One caveat – with 2007, I always found that it was a requirement to also run the Central Administration application on the machine with Reporting Services. I don’t know if that was a true limitation, or something that I was missing. I haven’t yet tried it using R2 and 2010, but when I do, I’ll come back here and update this. Also – if you know something I don’t, please feel free to enlighten me!

5 Comments