Using count with field in DB and another formula

[rdrose2499]

I am trying to build a report that will do 3 things. One, the user enters a date, then on the report going across the top the date they entered is in the first column, then the subsequent columns increment by one so that I can display a week's worth of data at a time.

Now, on the row, I have tech's names and for each tech I need to see 2 things. One is how many calls did the tech log and how many calls did the tech close. I thought I could do a formula saying something like:

While calls.calldate = ?startdate do count (calls.id).

The objective is to show the 2 counts for each tech for each date. If I put in a starting date of 1/13/03, then I need to see under 1/13 something like:
Logged Closed
Clark, Aimee 15 18

Can someone help me with this? I'm not sure I'm even on the right track.

 

[HowardHammerman]

You are describing a "rolling date" report. Here is how I have done it:

1) Create a parameter field, {?what_date} that will prompt the user for the first day of the report.

2) Decide how many dates you will show. Let's use 7 for this example.

3) Group on the subtotal field. In your case it will be the tech's id number or some such field uniquely defining the tech.

4) Create X number of formula fields for the column headers. In this case X=7. For the first column (call it column A) use this the parameter field. For Column B use the following:
{?what_date}+1
and so on for the rest of the columns. Place these objects in the page header section. Format as required.

5) Supress the group header section and the detail section

6) in the group footer section

7) Create two running totals for each column. Click on the "X+2" button in the evaluate section. Put in the necessary logic for each of your two fields: number of calls and closed. That is, one formula for each running total. I am not sure whether you will sum a numeric field or count a call record. That depends on the structure of your database. Reset at the group defined by the tech id.

8) place the running totals in the group footer, format as required.

enjoy! Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[rdrose2499]

Ok, I had actually done the first part where I set a formula for each of the column headers. However I'm perplexed with how to write the formula in the running total. If I were to write this out in SQL it would be something like this:

select count(id) from calls where calldate = @columnBstartDate

The ID field is the actual helpdesk ticket number and I just want to see how many tickets did each tech log for that day.

Then for the second running total, it needs to do this:

select count(id) from calls where datetimeclosed = @columnBstartDate
and closed = 'Y'

How do I write this in the formula for the Running Total?

Thanks,
Aimee'

 

[HowardHammerman]

When setting up running total #1, pick your ID field and select 'distinct count' as the summary method.

In the accumulation section click on the 'x+2' button and enter the following for the first column:

{table.datefield}={?start_date}

For the next column it will be:

{table.datefield}={?start_date}+1

etc.

For the second running total do the same things except the formula for the first column would be:

{table.datefield}={?start_date} and {table.status}="closed"

Does this help? Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[rdrose2499]

Actually, what you suggested worked except I found out as I worked through my logic that I am actually working with 2 different recordsets. The closed parameter looks at the tech field whereas the logged total comes from another field call callreceivedby.

I'm trying to figure out another way to do my report and I understand from what I've researched that I can't work with 2 different recordsets at the same time in Crystal unless I dump both recordsets into a temp table (which isn't going to work) or I use a subreport, which again isn't going to work.

Does anyone know a way to build report using two recordsets?

My boss is recommending that I for right now dump the data to a temp_logged table and a temp_closed table and then merge those two tables into one, and from there, use that table to display the information.

Thanks,
Aimee'

 

[HowardHammerman]

I think your boss has the right idea. Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269