Omicron Llama

Coding all day, every day.

Creating Custom PerformancePoint Reports in C#

(Concepts here are discussed around SharePoint 2010 but should equally be applicable to SharePoint 2013. All concepts here apply only to full-trust farm solutions, and cannot be done at all with Sandboxed solutions or with the App model)


Occasionally you might give given the requirement (as a developer) to provide the ability to insert a chart in PerformancePoint that cannot be done with any other technology available to us (like SSRS, Excel Services, or even the native PerformancePoint charts).

An example of this (and this is a real-life example) was the scenario where a customer wanted a chart where their users could select the axes for the chart at run time, but really this could be any kind of chart or some other data display that just isn’t available within the  regular Excel Services or SSRS capabilities. You’ve probably been through the spec and know it’s possible, or even trivial, to create the type of display that you need using standard .NET Framework controls (such as WebParts, etc).

However, in order to make this a real part of PerformancePoint Server, including the ability to use this chart within Dashboard Designer for both design and deployment purposes, we need to make use of the PerformancePoint Services Code Samples. These are already available on MSDN and they are documented. However I found the documentation was a bit dense and difficult to read for particular scenarios.

The MSDN Documentation – a critique
The SDK for PerformancePoint (not the official title) describes how to create custom Tabular Data Sources, custom Filters and custom Reports. At this stage I don’t think it’s possible to create custom Scorecards. Note that while the MSDN Documentation refers to these custom objects as ‘Extensions’, the are really distinctly separate and new objects (whose underlying functionality inherits from the custom type you’re defining), each are individually available within Dashboard Designer and are individually configurable.

Another thing to point out is that you may see references to ‘FCO’ or ‘First Class Objects’. This refers to anything in PerformancePoint, such as DataSources, Charts, Filters, Scorecards, etc. The SDK does not let you create new FCO types, but it lets you create new FCOs that extend existing ones. For example, there is an FCO type of ReportView, and PerformancePoint out of the box lets you create new SSRS Report Views (otherwise known as Analytical Report Views), Excel Services Report Views, and even a ‘Web Page Report View’ (which is really just an IFrame that can be added to a PerformancePoint Dashboard). The sample project linked at the end of this article will create another type of Report View which we can use in Dashboard Designer.

One of the things I disliked about the documentation on MSDN about this was the deployment model. The steps tell you to ‘sign the assembly’ and then ‘deploy to the GAC’, as well as putting the Editor ASPX pages directly in the LAYOUTS directory in your 14 hive. These are all parts which are handled for you if you use the Visual Studio SharePoint project template. The only part of the deployment you will have to do manually is modifying the web.config file of the Monitoring Server endpoint. The attached solution contains a ReadMe.txt file in the Configuration folder describing how to do this to enable the functionality within that solution. The attached solution is also a SharePoint Project, which should easily be deployed to your development environment.

Another point is that on this page (http://msdn.microsoft.com/en-us/library/ee557288(v=office.14).aspx) there is a line towards that end that calls  SPDataStore.GlobalDataStore.GetReportViewForExecution(elementLocation); however the method here appears to be an Extension Method and none of the instructions tell you what assembly it’s in, or even if it still exists (as I couldn’t find it). In my code sample, I have replaced this method with just GetReportView() and this seems to work just as well.

The Nuts & Bolts
So, despite the complexity of the documentation or the poor deployment model described therein, creating custom ReportViews for use in PerformancePoint Server is actually very straight forward.

Essentially, there are only two parts to this (at least, only two parts to the PerformancePoint specific side of things. Your charting or display requirements could be more complex, but the actual bits to get it working in PerformancePoint only require these two bits).

You need a Renderer class and an Editor page.

Do pay attention to the details on creating these. Use the code samples they provide (along with the Helper classes) as templates for your own customisations. Doing so is a recommended practice as documented on MSDN. All of the helper classes provided on MSDN are included in my sample solution. I have modified the Renderer class to simplify the code to make it somewhat easier to read, although the sample code on MSDN will display every detail about the filters connected to the report.

The Editor Page is a basic ASPX page, but this could just as easily be a SharePoint Application page, as long as it performs the necessary tasks to the underlying PerformancePoint object (in this case, the ReportView object).

When using the Editor Page the make changes to your Report, and you’ve clicked ‘OK’ and returned back to Dashboard Designer, you will have to Refresh Dashboard Designer in order to load the changes you’ve just made into Designer. This is because the Editor Page lives in SharePoint and isn’t a series of Forms in a wizard like the native reports. This is a minor annoyance but once it becomes habit you tend not to notice as much.

Within the Renderer class, you’ll notice I’m explicitly checking for and displaying the value of the MemberUniqueName and MeasureUniqueName columns (the latter is a custom one I’ve added into Tabular filters I’m using elsewhere in my environment). I’ve yet to figure out how to get the value of whatever column was specified at run time, though this must be available somehow (I’ve just yet to find out where). Modify the code as you need, or if you can, discover how to get just what was specified in Dashboard Designer.

When developing your custom charts, do keep in mind maintainability and try to keep your code lightweight, and reduce dependencies. Try not to do all of the logic to draw the charts within the Chart Renderer class. Do your heavyweight logic within a separate class, such as a WebControl class or similar, then simply use the Chart Renderer to instantiate your separate class, and call the RenderControl method of that instance. This should keep your PerformancePoint-specific code more maintainable.

EndPoints
A note on EndPoints – the way I have implemented EndPoints is to simply store a semi-colon separated list of EndPoint names in the reports CustomData property, which will be used to dynamically generate the EndPoints to which you can attach filters. The ReportView class which you have to use does not seem to allow adding extra properties (Though you may be able to inherit the ReportView class and add your own, I have not tried this, but I’m not certain if the extra properties you add will be serialized – if they do then that is excellent – just extend the ReportView class into your object model and add the properties you need).

Like I said earlier, you would ideally separate the logic for rendering your custom report away from the custom Renderer or Editor classes. This separate logic would have it’s own requirements for how to draw the content, and what input data it needs (such as the path to an XML file perhaps). Your custom logic can then somehow expose the EndPoint information to dynamically create EndPoints in the Report (see CustomReportEditor.cs Line 236 for an example of generating the EndPoints).

Note that in the Renderer class (in my example) there is little reference to the EndPoints themselves, rather the ParameterMessages object which contains the filter values that have been passed in.

If you don’t want to inherit ReportView or can’t, then you can use the ReportView.Properties collection to create, store or read Properties for the report instance. These can be viewed and edited in Dashboard Designer on the Report Properties page. Keep in mind that you will need to cast the Property you want to create/edit/read to the correct BpmProperty type, and there are some available for storing different data types such as Text or DateTime. Try to use the correct one so your property values are correctly serialized.

Do let me know if you can’t get the Sample Code to work right, or if you spot any problems in the sample I’ve given.

Resources:
Link to my code sample (CustomPPSReport.zip): https://skydrive.live.com/redir?resid=C0F61214D82AC938!1809&authkey=!ACtgiRr81oxM-fs

Create Report Extensions for PerformancePoint: http://msdn.microsoft.com/en-us/library/bb837254(v=office.14).aspx

PerformancePoint Services Code Sample: http://msdn.microsoft.com/en-gb/library/ee558401(v=office.14).aspx

6 thoughts on “Creating Custom PerformancePoint Reports in C#

  • Tim says:

    I am having problems with the endpoints on the custom reports. I can create the endpoints, but when I try to connect a filter to the report view I don’t see the custom report in the Send Values To dropdown in Connection dialog in Dashboard designer. When the report view renders there are no values in the BIDataContainer.ParameterMessage collection.

    I am attempting to use this sample in SharePoint 2013.

    Any ideas?

  • ISTHMUS says:

    Is it possible to implement the code on a remote machine where there is no sharepoint and PPS .

    • Jimmywim says:

      You can certainly implement the code with the libraries copied over to a local machine but you will not be able to debug the code nor correctly be able to test it without a complete installation.

      As always, do not develop against or debug on a production environment, do all your custom development on a non-production copy of your SharePoint farm.

  • colorita says:

    I cannot find the “PerformancePoint SDK Service Reference Samples” under http://code.msdn.microsoft.com/ppsSdkRefSample. (See your reference, PerformancePoint Services Code Sample: http://msdn.microsoft.com/en-gb/library/ee558401(v=office.14).aspx . The PPS SDK samples is not complete)

    Do you have a copy of this SDK samples?
    Thanks in advance

  • Ibrahim Ali says:

    can I create cusom excel report that impersonate credentials?

    the scenario that I have is powerpivot excel workbook in a site doesn’t allow anonymous access, as Excel service doesn’t work with anonymous, but I need to make it available for public, so I created another anonymous BI site and used dashboard designer to create excel report to that excel workbook in the other site, the problem still when I access the dashboard page that includes the excel report, I am prompted for user name and password.

    appreciate your feedback

Leave a Reply

Your email address will not be published. Required fields are marked *