Creating SSRS Report joining from multiple SharePoint Lists

http://sprider.org/2013/01/22/ssrs-join-multiple-sharepoint-list-columns-using-lookupset-function/  -  SSRS –Join Multiple SharePoint List Columns Using Lookup Set Function


Follow these steps to install SSRS in SharePoint Integrated Mode with SQL Server 2012.
1. Ensure you first install SP1 for SQL Server 2012 - because only this contains the required assemblies for this integrated setup to work
2. I used the below Powershell script to install and configure SSRS in SharePoint Integrated mode


#This script Configures SQL Server Reporting Services SharePoint mode

$starttime=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime 

Write-Host -ForegroundColor Green "Import the SharePoint PowerShell snappin"
Add-PSSnapin Microsoft.Sharepoint.Powershell –EA 0

Write-Host -ForegroundColor Green "Install SSRS Service and Service Proxy, and start the service"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
    
    Write-Host -ForegroundColor Green "Install the Reporting Services Shared Service"
    Install-SPRSService

    Write-Host -ForegroundColor Green " Install the Reporting Services Service Proxy"
    Install-SPRSServiceProxy

    # Get the ID of the RS Service Instance and start the service 
    Write-Host -ForegroundColor Green "Start the Reporting Services Service"
    $RS = Get-SPServiceInstance | Where {$_.TypeName -eq "SQL Server Reporting Services Service"}
    Start-SPServiceInstance -Identity $RS.Id.ToString()

    # Wait for the Reporting Services Service to start...
    $Status = Get-SPServiceInstance $RS.Id.ToString()
    While ($Status.Status -ne "Online")
    {
        Write-Host -ForegroundColor Green "SSRS Service Not Online...Current Status = " $Status.Status
        Start-Sleep -Seconds 2
        $Status = Get-SPServiceInstance $RS.Id.ToString()
    }

$time=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime 
write-host -foregroundcolor DarkGray $time

Write-Host -ForegroundColor Green "Create a new application pool and Reporting Services service application"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
Write-Host -ForegroundColor Green "Create a new application pool"
#!!!! update "-Account" with an existing Managed Service Account
New-SPServiceApplicationPool -Name "Reporting Services" -Account "<domain>\User name>"
$appPool = Get-SPServiceApplicationPool "Reporting Services"

Write-Host -ForegroundColor Green " Create the Reporting Services Service Application"
#!!!! Update "-DatabaseServer", an instance of the SQL Server database engine 
$rsService = New-SPRSServiceApplication -Name "Reporting Services Application" -ApplicationPool $appPool -DatabaseName "Reporting_Services_Application" -DatabaseServer "<server name>"

Write-Host -ForegroundColor Green "Create the Reporting Services Service Application Proxy"
$rsServiceProxy = New-SPRSServiceApplicationProxy -Name "Reporting Services Application Proxy" -ServiceApplication $rsService

Write-Host -ForegroundColor Green "Associate service application proxy to default web site and grant web applications rights to SSRS application pool"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"   
# Associate the Reporting Services Service Applicatoin Proxy to the default web site...
Get-SPServiceApplicationProxyGroup -default | Add-SPServiceApplicationProxyGroupMember -Member $rsServiceProxy

$time=Get-Date
write-host -foregroundcolor DarkGray StartTime>> $starttime 
write-host -foregroundcolor DarkGray $time

Write-Host -ForegroundColor Green "Enable the PowerView and reportserver site features"
Write-Host -ForegroundColor Green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
#!!!! update "-url"  of the site where you want the features enabled
Enable-SPfeature -identity "powerview" -Url http://server/sites/bi
Enable-SPfeature -identity "reportserver" -Url http://server/sites/bi

####To Verify, you can run the following:
#Get-SPRSServiceApplication
#Get-SPServiceApplicationPool | where {$_.name -like "reporting*"}
#Get-SPRSServiceApplicationProxy 

The above was taken from: https://msdn.microsoft.com/en-us/library/jj219068.aspx#bkmk_full_script

3. Subsequent to this, you need to create a SQL Server Reporting Services Search Application (all default options and associate with the Web Application for which you want to activate this feature)

4. Then the SQL Server Reporting Services Service has to be started under Manage Services on Server

5. Then, you can develop your report in Visual Studio 2013 and Deploy, now if the Report does not render probably, then, follow the below.
Sometime following error occurred in the SQL Server Reporting:
Error 1:
 Web Part Error: A Web Part or Web Form Control on this Page cannot be displayed or imported. The type Microsoft.ReportingServices.SharePoint.UI.WebParts.ReportViewerWebPart, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 could not be found or it is not registered as safe. Correlation ID: 27a1579c-5445-00e2-0000-0738bef219cc.

SolutionAdd the following line in the web.config file of the sharepoint web application.

< SafeControl Assembly="Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" TypeName="*" Safe="True" />

Error 2:
"The Report Viewer Web Control HTTP Handler has not been registered in the application's web.config file.  Add <add verb="*" path="Reserved.ReportViewerWebPart.axd" type = Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> to the system.web/httpHandlers section of the web.config file, or add <add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebPart.axd" type="Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> to the system.webServer/handlers section for Internet Information Services 7 or later."

SolutionAdd the following line in the web.config file of the sharepoint web application.
< System.web><httpHandlers>
< add verb="*" path="Reserved.ReportViewerWebPart.axd" type = "Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /></httpHandlers>
And
< System.webServer><handlers>
< add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebPart.axd" type="Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /></handlers>

This is given in the below link:


All the best!

Comments

Popular posts from this blog

Using External Content Types with Stored Procedures with Input Parameters

Windows 7 Blue Screen because of ConnectiFy

HTTPS Site InfoPath Issue : You do not have permissions to access a web service that provides data required for this form to function correctly