Crosstab workaround - having problem(s)

[rorymo]

Sql Server 2000
Crystal Reports (BOXI)

Hi,
I have created a report that is keeping track of help desk tickets for a certain group of people.
The report is grouped by the name of who is assigned to work the ticket.

In a crosstab, I need to show:

Row: Assignee Name

column/cell:
1. count of tickets (per assignee)
2. count of tickets that are within a certain date range (considered "late")
3. Of #1, what is the percentage of #2

Example: John Doe
16 total tickets (for John Doe)
3 tickets meet the criteria in #2
19% of the 16 tickets were in the date range (3 tickets)

I found the threads and link about the two crosstab workaround techniques. I was trying technique #2 (creating the two formulas from the summary fields and using the "whilereadingrecords;0".

Where I am running into trouble is that that it is not working - I think because the #2 count is coming from a calculation instead of a field in the database.

The calculation that I use for #2 is:
If date1 < date2 then 1, then using a running total to count the instances of 1.

This is complicated by the fact that our dates are stored as numeric fields, so we have to use DateAdd to convert them to dates. (don't know if that is relevant or not)

I can't use the "If Top <> 0
then totext((Bottom - Top)%Top)+'%' because of the calculation (#2)

I hope this is not too confusing. Does anyone see any way to do this?

Oh, the Gf1 contains the info that they are looking for, but it is a "drilldown" ..they want a chart that shows everyone at once (in addition), so I couldn't do a "fake" crosstab.

Thanks very much in advance.

Rory

 

[lbass]

Why are you using a running total to summarize your date comparison? Why not just insert a sum? And what do you mean that it is not coming from a field in the database? Please show the actual formula.

-LB

 

[rorymo]

Hi LB,
Thanks for the reply.
This is the formula:
if {@Est Comp Date} < {@Close Date} Then true

So of course, I want to count the "true" and use that in the crosstab.
When I create a summary on this formula, I choose "count" and put the summary at the group (assignee) level. The number doesn't match the actual result.
I have a running total on the above formula that I use in the GF section. As one example, it shows that the number of tickets that are true (based on the formula) is 5. When I use the summary, it shows 23 - the total number of tickets for that assignee.
I know that the 5 is correct because I can check it against the details in the ticket.

(I meant that there is not a field in the database that shows how many tickets that had a close date that was after the est compl date.)

I may be not seeing the forest for the trees...I tend to "overthink" things that are really simple.

 

[lbass]

Your formula should be:

if {@Est Comp Date} < {@Close Date} Then 1

Then you would right click on this and insert a sum (NOT a count) at the assignee group level. This result should be correct unless you have row inflation. If correct, you can use the sum of the formula in your crosstab.

Note that a count of a formula will return the number of times a formula executes, NOT the count of how many times your criteria are met.

-LB

 

[rorymo]

Thanks LB. That really clears a lot of things up!
I really appreciate your advice..will put into place right now. Have a good day!
Rory