PerformancePoint Multi-Select Tree Filter Zoom Issue in Internet Explorer

Recently I needed to create a Multi-Select Tree Filter for one of my PerformancePoint dashboards. I’ve created plenty of List Filters against Named Sets, Member Selections and MDX Queries in the past, and I’ve even created my own custom textbox PerformancePoint filters, but until recently, I hadn’t had the need for a Multi-Select Tree Filter. For a new dashboard that I was creating, I basically needed a simple checkbox list to use as a PerformancePoint dashboard filter and the Multi-Select Tree Filter fit the bill perfectly. It was easy to create and it seemed to work well too, until I attempted a live demo of the dashboard to an internal group.

With the dashboard projected onto a wall and zoomed in using Internet Explorer’s zoom so that everyone could clearly view the dashboard, I expanded the filter, only to get something similar to the following:

 

 

A complete fail.

In the moment, I neglected to realize that it was IE's zoom causing the problem, although I quickly discovered that back at my desk. At 100% it displayed perfectly. As IE's zoom increased or decreased away from 100%, the list moved out of view either to the upper left or to the lower right, eventually, just showing the word "false". Setting a zoom level in Chrome worked fine (of course), which did help me in finding a solution rather quickly.

 

Using Chrome's Developer tools I found that one of the enclosing DIV elements that displays the dropdown was using the following filter:

 

.pps-tree-layer1 {
    filter: progid:DXImageTransform.Microsoft.Shadow(color=#333333,direction=130,strength=3)
}

 

Switching over to IE's Developer tools and disabling that filter, enabled the Multi-Select Tree Filter to display properly when zooming. A quick Google search for how to remove the filter via CSS yielded a blog post by Brian Johnson on How to Disable a CSS Filter in Internet Explorer. I applied that to the correct element as follows:

 

.pps-tree-layer1 {
    /* For IE 8+ */
    -ms-filter: "progid:DXImageTransform.Microsoft.Shadow(enabled = false)"!important;
    /* For IE 5.5 - 7 */
    filter: progid:DXImageTransform.Microsoft.Shadow(enabled = false)!important;
}

and viola...

 

 



Zoomed and displaying correctly!

SSRS Beginner Tip: Generating PNGs from an SSRS Report (and PowerShell)

I recently had to generate a couple of dozen map images to be used by a client in their company website. The required map basically needed to be centered and zoomed on a sales territory boundary, highlighting it, displaying the trade areas within it and showing a Bing Maps background. SSRS wouldn't have necessarily been my first choice to create maps (MapInfo would have been), however I already had an SSRS report that contained this exact map. All I had to do was copy it and remove a couple of tables.

So setting up the SSRS report was pretty straight forward. In order to generate an image for each of the sales territories, I had to additionally create a Data-Driven Subscription, passing in the sales territory code into a report parameter. Again, pretty straight forward, until I realized that the only image export format available was a TIFF image file, which wouldn't work for the clients web page. They required a JPG or PNG.

The answer was to go ahead and generate the TIFFs using the subscription and then run this handy PowerShell script which would convert each image to a PNG:

#Load required assemblies and get object reference 
[Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms");

$path = "C:\MapImages"

Get-ChildItem $path *.tif |
    ForEach-Object { 
        $i = new-object System.Drawing.Bitmap($_.FullName);
        # Save with the image in the desired format
        $i.Save($("$($path)\$($_.BaseName).png"),"PNG"); 
    }

This script loops through each of the TIFFs located in the specified path, opening them and then saving them with the specified PNG format with the same base name as the TIFF. Most of this tiny script can be credited to the Hey, Scripting Guy! Blog post Hey, Scripting Guy! How Can I Use Windows PowerShell to Convert Graphics Files to Different File Formats?

SSRS Beginner Tip: How to Determine if All Values are Selected in a Multi-Value Parameter

In Reporting Services, there are generally two different methods to determine when a user has selected all of the values in a multi-value parameter (i.e. a parameter where the “allow multiple values” checkbox has been checked). The method to choose is dependent upon whether you are trying to determine this during report rendering or as part of the data retrieval for the report. Usually this will become a requirement when the multi-value parameter is populated from a dataset (i.e. “Get values from a query” has been selected from the Available Values tab) as opposed to being hard-coded (i.e. “Specify values” has been chosen from the Available Values tab) where the number of available values is static and known.

An example of needing to determine if all parameter items are selected during report rendering, is when you need to display the selected values from a multi-value parameter on your report. Rather than display a list of all items in the case that all are selected (or none were de-selected), you could show something like: “Selected Brands: All”. This would be particularly nice is the parameter list is long.

This can be accomplished by comparing the count of items from the dataset that populates the available values of the parameter using the CountRows aggregate function, to the count of selected parameter items using the parameter object’s Count function. In the example below, the dataset is named dsBrands and the parameter is named Brands:

="Selected Brands: " & IIf(CountRows("dsBrands").Equals(Parameters!Brands.Count),
"All", Join(Parameters!Brands.Label, ", ")

You cannot, however, use this method if you are trying to determine if the user has selected all values as part of the data retrieval process for the report. An example of this would be when you are constructing your dataset query as part of a function based on the selected items from a multi-value parameter (this will become clearer in the example below). If you try to use the CountRows function at this point, you’ll receive an error similar to the following:

“Aggregate and lookup functions cannot be used in query parameter expressions”

The Parameter object does not provide a function to get the total item count, but as mentioned above, it does provide the selected item count. Therefore you can set up a second Internal or Hidden parameter and set the default values to the same dataset, thereby allowing you to retrieve the total item count from the hidden parameter and the selected item count from the visible parameter. In the following simple MDX query, I am adding the Brand constraint only if the user has not requested all brands:

="SELECT NON EMPTY { [Units] } ON COLUMNS, " &
    "NON EMPTY { [Geography].[State].[State] } ON ROWS " &
IIf(Parameters!Brands.Count=Parameters!HiddenBrands.Count, Nothing, 
    "FROM ( SELECT ( STRTOSET(@Brands, CONSTRAINED) ) ON COLUMNS ") &
    "FROM [Motorsports]" & 
IIf(Parameters!Brands.Count=Parameters!HiddenBrands.Count, Nothing, ")")

As a note, you can use this method (i.e. creating a hidden parameter) to perform the first task of determining if all parameter items are selected during report rendering, however it is not as efficient, as it results in the dataset query being performed twice, once for each of the parameters (visible and hidden).

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.

 

 

Removing the Word "(Hidden) " from SharePoint Web Part Tooltips

When developing a dashboard in PerformancePoint 2010, one approach to managing the web parts that are displayed on the page is to use a PerformancePoint Filter to conditionally display web parts as a group. For example, I could have a filter that consists of the values "Market Share", "Dealers" and "Trend". By connecting this filter to web parts on the dashboard using the Display Conditions tab on the Configure Connection dialog, I can display certain web parts when "Market Share" is selected, others when "Dealers" is selected, etc.

The problem arises when, after a page refresh, you click on your filter to display a different group of web parts. The tooltip will now have the word "(Hidden) " prefacing the name of the web part in the tooltip for the web part menu, the web part icon and the web part description (see below).

Clearly the web part is not hidden. For whatever reason, this is indicating the initial state of the web part after a full page postback. Helpful? Not Really. Annoying? Definitely.

With a little jQuery, we can easily remove that word from all web parts in the document ready function.

$(document).ready(function () {
    // Remove "(Hidden) " from a title, img alt and td title tags
    $('td[title^="(Hidden) "]').each(function (index) {
        $(this).attr('title', $(this).attr('title').replace('(Hidden) ', ''));
    });
    $('img[alt^="(Hidden) "]').each(function (index) {
        $(this).attr('alt', $(this).attr('alt').replace('(Hidden) ', ''));
    });
    $('a[title^="(Hidden) "]').each(function (index) {
        $(this).attr('title', $(this).attr('title').replace('(Hidden) ', ''));
    });
});

There is certainly room for targeting the selector better here, but I did want to show that there are several tags that need to be addressed. Either way  the result is the same: a less confusing dashboard for your users.