Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Showing a count of attached items within a report.

    Ok, lets say I'm running a report that is polling table EMPS, and it has say 5 fields, and I'm retreiving all 5 fields for every EMP_ID (the key field).

    Now, I also want one additional piece of information. There is another table, say JOBS (Key = JOB_ID, FKey = EMPLOYEE_ID). Each job FKey refers to one EMP_ID. One EMP_ID can have many jobs.

    What do I do to show a count of how many jobs each employee has along with the rest of the data? All I want is a count, I do not want to show what each job is.
    Last edited by Disson; 04-07-04 at 15:17.

  2. #2
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    5

    Re: Showing a count of attached items within a report.

    Just getting into Crystal Reports myself, so I am not sure of everything it can do. I will tell you what comes to my mind first, and hopefully it will help you.

    Here is a quick, but not efficient way of doing what you are trying to do. You could run a subreport that returns or shows the total count. The main report will be linked to the subreport by EMP_ID and EMPLOYEE_ID. In the subreport put the jobs(Employee_id) in the detail section. Suppress everything including the details section, except the report footer. Right click on the Employee_id field and click insert->Grand Total. On the drop down menu select count. You want this grand total field to be to the far left of the report so that your subreport can be small. Now you are done with the subreport. In your main report, insert the subreport where you want the count to be. You might have to play around with it a little bit so that you can see the number.

    Another way would be to do about the same thing, but you could pass the count back through a shared variable.

    Both of these ways arent good because they will get real slow as your employees and jobs grow.

    Hopefully someone with more experience using Crystal Reports can tell you how to do this more efficiently. I'm sure there is a better way.

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Thanks very much for your reply. I was 90% through another fix as I saw your post.

    I found another way to kludge it together. I left outer joined the JOBS table, then added a running total field (i.e. countJobs) summarizing JOBS.EMPLOYEE_ID by type "count" and reset on change of field EMPS.EMP_ID, and placed the running total to the details section.

    This was ok, but had a problem. If an employee had more than one job, I'd get a separate detail for each job, and the countJobs would go up one for each detail. In comes the kludge factor -> I just suppressed my details with:
    Code:
    if onlastrecord then false else 
      (if {EMPS.EMP_ID} <> next({EMPS.EMP_ID}) then false else true)
    This hides all the iterations of the duplicate details except for the one with the highest (and correct) # of jobs. Seems to be working, of course any other running totals had to be changed to evaluate only on change of field EMPS.EMP_ID, instead of for each record. Else they would be adding totals from the now-phantom-records.
    Last edited by Disson; 04-08-04 at 16:23.

  4. #4
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    5

    Re: Showing a count of attached items within a report.

    Unfortunately the way I am using Crystal Reports, a solution like this is just not possible. A lot of options in Crystal Reports, like join type, are unavailable because of the type of data I am connecting to. Anyway, glad your getting it to work now. Good luck with the rest of your project.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •