Paul Busby | SharePoint (and Project Server) Shenanigans

gantt charts in SSRS

August 17, 2012 Giles Hamson Leave a comment

This is just a quick post while I am in a meeting on how to create gantt charts within SSRS.

http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

I hope to expand on this post in the future with my own experiences.

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/08/17/gantt-charts-in-ssrs/

Paul is a an expert SharePoint and Project Server developer and is responsible for designing and implementing custom solutions on client systems using the latest SharePoint and .NET technologies.
Paul has extensive experience with SharePoint systems across all sizes of implementation, ranging from small to large farms and has an excellent understanding of all the elements of SharePoint.

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Categories: Paul Busby, Work Tags: Development, Project Server, SharePoint

Project Server workspace ProjectUID filter web part

August 10, 2012 Giles Hamson Leave a comment

This is a great post containing a new filter web part that will send the projectGUID from a project server workspace to a web part.

http://epmsource.com/2011/10/09/projectuid-filter-provider-for-reporting-services-viewer-web-part/

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/08/10/project-server-workspace-projectuid-filter-web-part/

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Categories: Paul Busby, Work Tags: Development, Project Server, SharePoint

Generate unique X and Y coordinates or grid references from a row number

August 9, 2012 Giles Hamson 3 comments

I was creating an SSRS report and using a matrix table. I needed to be able to position data within each group by. To do this i needed create a X and Y number for each item where the 2 together would be unique.

This is an example of what i wanted

Row number	x	y
1	0	1
2	0	2
3	0	3
4	1	1
5	1	2
6	1	3
7	2	1
8	2	2
9	2	3

And here is the SQL that creates it

SELECT  ROW_NUMBER() over (ORDER BY prj.ProjectName) as Row       
            , ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) / 3  as X
            ,  ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) % 3  as   Y

This took a lot longer to work out that i care to admit. So hopefully someone else will find this useful

If you need more columns before the split then simply change the 3 to be what ever number you need.

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/08/09/generate-unique-x-and-y-coordinates-or-grid-references-from-a-row-number/

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Categories: Paul Busby, Work Tags: Development, Project Server, SharePoint

New Programming Jargon

August 6, 2012 Giles Hamson Leave a comment

Love this new list of jargon used by teams around the world. Find it here http://www.codinghorror.com/blog/2012/07/new-programming-jargon.html

My favourite – possible because it happens far to often

Protoduction

A prototype that ends up in production. Heard this from a tech at the Fermi lab. He said he didn’t coin the term but had heard it used a number of times at Fermi.

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/08/06/new-programming-jargon/

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Categories: Paul Busby, Work Tags: Development, Project Server, SharePoint

Infopath “The following form template cannot be upgraded because it is not currently uploaded on this farm” #sp2010

August 1, 2012 Giles Hamson 1 comment

So what i was trying to do is upload a new form template to a clients QA system. Something that has been done tens of time since the system went live. But today I get.

Now i have never seen this message before so i instantly start googling to try and save the day.

I have to say not a lot of stuff around this error is available but there are a couple of MSDN forum posts that got me in the right direction.

The SharePoint farm in question has recently had some more servers added to the farm and what i suspect has happened is that the form files have not correctly deployed to the new servers. But to prove this i need the feature GUID that is created when you first upload a InfoPath form.

I found the quickest way to get this is to navigate to the site collection the form exists on. Go to Site settings and under Site collection Administrators select the Site Collection Features in the list you will see a feature that maps to the name of the XSN file.

Click on the Deactivate button (don’t worry there is a confirmation step). Now in the query string the feature GUID is shown. Copy this as you will need it later. You don’t need to deactivate the solution so can close the page if you wish.

I was then able to see, under Solution Management within central admin the WSP called the same GUID. Clicking on the WSP showed me that the wsp had not been deployed to all the servers in the farm. For some reason all of the other WSP in the list had all been deployed. I guess i was just lucky that this was the only one.

Now to fix the issue you need to run an STSADM command

stsadm -o uninstallfeature –id <the feature GUID you got from the query string> –force

This will remove the feature and i guess some other magic that fixes stuff.

Now the form can be upgraded without an error. Once the form has upgraded the feature is reactivated automatically (did for me anyway)

And YAY it all works again. 5 minutes job only took an hour

Hope this helps someone

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/08/01/infopath-the-following-form-template-cannot-be-upgraded-because-it-is-not-currently-uploaded-on-this-farm-sp2010/

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Categories: Paul Busby, Work Tags: Development, Project Server, SharePoint

Getting Project Server database information via C# / How to run PowerShell command in C#

July 31, 2012 Giles Hamson Leave a comment

For a long time now I have had a few scenario’s where it would be helpful for me to know what the database server and database names are of a Project Server instance. The PSI does not provide this information but there is a PowerShell command that will give you the information.

Get-SPProjectWebInstance –url <URL>

This gives me all the info a want about the instance.

So now I know I can get the info i need i just need a way of getting this within c# code.

Helpfully you can use System.Automation to execute PowerShell scripts within .NET

We start with a simple method that will execute PowerShell commands

private string RunScript(string scriptText)
        {
            string addWss = "Add-PSSnapin microsoft.sharepoint.powershell";
            Runspace runspace = RunspaceFactory.CreateRunspace();
            runspace.Open();
            Pipeline pipeline = runspace.CreatePipeline();
            pipeline.Commands.AddScript(addWss);

            pipeline.Commands.AddScript(scriptText);
            pipeline.Commands.Add("Out-String");
            var results = pipeline.Invoke();

            runspace.Close();

            StringBuilder stringBuilder = new StringBuilder();
            foreach (PSObject obj in results)
            {
                stringBuilder.AppendLine(obj.ToString());
            }

            return stringBuilder.ToString();
        }

The above code needs

using System.Management.Automation;
using System.Management.Automation.Runspaces;

NOTE: You’ll need to add a reference to the version of PowerShell you have installed in your GAC to the Visual Studio project file. To do so, you’ll open the project file as a text file and add the following line into the <ItemGroup> section:

I was able to cheat and “browse” to the assembly using the following path

C:\Windows\assembly\GAC_MSIL\System.Management.Automation\1.0.0.0__31bf3856ad364e35\System.Management.Automation.dll

Now that we have the helper method I can actually get the info I want.

string answer = RunScript("Get-SPProjectWebInstance -url http://vm641/pwa2");
string reportingDatabaseServer = string.Empty;
string reportingDatabaseName = string.Empty;

var output = answer.Split(new string[] {Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries).Cast<string>();

reportingDatabaseServer = (from x in output where x.StartsWith("ReportingServer") select x).First().ToString();
reportingDatabaseName = (from x in output where x.StartsWith("ReportingDatabase") select x).First().ToString();

reportingDatabaseServer = reportingDatabaseServer.Replace("ReportingServer", "").Replace(":", "").Trim();
reportingDatabaseName = reportingDatabaseName.Replace("ReportingDatabase", "").Replace(":", "").Trim();

Bingo I have the name of the reporting server and the database name.

I needed this months ago to auto configure a PSI extension that was written but i need it now to auto configure a feature when it is deployed.

I’ll wrap all the above code into a single class that accepts the instance URL in its constructor and then expose properties with the information.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Management.Automation.Runspaces;
using System.Management.Automation;
using System.Text;

public class ProjectWebInstanceInformation
{
    #region Fields and Properties
    public string ReportingServer { get; private set; }
    public string ReportingDatabase { get; private set; }
    #endregion

    #region Constructor
    protected ProjectWebInstanceInformation()
    {
    }

    public ProjectWebInstanceInformation(string Url)
    {
        try
        {
            string answer = RunScript(string.Format("Get-SPProjectWebInstance -url {0}", Url));
            string reportingDatabaseServer = string.Empty;
            string reportingDatabaseName = string.Empty;

            var output = answer.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Cast<string>();

            ReportingServer = (from x in output where x.StartsWith("ReportingServer") select x).First().ToString().Replace("ReportingServer", "").Replace(":", "").Trim();
            ReportingDatabase = (from x in output where x.StartsWith("ReportingDatabase") select x).First().ToString().Replace("ReportingDatabase", "").Replace(":", "").Trim();
        }
        catch { }

    }

    #endregion

    #region Methods
    private string RunScript(string scriptText)
    {
        string addWss = "Add-PSSnapin microsoft.sharepoint.powershell";
        Runspace runspace = RunspaceFactory.CreateRunspace();
        runspace.Open();
        Pipeline pipeline = runspace.CreatePipeline();
        pipeline.Commands.AddScript(addWss);

        pipeline.Commands.AddScript(scriptText);
        pipeline.Commands.Add("Out-String");
        var results = pipeline.Invoke();

        runspace.Close();

        StringBuilder stringBuilder = new StringBuilder();
        foreach (PSObject obj in results)
        {
            stringBuilder.AppendLine(obj.ToString());
        }

        return stringBuilder.ToString();
    }
    #endregion
}