Creating a Free Form Text Search filters for Performance Point

While Performance Point offers a wide array of “Out of the box” filter solutions, one of the holes that we found was the inability to perform a user input search.  While working on a project, we encountered a situation where the members we wanted to list blew way past the upper limit of the PPS filters.  While one solution would be to increase the upper limit, this leads to performance hits on the pages as the reports wait until all of the selected members are loaded.

Our solution: A free form text box that would perform a search against dimensions / members in our cube, and in turn apply the searched item as a filter to the dashboard report.

I used a blog post by Joe Hayes of the PerfomancePoint Product team as an initial stepping stone (as well as the inspiration), as well as the PPS SDK found here.

The solution requires a two part development effort, on one hand you need to create a PerformancePoint Dashboard Designer plug-in in order to create the filter and add it to your dashboards.  The second part requires that you build a custom web server control.

 

The screenshot above has 3 projects included in one solution, the third being an installer to be used to help automate the rollout of the filter to multiple machines, but that is a topic for a different post.

The creation of the PerformancePoint Dashboard Designer plugin is detailed by Joe Hayes, and in the SDK, but more than that, Joe gives you a solid code base to begin building your own filters from. When everything works right and your plug-in loads, you will have a new filter template available to you in Dashboard Designer.

One of my biggest sticking points was understanding what exactly the GetDisplayData and the GetMessageData methods in the DataProvider did, and how they facilitated the communication between not only Dashboard Designer and SharePoint, but the fitler and the report item itself. 

GetDisplayData builds the datatable structure that the filter/server will use and expect, as well as define which columns are in the table are to be displayed in the “Source Value” selection box when you link the filter to the dashboard item.  GetMessageData is the actual messenger of the system, it builds the datatable (by calling GetDisplayData) and fills it with the members that are to be passed to the report item.  What this means is that all your “search” logic is to be built into or called from this method.

Joe and the SDK give you a good base for the Dashboard Designer part, the web server control is a little more of a challenge.  While digging through the SDK I never found a section for “Building your own selection controls”, but when I was reading through the Web.Config, I noticed that the inherent PPS selection controls were registered under “CustomReportViews”, which Joe verified what how they were implemented.  This means that the DLL for the web server control is to be registered under “CustomReportViews” in the Web.Config of the parent SharePoint site.

The web server control is really nothing more than a class that overrides the Render method of a page.  This method is passed an HTMLTextWriter from which the raw HTML of our search box is loaded and written to the page.  I used as StringBuilder to do this, as it allowed for a logical way to stick together a long string of HTML code, as well as the JavaScript that puts together our search value and triggers the post to the report item.  This being said, the only real interaction with the report item comes in the onClick event of my button where it calls:

“PPSMA.DashboardController.get_instance().updateParameter(‘{0}’,'{1}’,[txtVal]);”, base.DashboardItemId, base.BeginPoints[0].ParameterUniqueName);”

where “[txtVal]” is the value from my textbox.  It is worth noting that the value passed to the dashboard has to be a JavaScript array, even if it is only one value.

Through the web server control you can load any raw HTML controls, but you are not able to utilize ASP.Net controls because of where in the page lifecycle the control is being inserted. 

 

Development notes and suggestions.

The very first suggestion that I might give is to develop on the server.  This sounds a little weird, but I have 2 reasons for this:

  1. Both projects require references to some PerformancePoint DLLs that are loaded on install
  2. Debugging is much easier locally rather than messing with Remote Debugging.  It’s so much easier just to attach to a local process.

There are a couple of tools out there that will ease development as well, the first being CriTrace which is detailed here.  One thing to note, by changing the value from “friendlyfile” as it suggests in the blog post to “spy”, SharePoint will print out the results actually on the page versus on a file that you have to dig to find.

Also this blog details how to create an ASPX page that does nothing more than print out the parameters that are passed to the report item.  This may sound simple, but it was the best tool I could find for figuring out exactly why my filters were broken, and what the inherent PPS controls were passing that I was not. 

And finally download Reflector and read the PerformancePoint DLLs.  They can and should be your template / insight into how to build your own filters.

Hope this points you in the right direction, and please feel free to get a hold of us if you have any questions!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s