1 Table, 1 Person, 4 Fields

[DataDog]

CR 8.5
CE 8.5
SQL Server Data

While I've written a few hundred Crystal Reports, This one has me stumped.

I need to write a summary report for our High Speed Imaging application. There are 4 jobs that people do: 1. Document Preperation, 2. Document Labeling, 3. Scanning, 4 Indexing.

In each row of a single table, the person's e-mail alias MAY be populated in DOC_ID, PREP_ID, SCAN_ID, or INDEX_ID, depending on which task they performed. The person may only do one task or up to all 4 tasks for each Batch (1 row in the table).

What I want to show in the report:

Alias:a9i2 (could be any of Prep, Label, Scan or Index ID)

Job Batches Documents Pages
Prep 25 100 500
Label 0 0 0
Scan 1 10 20
Index 200 5000 10000

I considered grouping a main report on the PREP_ID, and then having 3 sub-reports on the other IDs, but if a person didn't do any Doc Prep, the rest of their work would not be found.

Any help would be appreciated.

DataDog
'Failure Is Not An Option'

 

[synapsevampire]

Please provide a table layout and example data, then the resulting expected output.

You don't mention Batches, Docs or Pages in your decription of a technical spec. (that is to say in lieu of a tech spec), but then in the end they show up in the output.

-k

 

[tektipdjango]

Blank main report with 4 subreports may be an option

If you can go in the SQL statement may be you can write something like (sql server syntax)

select Id=
case
when index is not null then indexId
when prep is not null then prepId
when scan is not null then scanId
else labelId
end,
...

Then with this recordset you can group on Id and get the summary report you want

I put the different statements in an order based on the numbers you provided, as the first test will succeed in 90% of all cases, speeding up the query


django
bug exterminator
tips'n tricks addict