DOTNET

DECLARE @StudentDetails XML='<StudentsDetails><StudentHeader Code="lt8990" JoiningDate="12 Nov 2014" OpeningDate="12 Nov 2014" ExpiryDate="12 Nov 2014" RollNumber="ref333">

</StudentHeader></StudentsDetails>'

DECLARE @StudentDetail AS TABLE

(

Code NVARCHAR(200),

JoiningDate DATETIME,

OpeningDate DATETIME,

ExpiryDate DATETIME,

RollNumber NVARCHAR(300)

)

INSERT INTO @StudentDetail

(

Code,

JoiningDate,

OpeningDate,

ExpiryDate,

RollNumber

)

SELECT

Students.value('(@Code)', 'NVARCHAR(200)'),

Students.value('(@JoiningDate)', 'DateTime'),

Students.value('(@OpeningDate)', 'DateTime'),

Students.value('(@ExpiryDate)', 'DateTime'),

Students.value('(@RollNumber)', 'NVARCHAR(200)')

FROM @StudentDetails.nodes('/StudentsDetails/StudentHeader')

AS Data(Students)

SELECT * FROM @StudentDetail

DECLARE @StudentDetails XML='<StudentsDetails>

</StudentsDetails>'

DECLARE @StudentDetail AS TABLE

(

StudentHeader NVARCHAR(200),

JoiningDate DATETIME,

OpeningDate DATETIME

)

INSERT INTO @StudentDetail

(

StudentHeader,

JoiningDate,

OpeningDate

)

SELECT

Students.value('(StudentHeader[1])', 'NVARCHAR(200)'),

Students.value('(JoiningDate[1])', 'DateTime'),

Students.value('(OpeningDate[1])', 'DateTime')

FROM @StudentDetails.nodes('/StudentsDetails') AS Data(Students)

SELECT * FROM @StudentDetail

First create a Data Set

Click on Add Button

It prompts for a message saying as

Click on Yes Button

Click on Table Adapter.

Click on New Connection

Click on Continue

Give your Server Name

User Name

Password

And Click on Test Connection.

And click on Ok

And Click on Next Button

You can write your own query or u can use the stored procedure

Click on Finish

Select crystal report.rpt file and the below screen is visible

And Click on Ok button

And then press ctrl +alt +T

Double Click on ADO.NET(XML)

Give the dataset path of the folder in FileNamePath

Click on Finish

And Build the solution

And Click on Ok Button

Delete Print Date and Page N from the crystal Report

Go to field explorer and drag your fields to Detail View

We can Format the Header Text by

We can change the font and color by Format Object Option

To increase the text size automatically

Select the text object in details tab and then

Right click on it (Format Object)

Now, the Report is Ready add it to the Default page as below

Write the Following Code in .cs page as below

private ReportDocument _reportDocument;

protected void Page_Load(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType =CommandType.StoredProcedure;

cmd.CommandText = "GetDepartmentInformation";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

_reportDocument = new ReportDocument();

_reportDocument.Load(Server.MapPath("CrystalReport.rpt"));

_reportDocument.SetDataSource(ds.Tables[1]);

CrystalReportViewer1.ReportSource = _reportDocument;

CrystalReportViewer1.DataBind();

}

Run, it display a blank page without output

To solve this issue,

Go to this Path : C:\inetpub\wwwroot\aspnet_client\system_web\4_0_30319 .

2) Copy folder name crystalreportviewers13 , and iclude it in your web project.

3) At the Head form where you load and have the crystal report viewer call this java script file.

<head runat="server"> <title>View Report</title>

</script>

</head>

In case if you want to Add your own Header Name, like Company Name and Address then, write the following code in .cs page as below

_reportDocument = new ReportDocument();

_reportDocument.Load(Server.MapPath("CrystalReport.rpt"));

_reportDocument.SetDataSource(ds.Tables[1]);

CrystalReportViewer1.ReportSource = _reportDocument;

CrystalReportViewer1.DataBind();

string CompanyName = "abc";

string Address = "Hyd";

_reportDocument.DataDefinition.FormulaFields["Company"].Text = "'" + CompanyName + "'";

_reportDocument.DataDefinition.FormulaFields["Address1"].Text = "'" + Address + "'";

Go to Crystal Report

Press Ctrl+Alt+T

Field Explorer

Similary Add Address in Field Explorer

Then Drag them to Report Header as below

In case if you want the output to be displayed in different format like excel, pdf, word, etc

private void ShowReport()

{

Response.Buffer = false;

Response.ClearContent();

Response.ClearHeaders();

//for pdf format

_reportDocument.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "abc")

//for excel forma

//_reportDocument.ExportToHttpResponse(ExportFormatType.Excel, Response, false, "abc");

//for rich text

//_reportDocument.ExportToHttpResponse(ExportFormatType.RichText, Response, false, "abc");

//for word

//_reportDocument.ExportToHttpResponse(ExportFormatType.WordForWindows, Response, false, "abc");

}

And call that method in main method

string CompanyName = "abc";
string Address = "Hyd";
_reportDocument.DataDefinition.FormulaFields["Company"].Text = "'" + CompanyName + "'";
_reportDocument.DataDefinition.FormulaFields["Address1"].Text = "'" + Address + "'";

　　　 ShowReport();

To Insert Border

To Insert Line (Similar to Table Columns)

DOTNET

Monday, December 15, 2014

Working With Xml DataTypes

Tuesday, December 9, 2014

Crystal Report Example

Kubernetes

Report Abuse