Kindly consider this scenarion. I am currently making a timesheet application, and i need to generate a couple of reports. My table structure is as follows:
I would need report to be generated for each day in the date range selected. for example if the date range selected is '01/05/12' - 13/05/12' and the cost code selected in CC01
then the report would be as follows
Day Hours
May 1
May 2
May 3
May 4
May 5
May 6
May 7
May 8
......
May 13
Total
Mark as Answer, if i have been able to solve your problem.....
sajitkumar
Member
178 Points
245 Posts
generating report
Jun 10, 2012 06:45 AM|LINK
Dear All,
Kindly consider this scenarion. I am currently making a timesheet application, and i need to generate a couple of reports. My table structure is as follows:
mtbProjects
CCode SDATE EDATE
CC01 01/05/12 31/05/12
CC02 01/05/12 30/06/12
MtblDate_Mst (This date is populated in the form where timesheet is entered)
Date_ID SDATE EDATE
01 30/04/12 06/05/12
02 07/05/12 13/05/12
03 14/05/12 20/05/12
04 21/05/12 27/05/12
05 28/05/12 03/06/12
mtblTImesheet
CCode Date_ID Day1 Day2 Day3 Day4 Day5 Day6 Day7
CC01 01 8 0 8 0 0 8 8
CC02 01 0 8 0 8 8 0 0
CC01 02 0 8 0 0 8 8 8
CC02 02 8 0 8 8 0 8 0
CC01 03 0 8 0 8 0 0 8
I would want the following report to be generated
Report1
Name of the report: Total hours spend on CC01
CCode CC01
Sdate: 01/05/12 Edate: 13/05/12
the report should be in the format specified
Date Hours
May 1 0
May 2 8
May 3 0
May 4 0
May 5 8
May 6 7
May 7 0
........
May 13 8
Total (hrs) 64 (total of the rows italiced)
No. of days 8 (64 div by 8)
Can anyone suggest how do i generate the report.
Mudasir.Khan
All-Star
15346 Points
3142 Posts
Re: generating report
Jun 10, 2012 10:28 AM|LINK
call a stored procedure which will return the data and simply bind the data to grid view
inside SP do the grouping by Date_ID and sum all the CCode
sajitkumar
Member
178 Points
245 Posts
Re: generating report
Jun 11, 2012 08:47 AM|LINK
Dear Mudasir,
I would need report to be generated for each day in the date range selected. for example if the date range selected is '01/05/12' - 13/05/12' and the cost code selected in CC01
then the report would be as follows
Day Hours
May 1
May 2
May 3
May 4
May 5
May 6
May 7
May 8
......
May 13
Total
sajitkumar
Member
178 Points
245 Posts
Re: generating report
Jun 12, 2012 05:53 PM|LINK
can anyone help in generating this logic
Alok Rawat
Member
64 Points
31 Posts
Re: generating report
Jun 15, 2012 08:41 AM|LINK
hi,
check this code to generate report(this code for crystal report).
public DataTable getAllOrders()
{
DataTable dta;
dta = new DataTable();
MySqlDataAdapter adp;
MySqlCommand cmd = new MySqlCommand();
objDatabaseConnection.GetConnection();
objDatabaseConnection.OpenConnection();
cmd = objDatabaseConnection.CreateCommand();
strSql = "spVendorrepNew"; //stored procedure
cmd.CommandText = strSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("SD", MySqlDbType.DateTime, 10); //SD:-start date
cmd.Parameters.Add("ED", MySqlDbType.DateTime, 10); //ED:-End date
cmd.Parameters["SD"].Value = Convert.ToDateTime(FromDate.Text).ToShortDateString();
cmd.Parameters["ED"].Value = Convert.ToDateTime(ToDate.Text).ToShortDateString();
adp = new MySqlDataAdapter(cmd);
adp.Fill(dta);
objDatabaseConnection.CloseConnection();
return dta;
}
private void btnViewReport3_Click(object sender, EventArgs e)
{
// MessageBox.Show(cmbArea.SelectedValue.ToString());
DataTable objDt = new DataTable();
//de dv = new CrystalReport5();
demoVendorNew dv = new demoVendorNew();
try
{
objDt = getAllOrders();
if (objDt.Rows.Count > 0)
{
crystalReportViewer1.Visible = true;
dv.Load(System.IO.Path.GetFullPath("CrystalReport5.rpt"));
dv.SetDataSource(objDt.DefaultView);
dv = SetParameterFields(dv);
TextObject SD = (TextObject)dv.ReportDefinition.Sections["Section2"].ReportObjects["txtFrom"];
SD.Text = Convert.ToDateTime(FromDate.Text).ToString("dd-MMM-yyyy");
TextObject to = (TextObject)dv.ReportDefinition.Sections["Section2"].ReportObjects["TxtTo"];
to.Text = Convert.ToDateTime(FromDate.Text).ToString("dd-MMM-yyyy");
crystalReportViewer1.ReportSource = dv;
lblMsg.Visible = false;
crystalReportViewer1.RefreshReport();
crystalReportViewer1.ReuseParameterValuesOnRefresh = true;
crystalReportViewer1.RefreshReport();
}
else
{
crystalReportViewer1.Visible = false;
lblMsg.Visible = true;
lblMsg.Text = "No Record Found";
}
}
catch (Exception ex)
{
ex.Message.ToString();
}
finally
{
}
}
protected demoVendorNew SetParameterFields(demoVendorNew rpt)
{
int cnt = rpt.DataDefinition.ParameterFields.Count;
ParameterValues myvals = new ParameterValues();
ParameterDiscreteValue myDiscrete = new ParameterDiscreteValue();
ParameterValues myvals1 = new ParameterValues();
ParameterDiscreteValue myDiscrete1 = new ParameterDiscreteValue();
if (FromDate.Text == "" && ToDate.Text == "")
{
myDiscrete.Value = DateTime.Now.ToShortDateString();
myvals.Add(myDiscrete);
rpt.DataDefinition.ParameterFields[0].ApplyCurrentValues(myvals);
myDiscrete1.Value = DateTime.Now.ToShortDateString();
myvals1.Add(myDiscrete1);
rpt.DataDefinition.ParameterFields[1].ApplyCurrentValues(myvals1);
}
else if (FromDate.Text == "" && ToDate.Text == "")
{
myDiscrete.Value = Convert.ToDateTime(FromDate.Text).ToShortDateString();
myvals.Add(myDiscrete);
rpt.DataDefinition.ParameterFields[0].ApplyCurrentValues(myvals);
myDiscrete1.Value = DateTime.Now.ToShortDateString();
myvals1.Add(myDiscrete1);
rpt.DataDefinition.ParameterFields[1].ApplyCurrentValues(myvals1);
}
else if (FromDate.Text == "" && ToDate.Text == "")
{
myDiscrete.Value = DateTime.Now.ToShortDateString();
myvals.Add(myDiscrete);
rpt.DataDefinition.ParameterFields[0].ApplyCurrentValues(myvals);
myDiscrete1.Value = Convert.ToDateTime(ToDate.Text).ToShortDateString();
myvals1.Add(myDiscrete1);
rpt.DataDefinition.ParameterFields[1].ApplyCurrentValues(myvals1);
}
myDiscrete2.Value = cmbArea.SelectedValue;
myvals2.Add(myDiscrete2);
rpt.DataDefinition.ParameterFields[2].ApplyCurrentValues(myvals2);
return rpt;
}