Monday, February 20, 2012

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.

Saturday, February 18, 2012

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:

Sunday, February 5, 2012

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.