Author |
Message |
ultraca
Newbie
Joined: 31 Mar 2011
Location: United States
Online Status: Offline
Posts: 23
|
Topic: Syntax for datediff and date range problems Posted: 08 Apr 2011 at 8:38am |
Crystal noob here, taken on more than I can chew...
We had a previous admin that put together a rather complicated report pulling data from sql, inserting into excel and using a bunch of formulas to create subreports.
The first criteria of the data is a list of all open tickets, their ticket number, the open date and the assigned user. That part is simple.
The second criteria for data is a list of closed tickets for the last 365 days.
The criteria of the report are the following: 1. For each ticket number show the number of days open. -I tried acheiving this by usind DateDiff in the Section Expert and inserting a new Detail, then using the x-2 (next to Supress) and the following formuala attempts: Attempt 1: DateDiff ("d", {open_date}, {close_Date}) Attempt 2: Local DateTimeVar d1 := {cr.open_date}; Local DateTimeVar d2 := {cr.close_date}; DateDiff ("d", d1, d2) Both formulas tell me that a boolean is expected (not sure what to do) Could this be an issue of date format? Is using the x-2 the correct way to enter calculated results into my report?
2. Create a subreport showing the department which the ticket was generated from (this is in another table). I linked the two tables with the appropirate field without problem. For each department, show the assigned user then the amount of tickets still open within a certain range of days. 0-20, 21-50, 51-70 and so on and at the end show the total tickets. Like above, trying to get a date range formula to work is just not happening for me.
The part where I am really stuck is with the Crystal syntax... it seems to simple when using the formula editor but I just keep getting formula errors.
As and FYI here are the SQL queries that captured the data: ------------ Query 1 ---------------------- SELECT ref_num, DATEADD(second, call_req."open_date", '12/31/1969 20:00:00') AS open_date, (SELECT userid FROM ca_contact WHERE (contact_uuid = call_req.assignee)) AS assignee FROM call_req WHERE (status = 'OP') AND (assignee <> 'NULL') ORDER BY assignee DESC
------------ Query 2 ---------------------- SELECT c.name, a.userid FROM ca_contact a inner join call_req b on a.contact_uuid = b.assignee left outer join ca_resource_department c on a.department=c.id where b.status = 'OP' AND b.assignee is not null group by a.userid, c.name ORDER BY c.name, a.userid DESC
I can post the excel with final results if it helps clear things up.
Edited by ultraca - 08 Apr 2011 at 8:38am
|
IP Logged |
|
mpcrob
Newbie
Joined: 11 Apr 2011
Location: Netherlands
Online Status: Offline
Posts: 6
|
Posted: 11 Apr 2011 at 4:50am |
CR is expecting the number of days to consider as parameter:
DateDiff ("d", {open_date}, {close_Date}) < 365
For the second part I presume you want the different periods in one row for each department.
You have to create formula fields like:
Name: Open_20_0
if datediff("d", {OINV.DocDate}, CurrentDate) in 0 to 20 then 1 else 0
Name: Open_21_50
if datediff("d", {OINV.DocDate}, CurrentDate) in 21 to 50 then 1 else 0
Etc.
Put these fields in one row in the details section.
Group by Department.
Insert Summary Sum for these fields in the group footer.
Suppress the details section et voilá . . . . .
|
IP Logged |
|
ultraca
Newbie
Joined: 31 Mar 2011
Location: United States
Online Status: Offline
Posts: 23
|
Posted: 11 Apr 2011 at 4:55am |
Very much appreciated mpcrob! Might take me a while but I think I should be able to figure this out now :)
|
IP Logged |
|
ultraca
Newbie
Joined: 31 Mar 2011
Location: United States
Online Status: Offline
Posts: 23
|
Posted: 11 Apr 2011 at 8:33am |
argh...
So I think I confused myself using the Section Expert and tried adding a custom Formula Field from the Field Explorer (DateDiff ("d", {cr.open_date},{cr.close_date} ) < 365) then inserting it into the Report, however I just get blank results, no errors, nothing. My date/time format is "mm/dd/yyyy hh:mm:ss am/pm"
Does that format work with "d" type value for datediff?
EDIT: Uh wait-a-minute... this formula does not compute for Open requests... what I need is the difference between open date and whatever the current date is.
So now I want this: DateDiff ("d", {cr.open_date},CurrentDateTime ) < 365 but of course I get an error stating the field name is not known, which means the formula needs a field name, but the current table doesnt have a Current date & time value.
Does that mean I need to find that field in the DB or should I be looking into creating a custom one?
Sorry I am trying to talk this out to myself to figure it out...
EDIT 2: So here is what I did to get the correct results (fyi using the < 365 was producing "true" as result)
DateDiff ("d", {cr.open_date}, CurrentDateTime)
Edited by ultraca - 11 Apr 2011 at 9:00am
|
IP Logged |
|
mpcrob
Newbie
Joined: 11 Apr 2011
Location: Netherlands
Online Status: Offline
Posts: 6
|
Posted: 11 Apr 2011 at 9:25am |
This is an example of a query I made in our system, so the fieldnames are different:
So you can see where to create the formula fields and where to place them.
These are the results.
With details:
Details suppressed:
Does this help?
Edit:
The field CurrentDate is a standard field available in the CR formula editor:
Edited by mpcrob - 11 Apr 2011 at 9:33am
|
IP Logged |
|
ultraca
Newbie
Joined: 31 Mar 2011
Location: United States
Online Status: Offline
Posts: 23
|
Posted: 12 Apr 2011 at 3:26am |
Definitely helps, but here is where I am stuck now.
I was able to create the second part of the report , group by department
and show the 0-20 and 21-50 and so on but under the 0-20 etc I want to
show the quantity of tickets per user instead of having a user repeated
over many rows.
Here is what I currently have: Here is what I want:
|
IP Logged |
|
mpcrob
Newbie
Joined: 11 Apr 2011
Location: Netherlands
Online Status: Offline
Posts: 6
|
Posted: 12 Apr 2011 at 3:46am |
If you've added the summaries in the group section (I can't see if you did as that would require the design view), then it should be sufficient to hide the details section:
|
IP Logged |
|
ultraca
Newbie
Joined: 31 Mar 2011
Location: United States
Online Status: Offline
Posts: 23
|
Posted: 13 Apr 2011 at 1:58am |
mpcrob, your assistance has been invaluable! Here is my final report: Using if DateDiff ("d", {cr.open_date}, CurrentDateTime) in 0 to 20 then 1 else 0 for the date range queries and inserting summaries and supressing details I was able to complete this: (there is a minor misconception in my 0-20 range title, although it says Count, it is really Sum - its just a naming issue because I initially chose Count then changed it to Sum later)
|
IP Logged |
|
mpcrob
Newbie
Joined: 11 Apr 2011
Location: Netherlands
Online Status: Offline
Posts: 6
|
Posted: 13 Apr 2011 at 4:23am |
Glad I could be of help.
Are your Group totals correct now?
Because in the examples you show the totals seem to be off, and not in the 0-20 column only.
|
IP Logged |
|
ultraca
Newbie
Joined: 31 Mar 2011
Location: United States
Online Status: Offline
Posts: 23
|
Posted: 13 Apr 2011 at 4:36am |
It is now, the screenshot was from before I fixed the problem.
thanks!
|
IP Logged |
|
|