Rank 1
i have created report without using report wizard... here my coding is
private void ShiftReport_NeedDataSource(object sender, EventArgs e)
{
this.DataSource = null;
this.shifttab.DataSource = null;
DateTime StartDate = Convert.ToDateTime(this.ReportParameters["StartDate"].Value);
DateTime EndDate = Convert.ToDateTime(this.ReportParameters["EndDate"].Value);
string UserName = Convert.ToString(this.ReportParameters["UserName"].Value);
OleDbConnection con = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["FMS_1_0_0"].ToString());
OleDbCommand sqlCmd = new OleDbCommand("Shift_Report");
DataSet dsGetSearchResults = new DataSet();
sqlCmd.Connection = con;
sqlCmd.CommandType =
CommandType.StoredProcedure;
sqlCmd.Parameters.Add(
"Start_date", OleDbType.Date).Value = StartDate;
sqlCmd.Parameters.Add(
"End_date", OleDbType.Date).Value = EndDate;
sqlCmd.Parameters.Add(
"UserName", OleDbType.VarChar).Value = UserName;
con.Open();
OleDbDataAdapter Sqlda = new OleDbDataAdapter(sqlCmd);
try
{
Sqlda.Fill(dsGetSearchResults);
this.shifttab.DataSource = dsGetSearchResults;
this.DataMember = "";
}
catch (Exception ex)
{
}
}
and
/*the stored procedure is*/
create or replace PROCEDURE Shift_Report
(
Start_date IN DATE default null,
End_date IN DATE default null,
UserName IN Varchar default null,
cv_1 IN OUT SYS_REFCURSOR
)
as
tmp_userid number(10,0);
BEGIN
tmp_userid:=0;
BEGIN
select user_id into tmp_userid from ztuiusers where legacy_user_name = UserName ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
OPEN cv_1 FOR
select distinct
CASE
WHEN to_char(dol.datecreated,'HH24:MI:SS') BETWEEN '08:00:01' AND '16:45:00' THEN '08:00 - 04:45'
WHEN to_char(dol.datecreated,'HH24:MI:SS') BETWEEN '16:45:01' AND '23:59:59' THEN '04:45 - 12:00'
WHEN to_char(dol.datecreated,'HH24:MI:SS') BETWEEN '00:00:00' AND '08:00:00' THEN '12:00 - 08:00'
END AS ShiftTime,
-- to_char(dol.datecreated,'HH24:MI:SS') datecreated,
f.fax_id,
dol.title,
Users.legacy_user_name
from fax f
JOIN data_object_log dol
ON dol.entity_id = f.fax_id
JOIN ztuiusers Users
ON Users.user_id = f.user_id
JOIN data_object_log_types dolt
ON dolt.data_object_id = dol.data_object_id
JOIN data_objects do
ON do.data_object_id = dol.data_object_id
WHERE
(( UserName is null) or( Users.user_id = tmp_userid))and (f.received_date_time between Start_date and End_date)
order by ShiftTime ;
END;
i want to group the time column (i.e giving in case condition)
plz help me.. and post the results as soon as possible...
thank u,
ramya