Formula headache! 1

[LidoDeJesolo]

Crystal Reports v10
SQL 2000 Database

I hope the gurus in here can help me. I've been fighting with this database for hours upon hours!

I will try and explain what I require as best I can:
Below is an example of the data I have in one table in a SQL database. I have no control over the construction of the database and am merely responsible for producing the reports.
A & B = unique identifier for each "record" which I will refer to as "incidents" (ie BSSCO2 and BSSCO9)
G & H = Categorisation of the record
I, J, K = Days, Hours, Minutes

I need to produce a cross-tab/chart of hours vs category for each incident. This will enable our management team to see how much time is being spent on each category.

The problem:
The category is defined whenever G=5. So G=5 and H=1 would be catA. G=5 and H=2 would be catB and so forth.
However the time spent on each incident appears on a different row (record) in the database. Time is given when G=4.
So what I need to do is:
1. For each incident (BSSCO9), calculate the sum of all the days, hours and minutes (I,J,K) when G=4. (preferably converted into total minutes assuming 8 hour days)
2. Associate this sum of minutes to each particular category when G=5.

Each incident is only categorised once. So there will only be one value of G=5 for each incident.

What this will give me is total minutes for, say, a month for each category. So:

G H Minutes
5 1 531
5 2 842
5 3 etc..


A B G H I J K
BSSCO 2 0 1 0 0 0
BSSCO 2 0 4 0 0 0
BSSCO 2 5 1 0 0 0
BSSCO 2 0 5 0 0 0
BSSCO 2 0 6 0 0 0
BSSCO 2 0 6 0 0 0
BSSCO 2 0 4 0 0 0
BSSCO 2 0 5 0 0 0
BSSCO 2 0 6 0 0 0
BSSCO 2 4 2 0 0 5
BSSCO 2 0 6 0 0 0
BSSCO 2 0 4 0 0 0
BSSCO 2 0 6 0 0 0
BSSCO 9 0 1 0 0 0
BSSCO 9 0 4 0 0 0
BSSCO 9 5 4 0 0 0
BSSCO 9 4 2 0 0 0
BSSCO 9 0 6 0 0 0
BSSCO 9 4 2 0 0 5
BSSCO 9 0 6 0 0 0
BSSCO 9 0 6 0 0 0
BSSCO 9 4 1 0 5 30
BSSCO 9 0 6 0 0 0

Is this possible?



Lido
Development & Reporting
UK

 

[Madawc]

If you grouped by unique identifier, then you could use running totals to collect the values you needed, using the formula option to collect the right set of details. This should allow you to put the correct values in the Group Footer, and you could suppress the header and details.

Getting from this to a crosstab would be another matter. I don't see a way, though maybe someone else can.

Another possibility would be a stored procedure, written in SQL and merging the relevant items into a single record. That's assuming you are allowed to write stuff in SQL. This would be a lot more efficient to run and should translate into a crosstab very easily.

Madawc Williams (East Anglia, UK)

 

[LidoDeJesolo]

Thanks Madawc

I was able to write a stored procedure which merged the data as I wished.

Top advice!

Lido
Development & Reporting
UK