August 19, 2016

Excel Services Fix – "The workbook cannot be opened".

Error:
When I tried to open any excel file in SharePoint, I was facing below issue: Excel Services – "The workbook cannot be opened".



Cause:
After spending some time on checking configuration settings of Excel Service Application, I found root cause of the issue was permission to Service Account under which Excel Service is running. It does not have enough permission to Content Database of Web Application.

Resolution:
To fix the issue, follow below steps and execute PowerShell Commands.
1. Click Start, click All Programs.
2. Click Microsoft SharePoint 2010/2013 Products.
3. Click SharePoint 2010/2013 Management Shell.
4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

$webApp = Get-SPWebApplication –Identity <URL of the Web application>
$webApp.GrantAccessToProcessIdentity(“<insert service account>”)

When you run these commands SharePoint does following actions:
1. Add Service Account user to Content Database of the Web Application. 
(Security > Users).
2. Assign "SPDataAccess" membership role to Service Account user. (Account > Membership)
3. Assign full control permission to the Service Account user on the Web Application.

You can get the “Service Account” details for Excel Service Application from Central Administration. Please follow below step:
1. Go to Central Administration > Security > Configure Service Accounts >
2. Select the application pool “Excel Services Application”.
3. You will find associated Service Account with application in other dropdown.





Reference: http://support.microsoft.com/kb/981293

If you have any questions you can reach out our SharePoint Consulting team here.

August 17, 2016

Add Site Column to multiple Content Types using PowerShell

Scenario
In large SharePoint Environment, we have hundreds of Site Content Types in site collection. And the requirement was to add new site column to all site content types of specific group.

Approach
Yes, first thing comes to mind is, lets add it to content type one by one through GUI. But it is not feasible approach while dealing with hundreds of content types as it becomes very tedious task.

So, I have decided to implement PowerShell script through which it can be added to all content types of specific group at once.

Code:

$site = Get-SPSite "<Site Collection Url>"
$web = $site.RootWeb

foreach ($ctype in $web.ContentTypes)
{
    if($ctype.Group -eq "<Site Content Type Group Name>")
    {     
        foreach($field in $web.Fields)
        {
            if($field.Group -eq "<Site Column Group Name>")
            {
                if($field.Title -eq "<Site Column Name>")
                {
                    if($ctype.Fields.ContainsField($field) -eq $false)
                    {
                        $link = new-object Microsoft.SharePoint.SPFieldLink $field
                        $ctype.FieldLinks.Add($link)
                        $ctype.Update($true)
                    }
                }
            }
        }      
    }
}

That's it, Done!!!


If you have any questions you can reach out our SharePoint Consulting team here.

August 12, 2016

SharePoint List View: Filter by Me and My SharePoint Groups

Scenario 
I have a SharePoint List containing task items. Tasks are assigned to a specific SharePoint Group or individual user. Each group has its own members. Requirement is, if current user is member of any of those groups, can see assigned tasks under My Tasks View.

Approach
There are few possible solutions, one of them is to modify Query filter via SharePoint Designer.

Filtering a list view by group membership
Step: 1
Open SharePoint designer then open site.  Select the Task List under Lists and Libraries. (For example, here we have selected "ResourceDocumentTypes").


Step: 2
When you select task list you will see a panel on right side named "Views". Select the view that needs to be modified. (For this example it’s "My Events")







Step: 3
Note: If you cannot edit file, you must select advanced mode.

In source view, search for <Query> and you will see View Query. If you have already modified view as filtered by [Me]. In that case, you need to modify the query which contains

<Query><Where><Eq><FieldRef Name="AssignedTo"/><Value Type="Integer"><UserID 

Type="Integer"/></Value></Eq></Where></Query>

With below code:

<Query>
<Where>
<Or>
<Membership Type="CurrentUserGroups"><FieldRef Name=" AssignedTo"/>
<Membership>
<Eq>
<FieldRef Name="AssignedTo"/><Value Type="Integer"><UserID Type="Integer"/></Value>
<Eq>
<Or>
</Where>
</Query>

Be sure to change the "FieldRef Name" to your own field, and click Save. Done!

Now, User can see all task items where “AssignedTo” field contains a SharePoint Group that logged in user is a member of and also assigned to him/her directly.

Results
Here is a list view showing all tasks assigned to different SharePoint Groups:

Below is filtered list view showing only tasks assigned to SharePoint Groups for which current user is a member.

If you have any questions you can reach out our SharePoint Consulting team here.