共用方式為

Filters on PerformancePoint Server 2007 M&A Excel, PowerPoint Report Decks

  • 發行項

Eric Friedrichsen (ericfr@microsoft.com)

Microsoft Corporation

November 2007

Required:
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 filtered Excel / PowerPoint report decks. Once you've used PPS 2007 to create Dashboards containing Scorecards and ReportViews, you can use a remote, automated process to periodically generate filtered Excel or PowerPoint replicas of the Dashboards that your users are already familiar with.

This article is a companion piece to the blog Using PPS M&A Reports to Create an Excel, PowerPoint Report Deck. This article explains how to create the same Excel and PowerPoint report decks, but with filters applied to the dashboard items.

Introduction

Using PPS M&A Reports to Create an Excel, PowerPoint Report Deck is interesting if:

· Your Dashboard Items have filters. Note: This is a critical distinction. If the Dashboard Items that you want to include in your Report Deck don't have filters associated with them, please see the other blog entry Using PPS M&A Reports to Create an Excel, PowerPoint Report Deck instead, which will require significantly less custom work on your part.

· Your users like the "Export to Excel / Export to PowerPoint" feature, but they dislike having to browse to each PPS 2007 filtered 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 filtered Dashboard Items in printed form. Having all the PPS 2007 filtered 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 filtered 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 filtered 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 Filtered Excel, PowerPoint Report Decks ~ Code Sample

The following code is all the c# code necessary to create Filtered Excel, PowerPoint Report Decks.

using System;

using System.Collections;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.IO;

using System.Net;

using System.Text;

using System.Web;

using System.Xml.XPath;

using System.Xml.Serialization;

class Program

{

static void Main(string[] args)

{

    // Change the serverUrl to point to your PPS 2007 server.

    string serverUrl = "https://localhost:40000";

    // Change the inputDir to be the dir where your

    // CriTrace.*.xml files are.

    string inputDir = Path.GetTempPath();

    // Change the outputDir to be the dir where your

    // xlsx and pptx files should be output.

    string outputDir = Path.GetTempPath();

    // Generate the files.

    FileInfo[] traceFiles =

    (new DirectoryInfo(inputDir)).GetFiles("CriTrace.*.xml");

    foreach (FileInfo traceFile in traceFiles)

   {

        try

        {

            string outputNameBasis =

                Path.Combine(outputDir, traceFile.Name);

            GenerateReportDeckItem

            (serverUrl + "/Preview/res/OfficeExportPage.aspx",

             traceFile.FullName,

    "Excel",

             outputNameBasis + ".xlsx"

            );

            Console.WriteLine(outputNameBasis + ".xlsx");

            GenerateReportDeckItem

            (serverUrl + "/Preview/res/OfficeExportPage.aspx",

             traceFile.FullName,

             "PowerPoint",

             outputNameBasis + ".pptx"

            );

            Console.WriteLine(outputNameBasis + ".pptx");

        }

        catch (Exception ex)

        {

            Console.WriteLine(ex);

        }

   }

}

private static void GenerateReportDeckItem

(string address,

 string criTraceFileName,

 string exportFormat,

 string outputFileName

)

{

    // New up a parms.

    NameValueCollection parms = new NameValueCollection();

    // Add parameters from the CriTrace file.

    StreamReader reader =

        (new FileInfo(criTraceFileName)).OpenText();

    string criTraceXml = reader.ReadToEnd();

    reader.Close();

    XPathDocument document =

        new XPathDocument(new StringReader(criTraceXml));

    Hashtable parmNames = new Hashtable();

    parmNames.Add("dashboardId", "dashboardId");

    parmNames.Add("dashboardItemId", "dashboardItemId");

    parmNames.Add("resourcePath", "resourcePath");

    parmNames.Add("targetControlId", "targetControlId");

    parmNames.Add("targetControlType", "targetControlType");

    parmNames.Add("proxyId", "proxyId");

    parmNames.Add("beginPoints", "beginPoints");

    parmNames.Add("inboundParameters", "inboundParameters");

    XPathNavigator navigator = document.CreateNavigator();

    XPathNodeIterator elements =

        navigator.Select("/renderingInstruction/parameters/*");

    while (elements.MoveNext())

    {

        if (!string.IsNullOrEmpty(elements.Current.Name) &&

            parmNames.ContainsKey(elements.Current.Name)

           )

        {

            if ("inboundParameters" == elements.Current.Name)

            {

                parms.Add

                (elements.Current.Name,

                 HttpUtility.

               UrlEncode(elements.Current.InnerXml)

                );

            }

            else

            {

                parms.Add

                (elements.Current.Name,

                 HttpUtility.

                    UrlEncode(elements.Current.Value)

                );

            }

        }

    };

    // Add parameters from our members.

    parms.Add

        ("exportFormat", HttpUtility.UrlEncode(exportFormat));

    parms.Add

        ("customExportSettings", string.Empty);

    // Get the response.

    WebClient client = new WebClient();

    client.Credentials =

        CredentialCache.DefaultNetworkCredentials;

    byte[] response =

        client.UploadValues(address, "POST", parms);

    // Dump the response to file.

    if (string.IsNullOrEmpty(outputFileName))

    {

        outputFileName = Guid.NewGuid().ToString("n");

    }

    FileStream fs = new

        FileStream

        (outputFileName, FileMode.Create, FileAccess.Write);

    fs.Write(response, 0, response.GetLength(0));

    fs.Close();

}

}

Before Using the Code Sample

Follow these steps before using the above code sample:

1. On your PPS 2007 server, turn on CriTrace by adding <add key="CriTrace" value="friendlyfile" /> to the <appSettings> of the web.config file of your RenderingService.asmx web service. (See my other blog entry on CriTrace for more information. There is a link to that blog entry below under comments.) Turning on CriTrace will cause the server to dump trace files in the form CriTrace.*.xml.

2. Using your web browser, navigate to the Dashboard that contains the Dashboard Item you wish to include in your Excel or PowerPoint report deck. Use one of the filter controls on that Dashboard page to select a filter value that alters the Dashboard Item you wish to include in your Excel or PowerPoint report deck. The Dashboard Item should repaint in the web browser.

3. On your PPS 2007 server, locate the system's temporary directory. (Mine is "C:\WINNT\Temp".)

4. In the temp directory, find the xml file that was just created as a result of your filter control manipulation. The naming of the xml files follows the convention "CriTrace.dashboardName.dashboardItemName.xml". For example, mine was named "CriTrace.Dashboard1.s3191.xml".

5. Now copy the xml file locally and rename it something that is indicative of the filter value you selected. In my case, I selected a filter value CY 2003 for the "Year" filter, so I renamed my xml file CriTrace.Dashboard1.s3191.CY2003.xml.

6. Repeat the above process, except select a different filter value. For example, this time I selected CY 2004 for the "Year" filter, so I renamed my xml file CriTrace.Dashboard1.s3191.CY2004.xml.

7. Repeat the above process as many times as necessary to create CriTrace information for all the Dashboard Item / filter combinations you need.

1. Don't forget to turn your CriTrace off again by removing the <add key="CriTrace" value="friendlyfile" /> from the web.config of your RenderingService.asmx.

2. Now that you've accumulated your xml files in the form CriTrace.*.xml, you're ready to use the code sample.

How to Use the Code Sample

Follow these steps to use the code sample:

1. Using Visual Studio 2005, create a new Console Application named ConsoleApplication1.

2. Replace the code in your Program.cs file the above code sample.

3. In the Visual Studio Solution Explorer, right click References, select "Add Reference". Add a reference to System.Web.

4. Modify the serverUrl in the Main method to point to your PPS 2007 Server.

5. Modify the inputDir to be the dir where your CriTrace.*.xml files are.

6. Modify the outputDir to be the dir where your xlsx and pptx files should be output.

Conclusion

You can use the above approach to create filtered Excel / PowerPoint report decks. Once you've used PPS 2007 to create Dashboards containing Scorecards and ReportViews, you can use a remote, automated process to periodically generate filtered Excel or PowerPoint replicas of the Dashboards that your users are already familiar with.

Comments