InfoPath 2010 - Restricting control visibility based on permissions.

Introduction

One of the common functional requirements of many InfoPath based systems is to restrict button and other control elements and associated functionality available to specific groups based on group membership. There are a number of technical approaches to this, including:


http://info.akgroup.com/blog-0/bid/69277/InfoPath-Restrict-visibility-to-users-in-a-SharePoint-Group

There is however a simple way based on a list external to the list the form is bound to, and setting the permissions that way. In this example, we look at a simple Save, Submit button configuration, but this approach could be expanded to many form elements on an InfoPath form.

Our requirements are:

1. User group one can only view “Save” button.
2. Users in group two can only view “Submit” button.

Creating the button list and setting up permissions

The first step in setting up this functionality is to create the button list. Create a list on the same site as your InfoPath form and associated list called “Buttons”, and change the column “title” to “Button Name”. Add the two buttons as items, one as “Save” and one as “Submit”.

Next, create two groups on your site, one called “CanSave” and one called “CanSubmit”. Add all users who should be able to save the form to the “CanSave” group. Add all users who should be able to submit the form to the “CanSubmit” group.

Go back to the button list you created, and edit the permissions for the two button items. The “Save” button should have the “CanSave” group associated, the “Submit” button should have the “CanSubmit” group associated.

Once complete these two steps, validate the permissions are working as expected. Log in as a user in the “CanSave” group (but not the “CanSubmit” group) and view the button list. You should only be able to see the “Save” button item in the button list. Login as a user in the “CanSubmit” group and ensure you can only see the “Submit” button.

Adding the buttons to the form and setting up rules

This step assumes you have otherwise already created a form and have it bound through its main data connection to a list or library. This step also assumes that you know how to configure both save and submit buttons otherwise for intended actions; the only functionality covered by this step is configuring the rules to show or hide buttons based on values in the external button list.

Click on the “Data” tab on your form in InfoPath, and add a new data connection to the SharePoint button list you created earlier. Go back to home tab, and add two buttons to the form. Right click on each, name one “Save”, and one “Submit”.

Open the “Rules” pane if it isn’t open already, “Manage Rules” button on office ribbon on “Home” tab. Click the “Save” button you just created, and, in the Rules pane, click “New” and select “formatting”. Name your rule “Hide Save". Click inside the “Condition” section and set up the condition as follows:

1. In the first drop down list, select “select a field or group”.
2. Change “Data Source” to “Buttons”.
3. Open the “dataFields” folder, open the connection, and select “ButtonName”. “Title” will show up in the “Select” field due to SharePoint limitations around changing the title column. This is fine, click “OK.

4. Since SharePoint InfoPath rules default to show, and are only conditional on hide, we have to isolate the hide condition rather than the show condition. Select “is not equal to” in the second rule area, and then select “Type Text” from the drop down list, and type in the value that this button should NOT be equal to to hide the value, as shown in the next figure. Click “OK”.

5. Within the rules pane, check “hide this control” under the “Formatting” section:

6. Repeat this process for each of the buttons on your form.
7. Publish your form to a SharePoint site or form library.

Troubleshooting and Conclusion

Once you have published your form, you should be able to login as a member of each group and open the form. Only the buttons items you have permissions to in the associated list should show up in the form. If all buttons show up (instead of obeying permissions), ensure the permissions are correct in the associated button list, you should only be able to view the items in the button list that your associated group has permissions to:

If this checks out, then the problem is isolated to the rules in the InfoPath form, tweak and user “Preview” until it’s working. There are some complex ways of achieving this functionality through web services and other approaches, as seen in the blog post first linked to from this blog. However, it can be much simpler, faster, and easier to user SharePoint list permissions to achieve the same functionality in an easy to maintain way.

The SharePoint Governance Challenge

Introduction

This is a scenario I run into frequently as part of my professional life.  An organization decides to purchase SharePoint, and make it available to their user base throughout the organization.  The software is purchased, IT installs the software and ensures it is running, various managers and units within the organization are provided sites for their usage, and they are given “site owner” permissions in order to allow them to select and configure the tools.

Other than keeping the software running, no official SharePoint support is given, and the sites and tools developed using the SharePoint toolset grow in a completely organic way, with some users embracing the technology and creating multiple sites and process management areas, the majority of users focussing on common and familiar tools, such as document repositories, and some sites and areas remain dormant, not used at all.  The initial success of the installation is high as end users feel empowered to create the sites and tools they need for their specific purposes.

When some organizations first install SharePoint, they view its management as similar to other installed applications, such as Microsoft Office, an Account Tracking system, a Case Management System, or a Library system.  Where the responsibility of IT or the server admin with the SharePoint installation is to deploy it and keep it functioning.  They then struggle to keep control of requests, cannot keep up with end user questions around custom configuration, they are in a consistent reactive mode as opposed to pre-planning, having processes, responsibilities and plans in place to handle the needs of the end users and organization.

As there is no overall guidance or structure to how the tools should be used, and no processes put in place to restrict the ability of end users, toolset choice, sites, navigation, and usage is highly dependent on the individual site owners and their teams.  This leads to inconsistencies across the SharePoint installation as each independent area is entirely self-directed.

Users begin to express frustration, inconsistency in navigation between sites leads to trouble finding important information, misunderstanding of document libraries and how they function lead to, at best, unreliable search results, and, at worst, exposed private documents to areas of the organization that should not have access.  Users, misunderstanding security, break permissions for their sites to create their own groups, but do not have the knowledge nor the planning to ensure the groups accurately control permissions or the processes in place to ensure those groups stay current as staff move or change responsibilities.

The very thing that made SharePoint a strong toolset choice in the first place, the ability of the end user to self-manage and self-create their own process support tools within the sites they have control over, (no need to involve IT in selecting and configuring tools) becomes part of the problem.   IT staff do not have availability for full scale SharePoint support since it wasn’t planned for, end users on their own develop inconsistent, and, in some cases partially non-functional, toolsets and sites to support their processes.

How do we counter this?  How do we allow our organization to fully leverage SharePoint and see some of the great efficiencies that can result from managing various processes from start to end within a single unified platform?  How do we build on the local successes achieved by site owners in supporting their own business processes within SharePoint?  The answer lies in Governance.

What is SharePoint?

Before we look at Governance, we first need to understand what SharePoint is.

The majority of users I encounter that express frustration with SharePoint end up blaming the platform for what was built with it.  SharePoint is a very sophisticated set of business support tools that allow extensive custom configuration to your own purposes.  It is also “just” that.  It is a toolkit.  It’s not the end product. The success or failure of a supported business process, a department’s site, an entire organizations SharePoint installation, is based on what you are able to build with it, rather than the native abilities within the toolset itself.

As SharePoint’s true value is in what you can build with it; this will require expertise, planning and experience to leverage.  You can have the finest tools in place to build whatever you need, but without the processes in place to manage that building, without the expertise to create those solutions, you end up with a sub-standard product.  For a successful long term strategy with SharePoint, you not only need the product, you need a strategy for its organizational usage and the expertise in place to support it.  You need governance.

Governance

Governance, at its highest level, is putting in processes and support systems that support the development, configuration and expansion of SharePoint throughout your organization.  The governance plan you have can differ greatly from other organizations in its specifics, but in general, all organizations undergoing governance are looking to take SharePoint beyond the random abilities of individual site owners, and the lack of coordination between sites, to a coordinated and planned installation that allows users quick and easy access to the information and tools they need.

Governance anticipates the needs of the organization, and customizes the SharePoint platform to support those needs, focussing resources, planning, and training on those areas identified as high priority.

Governance goes beyond delivering individual solutions and looks at the potential benefits of SharePoint organization wide.

As an example, some of the early benefits my clients see from SharePoint come from InfoPath and Workflow based systems, such as Vacation Request or Expense Claim form systems.  A company with an eye for governance will not look at these systems as individuals, but as a small portion of a potentially much larger system.  They understand that once they develop a single InfoPath system, they can save templates, processes, interfaces and approaches from that system to apply to another, to not only speed up development of new solutions, but also allow, through the use of a universal approach, organization wide ability to maintain those solutions as the approach is researched, known, documented and universal.  Governing the development of these systems allows this to occur.

This counters the challenge of many SharePoint installations where an individual approach of site owners, usually due to lack of guidance, may lead to customizations or other configuration that is specific to that site only, and therefore hard to maintain, or hard for users unfamiliar with the site to use.

A high level approach goes beyond unifying the way individual systems are developed and implemented however.  It also allows the development of solutions that touch many areas of your enterprise, such as an employee on-boarding/off boarding process, where the entire process, from the original job posting and description, through resume management and review, interview, hiring decision, involved groups informed of new employee (Security, IT for new accounts/groups, Library, Training, Human Resources) through to the end of the employees hire is managed in SharePoint.  Each stage is fully track-able; each party responsible for each element of the hiring process is automatically informed.  Overall system management is done using dashboards that allow site owners to monitor and maintain the system without involving IT. 

This multi-departmental collaboration on a business process simply doesn’t occur with organic or unmanaged growth.  And it is one area where the improvements in efficiency that are gained through SharePoint are significant, over 50% less time than managing the same process than using the previous approach. An efficient system can end up saving the equivalent of multiple full time individual salaries in measured time savings.

Governance provides a high level universal approach to your SharePoint installation so that navigation, document handling, security, form and workflow systems, reporting, and multiple other areas all use a standard organization wide approach.

This universal approach allows users familiar with one area of your SharePoint installation to easily navigate and interact with unfamiliar areas; it simplifies maintenance and ensures security is being correctly applied, so that information integrity is not compromised.  A universal approach to Form based systems means that once users become familiar with the usage and maintenance of one system, they can easily use newly developed systems based on the same templates, as process and interface are similar.  And users familiar with the maintenance of one system will understand and more easily be able to maintain others.  No more isolated systems that only one specialized user can maintain.

Governance allows SharePoint to move beyond disparate approaches, tough variable navigation, unreliable searches and individual approaches to an optimized experience where the end user spends less time attempting to learn the technology and find information to more time using it to increase the efficiency and track ability of the processes they are responsible for.

Move beyond organic SharePoint growth, with only specific local benefits to an optimized environment where the focus is on organization wide business process support improvement.  Truly benefit from the many advantages the SharePoint platform can provide and see real and measurable improvements in the efficiency of the supported business processes.

Effective governance, once implemented, allows you to focus on your business, the needs of your organization, rather than the technology required to track and manage that information.

There are three key elements to any effective governance strategy.  Without any of these elements, the overall approach will not succeed, and you will only see partial success over organic growth.  Let’s look at each of these three elements in detail.

 Full Time Long Term Farm Administrator

The first key to effectively managing a SharePoint installation in an organization of 200 or more regular users is the hiring or training of a full time SharePoint farm administrator.  Unlike many other commercially installed applications, SharePoint requires the constant monitoring, request gatekeeping, and analysis necessary to respond to the needs of the user base.  This means monitoring security, site usage and reporting to ensure they are set up to organizational standards and do not expose information to groups that should not have access.  It means assessing and optimizing the search experience, ensuring that end users can easily find the information they are looking for.  They ensure custom developed applications are safe and have been tested using known processes before deployment to production.  They deploy and monitor these custom developed applications and ensure they continue to function after patches and other upgrades to the overall system.  And they create and apply permissions to new site areas before allowing individual users to customize those sites to their needs.

This is just a small portion of the potential tasks a SharePoint administrator may have, the specifics are highly dependent on each organization.  But regardless of individual organizational needs, the scope and complexity of any SharePoint installation supporting a reasonably large user base (200+) is a full time commitment.

A long term commitment to a farm administrator is also essential, as aligning your SharePoint platform and toolset with your specific organizational needs is a long term project that continually evolves as it goes, and really never ends.  The amount of time needed to take an experienced farm administrator and get them familiar with the intricacies of your institutional processes, responsibilities, and other integrating technologies is considerable.  And the SharePoint farm needs to constantly be in a state of assessment and adaptation to keep up with changing needs.  A 3 month engagement from an external resource is simply not sufficient.

A full time, internal dedicated SharePoint farm administrator is the first key in effective governance of any medium sized or larger SharePoint installation.

High Level Long Term Planning

Far before templates can be determined, navigation schemes developed, and tools configured, high level analysis of the current organizations needs has to occur.  This analysis can be phased, where a specific area of need is focussed on and developed before another or, a coordinated approach where a number of areas are developed in conjunction with each other.

This includes looking at current systems, and identifying those that transfer well to the SharePoint technology.  And not just translating those systems to a newer technology, but also doing an assessment of the current system, what is working well.  What isn’t?  There is not nearly as much value in using SharePoint to simply replicate current systems as there is in being prepared to optimize and change a process for the better, allowing simpler interface and workflow, enabled through SharePoint.  This holds true for sites to be migrated from prior versions as well.  The assessment needs to include experts in both the current systems usage and ability (most likely internal employees) as well as experts in SharePoint technology, such as Architects and SharePoint Specialists in other areas (Branding, Records Management, Business Intelligence, Migrations, Custom code based development, etc.). 

While internal resources can be used to describe current systems and some of the challenges they include, most likely, an external partner will need to be hired for governance planning and execution support.  This partner can assist in the planning and execution of a governance model, including setting realistic milestones and coordinating the various components of the plan.

Even if you plan to approach governance on a large scale and to roll out multiple solution elements simultaneously, such as branding using master pages, form and workflow systems, and document management within a short period of time, I would still look for a long term commitment from your SharePoint partner, definitely nothing less than a year, as governance is going to have to necessarily evolve and focus on unanticipated areas as usage increases and the real value of SharePoint starts to be realized.  It also takes a good deal of time and effort to understand your organizations specific needs and how they can be supported by SharePoint, you want a long term commitment from supporting partners so that the business knowledge they develop can continue to be leveraged over time without having to re-teach new experts your system and approaches.

A long term partner in developing and maintaining strong governance is necessary in any organization that does not wish to make significant investment in currently scarce individual expert SharePoint resources.   A company that can provide this support long term also has the ability to provide expertise as it is needed, a SharePoint Records Management expert is valuable indeed, but is only required in certain phases of governance planning and execution.  The overall Architect should remain as consistent as possible throughout governance planning and execution, other experts should be brought in as needed by your SharePoint partner.

High level long term planning with an expert partner will result in a long term maintainable SharePoint strategy that can adapt to user needs as necessary, and prevent and counter many of the challenges described with organic growth.

Training

Another area that is consistently overlooked when doing long term governance planning is the training of the end users of the system, as well as site administrators and anyone else involved in the creation and upkeep of custom developed sites and solutions.

SharePoint is fairly unique in that it allows end users to develop and evolve reasonably complete business support solutions without involvement of IT staff.  This means the technical resource availability issue that occurs with all other types of custom solution development (.net, java, open source) does not occur with SharePoint, as the end users are enabled to do so much more.

However, as with any set of tools, training in how to use those tools and develop technical support for your environment is essential.  You may have a highly experienced farm administrator at the helm of your SharePoint farm, a well designed and implemented governance strategy, but if the end users are unable to use the templates, processes and tools you have put in place to support them, the effort is wasted.

Training should not be the “out of the box” generic training provided by multiple SharePoint training providers, it should be focussed on your specific needs and environment.  A training partner should be willing to look into and understand your governance plans in detail, and provide expert recommendations on what and how to train end users and site owners in the technology.  They should be willing to adapt and modify the materials being presented to your environment, and train your users on site using your custom configuration.

Training is a key element of governance strategy as it enables the execution of all of that careful planning and strategy you have already engaged in.  SharePoint is all about empowering the end users to develop their own sites and toolsets.  Having them understand in detail both the technology they are working with and their roles and responsibilities within the governance plan are critical to successful long term SharePoint use.

Third Party Tools and Governance

I am not going to go into real detail here about third party tools, except to say that there are some specific third party tools that can assist in the technical components of the governance process, tools that can perform tasks across sites, site collections, and farms considerably more efficiently than the toolset that comes standard with SharePoint, such as SharePoint Central Administration and the SharePoint client itself.

These tools definitely don’t replace expertise, but they can increase the efficiency of farm administrators and other experts on governance tasks, in some cases significantly.  I will be reviewing one such tool later in this blog.

The larger the installation, the more value such toolsets may have.  I leave it up to my clients to determine if the final cost of a third party tool represents good value to them by clearly defining the benefits that tool can provide, and letting them make the final determination.

Conclusion

SharePoint is a powerful and highly adaptable portal toolset that can allow the simple creation and maintenance of business support solutions by the users who are closest to those processes, the end users.  While this will occur after initial deployment and during organic growth, it quickly runs into technical, expertise and planning barriers as its usage grows.

Truly leveraging SharePoint’s toolset and seeing the efficiencies it can provide enterprise wide requires expertise, planning, resources and training.  A governance plan can coordinate and develop these necessary elements to allow successful enterprise wide adoption of SharePoint, and a long term commitment from a partner, in coordination with your farm administrator, along with a well-planned training effort, will result in long term success in all of your SharePoint developed and hosted systems.

InfoPath 2010 - Using the User Profile Service with Claims Based Authentication

Introduction

One usage for the User Profile Service in SharePoint is to inform InfoPath, it allows you to pass the current users identity and retrieve profile information, such as manager, department, and position.  If you use Classic Mode Authentication (as opposed to Claims Based Authentication), then following the process outlined here:

http://claytoncobb.wordpress.com/2009/06/21/userprofileservice-extended/

will allow you to use that service to retrieve the information.

However, if you migrate the form to an environment that uses Claims Based Authentication, any attempt to use the User Profile Service on a web enabled template will result in a 5566 error message, and an error message in the SharePoint logs that indicates an unauthorized user, most likely with a user account that includes strange characters such as "i:0#.w|[domain]\[user]".

This issue does not occur if you use an InfoPath Filler based template, so if that is an option in your environment, then you have no further modifications necessary.  If however, you are in an environment where the web enabled template is required, such as environment where not all client computers will have InfoPath Form Filler 2010, then you will have to modify the way your form queries the User Profile Service to correctly pass the current users information.

This post covers those modifications as well as confirming IIS and Central Admin configuration needed to allow the service to authenticate correctly.

This post specifically covers Standard Windows Claims based authentication, and discusses working with Forms Authenticated user claims. It does not cover any other claims formats. If a different claims format returns the appropriate credentials for the user profile service, it may also be possible to use that format as well???

Check Central Admin

A quick check of the Central Administration console confirms that the process outlined in this post is the one you need.

In SharePoint Central Administration, Application Management Section, click Manage Web Applications.

Click the name of your main site web application (or the one hosting your InfoPath form that you are attempting to configure if you have more than one web application):



And select "Authentication Providers".  If you see:

Then the procedure outlined in this post applies.

Ensure IIS Authentication is Correctly Configured

Another common barrier to using the UPS in CBA is the misconfiguration of IIS authentication.  Anonymous access settings that have no effect on UPS in Classic Mode Authentication will prevent authentication in a Claims Based environment. 



Ensure that anonymous and impersonation authentication are disabled, and that only the authentication methods used on your web server for this site are those used on your site, in this case Forms and Windows Authentication:

Determine Claims Based Token Format

Before we can modify the form, we first want to find out what the format of the value returned by the username() function is in InfoPath.  Create a new blank form in InfoPath, add a single text box, and populate that textbox with the username() function.  Ensure your form is browser enabled, and publish your form to the CBA based environment.  Open the form, and see what value is in the field populated by username().  If Standard Windows Claims are used, it will look something like:

i:0#.w|[domain]\[user]

where [domain] and [user] are the current users domain and login name.

This information includes the claim information associated with that user account, the format adheres to this standard:

(For more information on Claims Based Authentication refer to the source of this image, Wictor Wilén's blog post on this area)

What we are specifically looking for here is the number of pipe ("|") delimiters, one or two.  You will need to account for that in the upcoming expression that extracts the domain and username from the account provided by the username() function.

Although not encountered in this scenario, I have also seen blog posts where the username() function only returned the username, no domain information. In this case, the "concat" function in InfoPath is used to generate the AccountName for the user profile service, concat("[Domain]\", username()). Use the same process as outlined below, but replace the pipe delimiter handling with this handling.

Update the InfoPath Form

These steps assume the following:

1. You have an InfoPath form that uses the User Profile Service, and it works using InfoPath filler enabled templates on your Claims Based authentication site.  This ensures the User Profile Service is functioning, and that your data connections are configured correctly.
2.  You have disabled anonymous and impersonation based authentication methods in IIS.

Open your form in InfoPath Designer, and first ensure that the user profile service data connection does not query data on form open:

We are going to manually set the value used by the User Profile Service, the value passed as a query parameter in the User Profile Service data connection (for reference, no action required here):

At some point in your form rules (usually form load if the values are populated immediately), you will have a rule that queries the user profile service, and sets a series of values on your form based on the values returned. Just before that query, insert new step to set the AccountName value for the UPS.  For "Action", choose "set a fields value".  In the configuration screen that opens. field section, select the UPS connection referred to earlier, and choose the query parameter "AcccountName":

Click Ok to return to the configuration screen.  Now set the Account Name in the value section by first clicking the function (fx) button.  Choose "Insert Function".  Select the function category "text", and choose "substring-after".  For the first value, enter in the "username()" function.  If you have a single pipe delimeter (see Determine Claims Based Token Format) then, in the second value, put "|".  Click OK.  Your configuration screen should look like:



If you have the two pipe delimeter format (from forms based authentication), you will need to extract the username from after the second pipe, and then concatinate the domain to the front of the AccountName before it is passed.

Click ok to close the rule configuration box.  Your new rule should run just before the form uses the user profile service data connection to retrieve user profile values.  This way, the query parameter used by the user profile service is set to the correct user with the correct domain/account name format for the service.

Publish your form and test.

Custom Permissions In SharePoint

- Including removing the ability to delete saved InfoPath form data for contributors

Introduction

There are many reasons you may need a custom permissions level in SharePoint, as the default permission levels available do not cover all application scenarios.

One very common requirement for InfoPath Form Systems is to prevent the user from being able to delete their form information once submitted. The lowest permission level that will allow a user to submit to a form library is "Contribute", but, by default, contribute also includes the delete ability. A custom permission level is necessary here, see the "Contribute without delete" section of this post.

If you use content approval to control permissions on a form library (see: Prevent Multiple Active Forms for One User ), you may need a specific group of users that should be able to open and review any form, moving the information along as part of the process. A couple of common examples are a manager who approves a vacation request, or a help desk request that is then reviewed by support staff. In this case, we create a custom permission that can then be associated with this group of users to allow this functionality. See "Create an Approvers Permission Level".

Of course, you can create any custom permission that your solution requires. These are examples of the process that can be followed for any custom permission.

Contribute without Delete

1. “Site Actions” -> “Site Permissions”.
2. In the “Manage” section of the Office Ribbon, choose “Permission Levels”.
3. Click the “Contribute” permission level.
4. Scroll down to the bottom of the page and choose “Copy Permission Level”.
5. Name your new permission level “Contribute without Delete”.
6. Fill out the description with similar to “All permissions of Contribute without delete”.
7. In the “List Permissions” section, remove the checkmark beside “Delete Items – Delete items from a list and documents from a document library”.
8. Click “Submit”.

You now have a contribute without delete permission level. Create a group with this permission level and associate directly to the form library (not the site) that hosts the InfoPath form. Users who belong to the group with this permission level will be able to submit form information to the library, but not delete it.

Create an Approvers Permission Level

1. Login to your site as a site admin.
2. “Site Actions” -> “Site Permissions”.
3. In the “manage” section of the Office Ribbon, click “Permission Levels”.
4. Click the “Contribute” permission level link.
5. Scroll to the bottom of the form and select “Copy Permission Level”.
6. A new permission level form page will open, with all of the contribute permission levels displayed. Name this new permission level “Approve”. For the description, type “All permissions of Contribute plus Content Approval.”
7. In the “List Permissions” section, Check “Approve Items”.
8. Click “Submit”.

You can now use this permission level to give users contribute and approval levels in your groups. If you use content approval, users in this group will be able to see all items in "pending" status.

Building Data Visualizations in SharePoint

- Using Report Builder 3.0

 

Introduction

Report Builder 3.0 is a relatively simple tool used to generate reports and other data visualizations against many different data sources, sql, oracle, an access database. For our purposes, we are using it to connect to a SharePoint list in order to be able to generate meaningful visualizations and reports against that list data. Report builder is good way to generate visualizations that add meaning to reports and SharePoint sites. Here are a few examples of the visualizations:

Setting up the environment, creating a report and displaying within SharePoint are covered in this post.

Install Report Builder 3.0

Report Builder 3.0 is available for free from Microsoft from here. Download and install.

 

Environment

You will require SharePoint Enterprise 2010 in order to create a report library, with reporting services, and the following feature needs to be enabled:

If you have sufficient permissions, follow the directions following to activate this feature. If not, contact your farm administrator to see if these features/services can be enabled.

Turn on Enterprise Site Collection Features

1. Navigate to the site you are going to create reports on.
2. “Site Actions” -> “Site Settings”.
3. “Site Settings” -> “Manage Site Features”.
4. Click “Activate” beside the item “SharePoint Server Enterprise Site features”.
5. “Site Actions” -> “Site Settings”.
6. “Site Collection Administration” - > “Site collection features”.
7. Click “Activate” beside the item “SharePoint Server Enterprise Site features”.

You will also need Reporting Services configured and installed on your farm.

Verify reporting services is enabled

1. Navigate to the site you are going to create reports on.
2. “Site Actions” -> “More Options” and choose the “Library” Filter.
3. You should see “Report Library”. If not, contact your SharePoint farm administrator to activate it or follow the below directions on activating if you have permissions to the SharePoint Central Administration Site.

Enable Reporting Services (requires admin access to SharePoint Central Admin)

1. Open the SharePoint 2010 Central Administration Site.
2. Choose “General Application Settings”.
3. You should see the category “Reporting Services”. Click “Reporting Services Integration”:

4. Ensure “Activate feature in all existing site collections” is selected:

5. Click “OK”.
6. Exit the Central Administration site, go back to your hosting site, and now confirm that “Form Library” is one of the options available.

 

Create the Report Library

The first step to creating a SharePoint report is to create the report library in SharePoint. Creating a report library automatically associates the report content type with this library. The report content type is then recognized by SharePoint, and reports are rendered within SharePoint using Sequel Server Reporting Services without any further configuration.

1. Navigate to the SharePoint site that you wish to have the report library on.
2. “Site Actions” -> “More Options”.
3. “Filter By:” -> “Library”.
4. Select “Report Library”:

5. Name your report library and click “Create”.

Create a Report Builder Report

Before you can create a report builder report within SharePoint, you need to have a SharePoint list to report against. We are assuming that this list is already created, and that we will be binding directly to that list with our report. The list in this example is a task list, this list was generated by SharePoint from a Microsoft Project file. The techniques used here to generate a report can be used for any list.

We are creating a “gauge” data visualization, the same techniques can be used for any data visualization (chart, table, etc.).

 

Create a blank report and add Data Source and Data Set

1. Start Report Builder 3.0.
2. Choose “New Report” and click “Blank Report”.
3. Place your mouse pointer over the “Title” section and delete. Place your pointer over the “Execution Time” box and delete:

4. This should leave you with a blank report. In the report data window, right click on “Data Sources” and choose “Add Data Source”.
5. Name your data source with a name that identifies what the data source is.
6. Choose “Use a connection embedded in my report” (if the report is going to be deployed to different environments once completed, you may wish to create an external connection and bind to it here).
7. In “Select connection type:” choose “Microsoft SharePoint List”.
8. In the “Connection String” window, paste in the URL of the SharePoint Site that hosts the list that you are connecting to. Remove any trailing information, so that the link looks like:

http://www.yoursite.com/projects/Form_Project

9. Click “Credentials” in the left window and choose “Use current Windows User”.
10. Click “General” again and then click “Test Connection”. If the URL is correct and you have adequate permissions to the list, you should see:

11. Click “OK” to save your data source.
12. Right click on “Datasets” in the Report data window:

13. Choose “Add DataSet”.
14. By default, “Query” should be selected in the left navigation window. Select “User a dataset embedded in my report”.
15. For the “Data source”, choose the data source you just created.
16. Click the “Query Designer” button.
17. Find your list on the list of lists in the “SharePoint Lists” section, and click the “+” sign beside that list.
18. Put a checkmark in every field you will need to use in your report:

19. Click the green “Run Query” button to verify that your query returns the expected values.
20. Click “OK”.
21. You should now have a Data Set Properties window similar to:

22. Click “OK”.

 

Create Test Data Table and Project Completed Gauge

Once you have your form data connection and data set created, you can now report on the values returned by the Dataset. You can use any of the Visualization tools for this purpose, our demonstration uses the out of the box “Gauge” visualization to show how close to complete a specific project is (based on the task list).

1. First, we are going to insert temporary “Test” table that simply displays all of the values returned by our dataset. While still on the insert tab, choose “table”. Choose “Table Wizard”.
2. Choose your recently created dataset. Click “Next”.
3. Drag your dataset fields into the “E Values” section. We will not group by columns or rows for simplicity, since this is a testing table, we just want to see the raw data returned by our dataset/Data Source combination. Don’t worry about the “Sum” function it may place around some of your fields.
4. Click “Next”, then “Next” again. Click “Finish”.
5. Drag your table to a clear area of the report for display:

6. Click the “Home” tab, then the “Run” button. You should now see your table populated with raw report data:

7. Click the “Insert” tab.
8. Choose “Gauge” from the top menu.
9. Using your cursor, drag a square on the report where you would like the gauge to appear.
10. Next select the Gauge type from the selection screen, we choose the “180 degrees north” selection:

11. Click “OK, you should now see something similar to:

12. Now, it’s time to modify the look of the gauge to match what we wish. First, click on the red area that indicates the end of the dial to select it. Right click, and choose “Properties”. Our indicator bar should be green, and extend for most of the surface. In order to do that, make the following changes:

13. We also want the shaded area to be a green gradient, so click the “Fill” category, and configure as follows:

Click OK.
14. You should now have a green indicator bar extending for most of the dial:

Create Gauge Pointer Expression

Now we want a single value that will represent the proportion of the project that has been completed, based on the “Percent Completed”. We are going to approach this by giving an average of percent completes set in the associated table. This weights each task equally, no consideration has been given for tasks that take more or less time.

If we want to calculate the average of any group of numbers, we add them together, then divide by the total number of values. We will apply that approach here. First, we want to get a total count of the number of rows (and therefor tasks) associated with this report. Do this by:

1. Click on the pointer on your gauge.
2. Right click and choose “Pointer Properties”.
3. In the “Value” field, click the expression button (fx).
4. Click the “+” by “Miscellaneous” and double click on “RowNumber”. You should now see:

5. Now click the category “Datasets”, and select any of the values (by double clicking):

6. Remove all formatting except for the name of your dataset, so that it now looks like:

7. Now click inside the expression so that your cursor is flashing in front of “RowNumber”.
8. If “Datasets” hasn’t been selected in the “Category” field, select it again.
9. Double click “Sum(ID_Complete)”:

10. Complete the formula by first multiplying by 100, then dividing by the row number total, as follows:

11. Click “OK”, then “OK” again.
12. You should now be able to click “Run” and preview your form.

 

Publish and Expose Your Report

1. Save the report you created in your prior steps to the report library. You should be able to click on the report, and view the report in the built in SSRS report viewer:

2. Now navigate to the page you wish to display the gauge on.
3. Click “Site Actions” and choose “Edit Page”.
4. Click “Add a web part” in the appropriate column.
5. Choose “SQL Server Reporting” Category, and choose “SQL Server Reporting Services Report Viewer”:

Click “OK”.
6. The Report Viewer Web Part configuration will come up:

7. Click “Click here to open the tool pane”. This will open the report viewer tool pane:

8. Click the three dots browse button on the tool pane, this will bring up the local sites libraries.
9. Select your reports library, then choose the report you just added:

10. Click “OK”.
11. Click “View” to expand the View menu on the report viewer web part, and remove all checkmarks from the various tools so they won't display with our guage*:

12. Click the “+” sign by the “Appearance” section, title your report viewer. You will probably want to reduce the area used by your report to in the web part as much as possible. This will take tweaking, but use the Height and Width fields to precisely define, as follows:

13. Click “OK”.
14. Tweak the report viewing area by changing height and width until the gauge is correctly positioned with no scrollbars.

*SharePoint will reset default these values to included each time you attempt to modify a different setting, such as width and height. Remember to uncheck these values each time you modify the overall report viewer web part properties.

 

Next Steps

Here are some further ideas once you are familiar with report builder:

1. Create a report that includes data and gauges for client information:

2. Create a centralized reporting area that displays values for multiple projects:

InfoPath 2010 Dynamic Image Links

- Including a work-around to image field reversion issue (http://address)

Introduction

There are lots of reasons you need to link to a different resource from within an InfoPath form. Links to external documents, instructions, charts or other large images, videos, internal or external web links. Inserting a link into an InfoPath form is fairly trivial, and making an image a link to a resource is easy to do within InfoPath as well using the built in toolset.

However, by specifying the address within your InfoPath form, you are creating a static address, one that, if it changes, will need the form to be updated as well. If you include multiple links, then all of these links will need to be updated within the form when they change as well. This can create both challenges when moving to different environments as well as challenges for the team responsible for maintaining the form.

A better overall approach is to have the links stored in an external list, and dynamically generate the links when the form loads. The links can be updated through environments if necessary by updating the link values in the associated list, and the end user of the system can update resource links by updating the list values.

The solution described in this post works well for resources of multiple different types, like web pages, documents, and videos. If you have a single resource type, such as videos, that link to your form, you can create a library specific for that content type, and link directly to the library that hosts the content.

We will create a list within SharePoint to store these link values. A data connection to that list used by your InfoPath form will then retrieve the values from the list, and filter by the link type.

We are going to link to these resources using images (text based links work as well). There is a known issue with the picture insert function in InfoPath that causes a dynamic picture link to revert to a static value instead of the dynamically generated external value. We will also provide the workaround to this issue in this document. The result of this process will be multiple images, each linking to a URL determined by an external SharePoint list.

 

Create and Customize Link List

Ingredients
• SharePoint site to host links list
• Design Permissions on site

We first need a standard SharePoint link list to store our links in. These links can be to anything, external sites, SharePoint sites, images or videos stored on the SharePoint site. The only thing to be aware of when linking to internal SharePoint areas is that the user must also have permissions to the areas/content you are linking with. Otherwise, the link will not resolve and you will get a permissions error when the new window opens.

Since we plan on populating this list with multiple links, we need some way of telling them apart. We will add a “LinkName” field to the list to allow us to filter on that value.

Steps
1. Navigate to the site that you wish to add your links list to. Choose “Site Actions->More Options”.
2. “Filter By:”, select “List”.
3. Select “Links”.
4. Name your list something that describes its content (“SurveyFormLinks”).
5. Click “Create”. Your list will now be created.
6. Within your new list, click the “List” tab, and select “List Settings”.
7. In the “Columns” section, click “Create column”.
8. Name your column “Link Name”, leave it as a “single line of text”.
9. Check “Yes” for “require that this column contains information.
10. Leave other values as default and click “OK”.
11. (Optional) I like to warn users about the risk of changing values here, so I put that warning right in the list description. Click “Title, Description, and Navigation”. In the description section, I put the following text “DO NOT EDIT LINK NAME FIELDS, they are used by an InfoPath form to differentiate between links. To change a link, modify the URL.”. Click “OK”. Now when a user browses to this link list, they see this warning in the list description.
12. Add some links to your list, remembering the “Link Name” so that you can use it later on the query filter:

Create a Data Connection to the Link List

Ingredients
• SharePoint Out of Box link list created and populated with some links
• URL of site that hosts the list and data connections
• URL of data connection library
• InfoPath Designer 2010

Steps
1. Open InfoPath Designer 2010, New->Blank Form->Design Form
2. On the “Data” tab, click “Data Connections”
3. Click “Add”
4. In the data connection wizard, choose “Create a new connection to:” -> Receive Data ->Next
5. Select “SharePoint library or list” ->Next
6. Enter in the URL of your SharePoint site that has the list and connection library ->Next
7. Select your link list from the list ->Next
8. Select “URL” and “Link Name” from the list fields. ->Next
9. Check “Store a copy of the data in the form template”
10. Name your data connection and check “Automatically retrieve data when form is opened” -> Finish
11. Click “Convert to Connection File”
12. Paste in the URL of your data connection library. Something like:
http://www.mysharepoint.com/sites/ISTT/Form%20Data%20Connections

Then append a forward slash and a filename for your data connection, such as:
http://www.mysharepoint.com/sites/ISTT/Form%20Data%20Connections/QueryDataConnection

This will create a data connection file called “QueryDataConnection.udcx”

13. Leave Connection link type as “Relative to site collection”, click OK.
14. Navigate to your connection library and approve the connection.

 

Add the Data Connection to your Form

Ingredients
• Data Connection saved in connection library
• InfoPath form
• Form Links List populated with links

Steps
1. Open the form you wish to connect to this data connection in InfoPath Designer.
2. “Data” tab, click “Data Connections”.
3. Click “Add”.
4. Choose “Search for connections on a Microsoft SharePoint Server”.
5. Choose “select site” to see if your site is already listed in the “Site:” drop down list. If it is, go to step 11. If not, click “Manage Sites”.
6. Click “Add”.
7. Enter in the URL of the site that hosts your data connection library, for example:
http://www.mysharepoint.com/sites/MyForms.
8. Enter a display name.
9. Click “OK”.
10. You should see your site name in the list of sites. Click “Close”. 11. After a moment or two, you should be able to select your site from the list, and then see the name of your data connection library displayed in the dialog window. Click the “+” beside the data connection library and select your form links data connection:

Click “Next”.
12. Select both the “URL” value and the “Link_Name” value.
13. Click “Next”.
14. Check “Store a copy of the data in the form template”.
15. Name your data connection with a clear name for its purpose, such as “SharePoint Link List Connection”.
Check “automatically retrieve data when form is opened” (IMPORTANT). Click “Finish”.
16. You should now have your data connection viewable in the Data Connections Window (if this is a functioning form you will have other data connections as well):

17. Click “Close”.
 

Create and Populate the Hidden URL values

Ingredients
• InfoPath form
• Form Links List populated with links

Before we can dynamically set the URL values for images on the form we first need to extract those values and populate hidden fields with them. If we bind the values directly to the form images, we lose the ability to filter on the link type. By using hidden fields as an intermediate holding area we can filter the returned values from the link list to correctly populate those fields with the associated specific value.

Steps
1. Open your InfoPath form.
2. Add one new field for each link you wish to include, naming them clearly based on the type of link you are returning:

3. Right click your first field and select “Properties”.
4. In the “Default Value” section, click the Function (fx) button.
5. Click “Insert Field or Group”.
6. By default, the main data connection will be displayed. Click the drop down list at the top and select your link data connection.
7. Expand the “myFields” then “dataFields” folders, select “URL”:

8. Click “Filter Data”.
9. Click “Add”.
10. For the first drop down, select the link name column you created in the link list earlier (in our example, the name is “Video Name”, not “Link Name”):

11. Leave “is equal to” as the default. Click the drop down arrow on the third list, and select “Type text”.
12. Type in the name of your link. This will filter the URL based on your link name:

13. Keep clicking OK until you get back to the formula window, your formula should look similar to:

14. Click OK again, and one more time to close the field properties.
15. Repeat the same process for each additional link, associating each with a corresponding hidden form field. The only value that needs to change for each is the name of the link on the filter.
 

Hook up Links to Images

Field Reversion Issue
Intuitively, you may think that if you click the “Insert” tab on your InfoPath form, and then choose “Picture”, and insert the picture on the form, you would then be able to bind that picture to a dynamic link. When you open the picture properties, you can even see where you can bind it. If you right click on the picture, select “Hyperlink” you will see the following:

Great you are thinking! I just have to select my newly created dynamic field in the “Data Source” section and we’re good. Right? Unfortunately not.

So, for example, I will go ahead and select my first link hidden field to populate the form. Here is what it looks like when I do:

I then click “OK”. But it doesn’t work! Open the hyperlink properties again to see why:

The “Link to” field has reverted back to the “Address” selection, losing all of your binding information. This is the field reversion link issue.

Set Your Image URLS Dynamically (Field Reversion Work Around)

Ingredients
• InfoPath form
• Form Links List populated with links
• Image (or images) to link to queried URL values
• Hidden fields containing URL values

Steps
1. Open your InfoPath form.
2. Determine where on the form you wish to place the image.
3. Open Windows Explorer and browse to your link image:

4. Right click on the Image and Choose “Copy”.
5. Go back to your InfoPath form, click within the area you wish to have your image link, right click and choose “Paste”:

6. Your image should now appear on the form. Inserting an Image this way allows us to retain the data source binding. Click on the image if not selected already, and right click. Select “Hyperlink”:

7. Click the “Data Source” radio button.
8. Select the Appropriate hidden link field and click “OK”:

9. Click “OK”. Now, if you open the hyperlink properties of this image, it will remember the binding. Copying and pasting allows the binding to be remembered. Inserting an image using the InfoPath Insert command does not.
10. Repeat this process for each of the image/URL links you have in your form.
11. Publish and test. You should be able to click the images in your form and have the appropriate URL open in a new window.