Complete reporting services integration with SharePoint 2013

Happy New Year My dear friends hope in this year you guys have bright future and this year is very big year for shapointers releasing SharePoint 2016 100 % i think it is going to be massive hit... :)

In this article we learn how to configure reporting services integration with SharePoint 2013. SQL Server 2012 Service Pack 1 (SP1) is a version of Microsoft SQL Server 2012 that supports Microsoft SharePoint 2013 Excel Services usage of Excel workbooks containing data models and Reporting Services Power View reports.

What's new in SQL Server 2012 SP1 Installation for Business Intelligence:

• PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of, and external to, SharePoint, although the server running Analysis Services must be on the same network and the Active Directory Domains Services (AD DS) forest as the SharePoint farm. You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services account's server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.
   
• spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.
   
• Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013.

we are only discussing the reporting services configuration. We will see the remaining sessions, described previously, in future articles. Fundamentally, there are no real differences with how this installs when compared to installing SSRS 2012 on a SharePoint 2010 farm in SharePoint mode, so if you’ve landed here looking for 2010 information, it should be valid, but the screens will look a little different.

To start with, it’s important to understand that SSRS will install as a SharePoint service application. This obviously means that it must be installed on a machine that is part of the SharePoint farm. What this does NOT mean is that you should install SharePoint on your SQL server and join it to the farm (please DON’T do that!). In a single SharePoint front end environment it is much better to add SSRS to your SharePoint server than it is to add SharePoint to your SQL server. Obviously, if you have a separate SharePoint application server, that’s the best place for it.

To install, obtain the SQL Server 2012 SP1 (or greater) media and mount it on your SharePoint server. Run the installer, choose new install and follow the prompts. Eventually you will get to the feature section screen, and assuming that machine has no prior SQL on it will look something like the following when completed.

You’ll notice that everything selected is under the Shared Features section, which means that it is not installed as part of a SQL instance. In fact, you’ll notice that we don’t have the data engine installed at all. The two Reporting Services options shown are the only items that are actually required for SSRS Integrated mode to work. As you can see, I’ve also selected SQL Server Data Tools (formerly BIDS) and Management Tools as well. I like to install these tools as a matter of course on SharePoint servers, as they can come in handy for connectivity testing or quick BI project building.

Follow the remaining prompts until the installation is complete.

Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application

Install-SPRSService

Install-SPRSServiceProxy

Once registered, the service application can be created as below. If you install Reporting Service – SharePoint after the server has been joined to the farm, then the above steps are taken care of for you automatically.

The next thing that you need to do is to provision the service application. From Central Administration, navigate to Manage Service applications. Then, from the new menu, Select SQL Server Reporting Services Service Application.

Fill out the resulting form as appropriate, and select OK. Make sure that you navigate to the bottom of the form and select the applications to activate SSRS on.

Click on Provision Subscripts and Alerts

Click on Download script, save script and run in your SQL Server

Back in to the Provision Subscripts and Alerts settings, I have entered Polasoft\Administrator since it was sysadmin on the SQL Server.

Click ok. Once the service application and proxy have been created, click on it to access the management screen.

You’ll want to access each of the sections and fill out the appropriate options for your installation. The instructions are fairly self-explanatory, so I won’t go into them here. At a minimum, you should back up your encryption key in the key management section, Set your unattended execution account (the default account to use when no credentials are available), and your email server settings if you want to be able to deliver reports via email. If you want to enable self-service subscriptions and alerts, fill out that section, and it contains instructions for setting up the SQL agent service to support it.

The most important section is System Settings, which controls the bulk of how Reporting Services will run. Clicking on it accessed the service itself, and it’s the first place that you’ll see an error if you have configuration problems. In early builds, I have seen an error similar to the following:

The requested service, ‘http://localhost:xxxxx/SecurityTokenServiceApplication/securitytoken.svc/actas’ could not be activated

(xxxxx is a local port which varies from farm to farm)

This indicated a problem with the SecurityTokenService, which you can see by accessing IIS. After doing a little poking around, I tried to access the service directly in a browser via its base url:

http://localhost:xxxxx/SecurityTokenServiceApplication/securitytoken.svc

I was then presented with an error indicating that the server was too low on memory. The solution? Allocate more RAM. It was running with 4 GB and only SharePoint installed, but it did have most of the service applications activated. The lesson – if you want all the services to work, give your server enough memory. Bumping it to 8 GB did it in my case.

If you can access your system settings, then you should be good to go. The next step is to enable SSRS in you site collections Log in to the site with Admin credentials. In SharePoint 2013, apps and libraries are added using the little gear symbol in the upper right corner.  Click the gear and click ‘Add an App’.

From the list of Apps, click ‘Document Library’.  I named mine ‘Reports’

  Once the App is created, you will be presented with an empty library.  We need to change the library to accept SSRS items, so click the ‘Library Settings’ button.

  In the settings, click Advanced Settings so we can start managing the content types.

 Now you will have the option to select Content Types.  Just under the list of settings, is a new section ‘Content Types’.  Under that, Click ‘Add from existing site content types.

 A new window opens with selector boxes allowing you to choose content types.

Highlight in the left box and click Add to add the following types:

·         Report

·         Report Builder Model

·         Report Builder Report

·         Report Data Source

It should now look as follows.

Click OK.

You have now configured SharePoint 2013 to work with SQL Server Reporting Services in Integrated mode.

You should now be able to deploy reports.  In my example library above named ‘Reports’ on a server named DEVBOX the deployment URL will be:  http://devbox/Reports

If you want to separate shared data sources and data set files,  you need to navigate to the empty library and use the Files menu ‘New Folder’ button create the folders before deploying the project.  Each setting requires the full URL to the subfolder.  If you used spaces, click the elipses (…)  to get the proper url.

Here is the view of an SSRS Project Properties box ready to deploy.

If this has been helpful, leave a comment… :)

Minimal Download Strategy in SharePoint 2013

Over the past decade, web-based applications have developed to the point that each user interaction requires very little data to be downloaded as pages change and refresh. A prime example is Facebook, where each user interaction (such as the addition of a comment) results in an update of only that portion of the page, improving performance and response time considerably.

SharePoint is based on a very rich web interface containing lots of moving parts and lots of customizable areas. As you know the Interweb is based on a request-response approach where you request a page and get a response back. The approach is the same whether you update the whole page or portions of it. The last decade we've seen smart approaches working around this "problem" using DHTML, JavaScript, AJAX, Update Panels, you name it. Facebook, which also contains a very rich and dynamic interface, has been excellent in making a dynamic web interface minimizing the amount of data downloaded for each user interaction and in this way also increasing the perceived performance.

Up until now SharePoint (out-of-the-box) has not been that efficient on this, even though SharePoint 2010 did some improvements. Most often the whole page has been reloaded for each and every action; this of course affects all end-users and the perceived performance of SharePoint.

But now, with SharePoint 2013, we have a whole new paradigm thanks to the Minimal Download Strategy - MDS. In this post I will walk you through some details of MDS, how to leverage it in your own customizations and how to successfully adapt your current and future customizations to MDS.

The Minimal Download Strategy (MDS) - an overview

The Minimal Download Strategy is by default enabled on Team sites, Community sites and a few others in SharePoint 2013.All pages for that site is rendered "through" the /_layouts/15/start.aspx page. This page is responsible for loading pages from the site using the MDS. The start.aspx page has a specific JavaScript object asyncDeltaManager (defined in start.js). Basically it parses the URL, looks for the # sign and takes the path following that and dynamically loads that page.

http://polasoft/_layouts/15/Start.aspx#/SitePages/Home.aspx

Subsequent requests to pages are dynamically loaded through the asyncDeltaManager object. Once a link is clicked it will invoke a method in the JavaScript object which creates the MDS URL and appends the query string parameterAjaxDelta=1. A request is created and downloaded asynchronously and only the "delta" is returned to the client and assembled and rendered using JavaScript.

How is the delta pages generated?

One of the key things in the MDS implementation is that SharePoint 2013 contains a new Page class - the DeltaPage. This page class is the key to generating the full pages or just the deltas. Fortunately the common pages such as WebPartPage, WikiEditPage, UnsecuredLayoutsPageBase, LayoutsPageBase etc are inheriting from this page. so most of the out-of-the box pages works and hopefully your custom application pages as well. This page class is quite smart it can handle when MDS is not enabled (ie default rendering) and when MDS is enabled. When MDS is enabled for the site this page is responsible for creating the delta response. The DeltaPage is also responsible for handling exceptions such as when the master page is different (another one or a new version). Every delta request sent using the asyncDeltaManager has a specific request header - the X-SharePoint header. This header contains information about the master page, the language and if the page is in read-write or read-only mode. This header is important since it contains the master page, the version of the master page etc and SharePoint uses this to determine whether to do a full reload (if the master page have changed) or not. So if you’re working on a master page, be prepared for full reloads of the page, at least the first time you request a page using the modified master page.

Enabling or disabling MDS on a site

 First of all what sites do have the MDS enabled by default? MDS is actually a SharePoint feature (Name; MDSFeature, Id: 87294C72-F260-42f3-A41B-981A2FFCE37A) which is Web scoped. Some of SharePoint 2013 Site Templates has this feature stapled (for instance Team, Community, Wiki, Projects, App and Blog sites). The feature is very straightforward - it toggles the EnableMinimalDownload property of the SPWeb object. So you can quite easily yourself turn it off using code (server side or CSOM!!) or PowerShell. Of course you also use the Site Settings > Site Features to turn it on or off as well.

The MDS is not enabled on publishing sites and is (as I understand it) not compatible with publishing sites, there are a couple of reasons for this, continue to read for more information

Requirements for MDS

Except enabling MDS it is required for the MDS to work there is one control that must be placed in the master page - the AjaxDelta control. It should be added to the head section of the master page. This control is responsible for a couple of things. If the page is a start/home page it will clear all controls and make sure that the page is loaded fully and registers the correct scripts.

The PageRenderMode control

 There is one control called PageRenderMode that can be used on a master page, web part page or page layout. This control has one property called RenderModeType which can have one of two possible values;Standard or MinimalDownload. If the control is placed on a page and have the property set to Standard – it will prohibit the page from being rendered using MDS. If the control is not present or if it’s property is set to MinimalDownload it can participate in MDS rendering. So this control can be used on specific (master)pages to prohibit MDS.

1	<SharePoint:PageRenderMode runat="server" RenderModeType="MinimalDownload" />

A word of caution here, if you’re using the Design Manager to generate master pages, this control will automatically be inserted into the generated artifacts.

Another gotcha here is that even if you have the PageRenderMode control set to MinimalDownload whenever the publishing features are enabled and you have the ribbon on the page, specifically when using the PublishingRibbon in your master page, SharePoint will automagically inject, during runtime, the PageRenderMode control with the property set to Standard.

How does the delta response look like?

The response returned to the asyncDeltaManager has a specific format which very much resembles how ASP.NET UpdatePanels work. Each control that should be re-rendered on a page has it's HTML rendition and all these renditions are separated by a specific token which has the format:

-deltaboundary-<Guid>-

The Guid is unique (which is the purpose of guids :-) and the asyncDeltaManager retrieves the current token from the response header called deltaBoundary.

After all deltas, at the end of the response, is another encoded set of data This data tells what each delta boundary block is intended for and has the following format:

content length|type|id|data|

Type identifies what kind of delta data it is; controlDelta is a standard control, pageTitle the title of the page,pageRedirect a page redirection etc. There are about ten more different types. Id identifies the control identifier and data, is the data...

The asyncDeltaManager takes all this information and assembles the page, so it all looks fast and neat for the end users

In SharePoint master pages the AjaxDelta control is directly related to how the Minimal Download Strategy (MDS) knows which parts of the layout to refresh before the page is rendered. More specifically, anything not wrapped in an AjaxDelta control will not refresh between pages of a site with the MDS feature activated.

For example, if you have some dynamic page element, such as the page title, and it is not wrapped in an AjaxDelta control, every page on a site with MDS enabled will show the same title from page to page. This is because the first page routed through start.aspx causes MDS to cache everything on the page that isn’t wrapped in an AjaxDelta; every other page will show the same title. On the other hand, if it were wrapped in an AjaxDelta control, MDS would know it needs to render any differences from the previously loaded page.

<SharePoint:AjaxDelta ID="DeltaSearch" BlockElement="true" runat="server"> <asp:ContentPlaceHolder id="PlaceHolderSearchArea" runat="server"> <SearchWC:SearchBoxScriptWebPart UseSiteCollectionSettings="true" EmitStyleReference="false" ShowQuerySuggestions="true" ChromeType="None" UseSharedSettings="true" TryInplaceQuery="false" ServerInitialRender="true" runat="server" /> </asp:ContentPlaceHolder> </SharePoint:AjaxDelta>

Each AjaxDelta control has a unique ID and an optional attribute BlockElement. When BlockElement is true the AjaxDelta wraps the control in an HTML <div>; otherwise it uses a <span>.

1. The newly requested page has a different master page than the current page. This makes complete sense: requesting a page with a different master page than the current one means that the most basic structure of the page needs to be updated. These changes are too fundamental for MDS to handle. Keep your master page options as simple as possible to avoid this risk.
2. The current master page has been updated since the last page load. Also makes sense for the same reason mentioned above.
3. The current or requested page contains “non-compliant” content:
• The page uses the ASP.NET 2.0 framework. The MDS server engine is only able to run its magic on pages running on ASP.NET 3.5 or later. Hopefully you aren’t deploying ASP.NET 2.0 pages to SharePoint anymore, but if you are, stop. No, seriously–stop.
• The page uses CSS or Javascript files that aren’t registered with the MDS engine. For solutions deploying custom mark-up (in custom application pages), you can make MDS aware of CSS and JavaScript files by using the standard CssLink, CssRegistration, and ScriptLink server tags. If you aren’t in the habit of doing this already, see MSDN’s article on modifying SharePoint content for MDS for more info.
• The page contains “illegal” HTML. At the very least both <script/> and <style/> tags are considered illegal; as mentioned above, use the server-side CssLink, CssRegistration, and ScriptLink tags instead. “Illegal” HTML may cover other conditions that might prevent the MDS engine from correctly parsing the page. Avoid unclosed block-level tags, missing <head/> or <body/> sections, etc.
4. The current or requested page contains “non-compliant” controls:
• The control is not in the MDS engine whitelist. “Whitelisting” is achieved by registering resources with the SPPageContentManager class, using the RegisterClientScriptBlock(),
  RegisterClientScriptInclude(), RegisterInlineStyle(),
  and similar methods. See MSDN’s SPPageContentManager API reference for more details.
• The control assembly is not marked as MDS-compliant. Assemblies that contain custom controls have to be marked with the Microsoft.SharePoint.WebControls.MdsCompliantAttribute to alert the server-side MDS engine that they have controls that support MDS. Reference MSDN’s article on modifying SharePoint content for MDS for examples of how this is done.
• The control’s class doesn’t have the MDS attribute. Server-side control classes also have to be marked with the MdsCompliantAttribute to alert the MDS engine that they support it. This should only be done if all other compliance steps mentioned here have been taken.

As a developer of full-trust farm solutions for SharePoint 2013, you have a great deal of control over whether or not your custom code and controls cause MDS to fail.However, if you are developing SharePoint 2013 App Parts for SharePoint-Hosted or Provider-Hosted solutions, the MDS story for now is bleak. The control used by SharePoint to render embedded App Parts on a page causes MDS to fail: Every. Single. Time. Not only does it fail, but the failure appears to be caused by SharePoint’s internals; there’s no way to resolve this by making App Parts MDS compliant. We dug into the SharePoint 2013 source using .NET Reflector and discovered that the SPAppIFrame control used to render App Parts emits a raw <script/> tag using theHtmlTextWriter class. This makes the SPAppIFrame control non-MDS compliant  this doesn’t leave many options for Hosted Solution developers for now:

1. Use Apps instead of App Parts. There’s significant business value in the capability of adding custom, configurable app logic (i.e. web parts) to SharePoint pages. But if you can architect your solution to keep your custom logic confined to its own pages, you have the opportunity to introduce your own “proper”(read: non-retrofit) SPA behavior by using Angular, Backbone, or another SPA framework within your app. Developing your own SPA logic reclaims significant control of application flow and performance; don’t discount this option just because “the client expects web parts.”
2. Avoid routing requests to non-MDS-compliant pages through start.aspx. For example, if your site home page contains custom App Parts, any links to that page from your top navigation, quick links, wiki pages, and external sites should route directly to the <site>/SitePages/Home.aspx URL instead of using <site>/, which will cause it to route through start.aspx. Consider validating all of your navigation links, and any that contain start.aspx should be replaced if the link target includes a custom App Part.
3. Disable the MDS feature on your SharePoint web. MDS is packaged as a feature on your SharePoint web, and it’s easy to disable. This is “the nuclear option”, but for sites with significant customizations on multiple pages, the overall performance may very well improve with MDS disabled.

Hope this will help you guy’s cheers… J