thebee
November 19th, 2004, 12:11 PM
Ok, I have been given the wonder responsibility of learning how to use crystal reports at my job. Since we are a non-profit company, I will not be getting any courses or extra material that would allow me to learn the things I need to create reports. I understand the basics of reporting and understand SQL database synatx like a master. :)
the first project I decided to pick up, proved to be the most difficult I imagine, As I have seen MANY posts on the web about this exact subject. But only found one with an answer in it. I was directed by this post to download a sample report that crystal made that looks exactly like I want mine to but doesnt function the same way.
Let me tell you the initial scope of what I want to do and tell you what I so far have accomplished and the walls that I am dealing with.
I have a program that was purchased that does client->staff scheduling. Clients are assigned 'workshifts' and staff are assigned to the the workshifts. In the database I have 3 tables that are the focus of this report. EMPLOYEE, SCHEDULE, CLIENT. Fields I am concerned with are as follows
EMPLOYEE.LASTNAME
EMPLOYEE.FIRSTNAME
EMPLOYEE.PERINDEX <- number field for relations
CLIENT.SHORTNAME
CLIENT.CUSINDEX <- number field for relations
SCHEDULE.SHIFTSTART <-date/time field in this format YYYYMMDDHHMM
SCHEDULE.SHIFTEND <-same as above
SCHEDULE.PERINDEX <- relation to EMPLOYEE.PERINDEX
SCHEDULE.CUSINDEX <- relation to CLIENT.CUSINDEX
I want the report to prompt for employee, by picking a list of names that are in the format of 'LASTNAME, FIRSTNAME' but are a value of the PERINDEX field.
Then pick a month from a list and then type in a year in the format of YYYY.
Later I will want to be able to report on every employee instead of just one at a time.
The report needs to look like a calendar with a list of clients and times in each day 'subreport'.
I have it setup so far to have 7 columns span across the page and then 5 rows down. (well that isnt exactly working it only shows one subreport. i guess it will only display 35 boxes if i have it tied to a field that has exactly 35 records.) it only displays one box at the time.
Im really just having a problem with where to start. I know I need to somehow tell cyrstal on report parameter completion to pull the records from schedule that corrispond to the correct month for a certain employee. In SQL I know how to do that in a snap ... someything like ...
SELECT
s.SHIFTSTART, s.SHIFTEND, c.SHORTNAME
FROM
SCHEDULE as s
LEFT JOIN
EMPLOYEE as e
ON s.PERINDEX=e.PERINDEX
LEFT JOIN
CLIENT as c
ON s.CUSINDEX=c.CUSINDEX
WHERE
e.PERINDEX={?Employee}
AND s.SHIFTSTART LIKE '{?Year}{?Month}'
ORDER BY
s.SHIFTSTART
ASC
But with crystal and the fact that these tables im dealing with are DBF format, I seem to be stuck with how to do that. Can anyone give me a clue on how to do this?
the first project I decided to pick up, proved to be the most difficult I imagine, As I have seen MANY posts on the web about this exact subject. But only found one with an answer in it. I was directed by this post to download a sample report that crystal made that looks exactly like I want mine to but doesnt function the same way.
Let me tell you the initial scope of what I want to do and tell you what I so far have accomplished and the walls that I am dealing with.
I have a program that was purchased that does client->staff scheduling. Clients are assigned 'workshifts' and staff are assigned to the the workshifts. In the database I have 3 tables that are the focus of this report. EMPLOYEE, SCHEDULE, CLIENT. Fields I am concerned with are as follows
EMPLOYEE.LASTNAME
EMPLOYEE.FIRSTNAME
EMPLOYEE.PERINDEX <- number field for relations
CLIENT.SHORTNAME
CLIENT.CUSINDEX <- number field for relations
SCHEDULE.SHIFTSTART <-date/time field in this format YYYYMMDDHHMM
SCHEDULE.SHIFTEND <-same as above
SCHEDULE.PERINDEX <- relation to EMPLOYEE.PERINDEX
SCHEDULE.CUSINDEX <- relation to CLIENT.CUSINDEX
I want the report to prompt for employee, by picking a list of names that are in the format of 'LASTNAME, FIRSTNAME' but are a value of the PERINDEX field.
Then pick a month from a list and then type in a year in the format of YYYY.
Later I will want to be able to report on every employee instead of just one at a time.
The report needs to look like a calendar with a list of clients and times in each day 'subreport'.
I have it setup so far to have 7 columns span across the page and then 5 rows down. (well that isnt exactly working it only shows one subreport. i guess it will only display 35 boxes if i have it tied to a field that has exactly 35 records.) it only displays one box at the time.
Im really just having a problem with where to start. I know I need to somehow tell cyrstal on report parameter completion to pull the records from schedule that corrispond to the correct month for a certain employee. In SQL I know how to do that in a snap ... someything like ...
SELECT
s.SHIFTSTART, s.SHIFTEND, c.SHORTNAME
FROM
SCHEDULE as s
LEFT JOIN
EMPLOYEE as e
ON s.PERINDEX=e.PERINDEX
LEFT JOIN
CLIENT as c
ON s.CUSINDEX=c.CUSINDEX
WHERE
e.PERINDEX={?Employee}
AND s.SHIFTSTART LIKE '{?Year}{?Month}'
ORDER BY
s.SHIFTSTART
ASC
But with crystal and the fact that these tables im dealing with are DBF format, I seem to be stuck with how to do that. Can anyone give me a clue on how to do this?