[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1278
  • Last Modified:

formatting data in jasperserver

I need to create a variable called reponse time that is calculated within JasperServer / iReport.

I am currently using

datediff(S,$F{Call Started},$F{Crew Filled})/60

labeling is ResponseTime

both $F{Call Started},$F{Crew Filled} are passed via the stored procedure

error given during the compile time is S cannot be resolved

Purpose of variable - I need to take the start time and the end time to calculate the time it took from the call to the the time of filling the schedule. I can get it to work in the stored procedure but we are trying to avoid changing the st_proc.

Suggestions?

Will also be using this variable to get the average time between responses, the shortest time and the longest time.
0
tagomtech
Asked:
tagomtech
  • 3
  • 3
1 Solution
 
MIKESoftware Solutions ConsultantCommented:
What is he data type for field "S".....????
0
 
James0628Commented:
I haven't used JasperServer or iReport, but I'm guessing that the S is supposed to be to get the DateDiff in seconds.  Assuming that the DateDiff function is supported, I have two suggestions:

 Put the S in quotes - "S" or 'S'.  Single or double.  Whichever works.

 Maybe S is the wrong "code".  Maybe it should be s (lowercase), or seconds, or ...

 Either way (should be in quotes or it's not the right code), the error sounds like it's because something doesn't recognize the S and doesn't know how to interpret it, in which case one of those things should fix it.

 James
0
 
tagomtechAuthor Commented:
Jasper Reports uses java to calculate dates, crystal reports looks like you can create an sql query, I have created the same query in the st_proc.
ISSUE: coming up with three different results

code used in st_proc: datediff(S,clstartcalltime,a.cldatetime)/60 as [Response Time], rounds up and does not give the after decimal portion (1.48 minutes) - however this piece of code will need to be removed from the st_proc when I launch the reports. Currently I am merely trying to figure out why I am getting different results.

code used in crystal reports: (DateDiff ("s", {CC_Report_CrewHistory;1.Call Started}, {CC_Report_CrewHistory;1.Crew Filled}))/60 (This is the result that I must match using iReports - preferably without changing the st_proc.

code used in iReport (java) -$F{Crew Filled}.getMinutes()-$F{Call Started}.getMinutes() does not give me a double it returns an int - which rounds - I need to return or somehow cast this to a double - both crew filled and call started are timedate types in the database.

I am including two pics so that you can see the results of each piece of code. Any help is GREATLY appreciated.

 crystal reports - need to immulate the results shown here. crystal reports - need to immulate the results shown here. crystal reports - need to immulate the results shown here. ireport results showing both the sql and the java results
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
tagomtechAuthor Commented:
sorry for the multiple post of the first pic! happy fingers this morning
0
 
James0628Commented:
FWIW, for the stored procedure, you might have to specifically set the data type for the "Response Time" column to Decimal or Float.  It wouldn't surprise me if it defaulted to Int or something like that.

 For iReport, I don't know.  If that's part of a query, then maybe it's the same thing - Specifically set the data type for the column to one with decimals.  Or maybe, as you suggested, there's a Convert or Cast or similar function to set the data type.

 If you can't figure it out and no one else chimes in, you can try using the "Request Attention" link to see if you can get anyone else to help.  But, FWIW, I don't think there are many around here with Jasper / iReport experience.  I believe I've seen other questions about them go unanswered.

 James
0
 
tagomtechAuthor Commented:
Have been able to calculate now just working on formatting specific to iReports
0
 
James0628Commented:
OK.  I'm glad you were able to work it out.

 James
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now