Search adventures with SSRS in Integrated Mode

At Trek we’re all about the BI. Just so happens I sit within the BI team so it was no surprise when Steve wanted to go with all the BI tools. PowerPivot and PerformancePoint were already setup before I got there. With SQL 2012 we get an overhauled instance of SSRS in Integrated mode and the newly introduced Power View. SSRS is now a Service App rather than a separate application so it makes deployment a lot easier. I won’t bore you with the details of installing as you can find all sorts of other bloggers walking you through the steps. What most bloggers don’t cover are the details of searching for .rdl’s.

Installation is pretty easy and getting your report libraries up and running are relatively straightforward. We even managed to create a few shared data sources and reports the day we went live. Real “Ready, Fire, Aim” type stuff. We happened to do the install on a Friday so we didn’t really put everything through its paces until Monday. When we began emailing links to rdl’s around we started getting complaints that users couldn’t see these reports. In addition, rdl’s weren’t showing up in search results.

Now what?

Posted this to the technet forums: LINK. Nauzad was pretty helpful in his reply in pointing me in the right direction. Come to find out, rdl’s are not extensions SharePoint search crawls by default. That’s an easy fix. Navigate to Central Admin > Manage Service Apps > Search Service App > File Types > add “rdl” and you’re golden. Kick off an incremental crawl and you should start to see rdl’s showing up in your results.

But I also noticed that only Full Owners, Site Collection Admins, and Farm admins were seeing reports in search results and in the libraries themselves.

Based on my research for the search issue I found that Report Libraries rely heavily on Publishing. Because of this, only those with Full Control rights and Site Collection Admins or Farm admins will see the rdl’s until they’re published, but it doesn’t stop there. You also have to publish the data source file as well. After some trial and error we figured this all out.

It’s relatively easy to get around the publishing requirement. Navigate to the Library Settings > Advanced Settings > check the box for “all users can see draft items.” Doing so will make all reports and data sources viewable as well as surface all reports in search regardless of published status.

For now, we’ve made the intentional decision to leave publishing on as it will allow use to security trim who has access to publish (accurate) reports and provide layer of oversight.

Issues adding add’l SharePoint server when using SP1 media

Few months back we added an app server to our farm, and – as always – it turned out to be an adventure.

Got a fresh Windows Server 2008 R2 install, joined to the domain, and applied all pertinent hotfixes and patches. While things were installing I downloaded the latest .iso’s from Volume Licensing. I went with the SP1 .iso’s as I figured this would be fine since the farm was already well past SP1. Save some time right?

Popped the dvd’s in the server and away we went. Install went fine with SharePoint so I moved on to the Office Web Apps. No incident there either.

I started up the Products Configuration Wizard and tried to join the box to the farm when I ran across an (in)convenient message telling me the Office Web App language server proofs we’re missing. But didn’t they install when I laid down Office Web Apps? What gives!?!

I commenced to Googling and I ran across this:

Looks like the slipstream installers are broken. I would take a guess and say Microsoft won’t be fixing them any time soon either.

Needless to say, I ended up reformatting the hard drive and starting over. I downloaded the pre-SP1 media and installed in the following order: SharePoint 2010 pre-SP1 > Office Web Apps pre-SP1 > SharePoint 2010 SP1 > Office Web Apps SP1 > SharePoint June 2011 CU > Current SharePoint CU. Took considerably more time, but it joined the farm no problem.

Moral of the story here is if you stood up your farm prior to SP1 with Office Web Apps, and you want to add another server to the farm you’ll need to do so following the procedures I went through.

Anyone else run into this?

Remove ;# when using a SharePoint List as a data source in SSRS

I find myself creating more and more SSRS reports with a SharePoint List as the data source. If you’re like me you’ll run into this issue sooner or later; the dreaded ;#.

Generally speaking, you’ll run across this when there is a choice column setup to allow multiple values. Gina and Zach helped me put this fix together:

Function ReplaceChar(ByVal value As String) As String 

	Dim fixedString As String 

	if(String.IsNullOrEmpty(value)) then

		fixedString = String.Empty


		fixedString = value.Replace(";#",", ") 
		fixedString = fixedString.substring(2, fixedString.length-2) 
		fixedString = fixedString.substring(0, fixedString.length-2) 

	end if

    Return fixedString
End Function

You’ll take this code and add it to the Code section of the Report properties. MSDN explains it pretty well for me.

How’s it work? Pretty sweet if you ask me. Starts by declaring “fixedString.” Then we go with the all important if statement. If it’s Null or blank then leave it blank. For everything else, replace ;# with a comma. But we don’t want commas at the start and end of our string so we gotta take care of those. The next two fixedString.substring statements take care of that.

When you’re ready to use the code on a particular column, right-click said column then choose Expression. From there you should see your column setup like this: =Fields!DogNames.Value. Add the following red text and you’re golden: =Code.ReplaceChar(Fields!DogNames.Value)