Technology | The White Pages

That title could actually be longer….

SQL Server 2012 brings with it a number of key Business Intelligence features that apply directly to SharePoint environments. One of the major improvements is the way that Reporting Services installs. Prior to version 2012, when running in SharePoint integrated mode, it installed along side of SharePoint, and connected through an add-in. With 2012, it is now a full SharePoint Service application, with all of the associated benefits that brings.

It is simple enough to set this up on a new farm, but what about organizations that are already using SSRS in integrated mode? Since I was unable to find any prescriptive guidance on the upgrade process, I ran through it on a test farm, and below are my findings. This describes the process of upgrading from SSRS 2008 R2 to the RTM version of SSRS 2012.

SQL Server 2012 has some relatively strict operating system requirements. First and foremost, you need to be running at least Windows 7 or Windows Server 2008 R2 Service Pack 1, or Windows Vista or Windows Server 2008 Service Pack 2. If not, you’ll get the following message immediately.

In addition, depending on what you’re upgrading, it’s pretty fussy about your source environment as well. For example, if you’re upgrading management tools or BIDS, and you already have Visual Studio 2010 installed, it will need to be at least at the Service Pack 1 level. Your source SQL Server also has specific service pack requirements. The complete supported upgrade matrix can be found here. Unfortunately, if these requirements are not met, the installation will fail much further along in the process, and you’ll need to repeat several steps after correcting.

Once the SQL Server Installation Center launches, you’ll want to pick the Installation tab, and then the Upgrade option.

After a few steps, you’ll encounter one of the new screens pertaining to Reporting Services.

Previously, the installer was totally unaware of Integrated Mode Reporting Services. You would use SSRS configuration to set it up, but now the upgrade wizard, as well as the full product installer, is fully aware of Integrated Mode.

When performing the upgrade, the installer will go ahead and create the SharePoint service application for you. This is different than when you perform a fresh install – in that case you manually create the service application after installation. However, in order to do so, it needs to create an application pool for the service application, and you will be prompted for the credentials of that pool.

After several more standard screens, the upgrade rules will be run. This is where you will find out if you are missing a prerequisite, or it is not at the required patch level. However, if all is good, all of the rules should show as Passed, with the exception of “Direct Browsing to Report Server”, which will show a warning.

Previously, if you knew the correct URL, you could navigate directly to Reporting Services and the reports stored within SharePoint through a very rudimentary interface. This warning is simply alerting you to the fact that this is no longer an option with 2012.

The remainder of the installation is straightforward. When done (and if successful), you can navigate to the Service Applications section of Central Administration. There, you should see the new SSRS application.

I’m not a big fan of the name that the default upgrade uses for the application, but that’s simple enough to change. The important thing to note is that all of your subscriptions, snapshots, etc, will have migrated over. The upgrade upgrades the two Reporting Services databases (ReportServer, and ReportServerTempDB by default), and adds a new one, ReportServer_Alerting, which are all used by the service application.

In order to enable data alerts and subscriptions, a number of security modifications need to be made to the SQL Server. In addition, the SQL Server Agent must be running to use these features. Editing the Service Application shows a screen that has a link to Provision Subscriptions an alerts. Clicking through it reveals the following screen:

The View Status section simply gives you an indication of whether or not the Agent is running on the server, but clicking the “Download Script” button will give you a SQL script that will set up the required roles and permissions on your SQL Server. This script must be run on the SQL Server that holds the Reporting Services databases. In order to run it, simply open up SQL Server Management Studio, connect to the server, and click the New Query button. Once the query window opens, paste the query in, and run it (the Execute button).

Finally, enter the credentials for SharePoint to connect to your SQL Server Agent, and click OK. Once done, you’ll be in a position to use the new features available in Reporting Services, and all of your existing investments in reports should continue to operate as before.

For a major architectural change, this is actually a pretty smooth transition.

As I post this, it’s quite early in the life of 2012, so I would be quite interested to hear of any other experiences or gotchas. If there’s something that I should add to this post, please post a comment, I’d love to hear about it.

If you’ve been working with the RC0 version of SQL Server 2012 (formerly code named “Denali”), you are likely aware that the final release is now available as an evaluation download here, or from MSDN with in the Standard, Business Intelligence, and Enterprise editions.

If you want to a straight upgrade from the evaluation edition, it’s a relatively simple process. From the installation screen, select the Upgrade from a previous version of SQL Server option (it doesn’t list RC0, but it is the same process).

Select all of the appropriate options, and your server will be upgrade. However, if you want to move from the RC0 to one of the licensed editions, you could run into trouble. In my testing, what has happened is that parts of the installation will upgrade properly,, but others will fail. This can be seen from the instances screen when upgrade is run for a second time.

Unfortunately, the “left behind” instance can not be upgraded further, and now that this has been run, the Edition upgrade function will fail to work. In one case, I needed to do a complete SQL uninstall and then a reinstall. I’ve had more fun. The good news is that the reinstall did work.

The way to avoid this is to do the Edition upgrade first. If you’re unfamiliar with this, you can find it on the Maintenance tab of the SQL Server installation Center

Once you complete the edition upgrade, go ahead and perform the standard upgrade as you would with the evaluation version.

March 14, 2012
Posted by John White at 9:02 pm
No Responses
SQL Server, Technology
Editions, SQL Server 2012, Upgrade

Using Reporting Services integrated with SharePoint has some tremendous advantages for report re-use. In many cases, a single report can be designed, and then placed on contextual pages for customized display by setting the report parameters through the Reporting Services web part. Filter web parts can also be used to drive the Reporting Services parameters, and finally, users can set the parameters directly if allowed.

However, what happens when we want users to be able to set some of the parameters, but not all of them? This can be achieved through a combination of filters and web part settings.

Consider the following report:

Our requirement is to place a Reporting Services web part on a page that will display the report for the currently logged in user. In addition, the user should be able to select the time period to display, but the default for the time period should be the current year. Finally, users should not be able to use this report to view the submissions of other employees.

We can accomplish this firstly by adding three parameters to the above report – Start Date, End Date, and Employee. We will then restrict the results returned to the report to those meeting our parameters. Our main dataset query is as follows:


SELECT  ExpenseTotal,Employee,ExpenseType,SubmittedDateFROM  ExpensesWHERE SubmittedDate BETWEEN @StartDate AND @EndDate 

   AND Employee= @Employee

Ideally, we could use SharePoint filters to set the value of all three parameters (details on this below…), and then completely hide the Reporting Services parameter pane from the users. Unfortunately, the date filter in SharePoint doesn’t quite meet our requirement.

We can explicitly set the default value, or make it a function of the current date, but what we can’t do is to set it to the first day of the current year, last day of the current year, etc. True, on Jan 1 we could go to the page and edit the parameters of the filter, but we’re looking for something a little more elegant than that.

We can get these sorts of values through SQL Queries, so what we’re going to do is to set the default values of the parameters with a Reporting Services Dataset. The dataset query is:

SELECT   DATENAME(Year,GETDATE())+'0101' as StartDate,   DATENAME(Year,GETDATE())+'1231' as EndDate

This basically retrieves the current year and then appends Jan 01, and Dec 31 respectively to return the values in YYYMMDD format. These values are then set as the defaults for the start and end date parameters respectively:

We will be setting the value for employee with a filter, so we don’t need to set its available or default values.

Once this is done, and the report is saved, we’re ready to add the report to a SharePoint page. To do this, simply navigate to the destination page and edit it, then insert a Reporting Services Web Part on the page. Open the web part tool pane, select the report, and set all of the View/Display options that you want to use. You will need to make sure that the prompt are is displayed, as you will need it for your users to change the date ranges. However, you can start it collapsed.

Finally, you’ll want to load up the report parameters section

You should not that all of the parameters need a value for the report to render – hence the “Enter A User” value, which will return no records. At this point, we can save the web part and test the form by entering an employee name in the Employee parameter field.

The next step is to add the Current User Filter Web Part to the page (in the Filters section of the web parts list), and configure it. It’s a context filter, and it won’t display to the end user, so you can put it anywhere on the page that you wish.

You can send either the user’s Active Directory account name, or the value of any of the User Profile fields (as an aside, this is one reason that the user profile is really important…). In our case, the back end data is in a “First Name Last Name” format, and this corresponds with the Name property of the User Profile, and is therefore what we’ll use.

Once configured, we save the web part, and then we need to connect it to the report. We do this by accessing the Web Part Edit Menu, hovering over Connections, and selecting “Send Filter Values To”

We then select the report, and the parameter to set:

Once connected, the report should be using the name of the currently logged in user, in our case, Olaf Franz.

In the above screen I have opened the parameters pane (I have it closed by default) to show that users can change the dates, but by navigating to this single page, a user will see their own entries for the current year. However the important thing to note here is that although “Employee” is a parameter, it no longer appears in the parameters pane. This is due to the fact that its value is being set by a SharePoint filter, and it has the added benefit of allowing us to prevent users from seeing other users’ reports.

This is a perfect example of where the personalization capabilities of SharePoint can be combined with the analytic power of Reporting Services for a result that’s truly greater than the sum of the parts.

February 10, 2012
Posted by John White at 12:31 pm
No Responses
SharePoint, SQL Server, Technology, Uncategorized
Filters, Parameters, Profiles, Reporting Services

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

February 9, 2012
Posted by John White at 2:51 pm
No Responses
Business Intelligence, Cloud Computing, Office 365, SharePoint, Technology
Business Intelligence, Lync, MVP, Office 365, SharePoint

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.