In Part 1 we looked at the basics of connecting to web service and retrieving the metadata. Part 2 looked at how to create a datasource first class object (FCO) and the quirks of the "well known" properties (thanks to Wade Dorrell for the feedback and for raising a usability bug for the next version). In this post we'll look at how to create one of the other FCO's - in this case a report though as you can imagine the process for creating a KPI (the other "singular" FCO) is similar.
Once again, its not too hard to work out that we'll need to use the CreateReportView function and as before we'll create a ReportView object in our code that will get passed to the function once we've set up all the properties.
private static PPSM.ReportView CreateReport(PPSM.PmService mon)
//declare our reportview object
PPSM.ReportView rep = new PPSM.ReportView();
//set the guid for our object - this gets used as the primary key in the db table
rep.Guid = System.Guid.NewGuid();
We know how to set the "well known" properties from part 2 and it's exactly the same process here (including the identifier) so it makes sense to create a re-usable function to do this for all our FCO's
private static PPSM.BpmProperty SetWellKnownProperties(string name, string description, string owner)
//declare the three individual property types for the property array
PPSM.BpmPropertyText bpmName = new PPSM.BpmPropertyText();
PPSM.BpmPropertyLongText bpmDesc = new PPSM.BpmPropertyLongText();
PPSM.BpmPropertyUser bpmOwner = new PPSM.BpmPropertyUser();
//set some details for the properties
bpmName.Text = name;
bpmDesc.Text = description;
bpmOwner.Login = owner;
//initialise the properties array
PPSM.BpmProperty wkprop = new PPSM.BpmProperty;
//set the properties array with the three elements
wkprop = bpmName;
wkprop = bpmDesc;
wkprop = bpmOwner;
//declate a GUID and set the unique id of each property
wkprop.UniqueName = "8dd07d4d87794510afdb1f07664359bc_Element_Name";
wkprop.UniqueName = "8dd07d4d87794510afdb1f07664359bc_Element_Description";
wkprop.UniqueName = "8dd07d4d87794510afdb1f07664359bc_Element_Owner";
Lets call the function and also set the type of report (in our case an Olap Grid). All good so far....
//set the well known properties
rep.Properties = SetWellKnownProperties("My Report", "My Rep Desc", "Me");
//set the type
rep.TypeName = "OLAPGrid";
There are a couple more obvious properties that can be set (e.g. begin points and end points) but from here things get more complicated. The majority of the important properties are stored in a single XML property of the ReportView object called CustomData. This means that you'll have create and manipulate an xml document in your code that matches exactly the format required.
In practical use this may preclude using purely the web service to create FCOs completely from scratch. As an alternative workaround you could start from a template report stored on the server and then use and update the custom data property from that template. In reality of course you are unlikely to be automating the creation of reports completely from scratch - much more likely to just be updating the report datasource for example. Just for the sake of proving the concept, this is what we'll do in our case.
The other workaround is to code against the object model in Microsoft.PerformancePoint.Scorecards.Client.dll rather than the WSDL of the web service. This exposes the properties more clearly and is, as I understand, what happens behind the scenes in Dashboard Designer. IMHO this slightly defeats the object of a web service but I'm sure this will be rectified going forward.
As we've already gone through the process of creating a new reportview object we'll carry on down that path for this example. Of course it might be simpler in reality to grab an existing reportview object using GetReportView and simply change the required properties and assign a new guid. You use the CreateReportView function regardless of whether you are creating or updating - the existence of the object's guid in the underlying database table will define which happens.
This function returns the customdata for an object
private static string GetCustomData(PPSM.PmService mon, string ReportID)
Guid g = new Guid(ReportID);
PPSM.ReportView rep = mon.GetReportView(g);
Next we need to declare an XMLDocument to store the customdata and call our function passing in the Guid of a known report. I'm sure there are much cleaner ways to work with XML objects but it's Friday night!!
//declare an xmldoc to hold the customdata
XmlDocument custData = new XmlDocument();
//you'll need to find the id of a similar type report from your PPS Monitoring database
//load the custom data from ana existing report into an xmldocument
Now obviously using the GUI of Dashboard designer it's easy too grab the data source by name. In code you would need to come up with a clever way of doing this or just work with knowing the guid of the datasources you want to use for the report. It's possible that you may have just created or updated a datasource as part of your application.
//update the datasource elements of the customdata - quick and dirty hack!
custData.DocumentElement["QueryData"].ChildNodes.InnerText = "3da20613-0a02-4e6f-ad17-6a42ec4d6b62";
custData.DocumentElement["QueryState"].ChildNodes.InnerText = "3da20613-0a02-4e6f-ad17-6a42ec4d6b62";
//and now lets pass the edited customdata to our new reportview object
rep.CustomData = custData.InnerXml.ToString();
Finally pass to the web service
//finally lets pass our modified reportview object to the webservice
Updated project is on the download site as usual and includes an example of the customdata xml for a reportview object. Thanks as usual to Wade, Alyson and Tim at MS for taking the time to answer my DFQs!
Here's a quick one for creating an end date column when you only have an effective date column in your data source to work with. The secret is to join the table to itself with a greater than join on the effective date. You then use the effective date from your joined table to give you your end date.
--Add a default end date for current record and subtract a day so the end date is one day before the next start date
,ISNULL(DATEADD(d,-1, DT2.EffectiveDate), '31/12/2099') AS EndDate
dbo.PriceTable AS DT2
RIGHT OUTER JOIN
dbo.PriceTable AS DT1
DT2.AccountID = DT1.AccountID
DT2.EffectiveDate > DT1.EffectiveDate