Tag: Profiles

Using SharePoint Filters with Reporting Services Parameters for Personalized Reports

Published February 10, 2012

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,SubmittedDate
FROM
Expenses
WHERE 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.

2 Comments

What Domain Accounts to Use For SharePoint 2010

Published August 6, 2010

When setting up a SharePoint farm, whether 2007 or 2010, you have the option of providing various service identities throughout the process. Indeed, every application can run with its own identity. Far too often, administrators pick a single account, and use it for everything. While this is certainly the easiest approach, it is far from the most secure, and it can be very limiting down the road if you need to get granular with your permissions. The trouble is that there are a lot of intricacies as to what account does what, and getting it right requires a pretty comprehensive understanding of the product.

We now have enough work under our belt with SharePoint 2010 that I feel comfortable sharing some of our best practices around account creation for SharePoint 2010. The product itself has gotten more complex, and so therefore have the configuration options. There is no “one size fits all” approach for all scenarios, but the list that I am providing below should work as a good starting point. There is often a trade-off between the ease of manageability and providing good security, and the approach below,I feel,find a good balance.

The chart below describes the account, its purpose, what rights it needs to the local machines in the farm (including the SQL server machine(s), the rights it needs for SQL Server directly, and the rights it needs to the Active Directory domain.

Base Set of SharePoint 2010 Service Accounts

Account	Purpose	Local Rights	SQL Rights	Domain Rights
spSetup	Used to login to the farm servers Used to install bits on the farm servers	Administrator Remote Desktop Login	DB Creator Security Admin	Member
spFarm	Identity for all Windows Services Identity for all SQL Services (optional) Identity for Profile Synchronization Service Identity for all code running with elevated permissions (web parts)	None ⁽¹⁾⁽³⁾	DB Creator Security Admin	Member
spApps	Identity for all SP Application App Pools ⁽⁴⁾	None	None	Member
spServices	Identity for all SP Service Applications⁽⁴⁾	None	None	Member
spUPS	Identity for the User Profile Service	None	None	Member Replicating Directory Changes⁽²⁾
spCrawl	Used by the Indexer to crawl content	None	None	Member⁽⁵⁾
spBI	Trusted account for Reporting Services and PerformancePoint when not using Kerberos	None	DB Access as appropriate	Member
spSuperUser	Used for Object Caching	None	None	Member
spSuperReader	Used for Object Caching	None	None	Member

(1) Needs to be a part of the Local Administrators group while the User profile service is being created. See my previous post for more details. Once created, this account can be removed.

(2) AD Permission required by the User Profile service

(3) Required for a specific AD container when using the incoming email service. See this post for details on how.

(4) There may be a large number of these, one per entity

(5) Appropriate rights will need to be granted to this account for any EXTERNAL content being crawled (file system, shared folder, Lotus Notes, etc)

Hopefully this will help a few of you get started with a little less head scratching.

Configuring Profile Import in SharePoint 2010 – A Way Around the Minefields

Published May 2, 2010

Author’s Note – July 17 2012. When I originally wrote this in early 2010 it was based on some (at the time) sketchy Technet documentation and experience. It was meant to be an easy to understand guide to setting up the User Profile Service. I want to point out that there is a much more comprehensive guide out there on the topic, the Rational Guide to the User Profile Service by Spencer Harbar. It’s the reference I use when I get into trouble, and if this article doesn’t do it for you, I recommend going there.

Profile synchronization has changed drastically in SharePoint 2010 compared to 2007. The 2010 profile synchronization uses the Forefront Identity Management services. There are a lot of good things about this, one of which is that it provides for bi-directional synchronization. User changes to their profile store can be synchronized back to their identity store (Active directory, etc). This of course can be tightly controlled, on a field by field basis. There’s a great post on how to do so here.

Of course, all of this added power is not without its cost in complexity. I had a great deal of trouble even getting profile imports to work at all with some of the pre-release builds, and the final release is still a little rough around the edges. There is a Technet document available here that details precisely how to configure profile imports. It’s completely accurate, but doesn’t necessarily answer all of your questions. This post is my attempt to help guide around the worst of the thorns, and get it working with Active Directory.

First, you’ll need to have a Profile application running. If you’ve done a migration, you already do. If you’ve run the setup wizard, and selected user profile application, you also already have one. Synchronization will however not be happening,even if you’ve done a migration. It will need to be configured.

If you don’t already have a Profile Service application,you’ll need to create one. You do that from the Manage Service Applications screen and choose the New button in the upper left. You’ll want to select “User Profile Service Application”.

There is a lot of configuration here, make sure that you scroll to the bottom and fill out all of the relevant options. You’ll be choosing databases for social tagging, a database for storing user profiles, a database for storing user tags, and the URL of your MySite Host. If you haven’t already created a MySite site collection, the configuration screen here will allow you to do so. Once you have successfully created the application, you may then proceed to starting the service. This of course is in a completely different are where the services on the server are controlled. Once there, start the User Profile Synchronization Service.

When you start most of the services, they either start immediately, or give you a configuration screen and start quickly thereafter. Clicking this one gives you the configuration screen where you’re prompted to associate the service with your application from above, and the credentials that the two Windows Service accounts will run with. However once completed, you’ll notice that the service is in a “starting state”. This is normally a bad thing with SharePoint, and indicates that something is hung. Not so in this case. It takes a very long time for this service to start. When it does, you should see the following two services started:

DO NOT attempt to start these services manually, and that will confuse the system in a very big way. Just have patience, and all should be well. You should now be ready to go ahead and perform an import. However, there’s a very import step that likely needs to be performed. The service account that was specified above to run the synchronization service (the one that the two forefront services are running as) need to be granted the “replicating directory changes” permission. There is another Technet article on how to do this, so I won’t go into detail on how.

If you do not perform this step, your import will fail, and you’ll have very little idea as to why. The error message is far from clear. Below I’ll talk a little about how you can troubleshoot import issues.

The next step is to set up the import itself. To do this, open up or manage your Profile service application, and select “Configure Synchronization Connections”

Once here, you either edit your existing connection(s), or create a new one. The options for import source are greatly increased from 2007 and now include Active Directory, Active Directory Resource (ADAM), Active Directory Logon Data, BDC (they’ve forgotten to rename it to BCS), IBM Tivoli, Novell eDirectory, or Sun Java System Directory Server. Another improvement in this version is that the import can now use Forms authentication credentials, but can also make use of Claims based authentication if available.

Give the connection a name, select an authentication type, and any appropriate credentials. Once you have done this, press the “Populate Containers” button. If everything is OK, you should see your import source appear below:

This dialog is NOT particularly responsive, be prepared for long waits between mouse clicks. The beauty of it is that you can now very easily cherry pick which containers, and even users get imported very easily. This is not something that was straightforward in 2007. Once completed, select OK, and your connection should be created. Note – subsequent edits of the connection will not retain the credential information. This is normal.

At this point you are ready to perform your firs import. Return to the Profile Service Application main page, and in the Synchronization section, select “Start Profile Synchronization”. You then have the option to choose full or incremental sync. Once selected, synchronization runs, and you can keep track of its status on the main profile application screen:

The import job will take a very long time, compared to import jobs in 2007. The good news is that the status messages are relatively verbose. If however, you feel that the job is taking too long, and you feel that there is a problem and can’t locate it, an excellent tool to use is the Forefront Identity Synchronization Manager Client UI. THis will be installed on your server at:

<install Drive>:Program FilesMicrosoft Office Servers14.0Synchronization ServiceUIShell and the application to run is miisclient.exe. This application is installed by default, but there is no entry in the start menu. Running this file will give you a screen that looks like the following:

This screen shows no problems, but if there are, they will be displayed for each step in a great amount of detail. I’ve used it to troubleshoot a few connection issues already.

In conclusion, the new profile synchronization system in 2010 has quite a few more moving parts, is a little rough around the edges (at the moment), and can be a bit of a bear to get going. However, it’s new capabilities make it well worth the effort, and lay the groundwork for what I can see to be some great new features down the road.

11 Comments