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

Very much appreciated mpcrob! Might take me a while but I think I should be able to figure this out now :)

This is an example of a query I made in our system, so the fieldnames are different:

Edited by mpcrob - 11 Apr 2011 at 9:33am

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: