1.0 Prerequisites

1. SharePoint Server 2013 - Installed on SharePoint App and Web Server
2. Service Pack 1 (SP1) For SharePoint Server 2013 - Installed on SharePoint App and Web Server
3. Microsoft SQL Server Reporting services 2012 SP1 Add-in For Microsoft SharePoint (Only If you installed SQL Server 2012)
4. Reporting Services - SharePoint (SQL Setup ISO) - Installed on SQL Server and SharePoint Web Server.
5. Reporting Services Add-in For SharePoint Products (SQL Setup ISO) - Installed on SQL Server and SharePoint Web Server.

1.1 Installation

1. Run the SQL Server Installation Wizard (Setup.exe).

2. Click Installation in the left side of the wizard and then click New SQL Server stand-alone installation or add features to an existing installation.

3. Check the features related to reporting services as in figure 1.

Figure 1

4. Install Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013, and note that it is only available in 32-bit version to install it as a new instance instead of existing instance to avoid fault errors.

http://www.microsoft.com/en-us/download/confirmation.aspx?id=42313

2 Configuration

1. Right-click SharePoint 2013 Management Shell click Run as administrator.

2. Run the following PowerShell command to install the SharePoint service.

Install-SPRSService

3. Run the following PowerShell command to install the service proxy.

Install-SPRSServiceProxy

4.  Run the following PowerShell command to start the service

get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

or from centration administration

a) Manage Services on Server in the System Settings group.

b) Find SQL Server Reporting Services Service and click Start in the Action column.

5. Next step to configure SQL Reporting services service application. Go to Central Administration => Application Management => Click Manage service application under Service Applications, Click on New and click on “SQL Server Reporting Services Service Application”

Figure 2

6. Provide Service Application Name, Create New Application Pool and provide Application Pool Name, select security account for app pool name prefer domain account, Provide Database server and credential to connect to DB server. Select the Web Application to be provisioned for access by the current Reporting Services Service Application.

Figure 3

7. To start to use Reporting, make sure the site collection feature: [Report Server Integration Feature ] is activated

Verify an instance of the SSRS service application exists; "SQL Server Reporting Services Service Application". In SharePoint Central Administration, o to "Manager Service Applications".
Verify the service application proxy is configured and the service application is associated with web application as the following.

Figure 4

go to Configure service application associations

Figure 5

Figure 6

3 Build SSRS Reports

There are three ways to build your report as numbered below by VS, SSDT or on SharePoint. finally you will get the same result which is RDL file.

3.1 Build RRS Report on BIDS (Recommended)

- Now, Click File, Add, New Project to add a Report Server project to the solution.ui

Figure 7

- You also create a shared DataSource as below.

Figure 8

Figure 9

Figure 10

- Click on Credentials and fill in the dialog as shown below:

Figure 11

- Create a new dataset from the shared datasource as below figures.

Figure 12

Figure 13

Figure 14

- Right click on reports folder and click add new report.

Figure 15

- Note: When building a report that uses data in a SharePoint list or SQL Connection DataSet, the Data Source Connection String property must be set to the URL of the SharePoint site or subsite that contains the list you want to use in your report.
Note: Make sure you create only one shared data source that all data sets reference to it, on deployment, you will change the connection string for Shared Data Source only.

Figure 16

- Set SSRS Server Url

Figure 17

- Set a Document Library Url to deploy reports inside it. then Select your TargetServerVersion , so if you are using SQL Server 2014, you have to choose sql server 2008, 2012 or 2014, if you are using SQL server 2008 R2 or later, then choose sql server 2008 R2 or later, otherwise if you are using a version above SQL Server 2014, then choose SQL Server 2016 or later, this is Important to specify your target SQL Server Version.

Figure 18

- Deploy Reports Project as below figure.

Figure 19

3.2 Build Report on SSRS Server

1. Go to SQL Server 2014 Configuration Manager

Figure 20

2. Click on Report Builder as below figure.

Figure 21

3. Create new DataSource as below figure.

Figure 22

4. Create new black report as below figure.

Figure 23

3.3 Build Reports on SharePoint

1 Create a document Library, and make sure the Library [Allow management of content types] by going to Library Settings => Advanced Settings

2 Navigate down select “Add from existing site content types” under “Content Types”

3 Now we will add content types

Figure 24

5. Add new item in the document library “Data Source” and set below configurations

Figure 25

6. Create new DataSouce as below.

Figure 26

Figure 27

7. Add new item -> Report Builder.

8. If you face a problem, download the file below

https://www.microsoft.com/en-us/download/details.aspx?id=6116

9. If you face a problem, go to SSRS Configuration manager then click on report builder to install the report application as below figure.

Figure 28

10. Then the below figure will appear.

Figure 29

11. Create new black report as below

Figure 30

4 View Reports

There are many approaches to view SSRS report as below.

4.1 SSRS web Part (Recommended for SharePoint)

1. Edit your page and add SSRS Viewer Web Part as below.

Figure 31

2. Edit Web Part properties and set report URL that refers to a location in SharePoint document library.

Figure 32

3. Click on button beside of Report property.

Figure 33

Figure 34

Figure 35

4. You can also export SSRS WebPart and embed it in your custom site definition.

Figure 36

Figure 37

Figure 38

4.2 Report Viewer (Recommended for .NET)

This approach (Accepted) is to view SSRS on .NET Report viewer where you need to pass web service url Not Report server url programmatically, you will find the following issues that we made a workaround in order to resolve them.

You pass the report name “?reportname=xx” as a parameter in the ViewDirectorateReport.

1. Text orientation on word document

SYMPTOM:

- Text orientation is wrong when you export report as a word.

CAUSE:

- Report viewer take LTR direction as a default direction.

RESOLUTION:

- Right click on each textbox and choose properties windows then move to the localization tab and make direction field to RTL in case it is an Arabic text or LTR in case of English text.

2. Date Format

SYMPTOM:

- Date format appear with time in DATETIME fields.

CAUSE:

- View DATETIME field with time by default.

RESOLUTION:

- Reference technical problem #5.1.

3. Date Picker not appear

SYMPTOM:

- Date Picker not appear in report viewer.

CAUSE:

- There are script that leads to this conflict.

RESOLUTION:

- Reference technical problem #5.1..

1.1.1 Report Viewer Problems

- First find the following line in AppSettings in your web.config file and comment it out as shown below:

- Enable session at page level by setting the enableSessionState to true of <page…..> tag inside system.web.

<pages enableSessionState="true" enableViewState="true" enableViewStateMac="true" validateRequest="false" ……………………=""

- Add the following tag inside system.web\httphandlers section

<system.web>

</httpHandlers>

- Add the following line in the system. Webserver\handlers

- Comment out the following line in the system. Webserver\httphandlers (If Exists)

4.3 SSRS Report Link on Page Viewer (Not Recommended)

- This approach (Rejected) is to view SSRS report on a page viewer web part where you just pass the report URL, this approach not accepted because you need to add rules for each user want to access reports.

SYMPTOM:

rrsAccessDenied

Figure 39

CASUSE:

The permissions granted to user 'domain\username' are insufficient for performing this operation. (rsAccessDenied) as in figure 17

RESOLUTION:

Add the user account to the new system role, this is done in report manager under Site Settings ->Security -> New Role Assignment where you need to add each use want to access report or add user on folder/report -> right click -> permissions -> add user as a new role assignment where security rules is inherited.

Figure 40 Edit Report Security

Figure 41

5 Assumptions

5. Date Picker Not Appear Problem

Problem:

- In case date picker of .net report viewer does not be rendered:

Resolution:

- Go to page tag and add culture tag as below:

Figure 42 Change Culture of Page (Date Picker issues)

6. SSRS Integration Feature could not be activated in sharepoint site
Solutions:
Enable-SPFeature -Identity E8389EC7-70FD-4179-A1C4-6FCB4342D7A0 -Url http://siteurl -force
7. The report server has encountered a configuration error. Logon failed for the unattended execution account.”
Solution:
Go to Central Administration -> Manager service application Sql server reporting services Service Application -> Execution Account -> set your domain account