Calender popup

Page Load
-----------
btnProjectStartDt.Attributes.Add("onclick", "javascript:popUpCalendar(this,txtProjectStartDt,'dd/mm/yyyy');return false;");

filling the dataset in the report

Filling the dataset in the report
--------------------------------
string constr = ConfigurationManager.ConnectionStrings["Reports"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("CrossTab", con);
ReportDocument rpt = new ReportDocument();

SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

try
{

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectStartDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));
cmd.Parameters.Add("@Enddate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectEndDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));

adap.Fill(ds, "Orders");

if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
rpt.Load(Server.MapPath("CR.rpt"));
rpt.SetDataSource(ds.Tables[0]);

crvCrossTab.ReportSource = rpt;
crvCrossTab.DataBind();
crvCrossTab.Visible = true;
}
else
{
Response.Write("No Record Exist");
crvCrossTab.Visible = false;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();

con = null;


}

Displaying the data in GridView

string constr = ConfigurationManager.ConnectionStrings["Reports"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("Matrix", con);

SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

try
{

cmd.CommandType = CommandType.StoredProcedure;

CultureInfo ci = new CultureInfo("en-US");
cmd.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectStartDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));
cmd.Parameters.Add("@Enddate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectEndDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));

adap.Fill(ds, "Orders");

if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
gvCrossTab.DataSource = ds.Tables[0];
gvCrossTab.PageSize =10;
gvCrossTab.DataBind();
}
else
{
Response.Write("No Record Exist");
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();

con = null;
}

Dynamic Column Query

ALTER PROCEDURE [dbo].[Matrix]
(
@StartDate varchar(20),
@EndDate varchar(20)
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @T AS TABLE(y int NOT NULL PRIMARY KEY)

INSERT INTO @T SELECT DISTINCT YEAR(orderdate) FROM dbo.Orders

DECLARE @cols AS nvarchar(MAX), @y AS int
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N''
WHILE @y IS NOT NULL
BEGIN
SET @cols = @cols + N',['+CAST(@y AS nvarchar(10))+N']'
SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))

-- Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT
custname,
YEAR(orderdate) AS orderyear,
ISNULL(qty,0) AS qty
FROM dbo.Orders o
INNER JOIN
Customer c
ON
o.custid=c.CustId
where orderdate between '''+ @StartDate + ''' and ''' + @EndDate + ''''+
') AS D '+
'PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P'
PRINT @sql -- for debugging
EXEC sp_executesql @sql


END