SharePoint Web Part using ReportViewer(Reporting Services SharePoint Integration)

If you want to create Web Part using ReportViewer and is consumed by your SharePoint site and the Reporting Services is configured to SharePoint Integration. First you must change ReportServerCredential.

If you use this code :

ReportViewer1.ServerReport.ReportServerCredentials.NetworkCredentials =
new NetworkCredential("administrator", "password", "domain");

You will get a message that the NetworkCredential is readonly. So you must create a custom ReportServerCredentials that implement Microsoft.Reporting.WebForms.IReportServerCredentials. Below is the code and make sure your custom ReportServerCredentials class must using [Serializable] :

1:[Serializable]

2:  public class ReportServerCredentials : Microsoft.Reporting.WebForms.IReportServerCredentials

3:  {

4:     private string _UserName;

5:     private string _Password;

6:     private string _DomainName;

7:     public ReportServerCredentials(string UserName, string Password,
                                        string DomainName)

8:     {

9:         _UserName = UserName;

10:        _Password = Password;

11:        _DomainName = DomainName;

12:     }

13:     public WindowsIdentity ImpersonationUser

14:     {

15:         get { return null; }

16:     }

17:      

18:     public ICredentials NetworkCredentials

19:     {

20:         get

21:         {

22:             return new NetworkCredential(_UserName, _Password, _DomainName);

23:         }

24:     }

25:     public bool GetFormsCredentials(out Cookie authCookie, out string userName,
               out string password, out string authority)

26:     {

27:         authCookie = null;

28:         userName = null;

29:         password = null;

30:         authority = null;

31:         return false;

32:     }

33: }

The datasource of the report i use stored procedure. Where the parameter of the report @Status, @EmployeeId, @DeptName, @EmployeesListID and @ApprovedLeavePlanListID, i configured the parameter as a Hidden parameter because I do not want it to be displayed in the ReportViewer component. Below is the code

1:  CREATE PROCEDURE [dbo].[SP_LeaveBlockLeaveReport]

2:      @StartDate datetime,

3:      @EndDate datetime,

4:      @Status int,

5:      @EmployeeId int,

6:      @DeptName varchar(50),

7:      @EmployeesListID varchar(50),

8:      @ApprovedLeavePlanListID varchar(50)

9:  AS

10:  BEGIN

11:    SET NOCOUNT ON;

12:    DECLARE @Query varchar(1000)

13:      

14:    SET @Query ='SELECT Employee.nvarchar4 as EmployeeName,
       ApprovedLeavePlan.StartLeave, 

15:    ApprovedLeavePlan.EndLeave, ApprovedLeavePlan.LeaveType,
       Employee.float4 as LeaveBalance,

16:    Employee.float2 as LeaveWithPermission, Employee.float6 as BlockLeave,
       ApprovedLeavePlan.Amount

17:    FROM AllUserData as Employee 

18:    LEFT JOIN (SELECT int1 as EmployeeID, nvarchar3 as LeaveType,
                  float1 as Amount, datetime1 as StartLeave, datetime2 as EndLeave

19:               FROM AllUserData

20:               WHERE tp_ListId='''+@ApprovedLeavePlanListID+'''

21:        AND tp_DeleteTransactionId = 0x) as ApprovedLeavePlan
           ON Employee.tp_ID=ApprovedLeavePlan.EmployeeID 

22:        AND ApprovedLeavePlan.StartLeave >= ''' +
           CONVERT(nvarchar(30), @StartDate, 109) +'''

23:        AND ApprovedLeavePlan.EndLeave <= ''' + 
           CONVERT(nvarchar(30), @EndDate, 109)  +'''

24:    WHERE tp_ListId='''+@EmployeesListID+'''

25:    AND tp_DeleteTransactionId = 0x AND tp_RowOrdinal=0'

26:      

27:    if @Status=0

28:        SET @Query = @Query + ' AND tp_ID=' + CAST(@EmployeeId as varchar(10))

29:    else if @Status=1

30:        SET @Query = @Query + ' AND nvarchar8=''' + @DeptName +''''

31:    else if @Status=2

32:        SET @Query =  @Query + ''

33:    else if @Status=3

34:        SET @Query =  @Query + ''

35:      

36:    SET @Query =  @Query + ' ORDER BY EmployeeName'

37:    EXEC(@Query)    

38:END

Below is the code to call the Reporting Services at Page_Load

1:  [Serializable]

2:  public partial class WebUserControl : System.Web.UI.UserControl

3:  {

4:    protected void Page_Load(object sender, EventArgs e)

5:    {

6:       if (!IsPostBack)

7:       {

8:          ReportViewer1.Visible = true;

9:          ReportViewer1.ProcessingMode = ProcessingMode.Remote;

10:         ReportViewer1.Width = new Unit(100, UnitType.Percentage);

11:         ReportViewer1.Height = new Unit(100, UnitType.Percentage);

12:         ReportViewer1.Style.Add("width", "100%");

13:         ReportViewer1.Style.Add("height", "100%");

14:         ReportViewer1.ZoomPercent = 100;

15:         ReportViewer1.AsyncRendering = false;

16:         ReportViewer1.ShowDocumentMapButton = true;

17:         ReportViewer1.DocumentMapCollapsed = false;

18:   

19:         ReportViewer1.ServerReport.ReportServerCredentials = 
          new ReportServerCredentials("administrator", "password", "domain");

20:   

21:         ReportViewer1.ServerReport.ReportServerUrl = 
          new Uri("http://localhost/ReportServer");

22:         ReportViewer1.ServerReport.ReportPath =
              "http://localhost:1234/Reports/ReportsLibrary/LeaveBlockLeave.rdl";

23:   

24:         ReportParameter[] parameter = new ReportParameter[5];

25:         ReportParameter p = null;

26:         p = new ReportParameter("Status", "0");

27:         parameter[0] = p;

28:         p = new ReportParameter("EmployeeId", "22");

29:         parameter[1] = p;

30:         p = new ReportParameter("DeptName", "Information Technology");

31:         parameter[2] = p;

32:         p = new ReportParameter("EmployeesListID", 
                             "1319C369-3F19-42F8-87CA-8403AB940573");

33:         parameter[3] = p;

34:         p = new ReportParameter("ApprovedLeavePlanListID", 
                              "BB8AFBA9-FC27-4388-B4F6-76C1EB3DE9EF");

35:         parameter[4] = p;

36:         ReportViewer1.ServerReport.SetParameters(parameter);

37:      }

38:   }

39: }

Don’t forget to add this code to your web.config of your Share Point Site :

1. Add to <SafeControls> section.
<SafeControl Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TypeName="*" Safe="True" />

2. Add to <assemblies> section.
<add assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a" />

3. And also change enableSessionState to true at <pages enableSessionState="false".

Below you can see the Report is displayed in your SharePoint site. Fill StartDate and EndDate parameter and "View Report" button is clicked.