Last November, I was interviewed by Sr. Technical Evangelist John Weston on the MVP program, Office 365, Cloud Computing, Business Intelligence, and how these things all tie together. The entire interview was conducted online using Lync online, available in Office365. It’s now online, and can be seen below.
You can see other Technet Radio episodes by visiting the Edge site here
Category: Business Intelligence
One of the unsung heroes of the Microsoft Business Intelligence stack is Report Builder. Report Designer has been part of Business Intelligence Development Studio (BIDS) for quite some time, but BIDS is more of a designer tool. In order to get report design into the hands of power users, Microsoft provided Report Builder initially with a reduced set of functionality SQL Server 2005, but with Report Builder 3.0 which ships with SQL Server 2008 R2, it’s just as capable as BIDS. When running in SharePoint Integrated mode, you can design reports as if they were any other type of Office document.
Unfortunately, if you edit a report, you may be greeted with the message: “To use Report Builder, you must install .Net Framework 3.5 on this computer.”
Report Builder does require .Net Framework 3.5, but you’ll see this message even when you have it installed. Clicking on the “Install .Net 3.5” will reinstall it, but won’t help.
This only happens when using Internet Explorer 9, and is due to the fact that it doesn’t correctly detect the Framework’s presence. This doesn’t happen with any other browser, including previous versions of IE. It can be worked around by setting the browser’s compatibility mode.
To do that, either press the F12 key, or turn on developer tools from IE’s Tools menu:
Doing so will bring up the developer tools window, where you can set the broswer mode. Setting it to anything other than IE9 will work.
Just the act of setting it should start the Report Builder download process. The setting will persist for the life of the browser window, so you’ll need to do it again the next time that you edit the report.
I’d love to hear of any better fixes to the problem, but for now, this lets you get the job done.
2 CommentsWhile the blog has been quieter than usual for the past couple of months, owing to a hectic schedule, I do have a number of speaking engagements coming up that I wanted to promote. All of them are related to SharePoint Business Intelligence in one way or another, and all are in the Toronto/South Western Ontario area. If you’d like to come out and talk SharePoint, Business Intelligence, or just indulge in a SharePint or two, I would love to see you there.
| Hamilton SharePoint User Group |
| Thursday, Feb 16 2012 |
| SQL Server Reporting Services with SharePoint SQL Server Reporting Services provides a rich reporting environment, and integration with SharePoint makes it seem seamless to end users. How does Reporting Services impact the SharePoint environment? This presentation will walk through the basic features of Reporting Services, and architectural considerations when installing in a SharePoint farm. In addition, some of the differences included in the upcoming SQL Server 2012 version of Reporting Services will be discussed. |
| Toronto SharePoint User Group |
| Wednesday, April 18, 2012 |
| What’s new in SQL Server 2012 for SharePoint SQL Server 2012 brings a wealth of new features to the core database used by SharePoint. However, it also brings a number of exciting new Business Intelligence features right to your SharePoint users. This session will walk through a number of the new features that have a direct impact on SharePoint administrators, designers and end users, with in depth demonstrations of how to configure and use them. These features include significant architectural changes to Reporting services, the new BISM or tabular engine for Analysis Services and PowerPivot, and the new end user focused reporting tool, PowerView. |
| SharePoint Summit |
| May 15, 2012 |
| Reporting for Duty – Best Practices for Reporting Services with SharePoint
Reporting Services and SharePoint have been working together since SharePoint 2003. SQL Server 2005 SP1 brought the ability to use Reporting Services in Integrated Mode through a SharePoint Add in, and with SQL Server 2012, Reporting Services is a fully fledged SharePoint Service Application, and some features, the new PowerView in particular, are only available through SharePoint Integrated Mode. Reporting Services bring a wealth of benefits to your SharePoint farm, but can also have a significant impact on it. This session will discuss the do’s and don’ts for a successful Reporting Services implementation. It will cover architectural considerations through to Report design, for both the Reporting Services Add In (SQL Server 2005-2008) and the new Reporting Services Service Application (SQL Server 2012). |
There is a plethora of instructions out there on upgrading from SharePoint 2007 to SharePoint 2010, but relatively little on doing the upgrade where Reporting Services has been set up in SharePoint integrated mode. Given that there are a few gotchas that you can run into when doing this, I decided to put together this step-by-step, complete with the gotchas.
The most common scenario that will be encountered, given the vintages of the products will be an RS upgrade from SQL Server 2005 to 2008 R2. In addition, the in place upgrade is relatively painless (in the short term….) so I’ll be walking through a DB attach upgrade, which is just as applicable to RS as it is to SharePoint. Finally as I’ve written about previously, in a small farm, it’s likely a better idea to add the Reporting Services bits to a SharePoint front end server, than to add the SQL server to the SharePoint farm, and that will also be a part of our scenario.
The first question to answer is “why bother”? One of the advantages to using RS in SharePoint Integrated mode is that unlike Native mode, the reports, data connections and models are stored directly in SharePoint. It is therefore possible to just create a new RS database, and move forward. However, since subscriptions, schedules, and cache profiles are still in the database, it’s likely worth it to do the upgrade.
Step 1 – Back Up The Asymmetric Key
Reporting Services itself uses 2 SQL databases. One of the databases is for temporary operations, but the other database stores a number of important, and sensitive items for this reason, all sensitive items in the database are encrypted with a key. If we want to get access to these items, we need the key. To do so, we need to back it up from the source server before we move ahead.
Run the Reporting Services Configuration Manager on the source RS server, and select “Encryption Keys”. Click the Backup button, select (and remember) a password, and then save the key to the file system.
Once saved, copy the key file to the destination RS server (likely your SharePoint 2010 front end server).
Step 2 – Back Up the Reporting Services Databases
Run SQL Server Management Studio on the Server where the Reporting Services databases are located. Run full backups of the two RS databases. When complete, copy the backups to the destination SQL server (likely the server that will host the SharePoint 2010 databases).
Step 3 – Restore the Reporting Services Databases
Using SSMS, restore the two databases to the host SQL server. Once restored, it’s likely a good idea to set the recovery model to Simple, and the Compatibility level to SQL Server 2008. These steps aren’t required, but are recommended, unless you have a reason for not doing so.
Step 4 – Run the RS Configuration
If Reporting Services hasn’t yet been installed on the SharePoint server, do so, otherwise, proceed to configuration by running the Reporting Services Configuration Wizard on the destination RS Server. Configure the basic steps, and then when it comes to Database configuration, select the option to choose an existing database.
Select the server where you restored the files in step 3, and select the primary RS database (the one without the word “Temp” in it”).
Complete the configuration wizard.
Step 5 – Connect SharePoint to Reporting Services
From SharePoint Central Administration, select General Application Settings, and then Reporting Services Integration.
Complete the integration configuration, and then select OK
So far so good – now we’re ready for some gotchas. If you now click on the “Set Server Defaults” link in the Reporting Services section, you likely get a rather nasty looking error. You’ll also experience this error if you access ewither of the two RS URLs defined in the RS configuration wizard. The error is:
The report server installation is not initialized. (rsReportServerNotActivated)
This error happens when the server can’t access configuration information, and the most common cause of that is that it can’t decrypt the content. In our case, it can’t because we haven’t yet restored our key.
Step 6 – Restore the Asymmetric Key
On our new RS server, we need to run the RS Configuration Manager, Select Encryption Keys, and then click the Restore button. You will be prompted for the file that you created and copied in Step 1, and this is where remembering the password comes in very handy.
Once this is done, we can close the configuration manager and return to Central Administration. However, now when we try to access access any aspect of RS we get a new error:
The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
The reason for this error is that when we restored the key, it added an entry in the Keys table in the Reporting Services database, causing RS to think that we’re using multiple Reporting Services servers. This is what’s known as a scale-out deployment, and is only supported in the Enterprise version of Reporting Services. Obviously this isn’t a problem for anyone running Enterprise, but if not, it’s a showstopper.
The way to fix this is to remove the old server entry in the Keys table. Using SQL Server Management Studio, connect to the Reporting Services database, and open the dbo.Keys table. The old entry should be easy to spot as it will have the old server name. Simple delete the row.
Once the offending entry is deleted, RS should be good to go.
7. Fix up the content type names
I have posted about this already, but often, an upgrade will break the Content Type names for the Reporting Services content types. Just follow the steps in this post to clean them up.
8. Reconnect Reports with Data Sources And/Or Republish
In addition, moving connection files and reports around in SharePoint can cause them to be disconnected from each other, or for the connection files to be disabled. It’s a good idea to navigate to all of your reports to make sure that they are connected, or better yet, to republish from the source if you had previously used BIDS to publish reports.
Leave a CommentRecently, after helping to upgrade a customer from SharePoint 2007 to 2010, we noticed some oddities around the Reporting Services content types. The RS content types are deployed into a farm when the RS add-in is installed. The observed problem was that the names of the content types, and the content type group was all messed up.
The names displayed were the internal names that are used to support multilingual installations. What I believe caused this problem was the the content database had been upgraded to 2010 prior to the add in being installed on the farm, so the English names could not be resolved.
All existing items using the types continued to work, and reports could still be deployed using Business Intelligence Development Studio (BIDS). However some UI dependent functions, like Report Builder could not be used without the proper names. Luckily, the fix is relatively straightforward. All that we need to do is to set the correct names manually. To get the correct farm, all that is necessary is to inspect an unaffected farm to see the section below.
In order to fix this, you need to first navigate to the root of your site collection, and then select Site Actions – Site Settings, and then click on Site content types in the Galleries section.
At this point, you should see the affected section, it will be right at the top. We need to fix the content types one at a time, so click on one of the affected content types. The Content type editor window will appear. From that Window, simply select Name, description, and group.
You will then be able to edit the values for Name, Description, and Group.
For the first one that you edit, you’ll need to select the New Group option, and enter the correct name of the group, as indicated above. For the other two content types, you’ll be able to select the correct group from the existing group list.
Once you’ve edited all three content types, that’s it! You’re done.
Well, sort of…
Unfortunately the name change will only affect new libraries , or newly configured libraries. For libraries that already have these content types attached to them, you’ll need to repeat the process for each library that has been affected. I imagine that it’s quite possible to write a PowerShell script that will do this for all libraries, but so far, the remediation effort hasn’t warranted it.
Leave a Comment