Crystal Reports - Linking Databases Report
Crystal Reports - Linking Databases
Why do we need to Link Databases to create a report?
All the fields in Abra are grouped into multiple Databases as opposed to one large database. This makes for a more efficient system, i.e. speedier access and processing of data. However, when you need a report that consists of Fields from more than one Database, you must Link the databases in order to be able to select the Fields you want.
Abra Database Overview
Let's first review the structure of the Abra system to give you some insight into how we are going to approach our objective.
Basic Report Creation
When creating a report, you want specific Fields on the report, e.g. Last Name, Annual Salary, Job Title, etc.
How are Fields Organized in Abra?
1. Fields are stored in Database files
2. The map of the Abra Database files can be found in the Data Dictionary
3. The Data Dictionary can be found in Abra under Help on the top menu bar. Follow the path.
Help/ Contents/Reference Material/Abra HR Data Dictionary or Abra HR Database Files.
(Note, data dictionaries for Abra Payroll, Abra Attendance, Abra Train and Abra Recruiting Solution are also located in this area)
*** Did You Know? ***
The HRPersnl Database file contains all the Fields located in the first 4 panels of the employee record
***Demographics, HR Status, Current Pay, Current Job***
Hint: Always start with the HRPersnl Database!
4. Peruse the Databases, in particular HRPersnl and HBene, which we will use in our example. Note they have 2 Fields in common. These 2 Fields will become the "Link" between the 2 Databases.
Who knows what the 2 Fields are?
| Company (aka Employer) | Employee Number |
What are the system names for these Fields?
| p_company | p_empno |
Creating a Report Using 2 Databases
For our example, we will create a report that contains Demographic data (HRPersnl) and Benefits data (HBene)
1. When you launch Crystal Reports, select Using Report Expert in the Welcome to Crystal Reports dialog box.
2. Select the Standard Expert for our example and click OK.
3. The Standard Report Expert dialog box appears with several Tabs across the top and 3 buttons along the left side. Click on Database.
4. The Data Explorer dialog box appears with a list of choices in the window. Click on the + to expand the Favorites.
Note: Abra Data Access must be set up in Crystal so that the security you set up for reports in Abra will apply to the reports generated in Crystal. If you do NOT see Abra Data Access when you expand the Favorites, Abra Data Access is NOT set up. For instructions on how to set up Abra Data Access, go to the Help on the top bar in Abra, click on Contents, Index tab and type in Abra Data Access.
5. For our example, we want Fields from the HRPersnl database and from the HBene Database. Therefore, highlight the HRPersnl database first and click Add. Repeat the process to select HBene.
6. Now we have advanced to the Links tab in our Window. Note there are 2 boxes representing the 2 Databases we selected.
*** Does It Matter What Order the DATABASES Are In? ***
ABSOLUTELY!!
HRPersnl MUST Be On the LEFT in the First Position
You can scroll down and see the fields in each Database. Our objective is to link these databases using the 2 Fields from above (Company and Employee Number).
Crystal Makes it EASY!!
Put your cursor on p_Company in the HRPersnl Database and drag it over to p_Company in the HBene Database. Voila! Crystal draws a line between these 2 Fields! Now, do the same with Employee Number (p_Empno).
Notice that you can scroll up or down through these Databases, and the Link line stays in tact.
7. If you want to include employees on the report that have no benefits, click on the Link Options button. Under SQL Join Type we are going to select Left Outer.
8. Proceed to the Fields tab where you will see a list of Fields from both the HRPersnl and Hbene Databases. Highlight the Fields you want on your report and click Add. For our example, select the Employee Contribution (b_contrib), Employee Premium (b_epremium) and Dependent Premium (b_dpremium).
9. Advance to the Group tab, and select _Name2 (First, M, Last).
10. Under the Total tab, pull the Summary Type box down and select Count.
11. FINISH!
|Top|