Crystal Reports CrossTab Report in Asp.Net

In this post Crystal Reports CrossTab report, we shall see on how to create a Cross Tab report using Crystal Reports, and display the results in an Asp.Net page using the CrystalReportViewer control.

A Crosstab report is a Matrix kind of a report, it shows the relation between 2 parameters, one of the parameter is represented row wise and the other parameter is represented column wise and the matrix cells show the relation between the parameters represented in the rows and columns.

Let us consider the following table structure.

Here we will try to get the relation between the parameters Department.Name and Employee.Salery.

First create a new .aspx page and drag a CrystalReportViewer control into the page. This control can be located in the Reporting section on the VS.Net 2008 toolbar.

<form id="frmCrossTab" runat="server">

<div>   

    <CR:CrystalReportViewer

ID="crvCrossTab"

runat="server"

      AutoDataBind="true" />   

</div>

</form>

Now let us create a Typed Dataset with a DataTable to hold the values which will be used to create the Report. The Typed Dataset will look as follows.

Now add a new CrystalReport file CrossTab.rpt to the Project

In the Field Explorer (Crystal Reports -> Field Explorer), map the dataset in the Database Expert window as follows.

Now we are all set to configure the report, in the Report file CrossTab.rpt right click in the Report Header section and select the option CrossTab as follows. Note that a CrossTab report can be added only to a ReportHeader or ReportFooter section.

Open the CrossTab expert as set the properties as follows.

Now we are done with the configuration, switch to the code-behind view to query and bind the data to the Report, as follows.

rptCrossTab objRptCrossTab;

string strQuery;

protected void Page_Load(object sender, EventArgs e)

{

    DisplayReport();

}

//

private void DisplayReport()

{

    SqlConnection objConn;

    SqlCommand objCmd;

    SqlDataAdapter objDA;

    //

    string strConn = "";

    objConn = new SqlConnection(strConn);

   

    strQuery = "Select E.Name as EmployeeName, E.Salery, D.Name as Department ";           

    strQuery += " from Employee E INNER JOIN Department D ON E.DepartmentID = D.ID";

    objCmd = new SqlCommand(strQuery, objConn);

    objDA = new SqlDataAdapter(objCmd);

    DataSet dsCrossTab = new DataSet();

    objDA.Fill(dsCrossTab, "dtCrossTab");

    //

    if (dsCrossTab.Tables["dtCrossTab"].Rows.Count > 0)

    {

        objRptCrossTab = new rptCrossTab();

        objRptCrossTab.SetDataSource(dsCrossTab);

        //

        crvCrossTab.DisplayGroupTree = false;

        crvCrossTab.ReportSource = objRptCrossTab;

    }

    else

    {

        crvCrossTab.Visible = false;

    }

}

Save and run the project, you will be able to see the Cross-tab output as follows in your Asp.net page.

That's it we have created a CrossTab Report using Crystal Reports and displayed the same using the CrystalReportViewer control in an asp.net page.