UN-accounting for amounts in groups...

[MVisconte]

I have a peculiar report redesign, with even more peculiar results, and I've hit the cognitive wall...

We have two joined tables, one with basic (helpdesk) ticket information and a new table with multiple "ticket parts" in it. The time totals for each ticket are stored in the first table... I group all the records in a couple if ways and try to get total times for groups, but because of the join to "multiple" "ticket parts", I am getting really bizarre totals, and I can't think my way out of it.

Example:
Table 1:
Tkt# Thrd# TotOut
T1 EL-A 14
T2 EL-B 7
T3 EL-A 5
T4 ...

Where
Tkt# is the ticket number.
"Thrd#" is an internal group association (i.e., circuit number in this case).
TotOut is the total time the ticket was "out".

Table 2:
RTkt Tkt# Svc
R1 T1 S1
R2 T1 S2
R3 T1 S1
R4 T1 S3
R5 T2 S2
R6 T2 S1
R7 T3 S4
R8 T3 S1
R9 T3 S2

This table is essentially possible reasons for a circuit failure.
RTkt is the unique "reason" record identifier
Tkt# is a reference to the parent ticket
Svc is an service designator.

One Ticket may have multiple "Reasons".

I group and chart by Thrd#, and HOPE to total outage hours...
EL-A xxx hours
EL-B yyy hours.

Part of the problem might be because of the NEXT step... I am supposed to sub-group (and pie chart) by services that were involved, and from there, go the the ticket data (w/ a sub-report to show the multiple reasons associated w/ a ticket. Because the outage hours are contained in the ticket, which is left-outer joined w/ the Reasons table, I think I'm getting multiples of the outage hours.

For instance...
T1 has 14 hours and 4 "reasons" associated with it, T3 has 5 hours and 3 Reasons. EL-A is associate with T1 and T3.
When I group on Thrd#, EL-A _should_ have 19 hours... it has much(!) more.

How can I get my totals to take the outage hours from a single ticket instance, instead of the joined hours... (i.e., 4 * 14 + 5 * 3)?

I've tried conditional sums,
summing a formula which divided a ticket outage time by the number of "reasons",
Different joins

Is there a better way to approach this?

Going crazy in Virginia...

Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[synapsevampire]

Since you intend to show multiple lines (Ticket Parts) for each Total amount, you are probably experiencing row inflation, in that the total hours are repeated for each ticket part.

You described your problem pretty well, but you omitted some required basic information:

Crystal version
Database/connectivity used
Example data (you did this)
Expected output

Since you need to chart on this data, you might be better served to just use the ticket information in the main report, and in the subreport link to the ticket parts.

Now the display of the ticket parts would come from the subreport, whereas the graph would only use the ticket information table, hence no row inflation.

Yuo already mentioned a subreport, but you don't explain anything technical about your current design (where is the subreport, why did you use it if not solely for the ticket parts table?)

Anyway, hopefully this will get you over the hump.

-k

 

[MVisconte]

Argh. Row inflation... I couldn't come up with a term. I can describe it, I can witness it. I can't correct for it.

They have changed the underlying table design, going from a single reason for outage to a separate table w/ multiple reasons. There is supposed to be an associated outage duration for each reason event, and THAT is supposed to (!) add up to the total outage duration that is shown on the main table -- but it doesn't.

Supporting details:
Ver 8 (yes, I know... we'll be upgrading "RSN" (Real Soon Now))
ODBC to MS SQL
Expected Output? I'll have to describe the report action, also.

Top level, Chart and summary by "Thrd#":

EL-A 19 hrs (from example above)
EL-B 7 hrs
EL-C ...

If I didn't have the multiple "Reasons for outage", then all of the totals would come out fine.

NEXT level is a killer. They want another chart and summary by Svc. But, because you can have multiple (and REPEATED) services for each ticket, it is complicated. In the example data, S1 shows up 4 times... In Tkt2 and Tkt3, and twice in Tkt1. I need to COUNT the occurances for services:

Service Count
S1 4
S2 3
S3 1
S4 1

They also want "total hours" for service outages due to each service EVENT, but... the (sample) data I have is so hosed that I can't do that. In theory, this will tell them which services are causing the most trouble. In theory (again), I could just total the outage from each service event and get the "real" ticket outage (and ANY group level), but the application doesn't enforce that.

One fellow tried to help me by stocking sample data and wound up adding 19 (!) of the EXACT SAME service event -- including a 1488 hour outage. The hours set in the main table was only supposed to be 1200 hours but I'm getting much more than that when totalling at the element level.

Last level, thankfully, is just the details section of the records. Because they also wanted to see ALL "reasons for outage" (instead of the smaller set that a simple drill-down would provide), I added a subreport of all events in the second table linked on the parent record id. That part, at least, seems to work.

OK, back to what is twisting my mind in agonizing pretzels... how can I get an element level (EL-n) summary of ticket outage times (one element per ticket, more than one ticket per element) without getting the record inflation from the joined multiple outage table. Argh.

ANY ideas would be really, really appreciated. I've been pounding my brain against this for so long that I am going mentally blind.

Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[MVisconte]

Resolution, at least partially...

Another round of memos/complaints about:
dirty/bad data,
bad work-flow,
bogus records, etc.

Instead of trying to write a report to account for/ dis-abuse bad data, I am finishing my report against the data that exists.

The numbers I get will be "off", but correct. If a ticket with 1200 actual hours has bogus underlying data with 28277 hours, then that's what will be reported.

I have spent lots of time pre-development (on OTHER projects) explaining that we only report on the data in the database, our job isn't to hide, abuse, dis-abuse, account for other shortcomings. THIS is the way that we have standard, consistant reports, rather than a huge number of fudge/massage reports.

Whew.

On to the exercise.

Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports