Add calculated field in the Field list

I would like to calculate the totalrepair time of a unit.

I hope I can do it by adding a calculated field in the fieldlist, and use that field on my report, showing the (receivedate-returndate). About an hour ago, I created a new “calculated field” to my field list, but something unexpected happened andI closed without saving the report. Now I would like to add this field again(right click in fieldlist/add calculated field), but it never shows in the list, so I can edit it. What is needed before I can create this sort of field?

Thanks for your reply :slight_smile:

Best regards

Thomas

Hi Thomas

I have not used the calculated field ability myself so can not provide specifics on how to use .

Taking a look at it right now, it appears to only create a calculated field IF the report is a summary type report.

For example,when I go into a report template such as a summary type report template for the Service Workorker grid, that if I click on the Field List tab, I need to right-click on a datasouce such as the WorkorderServiceList and it displays the option to “Add calculated field”. When I do, it expands the WorkorderServiceList tree and I can see that it has added a field called calculatedField1.

Whereas if you do these same steps in a detailed type report template, no calculatedField1 is created - which means it is not capable of doing so.

If you have determined how to use the calculated field feature, do post here an example for the benefit of others.

The forum topic Displaying the TimeSpan in a report via script gives an example on subtracting dates for each other. - a suggestion is to check that out, as I am not quite sure how adding a “calculated field” to the Field List was going to subtract a datafieldfor return date from a datafield for received date.

If you would like me to create a create a custom script for you, do send your details as outlined in the forum topic Custom report templates and I can quote for you,including details such as:

  • From what grid / entry screen are you wanting to run this report?

  • What exact field in what exact screen equates to the “received date” field you are referring to in your post?

  • What exact field in what exact screen equates to the “return date” field you are referring to in your post?

  • Joyce

Ok. I have tried the scripting, but I always get “0” as result.

If I drag the (“Repair end date LT_WorkorderService_Label_CloseByDate”, or the "Record Created LT_Workorder_Label_Created"to my report, it shows the correct date.

But when I insert alabel to show how many days used, by inserting the following script on BeforePrint for this label, it seems like the same date is 01.01.0001 00:00:00.

Why don’t I get the correct value? The label always shows “0”.

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {
//this gets the object value of the Stop Date & Time
DateTime dt1 = Convert.ToDateTime(GetCurrentColumnValue(“Repair end date LT_WorkorderService_Label_CloseByDate”));
//this gets the object value of the Start Date & Time
DateTime dt2 = Convert.ToDateTime(GetCurrentColumnValue(“Record Created LT_Workorder_Label_Created”));
//this subtracts the start date & time from the stop date & time
TimeSpan ts = dt1.Subtract(dt2);
//this displays the timespan in total hours (if you want to display in minutes, than change TotalHours to TotalMinutes
xrLabel21.Text = ts.TotalHours.ToString();
}

Thanks in advance.

Thomas

Hi again Thomas

Possibly it is because of the same that occurred for the AyaNova user in the forum report template post Workorder total time report where for his specific report template, it was because he did not identify the proper name of the band where the datafields were coming from before the GetCurrentColumnValue.

Do note of course that your band name for your report template could be different. As a copy of your report template was not attached, unfortunately can not say for sure

  • Joyce

Hi Joyce,

I have tried what you sugested, but I am not sure if I do it correct.

xrLabel21 is used to show how many days there are between
[Record Created LT_Workorder_Label_Created] and [Repair end date LT_WorkorderService_Label_CloseByDate]

I have attached my report if you would like to take a look.

[LS Status report.zip](http://www.ayanova.com/downloads/reporttemplates/LS Status report.zip)

/Thomas

Hi Thomas

Two issues why not working.

  1. You only use the Key name in the script - do not include the text label

For example, you would use GetCurrentColumnValue(“LT_WorkorderService_Label_CloseByDate”)); You would not use GetCurrentColumnValue(“Repair end date LT_WorkorderService_Label_CloseByDate”));

So in your script, you need to remove the three text labels

  1. You have referenced the DetailReportItem band as where to GetCurrentColumnValue, but both the LT_WorkorderService_Label_CloseByDate and the LT_Workorder_Label_Created are actually from the DetailReport band which is bound to the WorkorderHeader datasource

You can also see this by looking in the Field List tab - that the LT_Workorder_Label_Created is from the WorkorderHeader datasource, as is the LT_WorkorderService_Label_CloseByDate. And in your design panel, the band DetailReport is bound to the WorkorderHeader datasource - so that is what you reference when doing a GetCurrentColumnValue in a detailed type report template.

So, you will want to edit your script to:

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
//this gets the object value of the stop Date & Time
DateTime dt1 = Convert.ToDateTime(DetailReport.GetCurrentColumnValue(“LT_WorkorderService_Label_CloseByDate”));
//this gets the object value of the start Date & Time
DateTime dt2 = Convert.ToDateTime(DetailReport.GetCurrentColumnValue(“LT_Workorder_Label_Created”));
//this subtracts the start date & time from the stop date & time
TimeSpan ts = dt1.Subtract(dt2);
//this displays the timespan in total hours (if you want to display in minutes, than change TotalHours to TotalMinutes
xrLabel21.Text = ts.TotalHours.ToString();
}

(note the name of the band before the GetCurrentColumnValue , and note that only the Key is identified, not the label text as that could be localized to anything)

Hi,

You are the best!

Now it shows the total number of days, so thats very nice. Thank you for the help :smiley:

I have to find a way of only showing working days, not saturday and sunday, but that seems very difficult. Can you give me a hint to do this?

/Thomas

Hi Thomas

Great to hear all working now.

I am assuming by “I have to find a way of only showing working days, not saturday and sunday” that you are referring to this same script - that you mean display only the number of working days to not include Sat and Sunday.

Sorry, but I do not know that off the top of my head, nor if that is even possible. I can certainly look into it for you for a fee to cover my time. Let me know if you do. Or a suggestion is to search online for C# code that may show you examples to start with.

  • Joyce

Hi Joyce,

I will search the Internet for an answer. It seems like I am not the first person with this problem :slight_smile:

Thank you very much for your help.

Best regards

Thomas

Hi Joyce,

Howbig a feedo you want forimplementing(write the code for) this saturday/sunday/holyday featureto my system?

It is the same template as I attached earlier. It have to take the “LT_WorkorderService_Label_CloseByDate” - “LT_Workorder_Label_Created” and write the result on a label next to these fields(xrLabel21). This will show how many days used on the repair. My problem is that I do not want it to calculate the weekends/holydays into this number,and I want you(if price is accepted :wink: ) to create the function to calculate these days out.Can you do that?

Best regards

Thomas

Hi Thomas,

I can do the script to exclude weekends (Sunday and Saturday) from the total count for your report template - quote would be $110 ($ quoted valid for 30 days from today).

Sorry, but I can not quote to create a script to also exclude holidays - what I have spent that last couple hours looking into on thisidentifies that it would have a lot of complications and a lot of time would go into it (easily could be hours and days). If you are willing to spend $1000 to begin looking into it, with open ended, we can certainly discuss this further - but I am not seeing where this could be a script that could be done for less than that just to start with. If interested, me directly at support@ayanova.comto discuss this.

If you would like to go ahead with script to exclude weekend days only, contact me directly at support@ayanova.com with the link to the forum topic and that you want to go ahead etc, and I will get you the link for payment and we can go forward.

  • Joyce