The Broken “Export to Excel” Web Part Menu Item for PPS SSRS Web Parts and a Workaround

One of the side effects of using cumulative updates (CUs) is that functionality can break. I experienced this when applying the February 2012 CU to SharePoint 2010 SP1, which was required to fix some crucial functionality that was broken in SP1. The result, however, was that the "Export to Excel" web part menu item no longer worked for PerformancePoint Reporting Services web parts. The menu item is available, but instead of exporting, it opens the web part in the RSViewerPage.aspx page. Not real useful.

Upon some Googling, I found this forum entry indicating that the problem existed in the December 2011 CU and that it still exists with the December 2012 CU: http://social.technet.microsoft.com/Forums/en-US/ppsmonitoringandanalytics/thread/1d39d413-2756-450c-a40c-60c4dfe76fa1

As an additional note, this may be limited to an environment using SharePoint 2010 with SQL Server 2012 (this does work correctly with SharePoint 2010 SP1 with no CUs applied and SQL Server 2008 R2 SP1).

As the "Export to Excel" web part menu item was now rendered useless, I used PowerShell to loop through all of my dashboard pages, identifying the web parts that display PPS SSRS reports, and then hiding the "Export to Excel" menu item for those web parts so as not to confuse my dashboard users.

$webPart.IsAllowExportToExcel = "Hide" # Enabled, Disabled or Hide
$webPartManager.SaveChanges($webPart)

I left it at that for quite awhile, until in January I found time to revisit this problem and explore a workaround to allow the PPS SSRS web parts to be exported.

Something that I already knew worked correctly was the PPS SSRS web part’s toolbar’s exporting capability. So I could have just checked the "Show toolbar" checkbox in Dashboard Designer’s Report Settings for each of my SSRS web parts, but then the entire toolbar would display, taking up valuable real estate and providing unnecessary functionality for my dashboard web parts (e.g. page navigation, search, zooming, etc.), when I really only needed the exporting capability.

So instead I thought if I could dynamically show the toolbar, that would be an improvement over having it always being displayed. So again, with a little Googling, I found inspiration in the MSDN Library under an entry within the SQL Server 2012 Reporting Services Features and Tasks section called URL AccessParameter Reference. I could use query string arguments in the iframe element that displays the report to not only show the toolbar, but limit the toolbar functionality to just the exporting capability.

The next decision was how to incorporate this knowledge into my dashboard web parts. My first idea was to use jQuery to add a menu item, such as "Show Toolbar", to the web part’s menu, but I didn’t like how it would then take up real estate and cause my report to be partially obscured since I was setting the web part’s height. So instead I thought what if the toolbar only displayed when I opened the PPS SSRS web part in a new window using the web part’s "Open in New Window" menu item. Not perfect, but acceptable and relatively easy to accomplish. This would require editing the DynamicView.master and DynamicReportView.aspx files located in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\PPSWebParts\ (I’ll leave it to you to determine how you would manage these two files and their deployment).
 
In the DynamicView.master file, I just had to add the PlaceHolderAdditionalPageHead server tag right before the closing HEAD tag (i.e. </head>):

<asp:ContentPlaceHolder id="PlaceHolderAdditionalPageHead" runat="server"/>

In the DynamicReportView.aspx file, I just had to add the following right before the "PlaceHolderMain" server tag:

<asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">
    <script src="../myScripts/jquery-1.7.1.min.js" type="text/javascript"></script>
    <!-- Shows Actions menu on toolbar (kb130521) -->
    <script type="text/javascript" language="javascript">
        function NotifyBrowserOfAsyncUpdate(elem) {
            $(elem).find('iframe[src*="%2Erdl"]').attr('src', function (i, val) {
                return val.replace('&rv:Toolbar=None&', '&rv:Toolbar=Full&rv:ToolBarItemsDisplayMode=128&')
            });
        }
        $(document).ready(function () {
            $('body').find('[text="Export to Excel"]').hide();
        });
    </script>
</asp:Content>

Like a couple of other blog posts that I have written, I’m using the NotifyBrowserOfAsyncUpdate function to notify the page when the asynchronous retrieval of the web part is complete. Then I use jQuery to find the iframe hosting the PPS SSRS web part and modify the "src" attribute to display the toolbar, but limit the menu items to just the export functionality. I additionally add a document ready function to hide the "Export to Excel" menu item that will appear in the web part's menu.

Now upon clicking a PPS SSRS web part menu's "Open in New Window" menu item, I get the following in a new window, showing the toolbar with just the Export menu option.