Using PPS M&A Reports to Create an Excel, PowerPoint Report Deck
Sample Code Program.zip
Eric Friedrichsen (ericfr@microsoft.com)
Microsoft Corporation
November 2007
Applies to:
Microsoft Office PerformancePoint Server 2007
Microsoft Office Excel 2007
Microsoft Office PowerPoint 2007
Summary: You can use Microsoft Office PerformancePoint Server 2007 Dashboards to create and maintain a periodically updated Excel / PowerPoint report deck. Once you've used PPS 2007 to create Dashboards containing Scorecards and ReportViews, you can use a remote, automated process to periodically generate Excel or PowerPoint replicas of the Dashboards that your users are already familiar with. This requires a minor development effort, but is very doable.
Introduction
Using PPS M&A Reports to Create an Excel, PowerPoint Report Deck is interesting if:
· Your users like the "Export to Excel / Export to PowerPoint" feature, but they dislike having to browse to each PPS 2007 Dashboard item individually and manually selecting "Export to Excel" and "Export to PowerPoint". Your users need the files, and they'd rather have a way to get the file representation of all their Dashboard Items quickly.
· Your users need to experience their PPS 2007 Dashboard Items in printed form. Having all the PPS 2007 Dashboard Items dumped to file is an easier starting point to getting everything printed.
· Your I/T department has been asked to create and maintain a periodically updated deck of Excel / PowerPoint documents based on PPS 2007 Dashboards located on a particular remote server. Your I/T department has been asked to periodically print or email those files.
Applies To
This article applies to the following cases:
· You've already installed Microsoft Office PerformancePoint Server 2007 on some server, and created Dashboards containing Scorecards and ReportViews on that server. You also have access to that server and have permissions to view the PPS 2007 Dashboards on that server. You have Visual Studio 2005 locally and can follow a few quick instructions to enable the code sample in this article.
· Right out of the box, this feature applies to PPS 2007 Scorecards, OLAPCharts, OLAPGrids, PivotCharts, PivotTables, StrategyMaps, and TrendAnalysisCharts. It is possible to extend these capabilities to other Dashboard Items, but that is a custom development effort not covered here.
· In PPS 2007, each Dashboard Item generates to a separate xlsx or pptx file. Watch for future releases of PPS for entire Dashboards dumping to a single xlsx or pptx file.
Creating an Excel, PowerPoint Report Deck
The following c# code fragment is extracted from the file attachment in this article. This is the main code that generates the Excel, PowerPoint report deck. The complete code for this is attached to this article as a file attachment.
// Substitute serverUrl with the URL that points to your
// PPS 2007 server.
string serverUrl = "https://localhost:40000";
string[] officeTypes =
new string[] { "Excel", "PowerPoint" };
PmService pmService = new PmService();
pmService.Url = serverUrl + "/webservice/pmservice.asmx";
pmService.Credentials =
CredentialCache.DefaultNetworkCredentials;
Dashboard[] dashboards = pmService.GetDashboards();
if (null != dashboards)
{
foreach (Dashboard dashboard in dashboards)
{
if (null != dashboard)
{
List<DashboardItem> dashboardItems =
new List<DashboardItem>();
GetDashboardItems(dashboard.Pages, dashboardItems);
foreach (DashboardItem item in dashboardItems)
{
foreach
(string officeDocumentType in officeTypes)
{
string processingLog =
RetrieveOfficeDocumentFromServer
(serverUrl,
pmService,
dashboard,
item,
officeDocumentType,
Path.GetTempPath()
);
Console.WriteLine(processingLog);
}
}
}
}
}
As you can see, the approach is simple. The above code connects to the PmService.asmx web service whose URL you specify. The code then enumerates over all the DashboardItems in all the Dashboards on that server, and for each of them calls RetrieveOfficeDocumentFromServer. That method is depicted below. The complete code for this is attached to this article as a file attachment.
private static string RetrieveOfficeDocumentFromServer
(string serverUrl,
PmService pmService,
Dashboard dashboard,
DashboardItem dashboardItem,
string officeDocumentType,
string outputDirectory
)
{
string processingLog = null;
string dashboardItemName = null;
string webControlLookupKey = null;
GetDashboardItemInfo
(pmService,
dashboardItem,
ref dashboardItemName,
ref webControlLookupKey
);
String permittedTypes =
",Scorecard,PivotTable,PivotChart,StrategyMap" +
",TrendAnalysisChart,OLAPGrid,OLAPChart,";
if (!string.IsNullOrEmpty(dashboardItemName) &&
!string.IsNullOrEmpty(webControlLookupKey) &&
permittedTypes.Contains("," + webControlLookupKey + ",")
)
{
try
{
// Retrieve the document, dump it to file.
WebClient client = new WebClient();
client.Credentials =
CredentialCache.DefaultNetworkCredentials;
string dashboardItemUrl = null;
dashboardItemUrl += serverUrl;
dashboardItemUrl +=
"/Preview/res/OfficeExportPage.aspx?";
dashboardItemUrl += "dashboardId=";
dashboardItemUrl += dashboard.Guid.ToString() + "&";
dashboardItemUrl += "dashboardItemId=";
dashboardItemUrl += dashboardItem.Guid.ToString();
dashboardItemUrl += "&";
dashboardItemUrl += "resourcePath=&";
dashboardItemUrl +=
"targetControlId=canbeanything1001&";
dashboardItemUrl += "targetControlType=";
dashboardItemUrl += webControlLookupKey + "&";
dashboardItemUrl += "proxyId=canbeanything1001&";
dashboardItemUrl += "beginPoints=&";
dashboardItemUrl += "exportFormat=";
dashboardItemUrl += officeDocumentType + "&";
dashboardItemUrl += "customExportSettings=&";
dashboardItemUrl += "inboundParameters=";
byte[] response =
client.DownloadData(dashboardItemUrl);
string outputFileName =
Path.Combine
(outputDirectory, dashboardItemName);
if ("Excel" == officeDocumentType)
{
outputFileName += ".xlsx";
}
else
{
outputFileName += ".pptx";
}
FileStream fs =
new FileStream
(outputFileName,
FileMode.Create,
FileAccess.Write
);
fs.Write(response, 0, response.GetLength(0));
fs.Close();
// Validate the file.
ValidateDumpedFile(outputFileName);
// Give the ok.
processingLog +=
"Successfully retrieved " +
outputFileName + ".\r\n";
}
catch (Exception exc)
{
processingLog +=
"ERROR Retrieving " +
dashboardItemName + "...\r\n";
processingLog += exc.ToString() + "\r\n";
}
}
return processingLog;
}
As you can see, the above code spins up a WebClient instance, passes it a URL that will retrieve the Excel or PowerPoint document from the server, and then dumps that document to file. In the case of this code sample, we chose to dump the files to the current temp directory, but that could be modified to suit your particular purpose.
You can modify this code to enable the following features:
Instead of sending the files to the local temp directory, send
them to a remote directory on some file server somewhere.
Send the files to be printed.
Attach the generated files to emails and send those out.
Create batched tasks that call this code periodically,
according to some schedule.
How to Use the Code Sample
This code sample attached to this article will read a PPS 2007 Server's dashboards and generate xlsx and pptx documents for each dashboard item. Follow these steps to use the code sample:
1. Extract the code sample from this article, it is carried as a file attachment, named Program.cs.
2. Using Visual Studio 2005, create a new Console Application named ConsoleApplication1.
3. Replace your Program.cs file with the Program.cs file you extracted from this article.
4. Modify the serverUrl in the Main method to point to your PPS 2007 Server.
5. In the Visual Studio Solution Explorer, right click References, select "Add Web Reference".
6. In the "Add Web Reference Form", type in https://localhost:40000/webservice/pmservice.asmx in the URL. (Actually, substitute the URL that points to the pmservice.asmx on your PPS 2007 server.)
7. Click Go. Change the Web reference name to PmServiceWebReference. Click Add Reference.
8. Execute ConsoleApplication1.
9. This code sample will read your PPS 2007 Server's dashboards and generate xlsx and pptx documents for each dashboard item. The documents will be dumped to the current temp directory.
Conclusion
You can use the above approach to create and maintain a periodically updated Excel / PowerPoint report deck. Once you've used PPS 2007 to create Dashboards containing Scorecards and ReportViews, you can use a remote, automated process to periodically generate Excel or PowerPoint replicas of the Dashboards that your users are already familiar with.
Comments
Anonymous
November 08, 2007
Sample Code Program.zip Eric Friedrichsen ( ericfr@microsoft.com ) Microsoft Corporation November 2007Anonymous
November 08, 2007
Thank you! This is exactly what I need!!Anonymous
November 08, 2007
A 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?Anonymous
November 12, 2007
Eric Friedrichsen ( ericfr@microsoft.com ) Microsoft Corporation November 2007 Required: Microsoft OfficeAnonymous
November 12, 2007
Note that the above article applies to creating an Excel, PowerPoint report deck where the Dashboard Items are not filtered. This is a special case that comes at a very low development cost. For the general case (such as: if you Dashboard Items have filters), the custom work you have to do is only slightly more involved, please see my blog entry at http://blogs.msdn.com/performancepoint/archive/2007/11/12/filters-on-performancepoint-server-2007-m-a-excel-powerpoint-report-decks.aspxAnonymous
September 29, 2008
Hi, In the above code, each report in the dashboard is exported to excel or ppt. Can I knw how to export the entire dashboard page(all the report present in the report say 3) instead of just exporting each report. Help is appericiatedAnonymous
September 29, 2008
Hi, In the above code, each report in the dashboard is exported to excel or ppt. Can I knw how to export the entire dashboard page(all the report present in the dashboard say 3) instead of just exporting each report. Help is appericiated