Summarizing a formula based on other summaries 1

[JimboK]

I have a need similar to this:

http://www.tek-tips.com/viewthread.cfm?qid=1578600

It's similar in that I want a report-level average for various ambulance response intervals (turnout time, drive time, etc.), as well as the following report-level summaries: minimum, maximum, median, 90th percentile, and (3 * standard deviation) + average (to identify statistical outliers).

The data come from two tables named Incident and Manpower. The Incident table includes data about the overall event and the Manpower table includes individual time stamps for individual units (fire trucks, ambulances, etc). All time stamps from start to finish are logged in Manpower for every unit, along with individual codes to identify what each specific time stamp represents (unit dispatched, unit enroute, etc.).

The report includes these fields (among others):
[ul]
Incident.Event_Number
Incident.Incident_PK
Manpower.Incident_FK, linked to Incident.Incident_PK
Manpower.Unit_ID
Manpower.Code
Manpower.Manpower_Record_Time
Manpower_1.Incident_FK, linked to Manpower.Incident_FK
Manpower_1.Unit_ID, linked to Manpower.Unit_ID
Manpower_1.Code
Manpower_1.Manpower_Record_Time
[/ul]

A second instance of Manpower is used to capture the second time stamp for the response interval measurement.

I group first by Event_Number and then by Unit_ID. I'm working now on turnout time, so for that I filter by the dispatch code from Manpower and the enroute code from Manpower_1.

Now, here is where the waters get muddied. There often are multiple time stamps with the same code but with different times (e.g., an ambulance mistakenly marks enroute twice), and the second and subsequent time stamps need to be excluded. So I have the following group-level summaries:
[ul]
Min of Manpower.Manpower_Record_Time
Min of Manpower_1.Manpower_Record_Time
[/ul]

To figure turnout time (in minutes and decimals) for individual events, I have this formula field named Turnout Time in the group footer for Unit_ID:
[ul]
datediff ("s",Minimum ({v_Manpower.Manpower_Record_Time}, {v_Manpower.Unit_ID}),Minimum ({v_Manpower_1.Manpower_Record_Time}, {v_Manpower.Unit_ID}))/60
[/ul]

It is this field from which I need to calculate the various summaries, but Crystal won't let me create a summary on that field.

I am a relative novice with Crystal, and I've struggled with various ways to work around this. I've tried it with a subreport and a shared variable -- my first venture into using variables. I've tried it with running totals instead of a group summary for the minimum values. It seems the sticking point is trying to summarize a formula field that is a calculation of another summary.

I am using CR Standard Version 10.0.5.1506. The database is SQL.

Can anyone help on a "See-Spot-Run" level? Thanks in advance!

 

[lbass]

It would be simplest if you could create SQL expressions to use in place of your basic summary, but you are using an alias table, which complicates matters. Are you sure you need to use an alias table to get the second time? Why not use a conditional formula instead?

-LB

 

[JimboK]

Thanks, LB. I've seen your many helpful posts in other threads, and I was hoping you'd respond here.

No I'm not sure I need to use an alias table. I've used it only because I know that's one way to get the data I need into the report. If you can describe how to do it with a conditional formula, that would be great. If it helps, here is a sample of relevant data from the Manpower table for one incident for unit M3:

Manpower_PK Incident_FK Unit_ID Manpower_Record_Time Code
16684799 910098 M3 12/8/2009 12:30:08 AM USDI
16684823 910098 M3 12/8/2009 12:32:34 AM USEN
16684826 910098 M3 12/8/2009 12:32:41 AM USEN
16684845 910098 M3 12/8/2009 12:35:57 AM USAR
16684848 910098 M3 12/8/2009 12:36:52 AM USPC
16684860 910098 M3 12/8/2009 12:39:07 AM USUC
16684861 910098 M3 12/8/2009 12:39:12 AM USTA
16684929 910098 M3 12/8/2009 12:51:42 AM USAH
16685079 910098 M3 12/8/2009 1:11:13 AM USCS
16685139 910098 M3 12/8/2009 1:22:14 AM USIQ

The turnout time is the difference between the USDI and USEN time stamps. This incident, however, is an example of one with multiple time stamps with the same code. So my calculation needs to be the difference between USDI and the first USEN record -- hence, the use of Minimum summaries from which to figure the turnout time.

As far as using SQL expressions, I'm even more limited there; I have very little experience with SQL. Having said that, I did try another approach, before trying the current one, using SQL expressions created with Crystal SQL Designer. I created Crystal commands to serve as virtual tables instead of either instance of the Manpower table, one for minimum dispatch time and the other for minimum enroute time. Here is the latter:

SELECT
v_Manpower."Incident_FK",
v_Manpower."Unit_ID",
MIN (v_Manpower."Manpower_Record_Time") AS 'Enroute Time'
FROM
"UDT4"."dbo"."v_Manpower" v_Manpower
WHERE
v_Manpower."Unit_Agency" = 'FR' AND
v_Manpower."Code" = 'USEN'
GROUP BY
v_Manpower."Incident_FK",
v_Manpower."Unit_ID"
ORDER BY
v_Manpower."Incident_FK" ASC

(To keep things a little simpler and clearer, I truncated the "v_" prefix in my table descriptions in my first post.)

This actually allows me to create the summaries I need. Problem is, the report runs painstakingly slow -- an hour or more to run for a day's worth of data. That's too slow to be practical for the end user, who might be running the report for weeks' worth of data at a time. I've talked to our IT people about that, but they don't know why it's so slow. So I resigned myself to using the actual tables.

Any other suggestions?

 

[lbass]

I'm not sure this will be faster, but try creating SQL expressions instead (field explorer->SQL expression->new):

[{%minUSEN}:]
(
select min("Manpower_Record_Time")
from "UDT4"."dbo"."v_Manpower" A
where A."Code" = 'USEN' and
A."Unit_ID" = "UDT4"."dbo"."v_Manpower"."Unit_ID" and
A."Incident_FK" = "UDT4"."dbo"."v_Manpower"."Incident_FK"
)

[{%minUSDI}:]
(
select min("Manpower_Record_Time")
from "UDT4"."dbo"."v_Manpower" A
where A."Code" = 'USDI' and
A."Unit_ID" = "UDT4"."dbo"."v_Manpower"."Unit_ID" and
A."Incident_FK" = "UDT4"."dbo"."v_Manpower"."Incident_FK"
)

Then your datediff formula would look like:

datediff("n",{%minUSDI},{%minUSEN})

Then you can create other summaries with this formula field. Just remember that this summary will repeat for each row if used in the detail section, so you'd want to use a maximum on it at the inner group level to get the correct figure.

-LB

 

[JimboK]

LB, thank you very much for your detailed response. I'm encouraged by the confidence you seem to have that we can get this working. That is, assuming it runs quickly enough.

For now, however, I'm getting an error. I pasted the first expression into a new expression field named "Enroute" in the SQL Expression Editor. It's giving me this error:

"Incorrect syntax near the keyword 'FROM'.'.

What now?

 

[lbass]

I can't really tell what the problem is. Did you include the parens? You should. I don't see anything wrong with the from line.

-LB

 

[JimboK]

Yep, I pasted it directly from your post.

 

[lbass]

Maybe you should try replacing only the fields that start (not those starting with "A"):

"UDT4"."dbo"."v_Manpower"

with those from your field list. Not sure what else to suggest here.

-LB

 

[JimboK]

OK, I'll try that. What's the "A" for anyway?

 

[lbass]

Alias table.

-LB

 

[JimboK]

Just a quick reply ... haven't had time to do this yet, and I'm out of the office tomorrow. I'll take a whack at it Thursday. Thanks again for your help and your patience.

 

[JimboK]

OK, I'm back on this. Sorry for the delay ... had some other things come up in the office.

I tried this:

[{%minUSEN}:]
(
select min("Manpower_Record_Time")
from "UDT4"."dbo"."v_Manpower"
where "Code" = 'USEN' and
"Unit_ID" = "UDT4"."dbo"."v_Manpower"."Unit_ID" and
"Incident_FK" = "UDT4"."dbo"."v_Manpower"."Incident_FK"
)

Am I'm still getting the same error as before. Any other suggestions?

 

[lbass]

(
select min("Manpower_Record_Time")
from "UDT4"."dbo"."v_Manpower" A
where A."Code" = 'USEN' and
A."Unit_ID" = [red]"UDT4"."dbo"."v_Manpower"."Unit_ID"[/red] and
A."Incident_FK" = [red]"UDT4"."dbo"."v_Manpower"."Incident_FK"[/red]
)

You need to put the "A's" back in--I said NOT to remove them. I meant for you to replace the fields in red above with the same field from the field list (by double clicking)--so that it is represented correctly.

-LB

 

[JimboK]

Oops, sorry, I misunderstood. I said I needed it on a See-Spot-Run level. ;-)

Anyway, I'm making progress. For your sake and the benefit of anyone reading this thread, I'll take it step-by-step. After your last post I engaged in some trial-and-error, which, combined with exploring the design of reports created by others, reading the (sometimes not very helpful) CR help files, and seeking occasional advice (like this), is how I've learned CR.

Within the SQL Expression Editor I pasted the original formula you provided, deleted the fields you highlighted in red, and double-clicked those fields in the field list. This was the result:

[{%minUSEN}:]
(
select min("Manpower_Record_Time")
from "UDT4"."dbo"."v_Manpower" A
where A."Code" = 'USEN' and
A."Unit_ID" = "v_Manpower"."Unit_ID" and
A."Incident_FK" = "v_Manpower"."Incident_FK"
)

That triggered the same error.

Because your last post does not include [{%minUSEN}:], I tried it without that. This time, no errors. (What purpose does that text serve, anyway?) I named it Enroute. I pasted the same formula into another SQL Expression field named Dispatch, changed the code to USDI, and deleted the "A"s. My logic for deleting the "A"s is that the aliased table was created only for the USEN time stamp. I need the report to show USDI and USEN side-by-side and calculate difference between the two, whereas they are separate records in the Manpower table.

From there, using the datediff function, I was able to create the formula field I need for Turnout Time and I placed into the Unit_ID group footer. And from that field, the report FINALLY allowed me to build the desired report-level summaries.

And, I might add, it runs very quickly. This seems to be a similar approach to using SQL expressions in commands as I tried earlier But runtime speeds are dramatically different. Wonder why? (Partially a rhetorical question, but theories, speculation, or SWAGs are welcome.)

Anyway, are there any flaws in my expressions or my logic that could lead to erroneous summary data? They look correct in preliminary testing.

 

[lbass]

The [{%minUSEN}] was just my name for the expression. I thought the brackets would comment it out--guess not. I believe you should leave the alias table in both expressions. Are the calculations correct without them? You should attempt to verify this.

It sounds like with the commands, you added more than one and even if you linked them, they would be processed locally. Notice that you had the commands pulling detail level data, not just the summaries, too, and that would have slowed things. If you are using a command in a report, it is generally better to use only one and use it as the sole datasource for your report.

-LB

 

[JimboK]

The calculations are correct as the expressions are currently written. I suppose I could change them, but as long as they're working, I'm inclined to leave them alone. What do you think?

Here is a sample report (which uses formulas to convert the turnout and summary values from numeric to MM:SS):

Turnout Interval Report

Low High Average Median 90th Pcntl UCL

Summary: 00:12 03:25 02:33 02:53 03:21 05:04


Incident Number Date & Time of Call Unit Dispatch Enroute Turnout Interval

FFR091215031639 12/15/09 0:42:28
M18 0:42:38 0:45:05 02:27

FFR091215031640 12/15/09 0:57:04
M19 0:57:59 1:01:24 03:25

FFR091215031641 12/15/09 1:01:54
E9 1:05:08 1:07:19 02:11

FFR091215031642 12/15/09 1:17:06
E18 1:18:47 1:20:38 01:51

FFR091215031643 12/15/09 1:24:13
M1 1:24:44 1:27:37 02:53

FFR091215031644 12/15/09 1:23:14
A427 1:25:06 1:28:23 03:17

FFR091215031645 12/15/09 4:11:37
A325 4:12:11 4:14:41 02:30
E2 4:12:11 4:14:41 02:30

FFR091215031646 12/15/09 5:04:30
A325 5:09:33 5:09:45 00:12
E24 5:16:27 5:19:28 03:01

FFR091215031647 12/15/09 5:22:24
M16 5:23:12 5:26:09 02:57

FFR091215031648 12/15/09 6:28:35
M3 6:29:07 6:32:16 03:09

FFR091215031649 12/15/09 6:35:38
M18 6:36:03 6:38:56 02:53

Again, the Dispatch and Enroute times derived from the expressions are in the Unit_ID group footer (shown here). The details section (suppressed here) includes the actual time stamps. To further validate the data, I've unsuppressed details and visually spot-checked their values with those from the expressions, especially for instances of multiple USEN and USDI times to assure the expressions capture the earliest times. Everything seems to be working properly.

 

[lbass]

If it's working, no need to fix it!

-LB

 

[JimboK]

LB, many thanks for all your help!

 

[melbenmartin]

Hi I am Novice in crystal report .

Your help will be hightly appreciatable . Thanks in advance.

See ,I have one report

Group Field - Tbl_Transaction.BillDate
Summary Field is - TBL_transaction.Credit
Summary Field is - tbl_transaction.debit

when the report generate,

I need to display the Report Like this .

Opening Balance 2000.00

12/12/2009
----------

Particulars Debit Credit
----------- ------ -------
some texts 200.00 100.00
some texts 200.00 100.00
some texts 200.00 100.00
some texts 200.00 100.00
some texts 200.00 100.00
------------------------
Group Summary 1000.00 30000.00(CreditGroupSumary + Opening Balance)
--------------------------------------------
C/D Balance is 2000.00 (CreditGroupSummary - debit GroupSummary)


13/12/2009
---------- (2000.00) +
Particulars Debit Credit
----------- ------- -------
some texts 200.00 100.00
some texts 200.00 100.00
some texts 200.00 100.00
some texts 200.00 100.00
some texts 200.00 100.00

Here I have to add the previous c/d Balance to the next groups Credit Summary .. and the same logic will continue for the subsequent groups ..

Plese help me how to do this functionality . Thanks in advance.

Please note : I could add the Opening Balance to the first groups credit summary ..

But i am not able to add c/d Balance to the next groups credit summary .. please help me on this as its very urgent

 

[lbass]

melbenmartin,

Please start a new thread.

-LB