Hi,
Within the workorders screen I am trying to create a report where I wish to subtract a custom field, that has been formatted as a date, from the date the workorder was created. So basically I have a three column table where I have
<Date Workorder created><Custom field date><Custom Field Date-Date Work Order Created>
Regards,
Martin
Hi again Martin
Not sure why - but you posted this question twice?
A suggestion is to take a look at the report template and its script in the topic Displaying-the-TimeSpan-in-a-report-via-script (search for timespan report) and use that as a reference
Hi again Martin
Do note that you can reply to an existing topic by selecting the Reply button to the upper right-hand corner of the topic - just in case that was what was going on.
Or if there is a problem with the forum (i.e. duplicating topics), do let me know so that I can get it resolved.
Thank you
Hi Joyce,
tried teh report you recommended but I keep getting a zero for the difference. Have tried numerous things with no luck. I have attached the report.
Regards,
Martin
Hi again Martin
I’ve taken a look, and modified the script so that I can see what it believes dt1 and dt2 is, and they are coming up both as 1/1/0001 12:00:00AM so that is why they are showing an amount of 0 when subtracted.
I’m willhave to take a look into this further and get back to you on a possible suggestion.
Hi again
When using GetCurrentColumnName script in a detailed report template format (as opposed to a summary report template format), one must always identify the band from where the label / datafield is located.
To figure out what was going on, I first created dragged over additional Label fields and than edited the script so that it would place the actual value of dt1 and dt2 into each of the labels - for example
xrLabel22.Text = dt1.ToString();
But they showed 1/1/0001/ 12:00:00AM which meant that the value was 0 - which means that if you subtract 0 from 0, you will get 0. So it was a case of figuring out why the GetCurrentColumnValue was resulting in 0 for each of the datafields.
That’s when I clued in, that in a detailed report template format, you must specify the band name from where the labels are located in. This is because unlike a summary report template format which is “flat”, a detailed is made up of tables and sub-tables
I attached a copy of your report template with the script edited as so that the total hours difference now displays.
private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) dir=ltr " ">
{
DateTime dt2 = Convert.ToDateTime(detailReportBand1.GetCurrentColumnValue(“LT_Workorder_Label_Modified”));
DateTime dt1 = Convert.ToDateTime(detailReportBand1.GetCurrentColumnValue(“LT_Workorder_Label_Created”));
TimeSpan ts = dt1.Subtract(dt2);
//this displays the timespan in total hours (if you want to display in minutes, than change TotalHours to TotalMinutes
//or if want to display in Days change TotalHours to TotalDays)
xrTableCell3.Text = ts.TotalHours.ToString();
}<P This should now get you going. Have a good evening<P - Joyce
Hi Joyce,
firstly thanks for the help.
I have attached a report where I am subtracting dates and I need just a bit more help.
xrTableCell8 contains the the creation date of the workorder.
xrtableCell6 contains custom field 4. This is a date field. This gets displayed correctly.
xrtableCell10 should display custom field 4 - creation date. It does not. The reason is that even though custom field 4 is showing the correct date I am actually extracting the date 1/1/0001 (i.e. 0). I think it is because I am actually pointing at the wrong place. I have attached the report.
I have tried numerous methods but without success.
Thanks,
Martin
Hi again Martin
Two issues are the cause:
You were calling a datafield from the WorkorderHeader band that is not actually in the WorkorderHeader area, but is from the WorkorderItem. The Custom fields are from the WorkorderItem area.
Custom field is not maintained in the database as a time field (unlike the Created time field) - as a custom field could be any type of data format and it could also be empty
-First I added the DetailReport band for Workorder Item
-Then I highlighted all fields that you had in the WorkorderHeader band andmoved them down to the Workorder Item band
-Then I edited the script for xrTableCell10 with the following:
private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
DateTime dt2;
if (DateTime.TryParse(Convert.ToString(DetailReport.GetCurrentColumnValue(“LT_WorkorderItem_Label_Custom4”)), out dt2))
{
DateTime dt1 = Convert.ToDateTime(detailReportBand1.GetCurrentColumnValue(“LT_Workorder_Label_Created”));
TimeSpan ts = dt2.Subtract(dt1);
xrTableCell10.Text = ts.TotalDays.ToString("#.0");
}
else
xrTableCell10.Text=“No Date”;
}
Because you might have a Custom4 field that is empty, you have to check it first to see if it has any data otherwise you would get an exception if any workorder item has thecustom4 field empty - that’s the TryParse statement.
Notice the DetailReport. when getting the GetCurrentColumnValue for the Custom4 field - and notice the detailReportBand1. when calling the Created datafield because Created is from the workorderheader, but Custom4 is from the workorder
I’ve attached an new copy of your report template
Hi Joyce,
again thanks again for your help.
One final question. How do I access the system time / date via a script ? I am trying to create a report where the total number of days a work order has been opened is displayed. I have a feeling that I have to access XRPageinfo properties.
Regards,
Martin
Hi Martin
Take a look at this Microsoft site http://msdn2.microsoft.com/en-us/library/system.datetime.now.aspx which provides information on the DateTime structure for C#- specifically the property DateTime.Now to return the current date and time.