Archive for the ‘HowTo’ Category

Disable Enhanced Features on Samsung Galaxy S6

Sunday, July 26th, 2015

To Disable Enhanced Features Pop up on Samsung Galaxy S6

  1. Pull down the menu from top and click on Gear to bring the Settings page
  2. Click on Applications under Device section
  3. Click on Application Manager
  4. Slide to the Running tab on the right. Scroll down the list and look for ‘Enhanced Features’. Tap on it. ActionTriggerService started that feature. [You will see it under running tab if the messaged recently poped up]
  5. Slide to the All tab on the right. Scroll down the list and look for ‘Enhanced Features’. Tap on it
  6. Click on Clear Cache button to clear its cache
  7. Click on Force Stop button. Click OK to the prompt.

Marrying CSV file type with Microsoft Excel with SharePoint as the Chaperone

Tuesday, February 24th, 2015

Version: 1.3, Modified On: 03/25/2015

The title of this post can be read as following in SharePoint terms:
How to open CSV files in Microsoft Excel when using CSV from SharePoint?

Scenario

.CSV file type is not tied with Microsoft Excel in SharePoint. When users click on a CSV file in a SharePoint Library, it opens the CSV file in Notepad on the user’s computer. In contrast, if user clicks on a .CSV file on their PC, it opens the file in Microsoft Excel.

Antoher scenario where a user has the CSV file checked out, but he closed the CSV file without checking in the file. Now the users clicks on the checked out CSV and it opens in the notepad.

Another issue, the CSV files don’t have any icon associated with the file in SharePoint.

CSV File in SharePoint – Default Behavior

Let’s review the default behavior of CSV file in SharePoint. We have a test CSV file already uploaded to a library in SharePoint.

1. You can see there is no descriptive icon associated with the CSV file unlike Excel file. See the Excel file and .TXT file has relevant icon.

SharePoint - CSV File with no icon - Default behavior
SharePoint – CSV File with no icon – Default behavior

2. Click on the CSV file and you will get a prompt to open, save or save as the file

SharePoint - CSV File type clicked - Default behavior
SharePoint – CSV File type clicked – Default behavior

3. Click Open and it will open the CSV file in Microsoft Excel.

SharePoint – CSV File Opened in MS Excel – Default behavior

4. Now let’s check out the CSV file

SharePoint - Check Out CSV File - Notice Edit in Excel not an Option in menu - Default behavior
SharePoint – Check Out CSV File – Notice Edit in Excel not an Option in menu – Default behavior

5. Click on the checked out CSV file

SharePoint – Check Out CSV File – Click to open – Default behavior

6. Click ok on the Open Document dialog box

SharePoint - Check Out CSV File opens Document dialog box - Default behavior
SharePoint – Check Out CSV File opens Document dialog box – Default behavior

7. The opens up in Notepad!!!

SharePoint - Check Out CSV File opens up in Notepad - Default behavior
SharePoint – Check Out CSV File opens up in Notepad – Default behavior

We will review the inside behavior and how we can enhance the user experience for the CSV files.

Different Areas To Review and Resolution

We’ll review few areas related to this issue:

a. Default Programs settings on the user’s PC
i. Set your default programs
ii. Associate a file type or protocol with a program
b. IIS Web Server MIME Settings for .CSV on SharePoint Servers
c. Adding line item for CSV file and behavior in DocIcon.xml on SharePoint Servers
d. Changing Hkey_Classes_Root.csv on user’s PC

a. Default Programs settings on the user’s PC

Excel is the default program for CSV and is usually not the issue for the .CSV file to open in Microsoft Excel when the CSV file is on the PC or network share.

i. Set your default programs

1. Go to Control Panel > Default Programs
2. Click on Set your default programs

CSV File Association - Control Panel > Default Programs
CSV File Association – Control Panel > Default Programs

3. Click on Excel from the list of program on left side. Then click on ‘Choose defaults for this program’

CSV File Association – Control Panel > Default Programs – Excel Defaults

4. Make sure the csv extension is checked for Excel. Then click Save.

CSV File Association – Control Panel > Default Programs – Excel Defaults

ii.    ii. Associate a file type or protocol with a program

Let’s review the file type association under Default Programs.

1.  Go to Control Panel > Default Program
2. Click on ‘Associate a file type or protocol with a program’

CSV File Association - Control Panel > Default Programs - Associate File Type or Protocol
CSV File Association – Control Panel > Default Programs – Associate File Type or Protocol

3. On next page, make sure .csv file type is associated with Excel. Click on Change Program button if it is not or if you want to change it.

CSV File Association - Control Panel > Default Programs - CSV File Type Associate
CSV File Association – Control Panel > Default Programs – CSV File Type Associate

4. From Open With dialog screen, choose Excel. If Excel is not listed then expand ‘Other Programs’ section by clicking on the small arrow on right side. If Excel is not listed there then use Browse button to go to folder location where Microsoft Excel is installed and select the Excel.exe executable file. Make you to check the box that says ‘Always use the selected program to open this kind of file’

CSV File Association - Control Panel > Default Programs - CSV File Type Association with Excel
CSV File Association – Control Panel > Default Programs – CSV File Type Association with Excel

5. Click OK and close Default Program window.

b. IIS Web Server MIME Settings for .CSV on SharePoint Servers

We’ll now review the MIME type in IIS.

1. On SharePoint Servers, open IIS Manager
2. Click on the Server name on the left side of the console. On the right side of console make sure you have Features View tab highlighted

Note: You can make this change for selective web application (IIS site) rather than to the whole server.

3. From the features list, click on the MIME Types

SharePoint Servers - IIS Manager > MIME Types for CSV Changes
SharePoint Servers – IIS Manager > MIME Types for CSV Changes

4. Look for .CSV under the MIME Types. [Hint: You can select the first item in list and type .csv on kleyboard. It will take you right to the CSV extension]

The default .CSV MIME Type config is set to: application/octet-stream

5. Double click on the .CSV (or select .cSV and choose Edit from actions area on right).
Enter this value for CSV: application/vnd.ms-excel

Note: I’ve also noticed few people used below .CSV MIME Type value:
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

SharePoint Servers - IIS Manager > Updated MIME Types for .CSV Extension
SharePoint Servers – IIS Manager > Updated MIME Types for .CSV Extension

The IIS MIME type change will make the CSV file open in Microsoft Excel. Read below for details. Some scenarios may require below changes as well.

c. Adding line item for CSV file and behavior in DocIcon.xml on SharePoint Servers

Now to better user experience, we would like to add some icon with CSV file and enabled ‘Edit in Microsoft Excel’ in file menu in SharePoint. Look into the icon for CSV file in the first image at the top of this post.

On SharePoint Servers, we have DocIcon.xml file at following location that holds the key to the file type and behavior.

Location:
C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEXMLDOCICON.xml

Under the ByExtension section add the line for CSV file type. Notice the CSV16.gif is the icon file that I’m using for the CSV. You can use other icons like icxltx.png
<Mapping Key=”csv” Value=”CSV16.GIF” EditText=”Microsoft Excel” OpenControl=”SharePoint.OpenDocuments”/>

The location to store your own file icon or use one already there:
C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEIMAGES

Note:
I’ve noticed people have used one of these configuration for the CSV file type in DocIcon.xml. I choose the above line entry.
<Mapping Key=”csv” Value=”CSV16.GIF” OpenControl=””/>

<Mapping Key=”csv” Value=”CSV16.GIF” OpenControl=”SharePoint.OpenDocuments”/>

<Mapping Key=”csv” Value=”CSV16.GIF” EditText=”Microsoft Excel” OpenControl=”SharePoint.OpenDocuments” OpenApp=”ms-excel”/>

Important:
After performing the MIME Type change and this DocIcon.xml addition on all SharePoint Servers, make sure to perform the IISreset/noforce on SharePoint Servers.

d. Changing Hkey_Classes_Root.csv on user’s PC

On few user systems, I noticed the checked out files will still open in notepad if clicked by the same user.

To resolve that issue, I had to delete “PerceivedType”=”text” from the following registry location. [Use at your own risk and with backups].

HKEY_CLASSES_ROOT.csv

SharePoint - Deleted PerceivedType entry from Registry for .CSV Extension on user PC
SharePoint – Deleted PerceivedType entry from Registry for .CSV Extension on user PC

That registry location had these value:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT.csv]
“PerceivedType”=”text”
@=”Excel.CSV”
“Content Type”=”application/vnd.ms-excel”

[HKEY_CLASSES_ROOT.csvExcel.CSV]

[HKEY_CLASSES_ROOT.csvExcel.CSVShellNew]

[HKEY_CLASSES_ROOT.csvPersistentHandler]
@=”{guid}”

Few other notable Registry Location:
HKEY_CLASSES_ROOT.***
HKEY_CLASSES_ROOT<ProgId>shellopencommand
HKEY_CLASSES_ROOTMIMEDatabaseContent Type

HKEY_LOCAL_MACHINESOFTWAREClasses.csv

After these changes, the CSV filetypes will show the icon on the left, the SharePoint file menu will show Edit in Microsoft Excel as an option and edit the file will open the file in Microsoft Excel.

SharePoint - CSV File Types Showing Icon, Menu Showing Edit in Microsoft Excel Option
SharePoint – CSV File Types Showing Icon, Menu Showing Edit in Microsoft Excel Option

The short summary of this overall jargon can be explained in this Softvative Graphics.

Quick Process to Make CSV filetype work in MS Excel on SharePoint
Quick Process to Make CSV filetype work in MS Excel on SharePoint

@@@@@ At the end, the CSV and MS Excel lived happily ever after. 🙂  @@@@@

Few good resource to read on MIME Types and CSV

Keywords:

  • How to open CSV file in Sharepoint
  • How to open CSV file in Microsoft Excel
  • How to open CSV file in Excel
  • Open CSV in Excel
  • Open CSV from SharePoint
  • open .CSV from in Excel
  • open .CSV from SharePoint
  • CSV MIME Type changes
  • CSV MIME Type changes in SharePoint
  • CSV MIME Type changes for SharePoint
  • CSV MIME Type changes for Excel
  • CSV open document behavior
  • CSV default program association
  • Marry the CSV to Excel
  • CSV Registry locations
Visit www.softvative.com for professional services.

Rename a Project in MS Project Server Part 5

Friday, January 30th, 2015

Rename a Project in Microsoft Project Server – Part 5

Version: 2.0

Applies to:
MS Project Server 2010
MS Project Server 2013
MS Project Online (Office 365)

This is the fifth and last part of the series to explain the details of different cases and how to best rename a project schedule in Microsoft Project Server EPM.

Table of Contents

1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional
3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion

 

Summary

Renaming a project in a MS Project Server EPM environment might require considerations for the OLAP data cube and the Business Intelligence (BI) Center reports.
 

5. Renamed Project Schedule and Business Intelligence Center Reports

We covered so far all the steps to rename project schedule in MS Project Server. The Business Intelligence (BI) Center reports out of SSAS OLAP Cube will show the old and new project name. It is best to select both project names for accurate EPM reporting.

Let’s look Project Web Access (PWA) > Business Intelligence (BI) timesheet reports.
As an example let’s pick one of the timesheet template. Go to PWA > Project BI Center > Templates Library on left> English (United States) folder > Server Name – OLAP Database Name folder > OlapTimesheet.xltx
The path of that file is something like this:
http://pwa.softvative.com/PWA/ProjectBICenter/Templates/English%20(United%20States)/SSAS_server_domain.com_instance%20-%20EPM_Project_Prod/OlapTimesheet.xltx
Click on the OlapTimesheet.xltx template file to open it in Microsoft Excel. First save the file to the root of your Template library using save option or to a place where you like to organize Project BI Reports. I saved it to following location
http://pwa.softvative.com/PWA/ProjectBICenter/Templates/
With Excel report still open, now choose the fields from the PivotTable Fields list on the right side. Create a timesheet report that will have Timesheet Project List in it.
 
Project Web Access - BI Center Excel Report - Choose PivotTable Fields
Project Web Access – BI Center Excel Report – Choose PivotTable Fields
 
Filter the project list and select only the project name. You will notice only the old project.
 
Project Web Access - BI Center Excel Report - EPM Timesheet Report before project renamed
Project Web Access – BI Center Excel Report – EPM Timesheet Report before project renamed
 
Close the OLAP Excel Report file.
Now enter hours under a different timesheet period and submit. Later process the PWA OLAP cube manually from Server Settings > OLAP Database Management. Highlight your OLAP Database name row (don’t click on cube name), then click on Build Now icon above. Once the cube builds successfully, launch your timesheet report again from the place where you saved it. Apply the filter on the project name and expand the row data. You will now notice the old and new project names there.
 
Project Web Access - BI Center Excel Report - EPM Timesheet Report after project renamed
Project Web Access – BI Center Excel Report – EPM Timesheet Report after project renamed
 
Here the linkto the sample cached excel report from the BI Center.

Conclusion

You can see renaming a project might look like a quick thing to do. But it requires careful steps to update name at different places. You miss a major step and you will see your users complaining about different issues:

  •  I don’t see my project anymore
  • My timesheet hours are lost
  • I can’t get my project SharePoint site
  • List goes on….
A better communication plan for the change and doing this change at non-peak hours is the recommended approach.

That finishes last part of this series. We covered the following topics in this series.

Keywords:

  • Renaming a project in Project Server
  • Renaming a project in Microsoft Project Server
  • Rename a project using MS Project
  • Rename a project using MS Project Professional
  • Rename a Project in Project Server
  • best practice to rename a project in Project Server
  • best practice to change a project name in Project Server
  • How to change a project name in Project Server
  • How To guide for renaming a project in Project Server
  • Primer on renaming a project in Project Server
  • Step by step guide to rename a project in project server
  • Edit Project details to rename a project
  • Rename a project schedule in Project Server
  • Rename a project plan in Project Server
  • How to rename projects in Project Server
  • How to rename a mpp file in Project Server
  • Renaming Projects
  • Project rename process
  • Project rename job aid



Visit www.softvative.com for professional services.

Rename a Project in MS Project Server Part 4

Friday, January 30th, 2015

Rename a Project in Microsoft Project Server – Part 4

Version: 2.0

Applies to:
MS Project Server 2010
MS Project Server 2013
MS Project Online (Office 365)

This is the fourth part of the series to explain the details of different cases and how to best rename a project schedule in Microsoft Project Server EPM.

Table of Contents

1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional
3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion

 

Summary

Renaming a project in a MS Project Server EPM environment might require team members to adjust their timesheets based on few conditions.
 

4. Scenarios: Renaming Project and different Statuses of Timesheets

There are few scenarios based on different statuses of timesheet in MS Project Server.

1. In progress timesheet
2. Approved timesheet (Submitted)
3. Period Closed timesheet
Below is the table that summarizes the conditions.
Timesheet Status Table
Timesheet Status
Description
Can Recall
Can Delete
Project Name
In Progress
Active timesheet created before rename
Yes
Old
Approved (Final)
Approved timesheet
Yes
Old
Period Closed (Final)
Timesheet period closed
No
No
Old
Not Yet Created
Not active yet
New
Note: In this scenario, we assume that users are listed as their own Timesheet Managers and so the submitted timesheets are auto-approved.
You can view status of a timesheet by going to Timesheet page and reviewing the bar above the timesheet grid area (below the ribbon).
 
Project Web Access TimeSheet - In Progress Timesheet
Project Web Access TimeSheet – In Progress Timesheet
 
Project Web Access TimeSheet - Approved Timesheet
Project Web Access TimeSheet – Approved Timesheet
 
New Project name will show up in new timesheets if you go to future or past timesheet periods that were not created at the time the project was renamed (those with status of ‘Not Yet Created’). Timesheets where the project’s tasks were not shown, once you add tasks using Insert Row > Insert Task, will also show the new project name.
You can review summary of timesheets by going to Manage Timesheets page under Settings section on left navigation.
 
Project Web Access Manage Timesheets Link
Project Web Access Manage Timesheets Link
 
Review the status column and you will find timesheets with either one of the three statuses.
Project Web Access Manage Timesheets Page
Project Web Access Manage Timesheets Page
• Clicking on ‘My Timesheet’ under timesheet column for a period will take you to that Timesheet’s page and will show the details.
• Highlighting the row by clicking any area on the row except ‘My Timesheet’ will activate the Recall or Delete icons on the top left ribbon area. Review the Timesheet Status Table listed above to see when you can recall or delete a timesheet based on timesheet status.
Timesheet Recall icon
Timesheet Recall icon
Timesheet Delete icon
Timesheet Delete icon

Exception 1: In Progress Timesheet previously created

Timesheets that were created prior to the project rename will still show the old project name.

Note:
In screen shots of this part you can notice, I renamed the project again before this part.

Project Web Access (PWA) - In Progress Timesheet showing old project name
Project Web Access (PWA) – In Progress Timesheet showing old project name
Resolution
1. Go to Manage Timesheets page, select the row for the timesheet and hit Delete icon on top left of ribbon to delete the In-Progress timesheet showing old project name.

Project Web Access (PWA) – Delete In Progress Timesheet

 

2. Now click over ‘Click to Create’ under Timesheet name to recreate the timesheet.
Project Web Access (PWA) - Create Timesheet
Project Web Access (PWA) – Create Timesheet

 

3. Once timesheet is created, you will be redirected to that timesheet. You can notice the new project name. Notice the previously saved hours are still intact.
Project Web Access (PWA) – Enter time in Timesheet under Project Tasks

Exception 2: Approved Timesheet previously created & Submitted

The timesheets that were approved prior to project rename will need to be recalled and deleted. Then recreate and resubmit the timesheets. Other than recall, the rest of the steps are the same as listed in exception 1 case.
Project Web Access (PWA) - Save and Submit Timesheet Got Approved
Project Web Access (PWA) – Save and Submit Timesheet Got Approved
Project Web Access (PWA) - Recall an Approved Timesheet
Project Web Access (PWA) – Recall an Approved Timesheet
That finishes third part of this series.
 
More on this and other scenarios is coming in next parts of this series. So far we have completed the three parts. Review the fourth part for timesheet related gotchas and remedies.
 


Keywords:

  • Renaming a project in Project Server
  • Renaming a project in Microsoft Project Server
  • Rename a project using MS Project
  • Rename a project using MS Project Professional
  • Rename a Project in Project Server
  • best practice to rename a project in Project Server
  • best practice to change a project name in Project Server
  • How to change a project name in Project Server
  • How To guide for renaming a project in Project Server
  • Primer on renaming a project in Project Server
  • Step by step guide to rename a project in project server
  • Edit Project details to rename a project
  • Rename a project schedule in Project Server
  • Rename a project plan in Project Server
  • How to rename projects in Project Server
  • How to rename a mpp file in Project Server
  • Renaming Projects
  • Project rename process
  • Project rename job aid



Visit www.softvative.com for professional services.

Rename a Project in MS Project Server Part 3

Friday, January 30th, 2015

Rename a Project in Microsoft Project Server – Part 3

Version: 2.1

Applies to:
MS Project Server 2010
MS Project Server 2013
MS Project Online (Office 365)

This is the third part of the series to explain the details of different cases and how to best rename a project schedule in Microsoft Project Server EPM.

Table of Contents


Summary

1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional
3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion

 

Summary

Renaming a project site in a MS Project Server EPM environment requires the following procedure.
 

3. Rename a Project Site

Renaming a project site is optional step. Even if you keep the old project site name, it will work and not break things. It is though good to have consistent naming across all places. The connection between a project schedule and project site is maintained by Project Server under Server Settings > Project Sites area. Renaming Project site has two steps:

a. Rename Project site’s Title and URL
b. Update the connection between Project schedule and Project site with new URL
 

a. Rename Project site’s Title and URL

To rename the project site follow the steps listed below.
1. Go to PWA > Project Center and locate your project
2. Select the project by clicking the first empty cell on left and then in the navigate ribbon group, click on Project Site
 
MS Project Web Access (PWA) - Select a Project and go to Project Site
MS Project Web Access (PWA) – Select a Project and go to Project Site


3. That will open your project site in new tab / window
4. Click on Site Actions > Site Settings on top left

MS Project Web Access (PWA) - Project Site > Site Settings
MS Project Web Access (PWA) – Project Site > Site Settings


5. Under Look and Feel section, click on ‘Title, description and icon’

MS Project Web Access (PWA) - Project Site > Site Settings > Look and Feel
MS Project Web Access (PWA) – Project Site > Site Settings > Look and Feel


6. Update the fields as needed:

Title and Description: to reflect the new name that will show up on the project site’s top left.
Web site Address: to update the URL address of the project site

MS Project Web Access (PWA) - Project Site > Site Settings > Title Description and Icon
MS Project Web Access (PWA) – Project Site > Site Settings > Title Description and Icon


7. Click OK to save your changes

b. Update the connection between Project schedule and Project site with new URL

Following the steps below. Without this step, the project site link will not work from Project Center and other places as it will be still be pointing to old project site URL. Users will get 404 not found error.

1. To update the connection URL between Project Schedule and Project Site, go to PWA > Server Settings
2. Click on Project Sites under Operations Policies section

MS Project Web Access (PWA) - Server Settings > Project Sites
MS Project Web Access (PWA) – Server Settings > Project Sites


3. Locate your project (using new name) in the list. Note the old URL still showing up even though the project schedule is showing the new name

MS Project Web Access (PWA) - Server Settings > Project Sites > Project showing old project name in URL
MS Project Web Access (PWA) – Server Settings > Project Sites > Project showing old project name in URL



4. Select the project by clicking on the row and then from top of the page click on ‘Edit Site Address’

MS Project Web Access (PWA) - Server Settings > Project Sites > Edit Site Address
MS Project Web Access (PWA) – Server Settings > Project Sites > Edit Site Address
5. In the Edit Site Address dialog box, update the URL address with new project site address (changed in section 3.a.6 above)


MS Project Web Access (PWA) - Server Settings > Project Sites > Edit Site Address Dialog box
MS Project Web Access (PWA) – Server Settings > Project Sites > Edit Site Address Dialog box


6. Click OK to any prompt and then locate the project on the page and verify the new site address

MS Project Web Access (PWA) - Server Settings > Project Sites > Edit Site Address Prompt for permissions
MS Project Web Access (PWA) – Server Settings > Project Sites > Edit Site Address Prompt for permissions
MS Project Web Access (PWA) - Server Settings > Project Sites > Project showing new project name in URL
MS Project Web Access (PWA) – Server Settings > Project Sites > Project showing new project name in URL


That finishes third part of this series.

More on this and other scenarios is coming in next parts of this series. So far we have completed the three parts. Review the fourth part for timesheet related gotchas and remedies.
 
 
Summary
 


Keywords:

  • Renaming a project in Project Server
  • Renaming a project in Microsoft Project Server
  • Rename a project using MS Project
  • Rename a project using MS Project Professional
  • Rename a Project in Project Server
  • best practice to rename a project in Project Server
  • best practice to change a project name in Project Server
  • How to change a project name in Project Server
  • How To guide for renaming a project in Project Server
  • Primer on renaming a project in Project Server
  • Step by step guide to rename a project in project server
  • Edit Project details to rename a project
  • Rename a project schedule in Project Server
  • Rename a project plan in Project Server
  • How to rename projects in Project Server
  • How to rename a mpp file in Project Server
  • Renaming Projects
  • Project rename process
  • Project rename job aid



Visit www.softvative.com for professional services.

Rename a Project in MS Project Server Part 2

Friday, January 30th, 2015

Rename a Project in Microsoft Project Server – Part 2

Version: 2.1

Applies to:
MS Project Server 2010
MS Project Server 2013
MS Project Online (Office 365)

This is the second part of the series to explain the details of different cases and how to best rename a project schedule in Microsoft Project Server EPM.

Table of Contents


Summary

1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional
3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion

Summary

Renaming a project using Microsoft Project Professional in a MS Project Server EPM environment requires the following procedure.

2. Steps to rename a project using MS Project Professional

To rename a project schedule, open the Microsoft Project Professional and make sure it is connected to MS Project Server.

1. Go to Filemenu and choose Open.

Microsoft Project Professional - File Open Dialog Box showing project from local cache
Microsoft Project Professional – File Open Dialog Box showing project from local cache
 
Tip: Notice in the screenshot that we renamed the EPM 2010 Rollout project in a previous step to EPM 2013 Rollout. But in the open dialog box it is still showing 2010 in the project name. That is due to local cache feature of MS Project Professional. Due to this reason, it is recommended that Project Managers publish their changes made in the project schedule using MS Project Professional before a project is renamed.
 
2. Click on the link ‘Retrieve the list of all projects from Project Server
3. Locate your project from the list and right click over it then choose Rename
Microsoft Project Professional - List of Projects - Right Click a project and choose Rename
Microsoft Project Professional – List of Projects – Right Click a project and choose Rename


4. Enter new name of project (append V2 in name as an example) and hit enter key. A message box will bring this message

The rename job was successfully submitted to server
Based on the server load, it may time some time for this to complete. You will continue to see the old name till the operation is completed.
Microsoft Project Professional - Project Renamed Confirmation Message
Microsoft Project Professional – Project Renamed Confirmation Message


5. Click OK to the message
6. Close the open dialog box and open it again. You will notice the new project name in the list.

Tip:
Notice in the Open dialog box that you are using the Working store. Select the Publishstore and locate your project in the list. You will notice that the old project name will still show in the list. What happened here?
In MS Project Server, there are multiple databases (stores) draft, published etc. Save and Publish operations update different tables in the system. To resolve the issue, you need to open the working store version of project and publish it.


Microsoft Project Professional - Previous Project name shows up in Published Store
Microsoft Project Professional – Previous Project name shows up in Published Store


7. Make sure your have Working selected under Store in the dialog box
8. Open the project (showing new project name) by double clicking on project name or by selecting it and clicking Open button at the bottom right

Microsoft Project Professional - Working Store showing new project name
Microsoft Project Professional – Working Store showing new project name

Tip:
If you get message regarding user submitted updates on the project, click No for now. You can approve those update later after the project is renamed. Update Center will show the new project name for the updates.

Microsoft Project Professional - Team Members submitted updates message
Microsoft Project Professional – Team Members submitted updates message


9. Go to File menu and choose Publish to publish the project data. That will update the new project name across all stores. (you can verify by going to file > open > ‘Retrieve the list of all projects from project server’ and then switching between working and published stores value and looking at the new project name)

Microsoft Project Professional - Publish Option
Microsoft Project Professional – Publish Option



10. Go to File > Close to close the project
11. Click Yes to any check-in message

More on this and other scenarios is coming in next parts of this series. So far we have completed the two parts. Review the third part to understand renaming the Project sites.
Summary

1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional

3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion

Keywords:

  • Renaming a project in Project Server
  • Renaming a project in Microsoft Project Server
  • Rename a project using MS Project
  • Rename a project using MS Project Professional
  • Rename a Project in Project Server
  • best practice to rename a project in Project Server
  • best practice to change a project name in Project Server
  • How to change a project name in Project Server
  • How To guide for renaming a project in Project Server
  • Primer on renaming a project in Project Server
  • Step by step guide to rename a project in project server
  • Edit Project details to rename a project
  • Rename a project schedule in Project Server
  • Rename a project plan in Project Server
  • How to rename projects in Project Server
  • How to rename a mpp file in Project Server
  • Renaming Projects
  • Project rename process
  • Project rename job aid
Visit www.softvative.com for professional services.

Rename a Project in MS Project Server Part 1

Thursday, January 29th, 2015

Rename a Project in Microsoft Project Server – Part 1

Version: 2.3

Applies to:
MS Project Server 2010
MS Project Server 2013
MS Project Online (Office 365)

When working on projects in a PMO environment using Enterprise Project Management (EPM) system like Microsoft Project Server, you come across situations where you might have to rename objects. Those rename item request could be for project, resource, tasks or other objects in the system.

MS Project Server has evolved as a mature EPM / PPM solution compared to its predecessor editions. In MS Project Server 2003 edition, we had to use other utilities to rename a project. Sometimes, special characters in project names used to cause so much trouble to the Cube build process.

These series of articles will explain the details of different cases and how to best rename a project schedule in Microsoft Project Server EPM.

Table of Contents

Summary

1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional
3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion

Summary

A project was created in Microsoft Project Server (MSPS) with a name. Later PMO / business teams wanted to change the name of the project.

1. Steps to Rename a Project using Project Web Access

Follow the steps below to rename a project using Project Web Access (PWA):

1. Go to PWA and click Project Center on left navigation

MS Project Web Access (PWA) - Project Center Link
MS Project Web Access (PWA) – Project Center Link

2. Find the project from the list that you would like to rename and then select a cell other than the project name in the row or click the first empty cell on the left of the row [Hint: Don’t click on the project name as it will bring the project details page]

Project Web Access PWA - Select the Project to Rename
Project Web Access PWA – Select the Project to Rename

3. Click on PROJECTS ribbon tab on top, Click on OPEN dropdown under Project ribbon group and select ‘In Project Web App for Editing’

Project Web Access PWA - Open Project in PWA for Editing
Project Web Access PWA – Open Project in PWA for Editing

4. On top area of left navigation, under project name, click on ‘Project Information’ or any PDP that has Name (that has project name) field in it.

Project Web Access PWA - Project Information PDP Page containing Name field
Project Web Access PWA – Project Information PDP Page containing Name field

5. Change the name of the project under NAME field
6. Click Save button in top left area of ribbon.
7. You will be presented with this message. Click OK to the following message:
“The page and left navigation titles will not be updated with the new name of the project until the page has been reloaded.”

Project Web Access PWA - Warning after Project Renamed in PWA
Project Web Access PWA – Warning after Project Renamed in PWA

You will see Save job message popping out from top right of screen.

Project Web Access PWA - Save Job Queued message
Project Web Access PWA – Save Job Queued message

then

Project Web Access PWA - Save completed successfully message
Project Web Access PWA – Save completed successfully message

At this point you can close the project and it will show the new name in PWA. I prefer to publish it just so that other database tables are updated as well.

8. Refresh the page to confirm the project has been renamed

Project Web Access PWA - Refresh the page to confirm the new project name on left nav
Project Web Access PWA – Refresh the page to confirm the new project name on left nav
9. Click on SCHEDULE link on top left
10. Under Schedule Tools > Tasks tab of ribbon, click on PUBLISH.
Project Web Access PWA - Go to Schedule PDP page and Publish the Project
Project Web Access PWA – Go to Schedule PDP page and Publish the Project
11. Watch for message popping from the top right with status message:
   a. Processing
   b. Save job queued
   c. Publish completed successfully
12. Once the Publish operation is complete, click on PROJECT ribbon tab on top
13. Click Close button
Project Web Access PWA - Close the Project in PWA
Project Web Access PWA – Close the Project in PWA
14. You will be presented with the Check-in message. Click Yes
Project Web Access PWA - Check in project in PWA
Project Web Access PWA – Check in project in PWA

Exceptions:

The TASKS page for team members will show the new project name. But the TIMESHEET page will show the old project name, if the timesheet was created prior to project rename. In that case delete / recall and create the timesheet again. It will then show the new project name.
More on this and other scenarios is coming in next parts of this series. So far we have completed the first part. Stay tuned for the second part.
Summary
1. Steps to rename a project using Project Web Access (PWA)
2. Steps to rename a project using MS Project Professional
3. Rename Project site
4. Scenarios: Renaming Project and different Statuses of Timesheets
5. Renamed Project Schedule and Business Intelligence Center Reports
Conclusion


References:
1. Back to Basics: Renaming a Project correctly in Project Server 2013


Keywords:

  • Renaming a project in Project Server
  • Renaming a project in Microsoft Project Server
  • Rename a Project in Project Server
  • best practice to rename a project in Project Server
  • best practice to change a project name in Project Server
  • How to change a project name in Project Server
  • How To guide for renaming a project in Project Server
  • Primer on renaming a project in Project Server
  • Step by step guide to rename a project in project server
  • Edit Project details to rename a project
  • Rename a project schedule in Project Server
  • Rename a project plan in Project Server
  • How to rename projects in Project Server
  • How to rename a mpp file in Project Server
  • Renaming Projects
  • Project rename process
  • Project rename job aid
Visit www.softvative.com for professional services.

Sharepoint Document Management Best Practices

Tuesday, December 2nd, 2014

SharePoint Document Management Best Practices

Version: 2.0, Last Updated: 12/03/2014

SharePoint like any document management system, has best practices that needs to be followed to get maximum value out of the system. A planned roll out of document management features helps the organization and users. A planned roll out should have some sort of discipline (Plan, Do, Check, Act) around it. In an unplanned roll out you will come across discipline (Do, check, plan, act) that cause more pain.

What are the “benefits of planned SharePoint roll out” :

  1. With planning, staff knows what is coming and when
  2. You select the features that best fit into the organization in the beginning. It might be difficult to change user behavior of how they work in the systems later if you started with rudimentary features and process
  3. Users might use a SharePoint library just like a network shared drive to dump files. Versus organizing a library with columns, content types or term store features might go long way
 4. Same department contents structured using right features might save lot of time and hence the cost (full time employees are not free, everyone has a cost associated)
  5. Structured information will help the team leads / Management make quick and intelligent decisions in short time

Now, what are some of the “common best practices around SharePoint Document Management”?

There are no hard and fast rules. Every situation and roll out might require different combination of features. Below I’ve listed some of the ‘”common best practices for Sharepoint”. This list by no mean is complete and there might be additional best practices needed around different department contents. Use this list as a reference and come up with your plan.

1. Use short Site, Library and List names

Don’t use long names in Sites, Libraries and lists in Sharepoint. Avoid having space in names. Like instead of ‘Billing Department’ use BD or Billing. Another example for site name se short name like AEC instead of An Excellent Company. Create sites, libraries and list with short names. Once created go the Title configuration areas to change to long title and add text in the description field. Creating those items with shot names conserves the URL address space.

2. 255 Characters URL Limit and Windows file / folder path Limits

There is a limit of characters in the URL address as part of web standard. It is 255 characters. Using long site, library, folders, files path can lead to situation where that limit might be reached. In Sharepoint that could cause some hard stop. Few examples could be users not able to edit files even with right permissions, previous versions of file not working etc.
References:
a. URL path length restrictions  
b. You receive a “The specified file or folder name is too long” error message when you create a new folder or a new document in a document library in SharePoint Portal Server or in Windows SharePoint Services http://support.microsoft.com/kb/894630
c. A webpage that has a long URL may not print to a network printer in Internet Explorer 9
d. You cannot delete long URLs from the browsing history in Internet Explorer on a computer that is running Windows 7 or Windows Server 2008 R2
e. Maximum URL length is 2,083 characters in Internet Explorer

3.  Avoid Using Special Characters in File, Folders , Library and Site names

Special characters cause special problems. To make a file / folder on the top of the list, use Sharepoint views, filters and sorts option. If you really need to make something on top of the list manually, use period (.) or underscore (_) in the beginning of the name. But it is better to use simple names.

Reference:
Information about the characters that you cannot use in site names, folder names, and file names in SharePoint

4. Avoid Using Spaces in Names and avoid having space in beginning

If you need to use two words in names, avoid space in the middle. That one space takes three character (i.e. %20) in the URL address. For example use BestDocument instead of Best Document. You can use underscore (_) in the middle of two words without adding spaces. In same example that will look like Best_Document.

5. Don’t add version number of file at the end of name

It is a common scenario users do on network shared drives. Don’t use the same practice in a content management system like Sharepoint. Instead turn on the versioning feature at the library or list level and use it to your advantage. Let the Sharepoint do its job of maintaining versions. Add those versioning columns in the view for users. On the side note, when you turn on the versioning feature don’t turn on unlimited versions, set a limit on the last number of versions to keep. For example 5 or 10 [Sharepoint site Admin can setup versioning for libraries and lists.].

6. Use Metadata instead of Folders

Metadata is much better way of working with contents compared to organizing contents in folders. Using Columns, Content Types, Metadata Term store feature of Sharepoint are recommend ways.
Reference:
a. SharePoint 2013 Best Practices: Using Folders [same concepts can apply to other Sharepoint editions]

7.  Don’t use long folder names

I’ve seen examples where people used kind of whole history of the folder as part of folder name. For  example “A Long Company Name, Contract Number, Customer Number, Customer Location” as part of folder name. Compare it to having a folder with just the short company name  ABC Company and using rest of the information as part of the metadata properties at file level to classify contents is much better and faster approach. You will run into long URL issues if you will have hierarchical chain of folders with longs names and long file names inside those folders.
Just like people don’t use long names. They use first and last name or maybe middle name. But people don’t add their grandfather, great grandfather and whole ancestral names as part of their name. Another example, book writers don’t add the full summary of the book on the title. It is usually in next few pages.

8. Think Before you Act

Always think and plan before you would like to do any important thing in SharePoint. Try to get your information organized from the beginning. Unplanned start can cause you lots of rework later that might take twice or thrice the efforts and costs. It is like plan and pay early and save a lot later versus do it without planning and keep paying forever.

9. Use Site, Library, List Templates

SharePoint comes with lots of templates for sites, libraries and lists. You can create a customized site, library or list using out of the box features and then save it as template. Those template can then be used later by other department and team. Thus saving lots of time and efforts later.

10. Know you SharePoint Document Management features

SharePoint offers lots of features for document management that help in different scenarios. Content Types, Content Organizer Rule, Documents Sets, Document ID, workflows and others are some of the advanced features that can bring value to your contents. Know what you have at your disposal, before users start using your libraries.

11. Learn about Content Management Features of SharePoint
SharePoint has lots of Enterprise Content Management (ECM) features that you may not be aware of it or you may not aware of other ways to leverage those features for your contents.

Learn about these features and jargon in SharePoint that will help your in the Document Management.

  • Metadata Term Store
  • Content Type Hub
  • Site Columns
  • Content Types
  • Document Center
  • Record Center
  • Content Organizer and Document Routing
  • eDiscovery and hold
  • Information Rights Management (IRM)
  • Versioning, Major & minor versions
  • Document Approval
  • Check-in / checkout
  • Folder Based automatic metadata
  • Metadata driven navigation
  • Workflows on Documents
  • Email Alerts on Documents, Folders , Libraries
  • SharePoint Search to locate documents
  • Social Aspects (Tagging, Ratings, Like It)
  • Audit Trails
  • Retention Schedule
  • Deletion
  • Storage (BLOB)
  • Large Document Sizes
  • Documents with most versions
  • Office Web Apps to view documents without any client

Common Errors in SharePoint Visio Services

Wednesday, November 26th, 2014

Common Errors in SharePoint Visio Graphics Services

Version: 1.0

Applies to: SharePoint 2010, SharePoint 2013

What is Visio Graphics Services

Visio Graphics Services is a service application in Enterprise editions of SharePoint Server 2010 and 2013. The service application allows SharePoint users to:

  1. view the Visio drawings without having MS Visio installed on their PCs
  2. allows data connected Visio drawings that can be refreshed from data sources
  3. allows users to view the SharePoint Workflow Visualizations

Common Issues with Visio Graphics Services

Following list shows common Visio Graphics Services errors, causes and resolutions.

  1.  The server failed to process the request

1. The server failed to process the request

A user tries to view Visio in browser (or view the Workflow Visualization page) from SharePoint and gets the error message:

The server failed to process the request

SharePoint Visio Graphics Service Error - The server failed to process the request
SharePoint Visio Graphics Service Error – The server failed to process the request

At this point a user tries to click on ‘Open in Visio’ link on top left of the workflow Visualization section to open the file in MS Visio on their desktop. The Visio file opens up in MS Visio.

In one scenario I was able to open the Workflow Visualization file in MS Visio on my desktop only in read only mode. I was getting a warning message:

Visio Standard does not support savings files of this type. When saving the file, you will be prompted to save as a different file type.

The reason of this warning is clear from the warning message. I had standard version of MS Visio on my PC. The Workflow Visualization file had .visio web drawing file extension (.VDW). The users with Ultimate, Premium and other higher version would be able to open and edit the web drawing format Visio files.

SharePoint Workflow Visualization - Web Drawing VDW can't be edited with Visio Standard
SharePoint Workflow Visualization – Web Drawing VDW can’t be edited with Visio Standard

Event Viewer Errors on Servers

Event Viewer Error 01

Log Name:      Application
Source:        System.ServiceModel 3.0.0.0
Date:          11/19/2014 4:44:00 PM
Event ID:      3
Task Category: WebHost
Level:         Error
Keywords:      Classic
User:          domainSPFarmAdminAct
Computer:      SPAppServer02.domain.com
Description:
WebHost failed to process a request.
 Sender Information: System.ServiceModel.ServiceHostingEnvironment+HostingManager/5426204
 Exception: System.ServiceModel.ServiceActivationException: The service ‘/20bf95936ed944db89431a9d16459372/VisioGraphicsService.svc’ cannot be activated due to an exception during compilation.  The exception message is: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401). —> System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)
   at Microsoft.Office.Visio.Server.GraphicsServer.VisioGraphicsServiceHostFactory.CreateServiceHost(String constructorString, Uri[] baseAddresses)
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CreateService(String normalizedVirtualPath)
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
   — End of inner exception stack trace —
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
   at System.ServiceModel.ServiceHostingEnvironment.EnsureServiceAvailableFast(String relativeVirtualPath)
 Process Name: w3wp
 Process ID: 4724

Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”System.ServiceModel 3.0.0.0″ />
    <EventID Qualifiers=”49154″>3</EventID>
    <Level>2</Level>
    <Task>5</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime=”2014-11-19T22:44:00.000000000Z” />
    <EventRecordID>730921</EventRecordID>
    <Channel>Application</Channel>
    <Computer>SPAppServer02.domain.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data>System.ServiceModel.ServiceHostingEnvironment+HostingManager/5426204</Data>
    <Data>System.ServiceModel.ServiceActivationException: The service ‘/20bf95936ed944db89431a9d16459372/VisioGraphicsService.svc’ cannot be activated due to an exception during compilation.  The exception message is: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401). —&gt; System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)
   at Microsoft.Office.Visio.Server.GraphicsServer.VisioGraphicsServiceHostFactory.CreateServiceHost(String constructorString, Uri[] baseAddresses)
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CreateService(String normalizedVirtualPath)
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
   — End of inner exception stack trace —
   at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
   at System.ServiceModel.ServiceHostingEnvironment.EnsureServiceAvailableFast(String relativeVirtualPath)</Data>
    <Data>w3wp</Data>
    <Data>4724</Data>
  </EventData>
</Event>

Causes:

There can be different reasons for the error ‘failed to process request’. Some of those are listed below:

  1. Service Account running Visio Services doesn’t have access to SharePoint Content Databases
  2. Application ID under Visio Graphics Service is not not correct or missing (Go to Central Administration  > Manage Service Applications > Visio Graphics Services > Global Settings)
  3. Secure Store Service application ID that is configured for Visio Graphics Services doesn’t have correct Permissions
  4. Visio Graphics Services on SharePoint Server are not running
  5. Some other failing services causing issue

Resolution:

In one of my scenario, everything was right except the fact that Secure Store Service was having some issues. That lead to issues for Visio Graphics Services. An IISReset /noforce on SharePoint Servers helped in resolving this issue.

Visio drawing started working in Workflow Visualization and also in Visio Web Part.

SharePoint Visio Graphics Services - Visio Web Access Web Part
SharePoint Visio Graphics Services – Visio Web Access Web Part

Workflow Visualization page showing the visio workflow

SharePoint Visio Graphics Services - Workflow Visualization Showing Process in Visio
SharePoint Visio Graphics Services – Workflow Visualization Showing Process in Visio

References:
1. Visio Graphics Service administration in SharePoint Server 2013
http://technet.microsoft.com/en-us/library/ee524059(v=office.15).aspx

2. SharePoint 2013 Visio Service: the server failed to process the request
http://alstechtips.blogspot.com/2014/05/sharepoint-2013-visio-service-server.html

3. Share Point 2013 – Visio Graphics Not Displaying – The server failed to process the request
http://squarelogics.com/share-point-2013-visio-graphics-not-displaying-the-server-failed-to-process-the-request/

4. Troubleshooting workflow visualization in SharePoint 2010
http://www.robertseso.com/2012/02/troubleshooting-workflow-visualization.html

5. Visio 2010 Services Error – The server failed to process the request
http://www.sathishtk.com/blog/post/2010/02/04/Visio-2010-Beta-Services-Error-The-server-failed-to-process-the-request.aspx

Keywords:

  • Common Errors in SharePoint Visio Graphics Services
  • Troubleshooting workflow visualization in SharePoint 2010
  • SharePoint 2013 Visio Service the server failed to process the request
  • SharePoint 2010 Visio Service the server failed to process the request
  • Visio Graphics Not Displaying
  • workflow visualization error
  • workflow visualization not displaying visio
  • workflow visualization errors out
  • Visio Web Access errors
  • Visio Web Access can not display visio
  • Visio Web Access issues
Visit www.softvative.com for professional services.

SharePoint Excel Data Refresh Common Issues

Monday, November 3rd, 2014

SharePoint Excel Data Refresh – Common Issues

Version: 1.0

SharePoint Excel Services requires different bits and pieces to work correctly in order for it to deliver your BI Reports correctly. There are times when you have to review and update the configurations in order to make it work.

I’ll cover some of my experiences related to issues and fixes in working with the SharePoint Excel Services in this publication.

Common SharePoint Excel Services Issues

Following are the few common issues that can happen with SharePoint Excel Services and the resolution of those issues.

a. Unable to refresh data for a data connection in the workbook. … The following connections failed to refresh

a. Unable to refresh data for a data connection in the workbook. … The following connections failed to refresh

I created a nice report in Microsoft Excel using the SSAS (SQL Server Analysis Services) as the data source. I was able to refresh the data connection within MS Excel file to pull the latest data or apply filters to get the data I wanted. No issue so far and every thing looks great.

MS Excel Report with SSAS Data Connection - Data Refresh Option
MS Excel Report with SSAS Data Connection – Data Refresh Working

I uploaded my report to a Business Intelligence (BI) Center site on SharePoint. I launched the report using Excel Web Services and I was able to see the data. I got the following error when I tried to do the Data connection refresh in an attempt to pull the latest data from SSAS data source.

Error:
Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh:
SSASserverName – InstanceName SSAS database ProjectDW TimeReport

SharePoint Excel Service Excel Report with SSAS Data Connection - Data Refresh Error
SharePoint Excel Service Excel Report with SSAS Data Connection – Data Refresh Error

More precisely, here is the what the screen looked like. Also I couldn’t apply the filters and my nice report turned out to be ‘acting not so nicely’.

SharePoint Excel Service Excel Report with SSAS Data Connection - Data Refresh Error Fullpage
SharePoint Excel Service Excel Report with SSAS Data Connection – Data Refresh Error Fullpage

Issue Cause

I started the SharePoint Log Viewer on the server and then launched my Excel report again in Excel Web services to reproduce the error. Paused the live monitoring of the ULS logs and started reviewing the log information. I found the line item in the log pertaining to data refresh issue.

Area: Excel Services Application
Category: External Data
Event ID: 2052
Level: Information

Highlighting the log entry revealed the message detail about it.

The workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSAS Project Control;Data Source=SSASServerNameInstanceName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

SharePoint LogViewer - Excel Data Refresh error with SSAS Cube - Unsupported Data Provider Used
SharePoint LogViewer – Excel Data Refresh error with SSAS Cube – Unsupported Data Provider Used

Though, above information is sufficient clue to go about fixing the issue, I’m putting few more log entries that were logged for the same session.

Few ULS Log Entries:

1. 
ExternalSource.GetExternalKeyAndStateAndMarkUsed: Populating the session’s credentials for external data. Index=0, Credentials=[8126098, domainfmasood]

2. 
ConnectionRequest.ConnectionRequest: New connection request. SessionId=1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, WorkbookVersion=ConnectionInfo.WorkbookVersion: Uri=http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx, Version=Thursday, 23 October 2014 19:19:17

3.
The workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSAS Project Control;Data Source=SSASServerNameInstanceName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

4. 
Refresh failed for ‘SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports’ in the workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

5.
ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.ValidateConnectionInfo(ConnectionRequest connectionRequest, ConnectionInfo connectionInfo)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, Extended…

6.
…ConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, externalSource=SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports

7.
ConnectionInfoManager.InitConnInfoFromOdcFile: ODC File Uri is local: C:UsersfmasoodDocumentsMy Data SourcesSSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports.odc

8.
ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOpe…

9.
…ration, Boolean verifyPreOperationConnection), Data Connection Name: SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports, SessionId: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, UserId: 0#.w|domainfmasood

10.
ExcelService.PostProcessRequest: finished request of type RefreshAll

Issue Resolution

I went to SharePoint Central Administration site and reviewed the Trusted Data Providers under Excel Services. The location of the configuration is Central Admin home > Application Management > Manage Service Applications > Click on your Excel Service Application > Trusted Data Providers.

In SharePoint 2010, MSOLAP.5 data provider is not present default. See the screenshot below.

SharePoint Excel Services - Trusted Data Provider - MSOLAP.5 not present by default
SharePoint Excel Services – Trusted Data Provider – MSOLAP.5 not present by default

Click on Add Trusted Data Provider link on top left and configure the MSOLAP.5 data provider.

SharePoint Excel Services - Trusted Data Provider - Configure MSOLAP.5 Data Provider
SharePoint Excel Services – Trusted Data Provider – Configure MSOLAP.5 Data Provider

Once added, you should see the MSOLAP.5 data provider in the list.

SharePoint Excel Services - Trusted Data Provider - MSOLAP.5 Data Provider Added
SharePoint Excel Services – Trusted Data Provider – MSOLAP.5 Data Provider Added

Once I had the MSOLAP.5 added as the Trusted Data Provider, in excel services, refresh all data connection feature started working. At that point I was also able to use filters in my report without any errors.

My SharePoint BI report lived happily ever after.

References:
1. Primer on SharePoint Excel Data Refresh Settings
2. SQL Server Version Numbers
3. Excel Services Data Refresh Flowchart – Review the screenshot titled E and section E in the table at bottom right for information related to this issue.
4. Add MSOLAP.5 as a Trusted Data Provider in Excel Services
5. How to: Install the Analysis Services OLE DB Provider on an Excel Services Computer

Keywords:

  • Fix Excel Data Refresh Issue
  • Fix Excel Data Refresh error
  • Fix Excel Data Refresh connection failure
  • Excel Data Refresh Issue
  • Excel Data Refresh connection failure
  • Data refresh issue due to MSOLAP.5
  • Missing Data Provider MSOLAP.5
  • Adding a new Trusted Data Provider
  • Trusted Data Provider in Excel Services
  • Trusted Data Provider in SharePoint Excel Services
  • Unable to Refresh SSAS Data from Excel
  • Event ID 2052
  • External Data Connection Failure
  • External Data Refresh Failure
  • SharePoint BI Report Refresh Issue
  • SharePoint BI Report data Refresh Issue
  • How to fix Excel Data Connection issue
  • How to fix Excel Data refresh issue
  • How to fix Excel Data Connection refresh issue
  • Get the real time data in Excel Services