Tag Archive for: SharePoint

Well, rules have changed. As Jaime Tarquino from the SSRS team explained in his blog, “SQL Server 2012 Reporting Services SharePoint Integrated Mode”, there is no rsreportserver.config file anymore moving to Reporting Services 2012 in SharePoint integration mode (there are no configuration changes with native mode). Instead, the configurations sections are now saved in the SharePoint configuration database.

Problem: A customer plans to upgrade to SQL Server 2012 and SharePoint integration mode. They had the following custom renderer registered in the rsreportserver.config file that uses device information settings to customize the Excel renderer to suppress report headers and use Excel headers instead:

<Extension Name=“EXCELNOHEADER“ Type=“Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering“>

    <OverrideNames>

        <Name Language=“en-US“>Excel (no Header)</Name>

    </OverrideNames>

    <Configuration>

        <DeviceInfo>

            <SimplePageHeaders>true</SimplePageHeaders>

            <RemoveSpace>0.012in</RemoveSpace>

        </DeviceInfo>

    </Configuration>

</Extension>

The customer claimed that this configuration is so important that there is no way they could upgrade if this doesn’t work in SQL Server 2012. And after ensuring them that this is an easy fix, I’ve found myself spending hours to understand what needs to be done. On the positive side, I’ve picked up a few PowerShell skills along the way which may come handy given that PowerShell is omni-present nowadays.

Solution: In SQL Server 2012 (SharePoint integrated mode), you need to make such changes with PowerShell. The PowerShell Cmdlets topic in BOL is a good writeup about the SSRS cmdlets. The following script gets the job done:

Add-PSSnapin Microsoft.SharePoint.PowerShell

$apps = Get-SPRSServiceApplication

foreach ($app in $apps)

{

New-SPRSExtension -identity $app -ExtensionType “Render” -name “EXCELNOHEADER” -TypeName “Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering” -ServerDirectives “<OverrideNames><Name Language=’en-US’>Excel (no Header)</Name></OverrideNames>” -ExtensionConfiguration “<DeviceInfo><SimplePageHeaders>true</SimplePageHeaders><RemoveSpace>0.012in</RemoveSpace></DeviceInfo>”

}

The script starts by importing the Microsoft.SharePoint.PowerShell snapi (you don’t need it if you run the remainder of the script in the SharePoint 2010 Management Shell). Then, the Get-SPRSServiceApplication cmdlet returns all the Reporting Services Service Applications available in the farm. For each application, I call the New-SPRSExtension to register the new renderer. To run the script:

1. Remote to the SharePoint server. You must have SharePoint Farm Administrator rights to run the script.
2. Open SharePoint 2010 Management Shell from the Microsoft SharePoint 2010 Products program group. Or, if you have installed the Windows PowerShell ISE feature, you can open Windows PowerShell ISE from the Accessories program group but make it sure to run it as administrator (right-click on it and click Run As Administrator).
3. Copy and paste the script, and hit Enter to run it. The figure below shows the script in Windows PowerShell ISE:
4. Do iisreset to apply the configuration changes.

For a full list of the New-SPRSExtension cmdlet parameters, you can execute the following command: 

Get-help New-SPRSExtension – full 

To verify that the changes are applied you could use one of the following two approaches:

1. Execute the following script to output the configuration of all extensions:
   $apps = Get-SPRSServiceApplication

foreach ($app in $apps) {

    $extensions = Get-SPRSExtension -identity $app

    foreach ($extension in $extensions) {

echo $extension.ExtensionType $extension.Name

echo $extension | select -ExpandProperty ConfigurationXml }

} 

2. In SSMS, connect to the SharePoint config database and run the following query. Examine the content of the Properties column to make sure that the custom extension is registered.

SELECT * FROM [dbo].[Objects] WHERE properties like ‘%excelnoheader%’     

I was tempted to try modifying directly the Properties column in the Objects table and add the new extension section but I realized I had better use of my time than troubleshooting failed SharePoint installations so I decided to stick with the supported way.

Special thanks to Prash Shirolkar from the Reporting Services team for shedding light in dark places. His blog has valuable insights about Reporting Services and SharePoint.

A week ago, I raved about how the new PowerPivot Configuration Tool would simplify configuring PowerPivot for SharePoint. Guess what? Setting up Reporting Services for SharePoint integrated mode got simplified too! In SQL Server Denali, Reporting Services leverages the SharePoint service application infrastructure and it doesn’t require installing a Reporting Services server. Not only this simplifies setup but improves performance because there is no round-tripping between SharePoint and report server anymore. Configuring Reporting Services for SharePoint integration mode is a simple process that requires the following steps:

1. Run the SQL Server Denali CTP3 setup and Install Reporting Services and the add-in on the SharePoint application server.
2. Once the bits are installed, open SharePoint Central Administration, and click Manage Service Applications. Expand the New button and click SQL Server Reporting Services Service application.
3. In the page that follows, configure the service application details as per your specific environment and click OK.
4. As with the previous releases, one more step is required to enable the SSRS content types in a given document library.

   a) Go to the document library settings page and click Advanced Settings. In the Advanced Settings page, select Yes for the Allow management of content types?

   

   b) Back to the document library settings page, click the Add from Existing Site Content Types link.
   
   c) In the Add Content Types page, add BISM Connection File (assuming PowerPivot for SharePoint is configured), and the Report Builder content types.

   

Voila! Once this is done, you can create Crescent reports. You just need to create a BISM Connection File that points to a PowerPivot model or BISM Tabular model.