Creating a Custom Dashboard Filter for PerformancePoint Server 2007 ~ Code Sample


Joe Hayes  (josehay@microsoft.com)

Microsoft Corporation

February 2008

Applies to:
Microsoft Office PerformancePoint Server 2007


Summary: This article contains a custom dashboard filter plug-in for Microsoft Office PerformancePoint Server 2007.

Sample Code File SQLQueryParameterSample

Introduction

You can create a custom dashboard filter plug-in for Microsoft Office PerformancePoint Server 2007. Filters let dashboard users refine the data that displays in scorecards and reports in a PerformancePoint Server dashboard. The custom filter sample in this article adds a “SQL Query” filter that works with ODBC data sources. The filter is populated with the results of a SQL statement or stored procedure.

The sample implements a custom filter data provider and wizard pages for building the filter. The data provider assumes that column 1 of the SQL result set is the key and column 2 is the display name. If there is only 1 column in the result set it is used for both key and display name.

Note: This sample filter does not contain a web control or implement filter persistence.

Instructions for Use

Prerequisite: PerformancePoint Monitoring Server and Visual Studio must be installed.

1. Unzip the file.

2. Follow the instructions in the INSTALL.txt file in the code sample.

3. Open ODBCSampleComplete.bswx and click Publish All on the Home tab.

6. Browse to your new dashboard on the web. When you change the filter, the data on the dashboard updates.

7. Now that you’ve built this sample from code and deployed it, use it as a model to create your own custom filters.

Conclusion

You can use the code sample as a model to create your own custom filters for Microsoft Office PerformancePoint Server 2007. For additional documentation surrounding this sample, see the “Dashboard Filters” section in the PerformancePoint Monitoring SDK on MSDN: http://msdn2.microsoft.com/en-us/library/bb836266.aspx


Comments (28)

  1. Joe Hayes ( josehay@microsoft.com ) Microsoft Corporation February 2008 Applies to: Microsoft Office

  2. Ronen says:

    Hi, i needs to filter the kpi data ,but the kpi’s are from different data sources.

    Is there away to use one filter for 2 data sources  (the filtered dimensions has the same data at the 2 datasourdces).

  3. josehay says:

    Hi Ronen,

    This can be done. You simply need to filter on something that is valid in both data sources. For example, your filter could provide a list of member unique names. Assuming these member unique names the filter provides are valid in both data sources, you could use them to filter your KPIs.

    Hope this helps,

    J

  4. ivonne.roche@gmail.com says:

    Hi,

    I tried installing the sample.  When I get to the part of opening the sample complete .bswx file, I get an error about workspace is not valid.   I go ahead and change the workspace to a valid one, but the there is no dashboards, kpi’s, or any scorecards.

    Can you help?  I will like to be able to use the sample for a project I’m working on.

    Thanks in advance,

    Aixa

  5. MSDNArchive says:

    This blog entry covers implementing and using a custom filter designer and custom filter data provider in a development environment. Once you get this code working in your local development environment and you want to publish your custom filter so that all your users get your bits automatically when they visit Monitoring Central, then follow the steps under

    "How to: Install filter Designers" in the article:

    [url]http://msdn2.microsoft.com/en-us/library/cc159441.aspx[/url]

    and the steps under "How to: Install Data Providers for Filters" in the article:

    [url]http://msdn2.microsoft.com/en-us/library/cc159503.aspx[/url]

    Joe Hayes (josehay@microsoft.com)

  6. josehay says:

    Hi Aixa,

    Send me an email at josehay@microsoft.com and I can send you an updated .bswx file.

    Thanks!

    Joe Hayes

  7. I am fortunate to have a colleagues that are contributing to my Blog. Time does not always permit me

  8. abirb says:

    I was in process of creating a custom filter provider taking the cue from your source , actually i need to expose the Parameter BeginPoints and EndPoints such that I am able to create cascade filters.

    I would be great to receive any pointers or help on this matter ..

    Thanks in Advance

    Abir

  9. josehay says:

    Hi Abir,

    Send me an email at josehay@microsoft.com and we can take this offline.

    Thanks!

    Joe Hayes

  10. yeshpal@hotmail.com says:

    Can you please email me the updated .bswx file.

  11. Debasishp says:

    am working on cascading prompts. I tried to deploy your above sample.I did all the steps mentioned in the install.txt and also followed the msdn links which you said about,I still faced ceratin errors regarding the same:

    1. When I open Report "AW Grid" it says that "data source is not available"

    2. In the dashboard I do not see the SQL Filter , when I try to create the filter I do not see the SQL Filter in the template list.

    Please help me on the same. Please consider this as an urgent basis. Your answer would be highly appreciated.

    I would send to you an detailed mail about the steps, I carried.

    Many Thanks,

    Debasish

  12. josehay says:

    Hi Debasish,

    Send me an email at josehay@microsoft.com and we can take this offline.

    Thanks!

    Joe Hayes

  13. While Performance Point offers a wide array of "Out of the box" filter solutions, one of the holes that

  14. Amit C says:

    I am able to create filters in PPS using Dashboard designer and applying those filters in Score Card (SC). My further requirements are

    – How to invoke PPS elements from ASP.Net (No Sharepointy) page

    – how to pass context to my dashboard/SC so that i show only contextual SC to the user. I don’t want to show generic SC with filter options. If it is possible from ASP.Net, great otherwise how to do this from Sharepoint.

    Could someone please help.

    thanks,

    Amit

  15. josehay says:

    Hi Amit!

    Send me an email at josehay@microsoft.com and we can take this offline.

    Thanks!

    Joe Hayes

  16. RobertLambrecht says:

    I think we have a custom filter with a lot of flexibility that you may want to check out:

    http://robertlambrecht.spaces.live.com/blog/cns!1738EAC7F6359C6D!2110.entry

    or

    http://robertlambrecht.com and browse the BI section

  17. Souhdi says:

    Hi Joe,

    I have the same question as Amit and Debasish, is there any answers and help.

    HAssan

  18. joephayes says:

    Unfortunately, cascading filters are not supported in PerformancePoint 2007. However, you can create cascading parameters using Reporting Services. See this link – msdn.microsoft.com/…/aa337169.aspx

    Hope this helps,

    J

  19. Athulya says:

    I tried following the instructions on the install.txt file and added config entries to all the required files. however, when I open the dashboard, it gives me a 'Object reference not set to an instance of the object' error. Also, the filter template doesn't appear when I try to create a new filter in another dashboard.

    Could you please point me to what I could be missing?

    Thanks!

    Athulya

  20. joephayes says:

    Athulya,

    Let’s troubleshoot this step by step.

    1. Did the project compile without errors?

    2. If you built without errors, can you verify that SqlQueryParameterProvider.dll was added to the GAC (through gacutil.exe or Explorer)

    3. Search on PscBuilder.exe. There should be a copy of pscbuilder.exe.config in the same directory. Open pscbuilder.exe.config and verify that this line was added to the ParameterTemplateExtensions section:

    <ParameterTemplateExtensions>

                   …

                   <add key="SqlQueryParameterTemplateFactory" value="SqlQueryParameterProvider.SqlQueryParameterTemplateFactory, SqlQueryParameterProvider" />

                   …

    </ParameterTemplateExtensions>

    4. Save the changes to pscbuilder.exe.config and run PscBuilder.exe directly. Create a new dashboard and a new filter. Do you see the “Sql Query” filter type?

    J

  21. mdxfan says:

    Hello Joe,

    This posting is great. We have successfully implemented a Custom Filter for PPS 2007. Since Microsoft doesn't support migrating Custom Filters developed for PPS 2007 to PPS 2010 I'm facing lot of challenges as to what should be correct path to follow to get this Custom Filter to work as it did on PPS 2007.

    I would like to know whether you have posted any articles on how to do this same on PPS 2010.

    Thanks in advance.

  22. joephayes says:

    See the posting "How do I create or migrate from PerformancePoint 2007 custom extensions for Dashboard Designer in PerformancePoint Services 2010?" on the PerformancePoint team's blog (see blogs.msdn.com/…/how-do-i-create-or-migrate-from-performancepoint-2007-custom-extensions-for-dashboard-designer-in-performancepoint-services-2010.aspx )

    Short version – you can re-use most of the server side code (data providers and renderers) by updating method signatures and references. You will need to re-write the custom filter editor. Let me know if you have more specific questions.

    J

  23. mdxfan says:

    Thanks Joe for your reply.

    If I tell you briefly how the Custom Filter was done in PPS 2007, there are three main components.

    1. Control (Renderer, etc)

    2. Wrapper for PPS Web Service (PmServer.GlobalServer.GetParameterDisplayData)

    3. Parameter Provider ( Plus Data Provider) – this is pretty similar to your sample code provided on this post

    There are no web services exposed in PPS 2010 (like it did in PPS 2007), so I guess I'll have to call the correct APIs directly from the code.

    The other challenge I'm facing is, in PPS 2007, based on the web service that was exposed I'm making a SOAP request and make a AJAX call from the Custom Filter control and then pass the SOAP request to the server (No. 2 listed above – wrapper for Web Service) to get data from the underlying data source (in my scenario it's an Analysis Services Cube.) Because of this, it's completely a re-write to get the communication part to work.

    Re: Filter Editor, I got this working fine. Control registration and creating an instance of the control are also working fine.

    Not sure whether I explained my situation well. Really appreciate any help that you can give.

    Thanks in advance.

  24. joephayes says:

    Send me an email at josehay@microsoft.com so we can take this offline.

    Thanks,

    J

  25. madams999 says:

    We have successfully implemented this update to Dashboard Designer and can create ODBC filters. But when we preview the dashboard, nothing is returned from the server. Can you point us in the right investigative direction? Thank you in advance,

    M & G

  26. joephayes says:

    I would start by looking in the PPS server's application log. The error messages the server logs can often be very helpful in finding out what is wrong.

    HTH,

    J

  27. madams999 says:

    I checked the log and found that the custom provider was not registered. When I run regsvr32 on SqlQueryParameterProvider.dll I get the error The module SqlQueryParameterProvider.dll  was loaded but the entry-point DllRegisterServer was not found. Make sure that SqlQueryParameterProvider.dll  is a valid DLL or OCX file and then try again.

  28. joephayes says:

    Please install the assembly to the GAC using gacutil.exe on the PPS server machine.

    Thanks!

    J