How to create calculative field in Jasper Studio

[SiladityaDhar]

Suppose we want to display total active time of any ticket. Please guide me how to create that calculative field in jasper studio.

 

Regards,

Saikat Das

[Legacy User]

One way to do that would be to take the difference between open_date and close_date, and if close_date is null then use 'now' in place of close_date.  Here is a Jaspersoft Studio variable calculation that does that, using the function 'NETWORKDAYS' to get the difference in terms of working days (i.e. ignoring Saturday and Sunday):

The full expression is shown below.  You could use 'DAYS' instead of 'NETWORKDAYS' if you just want the elapsed time in days:

Hope that helps.

Regards,

James

[SiladityaDhar]

Hi James,

Thank you. But here output is in days but I want it in hours. Please help me with that.

Actually, we want the total active time of any ticket in our report.

 

Regards,

Sai

[Legacy User]

Here is another option, giving a value in hours.  This is total elapsed time between ticket open and ticket close - using 'now' in place of close_date if the ticket is still open.  Assuming the ticket fields are labelled cr.open_date and cr.close_date, three variables are used:

Name	Type	Expression
vop1	Long	DATEVALUE($F{cr.open_date})
vcls1	Long	IF($F{cr.close_date} == null, DATEVALUE(NOW()), DATEVALUE($F{cr.close_date}) )
vDiff1	Long	($V{vcls1} - $V{vop1}) / (1000 * 60 * 60)

 

'vDiff1' is the elapsed time in hours.

Hope that gets you some way towards what you're looking for.

[SiladityaDhar]

Hi ,

We have tried as  below ,but didn't worked-

But we want as follows-(Report taken from boxi)

Boxi query is -

Please guide me how to do this in Jasper

[SiladityaDhar]

Hi James ,

Please give a solution to resolve this issue. Waiting for your response.

 

Regards,

Saikat Das

[Legacy User]

Hello Sai,

 

Can you tell me how the 'Active Time' and 'Inactive Time' fields are calculated in the original BOXI / Web Intelligence report?

 

From what I can see in your screen shots of the BOXI / WEBI report above, I'm wondering whether that report is based on cumulating the time between Activity Log entries that record a change of status, from 'Open' or 'In Progress' to something else?  I need to understand the business rules that determine what is 'Active time' and what is 'Inactive time' for your report.

 

Regards,

James

[Legacy User]

This is also relevant if you're looking for a more precise time that takes workshifts into account:

Jasper - WorkShift calculation