Reporting Services Web Part Won’t Save Parameters with SharePoint 2013 SP1

We recently performed an installation of SharePoint 2013 with SP1 for a customer using the slipstreamed ISO available on MSDN. Part of the rollout in this case involved migrating a number of reports from a standalone instance of SQL Server Reporting Services (SSRS) to SSRS running in SharePoint integrated mode. However, during the creation of some new reports, we observed something odd. The custom parameter values were not being saved for web parts that were using these reports.

SSRS web parts in SharePoint allow for a report to be displayed within a web part, a therefore in part of the page. Report parameters created in the report can be exposed as web part properties. Therefore, you can have multiple instances of the same report on different pages using different parameters. You can also connect the web part to other web parts, such as SharePoint filters, and have the parameters driven by these filter values. However, in this particular case, none of it was working. The parameter could be saved, and applied, but when the page was saved, the parameter values would revert to their defaults.

After doing some digging, I came across this discussion on the MSDN forums. The discussion indicated that there may be a regression bug in SharePoint 2013 Service Pack 1 that exhibited this behaviour, and that the application of the May 2014 Cumulative Update had helped them somewhat. We had never seen this issue with builds prior to SP1. Given that our farm had not yet gone live, we quickly acquired and applied the July 2014 CU in the hopes that it would help.

It did.

In our case, it fixed the problem completely, however there was one small negative side effect. All of the web parts that had been added to pages before the update completely lost all of their saved properties, including the report that they were pointing to. In our case, this was no big deal, but if you have a lot of these, it could be an annoyance. I also don’t know if this would happen generally with the July 2014 CU, or it was only because that the original system was affected (as I suspect).

In any event, this apparently was a regression bug introduced with SP1, and applying the July 2014 CU clearly fixed this problem for us. Our new baseline for SharePoint 2013 will not be sP1, but SP1 with the July 2014 CU.

Power BI Data Management Gateway 1.2 Changes the Game

Last week, a new version of the Power BI Data Management Gateway was released. If you’re unfamiliar with it, it is the Power BI component that allows for workbooks stored in the cloud to be refreshed on  a regular basis with data that exists on-premises, or outside of the hosting center.

I’ve been using the gateway since its initial availability in preview form, and in my opinion, this is the most significant functionality change yet. Until this release there were a grand total of three possible data source types that could be refreshed. With this release, the total increases to 18 by my count (you could argue 22, but that’s plenty).

With past versions, I would write up a quick post on how it is configured, but that has been done, along with the complete list of supported data sources and a helpful video on this blog post by the Power BI team. In addition, an very comprehensive (although amazingly already in need of update) white paper on hybrid data scenarios has just been published by Microsoft here.

The big change here is that this multitude of data sources is supported for data retrieved by Power Query, and NOT by Power Pivot natively. The catch is that they’re only supported for Power Query queries. There is absolutely nothing wrong with this, but it does require us to change our approach a bit to using the data management gateway.

As I first mentioned in a post almost a year ago on Using the Data Management Gateway, and in a number of posts since, data connection strings needed to line up with Power Pivot connections. At the time, the only supported sources were SQL Server and Oracle (for the gateway) and Power Query wasn’t supported at all. Version 1.1 of the Gateway brought Power Query support, but only for those 2 supported data sources. With this release, the Power Query support includes not only all of the new data sources, but also the three original Power Pivot data sources (note: Power Pivot data connections can be found in the Power Pivot add-in UI, while Power Query connections are available on the data tab in Excel).



As of this release of the Data Management Gateway, there is almost no reason to use native Power Pivot connections any longer. My recommendation is therefore that unless there is a good reason for not doing so, you should try to use Power Query for all data acquisition tasks. It is quite clearly the way forward, and will only gain in supported capabilities. My suspicion is that Power Pivot connections will be retained for backward compatibility reasons only.

With that said, there are a couple of good reasons for using Power Pivot connections directly. One of these reasons is if your data source is online, whether it is SQL Azure, SharePoint Online, or Project Server online. With these data sources, a Data Management Gateway is not required for refresh to work from an embedded Power Pivot connection. However, if Power Query is used to access these sources, it is.

What this means is that for Power Pivot connections to these sources, a refresh allows the Power BI service in the cloud to directly access these data sources in the cloud. However, because ALL Power Query connections require the Data Management Gateway for refresh operations, a Power Query refresh operation will require all of the data to be first downloaded to the on-premises gateway, and then sent back up to the Power BI service in the cloud. While functional, this is hardly the most efficient approach.

Apart from this one small caveat, this version of the Data Management Gateway spells the way forward. Additional data source support should come fast a furious, and the Power Query focus means that we can start to rely its powerful transformational capabilities without having to sacrifice refreshability (if that’s even a word….).

Incorrect URL When Using a Multilingual SharePoint Blog Site

I recently encountered a relatively puzzling circumstance on a multilingual blog site. In this scenario, the customer was using the SharePoint blog as part of their public facing web site and while the English site was working fine, the French site was not. More accurately, I should state that it wasn’t working for me, but I’ll get to that shortly.

For the most part, the French blog site was working. The posts showed up on the main page, and could be accessed. However, if you opened up the source list for the posts, either by using “All Content” or by selecting “Manage Posts”, the posts would appear, but clicking on them would result in the error “Page not found – The page you’re looking for does not exist”.

The first clue was that although the list is named “posts”, the site itself is a French site, so the actual list name is “Billets” as seen below.


This difference is a feature of SharePoint 2013, and it happens because this is a multilingual site that has been enabled for multiple language display. In my case, my user profile identifies my preferred language as English, and SharePoint automatically translates all system generated text into English. With this feature, an English system administrator can easily navigate a French site because pages like system settings are automatically translated.

Unfortunately, in the case of the blog site, the translation system gets a little overzealous. Clicking on one of the blog posts from the system view results in the error mentioned above. Why is there an error? All we need to do is to look at the URL to discover the reason.


As you can see, the list name requested is “Posts” but we know that the actual name is “Billets”. The URL itself is being translated for me. So how do we avoid this problem?

As good as the translation system is, it’s not worth the error, so we need to turn it off for this site. Doing so is as simple as turning it on. From the Site settings page, select “Language settings” under the Site Administration section.


From there, simply deselect all alternate languages, and automatic translation will no longer be performed.


Once this is done, the URL will no longer be translated, and opening posts will work. You will now need to navigate the site settings in a different language, and know that “Language settings” is the same as “Paramètres linguistiques”, but most SharePoint admins can navigate here with their eyes closed anyway.

I hope that this helps.

Problems Installing PowerPivot for SharePoint and Reporting Services From SQL Server 2012 SP2 on SharePoint 2013

I recently performed an installation of both PowerPivot and Reporting services for a customer that was running SharePoint 2013. In preparation for the installation, the customer had made the SQL media available to us (something that I always appreciate), so installation looked to be quick and easy. Since SP2 was released for SQL Server 2012 over a month ago (June 2014), they had downloaded the ISO file from MSDN that already included SP2.

I went ahead and installed the bits for both PowerPivot for SharePoint and for SQL Server Reporting Services. The next step in my case was to run the PowerPivot for SharePoint Configuration tool. There are normally two such tools available, the original one, for SharePoint 2010, and with the release of SQL Server 2012 SP1, there is the “PowerPivot for SharePoint 2013 Configuration Tool” which as the name might imply, is for configuring on SharePoint 2013. However, after this installation, there was only the one tool, which led me to believe that they had been consolidated. However running it resulted in the “Assembly not found” error that you get when installing on the wrong platform.

The next step in installing SSRS is normally to go and add the Service Application in Central Administration. However, it wasn’t available to add, and the typical PowerShell commands to register it (Install-SPRSService and Install-SPRSServiceProxy) resulted in not found errors. Something was clearly amiss.

After poking around a bit I was able to determine that the PowerPivot engine that was running was 11.0.2100.6, which corresponds to the RTM version of SQL Server 2012. (You can find a list of build numbers here) SharePoint Server 2013 requires at least SP1 for PowerPivot for SharePoint and for SSRS integrated mode. The problem had been identified, but what caused it?

After speaking with Todd Klindt (keeper of the SharePoint Patches log), it turns out that he had a similar experience with the SQL SP1 installation media. There is, or at least was a bug in the slipstream version of SQL Server 2012 SP1 that was available through Microsoft’s standard distribution channels. This bug caused only the RTM bits to get installed, not the patches. This has been documented in this Microsoft KB article. The bug was fixed, and the download media was updated within a few weeks. However, it appears that the same production glitch that caused the problem with SP1 happened again with the initial release of SP2, as documented in this blog post.

Happily, the problem has since been corrected, and any new downloads of SQL 2012 with SP2 should not be affected. However, if you have used an affected version, the fix is simple. All that is necessary is that you patch your install with the standalone SQLServer 2012 SP2 installer, available here. Ensure that you patch all elements, including your PowerPivot for SharePoint instance. You’ll know that you have the problem if you don’t see the PowerPivot for SharePoint 2013 configuration tool – there should be two as follows:

Perhaps the SQL team should talk to the SharePoint team about patching. Just sayin’…..

Changes to Data Loading Features in Power Query – May 2014

The Power BI team continues to deliver new features at a rapid pace. The May 2014 release of Power Query is no exception. There are quite a few new features packed into this release, which you can read about at your leisure but I’m particularly interested in the ones pertaining to data loading, as I’ve discussed several of the limitation in this area in the past.

This release is major indeed. There are three significant changes to the data loading features in this build.

Configurable defaults for data loading

I’ve just posted an article on how to do this and why. To put it simply, the default data load behaviour is to load data into a worksheet in most cases. This leads to larger than necessary workbooks, to the point where they may not work properly with Power BI. Now you can change this default behaviour, which will be welcome for anyone doing serious data modeling.

Worksheet Size Warning

If you do decide that you want to load data to the worksheet, or you’re simply unaware of the issues, you will be prompted to consider loading to the data model once your data hits 10 MB, which is the maximum non-model workbook size in Power BI. Prior to this update, the user wouldn’t know that there was a problem until after they tried to use the workbook in Office 365.

Data Model Preservation

Prior to this update, it wasn’t possible to modify the query without losing all changes to the data model, or formatting in worksheet tables. This release of Power Query remedies this situation. You can now go back and make changes to your query without having to recreate the model.

Power BI is being built in a surprisingly collaborative way, with a large amount of input from the community. I’ve never seen this done to this extent at Microsoft, and it’s very good to see. I know that all of these features have been asked for and discussed by the community in the last few months, and here they are. Kudos to everyone involved, and keep it up. This product keeps getting better and better.