I have a report in which I need to

[rdrose2499]

I have a report in which I need to specify how many tickets a tech has logged and closed for each day. I am able to get the closed value to display correctly. However, my logged field is a little tricky.

In the table, the field CALLRECEIVEDBY is the what is first populated when a ticket is created. The person who took the call will either make themselves the TECH or reassign it to someone else. This is what the SQL query looks like in my report:

SELECT
CALLS."ID", CALLS."CALLDATE", CALLS."TECH", CALLS."CLOSED", CALLS."DATETIMECLOSED", CALLS."CALLRECEIVEDBY"
FROM
"SMHDirectory"."dbo"."CALLS" CALLS, Tech
WHERE
calls.tech = tech.tech and team = 'desktop'
ORDER BY
CALLS."TECH" ASC , callreceivedby

The problem I have is for logged I need to make the formula look like this:

select count(id) from calls where calldate = ?startdate order by callreceivedby

I have one group in my report and that is CALLS.TECH. I tried playing with putting another group CALLS.CALLRECEIVEDBY, but it's like it's ignoring my SQL query because it's pulling back all the available techs instead of just the ones who are part of the "desktop" team.

Not only that, but it's not displaying the value for count on ID for that group.

Help?

 

[Naith]

Is that really the SQL as generated by your report? Or did you go in and physically alter it in the report or post?

As for this:

select count(id) from calls where calldate = ?startdate order by callreceivedby

where are you trying to use this?

Naith

 

[rdrose2499]

Yes, the first SQL statement is the SQL generated in the report and yes I did modify it to only show me techs who are part of the team "desktop".

As for the second select, that is what I need to do somehow in a formula for the logged calls total.

Basically I have to create a report using 2 different queries in order to display the output correctly. Does that make sense?

 

[Naith]

I'm not very clear as to why you're amending the SQL to provide you with what you want. You should be aiming to get the Desktop guys by using your record selection criteria (Report menu, Edit Selection Formula, Record) which should look something like {Tech.Team} = 'Desktop'.

This will automatically populate your SQL with the proper WHERE condition.

If you want to use your {?StartDate} parameters too, then this is also where you would specify the {Calls.CallDate} = {?StartDate} condition too. From the records brought back, you can insert a running total set to count to derive the record count total, instead of trying to manipulate this with the 'select count(id) from calls where calldate = ?startdate order by callreceivedby' query.

Naith

 

[rdrose2499]

Ok, I did as you suggested, however because the count for calls closed looks at the "tech" field and the count for calls logged looks at the "callreceivedby" field, I still am having trouble using a running total or formula for the count on the logged calls.

How do I use 1 main SQL query to generate running totals from two different recordsets?

 

[Naith]

Don't try to get the count accounted for the in the SQL. That isn't going to happen unless you create some SQL expressions or a stored procedure. The counts are worked out by Crystal, using running totals or formulas once the database returns the resultset.

Ideally, what you're trying to attain is a Record Selection Formula that looks like this:

{Tech.Team} = 'Desktop' and
{Calls.CallDate} = {?StartDate}
//Insert that last bit if you're only interested in calls for that one date.

...SQL that generates like this:

SELECT
CALLS."ID", CALLS."CALLDATE", CALLS."TECH",
CALLS."CLOSED", CALLS."DATETIMECLOSED",
CALLS."CALLRECEIVEDBY"
FROM
"SMHDirectory"."dbo"."CALLS" CALLS,
"SMHDirectory"."dbo"."TECH" TECH
WHERE
CALLS.TECH = TECH.TECH AND
TECH.TEAM = 'Desktop'
--and depending how you grouped your report--
ORDER BY
CALLS."TECH" ASC,
CALLS."CALLRECEIVEDBY"

...and count in Crystal like this:

Right click {Calls.ID}, select Insert/RunningTotal, set it to Count, and have it reset on change of CallReceivedBy group. In the Evaluate section, click X+2 and write the equivalent of {Calls.Field} = 'Logged'.

Repeat this process to set up a counter for 'Closed'.

If there are some other identifiers for the type of calls you want to count, like a certain type of logged call that makes running totals unfeasible, then use formulas like the following:

//@Formula1
//In the Details Section

WhilePrintingRecords;
NumberVar LoggedCounter;

If {Field} = "Whatever"
Then LoggedCounter := LoggedCounter + 1
Else LoggedCounter;

//@Formula2
//In the CallsReceivedBy Group Header

WhilePrintingRecords;
NumberVar LoggedCounter := 0;

If you're having some trouble getting your head around the running total function, press F1 and have a look at the files.

Naith