RSS

Resource Capacity Planning Report issue with old dates on Project Server 2013

Issue: Recently faced an issue with a client regarding Project Server 2013 Resource Capacity Planning Report. The client had some old projects uploaded and they wanted to view the resource capacity and allocated hours for last few months. They were only able to see information for one last month only.

Solution: The solution has two steps -

  1. Central Administration changes –
    1. In SharePoint Central Administration, click Application Management.
    2. On the Application Management page, in the Service Application section, click Manage Service Applications.
    3. On the Service Applications page, click the Project Application Service that contains the Project Web App instance for which you want to access the Resource Capacity settings.
    4. On the Manage Project Web Apps page, click the drop-down menu for the PWA instance for which you want to access the Resource Capacity settings, and click Manage.
    5. On the Server Settings page, in the Operational Policies section, click Additional Server Settings.
    6. On the Additional Server Settings page, in the Resource Capacity Settings section, for Active capacity view, enter the following:
      1. In the Months behind field, enter the number of months in the past that you want resource data to be calculated from. I changed it to 12.
      2. In the Months ahead field, enter the number of months in the future that you want resource data to be calculated from. Kept the default to 12.
    7. Click Save.
    8. You will have to run the timer job Project Server: Resource Capacity Refresh job for Project Server Service Application for getting the report refreshed with old dates.
  2. Open the ResourceCapacityPlanning Report in excel.

Click on Data -> Connections.

ProjectServerResourceCapacityPlanning1

On Connections Select Project Server – Resource Capacity and click Properties

ProjectServerResourceCapacityPlanning2

On Properties Window click on Definition Tab.

ProjectServerResourceCapacityPlanning3

On Definition Window check the Command Text column

ProjectServerResourceCapacityPlanning4

The SQL Command text should look like -
SELECT
(SELECT ResourceName from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [ResourceName],
MSP_EpmResourceByDay_UserView.ResourceUID as [ResourceUID],
ISNULL(AssignmentTable.AllocatedCapacity,0) as [AllocatedCapacity],
MSP_EpmResourceByDay_UserView.Capacity as [ResourceCapacity],
DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay) as [Month],
YEAR(MSP_EpmResourceByDay_UserView.TimeByDay) as [Year],
(SELECT [RBS] from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [RBS]
FROM
dbo.MSP_EpmResourceByDay_UserView
LEFT OUTER JOIN
(
SELECT
MSP_EpmAssignment_UserView.ResourceUID,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork) as [AllocatedCapacity],
MSP_EpmAssignmentByDay_UserView.TimeByDay
FROM
dbo.MSP_EpmAssignment_UserView
INNER JOIN
MSP_EpmAssignmentByDay_UserView
ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
AND MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID
AND MSP_EpmAssignment_UserView.TaskUID = MSP_EpmAssignmentByDay_UserView.TaskUID
WHERE
MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN
CONVERT(DATETIME, DATEADD(month, – 2, CURRENT_TIMESTAMP), 102)
AND CONVERT(DATETIME, DATEADD(month, 6, CURRENT_TIMESTAMP), 102)
GROUP BY
MSP_EpmAssignment_UserView.ResourceUID,
MSP_EpmAssignmentByDay_UserView.TimeByDay
) AS AssignmentTable
ON AssignmentTable.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID
AND AssignmentTable.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay
WHERE
(MSP_EpmResourceByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, – 2, CURRENT_TIMESTAMP), 102))
AND (MSP_EpmResourceByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 6, CURRENT_TIMESTAMP), 102))

Find all instances of CONVERT(DATETIME, DATEADD(month, – 2, CURRENT_TIMESTAMP), 102)) and change the -2 to the months you are interested in going back. There are two instances. I have bolded the text in the SQL above. The client was looking for 3 months back so I changed it to -

CONVERT(DATETIME, DATEADD(month, -3, CURRENT_TIMESTAMP), 102))

Save the file and now your report should pull the appropriate data.

 
 

Downloading InfoPath XML file as XML File through C# Program

If you have ever tried downloading XML form from the Forms library through code you might have realized that the downloaded file is no longer XML. 

Here is the code -

using (WebClient client = new WebClient())
{
client.Credentials = CredentialCache.DefaultCredentials;
string strXMLFile = “<siteURL>” + item.Url;
string strPath = @”D:\\” + listName + “\\” + item.Title;
client.DownloadFile(strXMLFile, strPath);
}

If you try the above code then you will be presented a HTML document but not the actual XML. In order to get around this issue you just need to add the parameter ?NoRedirect=true. What this actually tells the server is not to send the information to the forms server to be formatted. This just downloads the XML as it is. If you try the NoRedirect Parameter on IE then it will ask you where to download the file.

So, my final code looks like -

using (WebClient client = new WebClient())
{
client.Credentials = CredentialCache.DefaultCredentials;
string strXMLFile = “<siteURL>” + item.Url?NoRedirect=false;
string strPath = @”D:\\” + listName + “\\” + item.Title;
client.DownloadFile(strXMLFile, strPath);
}

I hope this helps someone :)

 
Leave a comment

Posted by on January 16, 2014 in Uncategorized

 

The maximum message size quota for incoming messages (65536) has been exceeded

Recently I was developing a SharePoint timer Job that references a WCF Service to retrieve some information. I was getting the following message -

The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.

The increase the MaxReceivedMessageSize you normally change the configuration file as follows -

<binding name=”BasicHttpBinding_MyService” maxBufferSize=”2147483647″ maxReceivedMessageSize=”2147483647″ />

But I was writing SharePoint timer Job and the timer job normally uses the owstimer.exe.config file. The file is located at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

I was not in favor of changing the config file so I wrote the following code to set the maxReceivedMessageSize -

BasicHttpBinding binding = new BasicHttpBinding();
binding.MaxBufferSize = 2147483647;
binding.MaxReceivedMessageSize = 2147483647;
EndpointAddress remoteAddress = new EndpointAddress(“<your service URL>”);
MyService.ServiceClient service = new MyService.ServiceClient(binding, remoteAddress);
MyService.fieldResponse fieldResponse = service.<Method>;

Happy coding….

 
Leave a comment

Posted by on January 8, 2014 in Uncategorized

 

BCS External List shows wrong Date Time

Issue:

Had a issue raised by one of our clients that while adding holidays to an external list the list was changing the values. For example, New Year day – Even after entering the date as 1/1/2013 12:00 am. It was changing it to 12/31/2013 6 pm.

Cause:

This was caused due to the Normalization Settings. When external content type is created using SharePoint Designer, DateTime fields are set to UTC using the NormalDateTime setting associated to TypeDescriptor for that particular field.

In my scenario, it was displaying as -

<TypeDescriptor TypeName=”System.DateTime” CreatorField=”true” Name=”HolidayDate”>
<Interpretation>
<NormalizeDateTime LobDateTimeMode=”UTC” />
</Interpretation>
</TypeDescriptor>

To fix the issue this needs to read as

<TypeDescriptor TypeName=”System.DateTime” CreatorField=”true” Name=”HolidayDate”>
<Interpretation>
<NormalizeDateTime LobDateTimeMode=”Local” />
</Interpretation>
</TypeDescriptor>

Resolution:

Follow these steps to fix this issue -

1. Open SharePoint Designer.

2. Open the Site.

3. Select External Content types under Site Objects.

4. Select the External Content Type. In my scenario Holidays. Do not click on the name. Just select the row.

5. On the Ribbon click on Export BDC Model.

6. Save the BDC Model to your local machine.

7. The extension would be – BDCM. Change this extension to xml.

8. Open the file in notepad.

9. Locate all the TypeDescriptor for your datetime fields. This is important step. We have to replace all the instances of the TypeDescriptor datetime field. Make sure the Typedescriptor’s interpration element matches the following format -

<TypeDescriptor TypeName=”System.DateTime” CreatorField=”true” Name=”HolidayDate”>
<Interpretation>
<NormalizeDateTime LobDateTimeMode=”Local” />
</Interpretation>
</TypeDescriptor>

10. Save the file.

11. Delete the External Content Type from SharePoint Designer.

12. Close SharePoint Designer.

13. Go to Central Administration

14. Click on Manage Service Applications under Application Management

15. Click on your Business Data Connectivity service.

16. On the ribbon click Import BDC Models.

17. On Import BDC Model browse and select the file.

18. Keep the remaining options as is.

19. Click Import.

20. If everything worked then it will give you import successful message.

21. Make sure you set the object permissions on the newly created External Content Type.

22. Go to your site and delete the external list created for the content type.

23. Recreate this list.

That is it !!!! Your date time should be displaying the correct format.

 
Leave a comment

Posted by on June 26, 2013 in Uncategorized

 

NAPA Office 365 Development Tools

After experimenting with Office 365 I ran into this really useful tool availabe called NAPA Office Development Tools. This tool allows you to create Apps for SharePoint and Office. Please refer to the following NAPA tutorials for more information.

Outlook Mail app – http://msdn.microsoft.com/en-us/library/jj220072.aspx

Excel App – http://msdn.microsoft.com/en-us/library/jj220065.aspx

SharePoint App – http://msdn.microsoft.com/en-us/library/jj220041.aspx

Please be aware that when you create an app for SharePoint it creates a sub web. If you are interested in accessing any lists on the top site level you need to provide permissions through the properties window in NAPA Application. I will blog about this soon.

 
Leave a comment

Posted by on January 4, 2013 in Uncategorized

 

Set-SPAppSiteSubscriptionName : There are no addresses available for this application

After a long time I am blogging about something new. I have started playing around with developing Apps for SharePoint in SharePoint 2013 and already ran into few issues. As developers or IT professionals we hate to read. We just get Visual Studio, SharePoint and just start with creating new app. But did not read the basic document before setting up my environment. So I got some weird errors while trying to deploy the app.

In short, before you even setup anything please read this article – http://msdn.microsoft.com/en-us/library/fp179923.aspx 

But I was receiving error on the final step for Set-SPAppSiteSubscriptionName -Name “apps”.

The error was Set-SPAppSiteSubscriptionName : There are no addresses available for this application.

This error was due to to their was no app domain URL entry for apps.ameettest.local was missing.

I opened the host file and added 127.0.0.1 apps.ameettest.local and then I was able to run the above command.

Ok now to the next problem :)

 
Leave a comment

Posted by on December 6, 2012 in Uncategorized

 

How do I add Custom User Profile Properties with PowerShell

If you have used the Central Administration to add the User Profile Properties you are aware that it can be frustrating with the wait time on adding a single property. That led me to a research on how can we add User Profile Properties through PowerShell. That landed me to this blog – http://gallery.technet.microsoft.com/scriptcenter/SP2010-PowerShell-to-0fda04f7

It worked for me for single property but most of our clients do have multiple properties and that does come from separate connections including External Content Types. So, I did modify the above script and created a csv file of all the available properties that I needed to track. The CSV file has the following properties -

  1. PropertyName – This field is used for the Internal Name of the User Profile.
  2. DisplayName – This field is used for the Display Name property of the User Profile Property.
  3. Privacy – The allowed values are – Public, Contacts, Organization, Manager, Private and NotSet
  4. Privacy Policy – The allowed values are – Mandatory, OptIn, OptOut and Disabled
  5. Type – The data type of the property.
  6. Length – In case of string, enter this value or enter 0
  7. ConnectionName – The Synchronization connection name.
  8. AttributeName – The attribute from the synchronization that you would like to map to.
  9. IsMultivalued – Whether this property will hold multiple values.

My CSV file example looks as -

PropertyName,DisplayName,Privacy,PrivacyPolicy,Type,Length,ConnectionName,AttributeName,IsMultivalued
testskills,Test Skills,Public,Disabled,string,200,EmployeeSkills,Skill,true
testskills1,Test Skills1,Public,Disabled,string,200,EmployeeSkills,Skill,true

EmployeeSkills is the BCS connection that pulls in skills from our database.

The PowerShell script mentioned in the above blog was not working for me. So, I made a few changes. This script accepts two parameters -

Parameter 1 – The csv file path and name

Parameter 2 – The Site URL.

The main thing I added was the IsMultivalued property and also the check to see if the type is string then assign the length. The entire selection is within a foreach loop that goes through each line to pull the property information.

param($Parm1, $Parm2)
#Load SharePoint User Profile assemblies
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Server”)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Server.UserProfiles”)
$userProps = Import-Csv $Parm1
 

 
#Get UserProfileManager
$mySiteUrl = $Parm2
$site = Get-SPSite $mySiteUrl
$context = Get-SPServiceContext $site
$upConfigManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileConfigManager($context)
$profilePropertyManager = $upConfigManager.ProfilePropertyManager
$coreprofilePropertyManager = $profilePropertyManager.GetCoreProperties()
$userProfileTypeProperties = $profilePropertyManager.GetProfileTypeProperties([Microsoft.Office.Server.UserProfiles.ProfileType]::User)
$userProfileSubTypeManager = [Microsoft.Office.Server.UserProfiles.ProfileSubTypeManager]::Get($context)
$userProfile = $userProfileSubTypeManager.GetProfileSubtype([Microsoft.Office.Server.UserProfiles.ProfileSubtypeManager]::GetDefaultProfileName([Microsoft.Office.Server.UserProfiles.ProfileType]::User))
$userProfileProperties = $userProfile.Properties 

Foreach ($userProp in $userProps)
{
#Set Custom Property values
$PropertyName = $userProp.PropertyName
$PropertyDisplayName = $userProp.DisplayName
$Privacy= $userProp.Privacy
$PrivacyPolicy = $userProp.PrivacyPolicy
$coreProperty = $coreprofilePropertyManager.Create($false)
$coreProperty.Name = $PropertyName
$coreProperty.DisplayName = $PropertyDisplayName
$coreProperty.Type = $userProp.Type

#Check if the Type is string then assign the length.
if ($userProp.Type -eq ‘string’)
{
$coreProperty.Length = $userProp.Length
}

if ($userProp.IsMultivalued -eq ‘true’)
{
$coreProperty.IsMultivalued = $true
}
$foundProperty = $coreprofilePropertyManager.GetPropertyByName($userProp.PropertyName)

#if the property is found then we delete that property.
if ($foundProperty -ne $null)
{

#Uncomment the code to delete the property.
#$coreprofilePropertyManager.RemovePropertyByName($userProp.PropertyName)
}
$coreprofilePropertyManager.Add($coreProperty)
$profileTypeProperty = $userProfileTypeProperties.Create($coreProperty)
#Show on the Edit Details page
$profileTypeProperty.IsVisibleOnEditor = $true
 
#Show in the profile properties section of the user’s profile page
$profileTypeProperty.IsVisibleOnViewer = $true
 
#Show updates to the property in newsfeed
$profileTypeProperty.IsEventLog = $true
 
$userProfileTypeProperties.Add($profileTypeProperty)
$profileSubTypeProperty = $userProfileProperties.Create($profileTypeProperty)
$profileSubTypeProperty.DefaultPrivacy =[Microsoft.Office.Server.UserProfiles.Privacy]::$Privacy
$profileSubTypeProperty.PrivacyPolicy = [Microsoft.Office.Server.UserProfiles.PrivacyPolicy]::$PrivacyPolicy
$userProfileProperties.Add($profileSubTypeProperty)
 
#Add New Mapping for synchronization user profile data
#SharePoint Synchronization connection
$connectionName =$userProp.ConnectionName
 
#Attribute name in Connection Source

$attributeName =$userProp.AttributeName
 
$synchConnection = $upConfigManager.ConnectionManager[$connectionName]
$synchConnection.PropertyMapping.AddNewMapping([Microsoft.Office.Server.UserProfiles.ProfileType]::User,$PropertyName,$attributeName)

}

 

You can use this code at your own risk :)

Good luck with PowerShell Scripting.

 
Leave a comment

Posted by on March 13, 2012 in Uncategorized

 
 
Follow

Get every new post delivered to your Inbox.