February 15, 2011 3:32 AM Subscribe
Help with using Crystal Reports to report on an Oracle database
I am responsible for reporting data from a commercially available Oracle-based database with a fairly complex table structure. We use Crystal to extract the data. I don’t have much training and although I can usually get out what I need I do a lot of matching in Access to merge data. At present I am running a lot of weekly reports which are taking about 20-25 hours to run manually, match, format, summarise etc. I have requested a break from these reports next week in order to use the time to try to automate as much of this as possible and put the reports on Crystal Scheduler.
I’m hoping people can advise about some specific things I don’t know how to do in Crystal, and also point me to other resources (books or websites). I have Crystal Reports for Dummies but mostly don’t find it helpful as it doesn’t seem to address things I want to do.
Firstly, I am having problems when linking two tables and wanting to return records in the first table based on specific criteria, plus linked fields in the second table with criteria attached. For instance, I have a table of service users which is linked to a table of involved staff. This is a one to many relationship. I want to list all open service users in the first table with current staff allocations of a particular type from the second table. If someone has no staff allocation of that type I still want to see the service user record anyway (it is a genuine open case, it just wrongly has no staff allocated or staff are allocated with the wrong allocation type recorded). So I would want an output including records in all three situations listed below:
ServiceUser1, AllocatedStaffName (where allocation is correctly recorded as caseholding)
ServiceUser2, blank staff details (where there is no matching staff record in the second table)
ServiceUser3, blank staff details (where there is a matching staff record in the second table, but allocation is not recorded as caseholding)
I also want every service user to appear once only, so if there is more than one caseholding allocation recorded I just want one to be returned (don’t mind which).
I understand about left outer joins so I can produce all service users with attached data from the second table or blanks if there are no matches. But if I add criteria to select only caseholding allocations in the second table I miss any records in the first table that do not have matched records in the second table.
Secondly, I want to report on the most recent visit date. Again, I have a table of service users which I can link to casenote recording (visits are recorded as a particular type of casenote), and again this is a one to many relationship. How can I output each service user with the date of their most recent visit (specific casenote type plus criterion to specify that service user was seen)? I’m doing this tediously at present by outputting all visits and manually deleting in Access all but the most recent one for each service user.
Thanks for your help – please let me know if I need to clarify anything.