Crystal Reports
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.
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.
When you do go to a site like sonic meat machine recommends, make sure you post exact details about the table structure. We don't know what your database is actually laid out like (what's "involved staff"? what's caseholding? What's allocation? What tables are these pieces of information stored in? How are these criteria "attached"?).
It definitely sounds to me like you need to research how to nest queries but it will be impossible to be more specific without more detail from you.
And actually, you might find that writing out your table structures in more detail is all that you need to solve the problem and give you that "a ha!" moment--it has certainly worked for me before.
posted by bcwinters at 4:57 AM on February 15, 2011
It definitely sounds to me like you need to research how to nest queries but it will be impossible to be more specific without more detail from you.
And actually, you might find that writing out your table structures in more detail is all that you need to solve the problem and give you that "a ha!" moment--it has certainly worked for me before.
posted by bcwinters at 4:57 AM on February 15, 2011
I use CR but I have given up on the query builder in favor of building the sql myself. SQL is really not that hard and you have much better control of your results. If you are doing the sql or have access to I might be able to solve your problems.
I think you can get the one copy of each serviceuser by putting the word 'distinct' in front of it. So select distinct ServiceUser ...
adding criteria from a left joined table is problematic but you can get around it one of two ways. First is making the table a sub query with the restriction you need inside:
... from Users u
left join (select * from Casehold where allocations >= 1) c on u.id = c.user_id...
or the second is to make the condition part of the join:
... from Users u
left join Casehold c on u.id = c.user_id and allocations >= 1 ...
You also might want to just count the matching staff rather then display them if you don't care who it is.
The second report uses the max aggregate function, so something like
select service_user, max(visit_date)
from ...
where ...
group by service_user
I am sure there is a way to user the max aggregate function in crystal
Hope I've helped.
PS I am used to postgres so some of the syntax may be a little off.
posted by d4nj450n at 8:22 AM on February 15, 2011
I think you can get the one copy of each serviceuser by putting the word 'distinct' in front of it. So select distinct ServiceUser ...
adding criteria from a left joined table is problematic but you can get around it one of two ways. First is making the table a sub query with the restriction you need inside:
... from Users u
left join (select * from Casehold where allocations >= 1) c on u.id = c.user_id...
or the second is to make the condition part of the join:
... from Users u
left join Casehold c on u.id = c.user_id and allocations >= 1 ...
You also might want to just count the matching staff rather then display them if you don't care who it is.
The second report uses the max aggregate function, so something like
select service_user, max(visit_date)
from ...
where ...
group by service_user
I am sure there is a way to user the max aggregate function in crystal
Hope I've helped.
PS I am used to postgres so some of the syntax may be a little off.
posted by d4nj450n at 8:22 AM on February 15, 2011
« Older Name the sappy, instrumental tune, whose basic... | Looking for work in Amsterdam and can't seem to... Newer »
This thread is closed to new comments.
Not to be a negative nellie, but StackExchange tends to have a higher volume and density of technically savvy respondents in specific technical areas than ask.mefi.
posted by sonic meat machine at 4:38 AM on February 15, 2011