Nearbaseline redesign and site overhaul

NearbaselineIt’s something I’ve wanted to do for sometime now, but finally found the time and resources to do it! Have a look around the new modern design (I guess that’s not actually modern-ui, meh?) and please let me know if you find something that doesn’t work! :)

Martin.

Correcting the alignment of PDP Web Parts

Recently I spoke at a Microsoft Project Server event here in Switzerland on the topic of Extending Project Server and using small pieces of JavaScript with jQuery to make little changes for big effects. One of my demos was to correct the following annoyance that many of us have probably come across but have no out of the box way to fix.

Project Detail Pages column alignment issues

Example

pdp screen 1

 

In yellow I have highlighted the issue that I’m talking about in case it is not obvious in the screenshot. As you see the alignment is actually based on the length of the longest custom field displayed in the web part, so as in the above example where we have used separate webparts to break up the webpage with headings the width of each column is unpredictable.

jQuery to the rescue

This is a great example of using jQuery as it shows how ridiculously simple some things can be to change! So let’s walk through the solution here if you’ve not done this before, as you will see the possible usage of this kind of “fix” is vast.

“Debugging” in Chrome or IE

The first thing you need to do to here is to identify the html element(s) in question, so the easiest way to do that is to use the “Inspect Element” feature available in both IE and Chrome browsers (and probably all others too), you’ll find it on the right click menu

pdp screen 2

When selected the inspect element will open up the developer console of your browser and focus on the specific element under the mouse, in this case the “Description” field text label.

pdp screen 3

Now you can browse the page source and as you see each element is highlighted, neat!

Now we can start using jQuery to select our columns to modify, start by dynamically loading the jQuery library using the following script:

var jq = document.createElement('script');
jq.src = "//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js";
document.getElementsByTagName('head')[0].appendChild(jq);

Now that we have jQuery available you can again look at the source and think about what we need to ‘select’, in this case we can see that our Description label is in a h3 inside of a table (tbody > tr > td) and specifically it has a class of “ms-formlabel”. Cool so in jQuery we can now select all such elements like this:

$("tr td.ms-formlabel")

Best thing is that being JavaScript all elements are selected and can be used in an array, but even better we can directly update all items like this:

$("tr td.ms-formlabel").width("300px");

Neat hey? If you run that command in the console immediately all the columns will update to be 300px wide!

Permanently applying fix to the page

So now we have our script let’s add it to a Content Editor Web Part (CEWP) on the page itself, to do that we need to wrap our line in some html which loads the jQuery library and runs the script when the page is ready, like this:

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script type="text/javascript">

$( document ).ready(function() {
	$("tr td.ms-formlabel").width("300px");
}

</script>

Now edit the PDP web part page, and add a Content Editor web part to it, then simply add the above HTML content to the web part HTML source like so:

First add the Content Editor web part and use the Edit Source ribbon option

pdp screen 4

Then paste in our html script

pdp screen 5

Now each time the page is loaded the script is run and all columns are aligned to 300px looking something like this:

pdp screen 6

Enjoy!

New Project Wizard

I like surprises! So today I was happy to find a brand new surprise shiny feature in ProjectOnline!

Behold the New Project Wizard:

newprojwiz1

And when you click Finish:

newprojwiz2

Neat, although seems to be currently un-configurable apart from the standard ‘New Project’ PDP changes that can be made, however I can see more ‘tabs’ being added up the top (where currently the numbers 1, 2 are) assumedly perhaps linked to other PDP’s?

More hints of what to expect in PS2015 I guess.

 

I’ll be speaking in June at Microsoft in Zurich

Microsoft Switzerland has a day devoted to Project Server 2013 in Zurich on the 11th of June 2014, and with my new TPG hat on I’ll be speaking about a Extending Project Server in the afternoon!

Event ID 1032573385 – Projektmanagement mit Project Server 2013

Its a German event, but don’t worry I won’t be putting the room though an hour of my German (!) so that session will be delivered in English, so I hope you come along!

Nearbaseline ID App released!

IDTile-96whOne of the most common things I get asked by customers is how can they assign a unique project number to projects in Project Server, it does seem strange that there is no out-of-the-box way to do it but now there’s an App for it!

Check out Nearbaseline ID available now in the SharePoint App store.

Project Site Custom List Reporting using SSIS OData Connector

I’ve been looking forward to using the new SSIS OData Connector for SQL 2012 since first hearing about it at Project Conf last month, Paul Mather wrote up a great step-by-step guide on getting it all up and running here so have a look at that if you haven’t yet.

However the devil is always in the details! Creating a simple report combining Project data and SharePoint site data is not as straight-forward as you might hope.

Reporting custom project site list data from ProjectOnline

Possibly the most common customer request I get when it comes to reporting is the need to report against non-standard Project site list data, something that was simple using SSRS 2008 R2 but made impossible in ProjectOnline, until now that is.

Following on from Paul’s post, in order to do this you’ll need an SSIS package that does the following:

  1. Retrieve Project Data from the ProjectOnline OData endpoint: /_api/ProjectData
  2. Get a list of Project Site URLs from the data and save it in a variable
  3. Loop through each Project Site and retrieve data from the SharePoint endpoint: /[ProjectURL]/_vti_bin/listdata.svc after dynamically updating the [ProjectURL]

Also you’ll notice there in step 3 I use the listdata.svc endpoint rather than the REST endpoint (e.g. /_api/Web/Lists/getbytitle(‘Risks’)/Items), if you followed Paul’s steps above and tried to retrieve the site list data then you probably are seeing the following error when building the connection:

ODataSourceError

Error message:

Test connection failed while parsing the XML document because it is not a valid OData service document.

If you’re seeing that message it is because the SSIS connector is expecting a ‘service document’ and not the actual OData feed. Not sure why the SharePoint REST endpoints don’t also have a service document at the root but we can work around it by using the listdata.svc.

Finally once we have the list of all of the Project site URLs then we need to update the Data Connection URL before we retrieve the site list data. I’ll cover all of these steps below.

Requirements for building the SSIS package

Paul Mather’s blog covers the basic setup, so I won’t cover that in detail but in summary to create / use the solution below you will need:

  1. SQL Server Integration Services 2012
  2. SQL Data Tools 2012 (the BIDS replacement on the SQL DVD)
  3. Microsoft® OData Source for Microsoft SQL Server® 2012
  4. SharePoint Server 2013 Client Components SDK
  5. An empty SQL database to write to, I’m going to use one called ProjectOnline_OData

You can run Data Tools on your client but the rest must be installed on the SSIS server.

Creating a SSIS package to retrieve site custom list for all projects

Create a new SSIS package and to start by adding the following Connection Managers:

  • OLE DB Connection which points to your SQL database: e.g. ProjectOnline_OData

oledbconn

  • An OData Connection pointing to the ProjectData OData service: e.g. https://tenant.sharepoint.com/sites/pwa/_api/ProjectData

ODataConnPData

  • An OData Connection pointing to the SharePoint ListData.svc OData service of an existing project site: e.g. https://tenant.sharepoint.com/sites/pwa/Test%20Project/_vti_bin/listdata.svc

ODataSharepoint

Make sure that each connection test’s successfully, and give them descriptive names which will be used later. I’m using OData_ProjectData and OData_SharePointListData for the last two and note that both names will be referenced below.

A note on security

The account used to authenticate with our OData connections must be a ProjectOnline user, and specifically it must have access to all projects and project sites. In order to do this easily I have added the users (ssis@blah.onmicrosoft.com) to the following:

  • Project Web App Administrators
  • Site Collection Administrators

Adjust as you see fit.

Retrieve project data from OData

Now that we have our data connections we need to first get our Project data (and optionally any other data such as tasks), then prepare to get our project site data.

  • As per Paul’s blog create a Data Flow task with an OData Source (OData_ProjectData) which points to the Projects collection.
  • Ensure that you select only your required Columns, however you must include the following as a minimum: ProjectIdProjectWorkspaceInternalUrl.
ProjectsData

Projects collection OData source with preview

  • Use the Destination Assistant to create the destination database
  • Create a new table to store the data in (I’m using the name Projects) and configure your mappings

ProjectsTable

  • Optionally you could add other Data Flow tasks in there to get Tasks or other data into additional database tables, but only using the OData_ProjectData connection at this point.

So now you should have a solution that runs and looks something like this when debugging:

FirstRunResult

Also if you started with Paul’s blog, then this is where those steps end.

Prepare to loop through all project sites

In order to get the site list data for each project in PWA we need to first save a temporary variable with a list of all ProjectWorkspaceInternalUrl‘s.

  • In your solution on the Control Flow tab add another Data Flow Task. Link the green arrow from the previous task to this one (so runs 2nd)

controlflow1

  • Add an OLE DB Source to the Data Flow using the Source Assistant
  • This source will be configured to get project url’s saved into our database in the last step using the following SQL command:
SELECT        ProjectId, ProjectWorkspaceInternalUrl
FROM            Projects
WHERE        (ProjectWorkspaceInternalUrl IS NOT NULL)

So it looks like;

sqloledbsource

Create a temporary variable to store URLs

  • Open the Variables window in Visual Studio (it’s one of the icons top right above the package area.
  • Add a variable called ProjectList and set the Type to Object

variableslist1

  • Now add a Recordset Destination to the data flow and link the blue arrow from our OLE Source to it
  • In the advanced properties specify the VariableName just created.

recordset1

  • On the Input Columns tab select all columns

recordset2

All other settings can be left with defaults. The Data Flow task should now look like this;

recordset

Create a Foreach loop to iterate through sites

Now we’re getting to the tricky part, SSIS enables this kind of iteration nicely, however we need to do a couple of special things to work with our OData list data, specifically update the datasource URL to the next project and extend the returned data by adding the ProjectId field of the project (for our FK).

  • Firstly add two new Variables to the package; ProjectId and ProjectUrl, both as type String

variableslist2

  • Next drag in a Foreach Loop Container and connect the green arrow from the previous item to it
  • In the Collection properties of the Foreach Loop select Foreach ADO Enumerator and specify the User::ProjectList variable created earlier

foreach1

  • Next under Variable Mappings specify our other variables in the following index order: Index 0 - User:ProjectId, Index 1 - User::ProjectURL, (Note this order is important and used in the Foreach below!)

foreach2b

Update the SharePoint Listdata datasource URL

The first action inside our Foreach loop must be to update the connection string property of our SharePoint listdata dataconnection created at the beginning. Don’t run away but we’ll do this using a Script Task and a few lines of code.

  • Drop a Script Task inside the Foreach loop container
  • In the properties make sure the ScriptLanguage is Visual C# 2010
  • Then add a ReadOnlyVariable pointing to User::ProjectUrl

scripttask1

  • Click Edit Script and we’ll be replacing the Main() block with the following code:
public void Main()
{
	// TODO: Add your code here
	String ProjectURL = (String)Dts.Variables[0].Value;

	Dts.Connections["OData_SharePointListData"].ConnectionString =
		System.Text.RegularExpressions.Regex.Replace(
			Dts.Connections["OData_SharePointListData"].ConnectionString,
			"https://.*(?=/_vti_bin/listdata.svc)",
			ProjectURL);

	Dts.TaskResult = (int)ScriptResults.Success;
}

IMPORTANT NOTE: In that code we reference the OData_SharePointListData connection by name which we created way back in the first step! So if you used another name make sure you update both references to that name in the code.

Save and close that script window and we can move on to the last couple of steps.

Getting project site data from each site

  • Add a Data Flow Task after the script task inside the Foreach loop container, and link the green arrow from the script task to the data flow task

foreach3

  • In the Data Flow tab for this item, first add an OData Source
  • Configure the source using the OData_SharePointListData source created and specify the required collection (I’m using Risks for the sake of this demo)
  • Select the desired columns and preview to test

risksOdata

Note here that I’m able to see the values in the test project site that I specified right back at the beginning, however when the solution executes the URL will have been updated before this step. Whenever you edit this solution in the future this site must be valid and accessible else you will get errors here.

Next we need to add our project server ProjectId to the data coming from OData so we can use it is a foreign key in the destination database table.

  • Drag a Derived Column to the canvas link it 2nd and edit the properties
  • Add a Derived Column Name as ProjectId 
  • From the Variables and Parameters drag the User::ProjectId into the Expression box
  • Lastly (importantly) from the Type Casts list drag the (DT_GUID) item to the start of the expression so it is exactly: (DT_GUID) @[User::ProjectId]

derived1

  • Now finally use the Destination Assistant to create and configure our Risks database table where the data will be saved
  • Configure the mappings as required, and double check that ProjectId has automatically been mapped

risksdest

We’re just about done, that Data Flow should now look like this:

sitedataflow

Final touches

The solution should now run and work as expected, with only one catch, each time you run it it will append all of the data again, so for a quick (hackish) solution to that the final step here is to add a step to the very beginning to delete existing data in our report database.

  • On the Control Flow tab add one more Execute SQL Task to the top of the canvas and make our first GetProjectData follow it.
  • Edit the properties and select our temp database ProjectOnline_OData as the Connection
  • Then set the following as the SQL Query (Note: Don’t use Build Query as that won’t let you cut and paste, just paste it into the SQLStatement field
DELETE FROM Projects WHERE 1=1
DELETE FROM Tasks WHERE 1=1
DELETE FROM Risks WHERE 1=1
  • No other options need be changed

cleandb

Done, test it out and it should run and look something like this when run:

debugresults

Final words

That was easy, wasn’t it? Aren’t we all glad to not need the SSRS SharePoint list datasource anymore! *ahem* [sarcasm]

Well now that you have your data-warehouse of project data and all artifacts, that should make your SSRS report writing not only simpler but actually much faster performing.

Download Source

Download this full solution here and any post any questions below.

Download

 

ProjectOnline_OData.zip

 

To use this you will need to update all your data-connections and recreate the destination Tables (as per steps above).