ASP .NET Cross tab Crystal Report Step by Step

1. Stored Procedure

2. Report Design

3. XML file creation and linking with table data

4. aspx Code

5. aspx.cs Code

1. Stored Procedure

USE [dbRegistration]
GO
/****** Object:  StoredProcedure [dbo].[sp_TabulationSheetDeptTermWise]    Script Date: 11/16/2011 11:28:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_TabulationSheetDeptTermWise]
as
--exec sp_TabulationSheetDeptTermWise
begin
	DECLARE	@Cols NVARCHAR(2000),
			@Parameter NVARCHAR(2000),
			@StudentId int,
			@StudentName varchar(100),
			@DeptId varchar(50),
			@CourseCode1 varchar(50),
			@CourseCode2 varchar(50),
			@CourseCode3 varchar(50),
			@CourseCode4 varchar(50),
			@CourseCode5 varchar(50),
			@CourseCode6 varchar(50),
			@CourseCode7 varchar(50),
			@CourseCode8 varchar(50),
			@CourseCode9 varchar(50),
			@CourseCode10 varchar(50),
			@CourseCode11 varchar(50),
			@CourseCode12 varchar(50),
			@LetterGrade varchar(5),
			@IDX int,
			@CountId int,
			@Cols1 NVARCHAR(2000),
			@HeaderShow NVARCHAR(2000)

			set @IDX= 1
			set @CountId = 0
			SET @Cols = ''
			set @HeaderShow = ''
			set @Parameter = '@Cols NVARCHAR(2000)'
					 
			--collect distinct Course Code that we have in tblStudentResult
			SELECT @Cols = @Cols + '[' + CourseCode + ']' + ', ' FROM
			(SELECT DISTINCT isnull(CourseCode,'N/A') as CourseCode FROM
			vw_TabulationSheetDeptTermWise ) AS s order by s.CourseCode
			SET @Cols = LEFT(@Cols,LEN(@Cols)-1)
			
			SELECT @HeaderShow = @HeaderShow +  CourseCode +  ', ' FROM
			(SELECT DISTINCT isnull(CourseCode,'N/A') as CourseCode FROM
			vw_TabulationSheetDeptTermWise ) AS s order by s.CourseCode
			SET @HeaderShow = LEFT(@HeaderShow,LEN(@HeaderShow)-1)
			
			set @Cols1 = @Cols
			
			WHILE @IDX!= 0
			BEGIN
				SET @IDX = CHARINDEX(',',@Cols1)
				IF @IDX != 0
					SET @CountId = @CountId + 1
				ELSE
					SET @CountId = @CountId + 1

				SET @Cols1 = RIGHT(@Cols1,LEN(@Cols1)-@IDX)
				IF LEN(@Cols1)= 0 
					BREAK
			END
			
			--select @CountId
			if(@CountId = 1)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','+ ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'+ ',[.........]'+ ',[..........]'+ ',[...........]'					
				end
			else if(@CountId = 2)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'+ ',[.........]'+ ',[..........]'					
				end
			else if(@CountId = 3)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'+ ',[.........]'					
				end
			else if(@CountId = 4)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'					
				end			
			else if(@CountId = 5)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'					
				end
			else if(@CountId = 6)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ',' 
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]' 				
				end		
			
			else if(@CountId = 7)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]'	
				end				
			else if(@CountId = 8)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'				
				end				
			else if(@CountId = 9)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' 				
				end	
			else if(@CountId = 10)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' 
					set @Cols = @Cols + ',[.]' + ',[..]' 			
				end	
			else if(@CountId = 11)
				begin
					set @HeaderShow = @HeaderShow + ',' 
					set @Cols = @Cols + ',[.]' 			
				end	
			else if(@CountId = 12)
				begin
					set @HeaderShow = @HeaderShow  
					set @Cols = @Cols				
				end	
			
			
			
			select @HeaderShow as MainRow
		--select @Cols	
			delete from T_TempReport
			declare @sqlstatement nvarchar(4000)
			--move declare cursor into sql to be executed
			set @sqlstatement = 'declare cursorName cursor for SELECT * FROM (SELECT StudentId,LetterGrade, CourseCode FROM
			vw_TabulationSheetDeptTermWise ) ps PIVOT ( MAX(LetterGrade) FOR CourseCode IN (
			'+@cols+') ) AS pvt'

			exec sp_executesql @sqlstatement,@Parameter ,@cols
			   
			open cursorName
			fetch next from cursorName
			into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6,@CourseCode7,@CourseCode8,@CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12
			while @@FETCH_STATUS = 0
			begin
					insert into T_TempReport(StudentId,Sub1,Sub2,Sub3,Sub4,Sub5,Sub6,Sub7,Sub8,Sub9,Sub10,Sub11,Sub12)
					values(@StudentId,@CourseCode1,@CourseCode2,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6,@CourseCode7,@CourseCode8,@CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12)
			
				fetch next from cursorName
				into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6,@CourseCode7,@CourseCode8,@CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12
			end
			close cursorName
			deallocate cursorName
      
     select Distinct CAST( T_TempReport.StudentId as Varchar(20)) as StudentId,T_Std_General_Information.StudentsName as StudentName,T_TempReport.Sub1,T_TempReport.Sub2,T_TempReport.Sub3,T_TempReport.Sub4,T_TempReport.Sub5,T_TempReport.Sub6,
     T_TempReport.Sub7,T_TempReport.Sub8,T_TempReport.Sub9,T_TempReport.Sub10,T_TempReport.Sub11,T_TempReport.Sub12 
     from T_TempReport INNER JOIN T_Std_General_Information ON T_TempReport.StudentId=T_Std_General_Information.StudentId
end
2. XML file 
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="xmlTabulationDeptTermWise"
    targetNamespace="http://tempuri.org/xmlTabulationDeptTermWise.xsd"
    elementFormDefault="qualified"
    xmlns="http://tempuri.org/xmlTabulationDeptTermWise.xsd"
    xmlns:mstns="http://tempuri.org/xmlTabulationDeptTermWise.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="xmlTabulationDeptTermWise">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="StudentId" type="xs:string" />
        <xs:element name="StudentsName" type="xs:string" />
        <xs:element name="Sub1" type="xs:string" />
        <xs:element name="Sub2" type="xs:string" />
        <xs:element name="Sub3" type="xs:string" />
        <xs:element name="Sub4" type="xs:string" />
        <xs:element name="Sub5" type="xs:string" />
        <xs:element name="Sub6" type="xs:string" />
        <xs:element name="Sub7" type="xs:string" />
        <xs:element name="Sub8" type="xs:string" />
        <xs:element name="Sub9" type="xs:string" />
        <xs:element name="Sub10" type="xs:string" />
        <xs:element name="Sub11" type="xs:string" />
        <xs:element name="Sub12" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  
  
</xs:schema>
3. .rpt design
4. .rpt--> file explorer-->set data source location--> ADO.NET --> XML-->browse from your project folder
5. write parameter field
6. aspx file
<div style="width: 580px;">
        <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
    </div>
7. aspx.cs file
using System;
using System.Collections;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.IO;
using System.Diagnostics;
using RegistrationandExamination.BLL;
using System.Configuration;

namespace Registration_and_Examination
{
    public partial class ReportTabulationSheetDeptTermWise : System.Web.UI.Page
    {
        String ConString = ConfigurationManager.ConnectionStrings["RegistrationandExamination"].ConnectionString.ToString();
        #region Declarations

        String rptSubstring = "";
        ReportDocument myReportDocument = new ReportDocument();
        String ReportPath = "";

        #endregion


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadReport();
            }
        }

        private void LoadReport()
        {
            String SqlString = "sp_TabulationSheetDeptTermWise";
            SqlCommand cmd = new SqlCommand();
            SqlConnection con = new SqlConnection(ConString);
            con.Open();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = SqlString;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataTable dt1 = ds.Tables[0];
            DataTable dt2 = ds.Tables[1];

            CreateReport("TabulationSheetDeptTermWise.rpt", dt1, dt2);
        }

        private void CreateReport(string ReportName, DataTable Herader, DataTable ReportSource)
        {


            try
            {
                String[] MainRow = Herader.Rows[0]["MainRow"].ToString().Split(',');
                String PSub1 = MainRow[0].ToString();
                String PSub2 = MainRow[1].ToString();
                String PSub3 = MainRow[2].ToString();
                String PSub4 = MainRow[3].ToString();
                String PSub5 = MainRow[4].ToString();
                String PSub6 = MainRow[5].ToString();
                String PSub7 = MainRow[6].ToString();
                String PSub8 = MainRow[7].ToString();
                String PSub9 = MainRow[8].ToString();
                String PSub10 = MainRow[9].ToString();
                String PSub11 = MainRow[10].ToString();
                String PSub12 = MainRow[11].ToString();


                CrystalReportViewer1.GroupTreeStyle.Reset();
                ReportPath = Convert.ToString(Server.MapPath("~\\" + ReportName));
                myReportDocument.Load(ReportPath);
                myReportDocument.SetDataSource(ReportSource);


                myReportDocument.SetParameterValue("PSub1", PSub1);
                myReportDocument.SetParameterValue("PSub2", PSub2);
                myReportDocument.SetParameterValue("PSub3", PSub3);
                myReportDocument.SetParameterValue("PSub4", PSub4);
                myReportDocument.SetParameterValue("PSub5", PSub5);
                myReportDocument.SetParameterValue("PSub6", PSub6);
                myReportDocument.SetParameterValue("PSub7", PSub7);
                myReportDocument.SetParameterValue("PSub8", PSub8);
                myReportDocument.SetParameterValue("PSub9", PSub9);
                myReportDocument.SetParameterValue("PSub10", PSub10);
                myReportDocument.SetParameterValue("PSub11", PSub11);
                myReportDocument.SetParameterValue("PSub12", PSub12);



                CrystalReportViewer1.ReportSource = myReportDocument;
                CrystalReportViewer1.DataBind();

                //Report Convert Into PDF Report
                //myReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, CommonVariable.PDFLoadPath);
                //psi.FileName = CommonVariable.PDFLoadPath;
                //prs.StartInfo = psi;
                //prs.Start();
            }
            catch (Exception ex)
            {
                CrystalReportViewer1.ReportSource = myReportDocument;
                CrystalReportViewer1.DataBind();
                ex.Message.ToString();
            }
        }
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s