Using SharePoint Filters with Reporting Services Parameters for Personalized Reports

Feb 102012
 

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:

image

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.

image

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:
 
image
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.
 
image
 
Finally, you’ll want to load up the report parameters section
 
image

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.

image

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.

image

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”

image

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

​image

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

image

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.

Using SQL Server Report Builder with Internet Explorer 9

Feb 072012
 

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

image

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:

image

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.

image

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.

Upcoming Speaking Engagements – Spring 2012

Feb 012012
 

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

Upgrading and Moving Integrated Mode Reporting Services With SharePoint

Dec 192011
 

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. 

image

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

image

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.

image

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.

image

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

image

Complete the configuration wizard.

Step 5 – Connect SharePoint to Reporting Services

From SharePoint Central Administration, select General Application Settings, and then Reporting Services Integration.

image

Complete  the integration configuration, and then select OK

image

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.

image

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.

image

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.

How To Fix Broken Reporting Services Content Type Names

Dec 092011
 

Recently, 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.

image 

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.

image

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.

image

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.

image

You will then be able to edit the values for Name, Description, and Group.

image

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.

 Older Entries  Newer Entries