none
How do you Export/Save Scorecards/Reports to Excel/PowerPoint via SDK/Webservice? RRS feed

  • Question

  • Is there ANY way to export a scorecard or a report object to Excel or Powerpoint via a built in SDK method or via the webservice?

     

    I have been trying to export objects to my filesystem programatically, but not having much luck.  I've seen this namespace: Microsoft.PerformancePoint.Scorecards.Common.Export but again I don't understand what it provides me, if anything.

     

    As far as I can tell, the only way to do what I am hoping to is to write (lots) of custom code to essentiallly create the excel/powerpoint file yourself given your scorecard or report object.  I've seen this article which could in theory do what I want to: http://blogs.msdn.com/performancepoint/archive/2007/08/22/creating-excel-and-powerpoint-documents-using-performancepoint.aspx

     

    However, it seems odd to me that a simple "Export" or "Save" method wouldn't already have been provided as part of the SDK.


    Can someone verify if my understanding is correct, or explain to me how I can do what I'm hoping to in an easier/less code intensive fashion?

    Thanks!

    Thursday, November 8, 2007 3:26 PM

Answers

  • Hey engy79, good to see you posting.

     

    Yes, there is a way to export a scorecard or a report object to Excel or PowerPoint by posting to OfficeExportPage.aspx.  That functionality has always been there in PPS 2007, but the documentation on how to use OfficeExportPage.aspx has been missing.  =(

     

    You don't want the article "creating-excel-and-powerpoint-documents-using-performancepoint.aspx".  That article is for using PPS 2007 apis to programmatically generate xlsx and pptx documents from scratch (i.e. not based on PPS 2007 objects).

     

    The article you want is hot off the presses (just posted today):

    http://blogs.msdn.com/performancepoint/archive/2007/11/08/using-pps-m-a-reports-to-create-an-excel-powerpoint-report-deck.aspx

     

    That explains how to post to OfficeExportPage.aspx, which will convert a PPS 2007 scorecard or report object to an xlsx or pptx file and return it to you.

    Thursday, November 8, 2007 6:48 PM

All replies

  • Hey engy79, good to see you posting.

     

    Yes, there is a way to export a scorecard or a report object to Excel or PowerPoint by posting to OfficeExportPage.aspx.  That functionality has always been there in PPS 2007, but the documentation on how to use OfficeExportPage.aspx has been missing.  =(

     

    You don't want the article "creating-excel-and-powerpoint-documents-using-performancepoint.aspx".  That article is for using PPS 2007 apis to programmatically generate xlsx and pptx documents from scratch (i.e. not based on PPS 2007 objects).

     

    The article you want is hot off the presses (just posted today):

    http://blogs.msdn.com/performancepoint/archive/2007/11/08/using-pps-m-a-reports-to-create-an-excel-powerpoint-report-deck.aspx

     

    That explains how to post to OfficeExportPage.aspx, which will convert a PPS 2007 scorecard or report object to an xlsx or pptx file and return it to you.

    Thursday, November 8, 2007 6:48 PM
  • Thank you!  This is exactly what I need!!

     

    Thursday, November 8, 2007 8:10 PM
  • Actually, another question in relation to this concept.


    Can you export a Scorecard/Report view for a given filter Selection?  For example, I want to export my scorecard at the top level of my organization filter, and also a level down in the hierarchy.  Can I programatically set the filter level for the scorecard that is exported?

     

    Thursday, November 8, 2007 8:19 PM
  • engy79,

    Your "attention to detail" is rapidly approaching legendary status.  Actually, I got the same question from another blog reader at almost the same exact moment.

     

    I hate to sound like a broken record, but the answer is, "yes, it has always been possible to do this in PPS 2007, but it is undocumented".  I can give you quick, incomplete glimpse of how it works.

     

    For the benefit of other readers, extract the Program.zip from my blog http://blogs.msdn.com/performancepoint/archive/2007/11/08/using-pps-m-a-reports-to-create-an-excel-powerpoint-report-deck.aspx

     

    In the Program.cs file, I build a url:

     

                        string dashboardItemUrl = null;
                        dashboardItemUrl += serverUrl + "/Preview/res/OfficeExportPage.aspx?";
                        dashboardItemUrl += "dashboardId=" + dashboard.Guid.ToString() + "&";
                        dashboardItemUrl += "dashboardItemId=" + dashboardItem.Guid.ToString() + "&";
                        dashboardItemUrl += "resourcePath=&";
                        dashboardItemUrl += "targetControlId=canbeanything1001&";
                        dashboardItemUrl += "targetControlType=" + webControlLookupKey + "&";
                        dashboardItemUrl += "proxyId=canbeanything1001&";
                        dashboardItemUrl += "beginPoints=&";
                        dashboardItemUrl += "exportFormat=" + officeDocumentType + "&";
                        dashboardItemUrl += "customExportSettings=&";
                        dashboardItemUrl += "inboundParameters=";

    If the inboundParameters are properly set to contain the xml representation of the filters, then the dumped xlsx and pptx files will be properly filtered.  This totally works, even if the filters alter the member slicers, even if the filters parameterize the post formula.  Don't get me started on how challenging this was to code the first time around.

     

    Challenge #1 here is that it is difficult to programmatically generate the xml representation of the filters.  What I recommend you do is to turn on CriTrace on the server (another feature that I wrote).  After that, all visits to the dashboard on the server to cause inbound filter information xml to be dumped to file.  You can then browse to the dashboard in IE, change the filter values to values you'll need in the export.  Then go back to the server and mine the dumped filter xml and use it to set the value for

     

                        dashboardItemUrl += "inboundParameters=";

    which is blank in the Program.cs code sample.

     

    Challenge #2 here is that the value for inboundParameters can get to be very long, and has to be properly escaped.  This means you have to use HTTP-POST to make the request, HTTP-GET won't cut it (it has length limitations).  But done properly this does work.  (This is the same way that the product works now when you select 'Export to Excel / Export to PowerPoint' manually in the face of the browser.)

     

    So, engy79, this is what I see as the next step.  Since the CriTrace is also undocumented (as far as I know), I feel like I owe you another blog entry on it.  (The CriTrace feature helps more things than just 'Export to Excel / Export to PowerPoint'.)

     

    The blog entry may take some time.  If you (or any of you readers out there) *can't wait* for the blog entry to appear, email me privately at ericfr@microsoft.com and I will send you an Outlook format messages (*.msg files) on the subject.  Otherwise, you can wait for the blog entry.

    Thursday, November 8, 2007 9:52 PM
  • engy79,

    Thanks for being patient.  Please see my new blog entry "Filters on PerformancePoint Server 2007 M&A Excel, PowerPoint Report Decks" at...

    http://blogs.msdn.com/performancepoint/archive/2007/11/12/filters-on-performancepoint-server-2007-m-a-excel-powerpoint-report-decks.aspx

    Monday, November 12, 2007 4:57 PM