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.

 Solution: Add 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."

 Solution: Add 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:

http://sharepoint2013suresh.blogspot.in/2014/02/how-to-configure-reporting-services.html - Report viewer configuration

All the best!

Comments

Post a Comment

Popular posts from this blog

Using External Content Types with Stored Procedures with Input Parameters

Image
You must have tried External Content Types - the exciting new feature in SharePoint 2010 to connect to an external SQL table and provide a list where it lists, creates, updates data straight into the SQL table. People always wonder, how do you get a ECT to work with a Stored Procedure, one that has input parameters. You could do it this way... Step 1: In your SharePoint Designer 2010, create a new external content type Step 2: Connect to your SQL Server and then make sure you see the Routines (your stored procedures must be listed here) Step 3: Rt Click on the Stored Procedure name and create operations for "Read Item" and "Read List" - both are required for the External Content Type's SharePoint list to be created. During both these wizards, in the Input Parameters, your SP's Input Parameter will be listed. Here, you need to create a filter, which sends out it's value to the SP's filter. For example, if your SP's Input Parameter is @Emp...
Read more

Validate a SharePoint Date Field against Current Date in Javascript

Consider the following scenario: You have a SharePoint Site which has its regional settings set to English (UK) - so that the date format becomes dd/mm/yyyy in all date pickers You would like to validate a particular date field in your List or Document Library against the current date If the validation does not pass, you should not allow the form to save So what the script does is: Uses the sputility (check my previous posts and also look at http://sputility.codeplex.com) to get the particular SharePoint date field It comes out as a date datatype, so I cast it to string Since the format is in dd/mm/yyyy and Javascript compare and date functions work only with mm/dd/yyyy, I am converting them back to that format then using UTC string conversions and comparing... Enjoy! <script type="text/javascript"> function PreSaveAction() {  var createdate = SPUtility.GetSPField('Date of Creation').GetValue();  var createdatestr=createdate.toString();      ...
Read more