Tuesday, April 18, 2017

Create Report using ReportViewer

Reports can be created using third party Applications like Crystal Report but if we are
using Microsoft's report we will get the flexibility as it avoids deployment issues
regarding versions and license.

In this article I will explain how to create a report using Visual Studio Report(.rdlc)
and display using ReportViewer Control in Local Processing mode.

ReportViewer Control can be used in two processing modes, Local and Remote.

In Local processing mode, report processing takes place in the client Application
where as in Remote processing mode, processing takes place in Reporting Services
Report Server of MS Sql Server 2008 or above.

Display report using ReportViewer Control in Local Processing mode can be used
as an alternative to Crystal Report or other Softwares.

For this DataSets can be used.

I will summarize the steps below:

1. Create one typed Dataset as follows :

Right click in Solution explorer -> Add New Item -> DataSet
It will be created by default in App_Code folder.

In DataSet Designer-> Right click -> Add DataTable.

In DataTable -> Add Column
Define all columns with definite DataType in the DataTable as per the fields
required as shown in the figure below :
















2. Create Report Wizard as following :

Right click in Solution explorer -> Add New Item -> Report Wizard- Click Add

















-> Click Next -> Choose the created DataSet as the Data Source





















-> Click Next -> Choose the report type as tabular





















-> Click Next -> Choose the available fields





















-> Click Next -> Choose the table layout as stepped




















-> Click Next -> Choose the table style





















-> Click Next -> Click Finish

Report will be created as the following :










We can design the report as per our need.

If we want to add a header image, we have to do the following :

Click on the top toolbar menu, Report -> Embedded images
New Image -> Add

Drag Image control from the report's toolbox and select in properties window,
source as Embedded and value as the image name from the dropdownlist.

Now the report wizard is ready.

3.  Create a function that returns the typed dataset created above and the code for
it in Business Layer and Data Layer Classes is as the following :

App_Code/AgentBLL :


public DataSet1 getAgentReportData()
{
   return DAL.getAgentReportData();

}

App_Code/AgentDAL :


public DataSet1 getAgentReportData()
{
        string connectionString =            ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        DataSet1 ds = new DataSet1();

        using (SqlConnection dbConnection = new SqlConnection(connectionString))
        using (SqlCommand dbCommand = new SqlCommand())
        {
            string commandText = "SELECT AGN_ID,AGN_NO,AGN_Name from Agents order by AGN_SEQ desc";

            dbCommand.CommandText = commandText;

            dbCommand.Connection = dbConnection;
            dbConnection.Open();

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = dbCommand;

            da.Fill(ds, "DataTable1");
            dbConnection.Close();
        }
        return ds;
}

4. Create webform and place Microsoft ReportViewer Control in it. The code for it is :

Report.aspx :


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Report.aspx.cs" Inherits="Report" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
    Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="882px">
        </rsweb:ReportViewer>
    </div>
    </form>
</body>
</html>

Report.aspx.cs :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;
using System.Data;

public partial class Report : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
            DataSet1 ds = new DataSet1();
            AgentBLL bll = new AgentBLL();
            ds = bll.getAgentReportData();
            ReportDataSource datasource = new ReportDataSource("DataSet1_DataTable1", ds.Tables[0]);
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(datasource);
        }

    }
}

The output will be as the following :




















Tips for common Issues :


Inorder to show width and height of the table columns and page of report correctly
while printing and while exporting to pdf we have to follow the rule that 
Body.Width <= Report.Width - Report.Margins.Right - Report.Margins.Left.

If we follow the above rule it will avoid blank pages coming in between in pdf
and also while printing. While printing we have to adjust the report page size 
both width and height according to the paper size and also according to the 
print Layout we require,either Portrait or Landscape.

If there is issue of Print button not displaying in ie, add the url of the website to
compatibility view settings.

While printing if it displays the error "Unable to load client print control", install
Microsoft ReportViewer 2008 SP1 in both web server and local pc, if we are
using ReportViewer of VS 2008 otherwise install SP1 of the appropriate version.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)