If you're a new ad hoc report writer, this article is designed to give you an overview of the steps needed to develop a basic report from start to finish.
Step 1 - Determine the Purpose of the Report
The first step in the ad hoc report process is to determine the purpose of the report and how it will be used. For example, let's say you're asked to identify the hire date of employees at your campus. Before you proceed, you'll need to nail down a bit more detail. Some good questions to start with are: Who, What, When, Where and How?
-
Who will be included in the report? Does 'employee' mean faculty, staff, and management? Will the report include active and separated employees? Does 'hire date' mean a permanent appointment?
-
What data elements are needed for the report columns and screening statements?
-
When is the timeframe for the data? Is current or historical data needed?
-
Where can you find the needed data? Is it considered to be employment history, payment history or leave accounting data?
-
How will the output be generated? A printed report? A file for downloading?
After you've done this step, you can search the Compendium to locate reports that meet your reporting needs. If you find one, great! If not, proceed to step 2.
Step 2 - Identify Required File(s) and Fields
After you've determined the purpose of the report, you need to identify the file(s) and field(s) required to satisfy your report needs.
-
After identifying the type of data needed (Employment History). Refer to the CIRS Data Element Dictionary for a list of all available databases containing Employment History data and identify the one that's the best suited for your reporting needs (Active Current Status file).
-
Identify the fieldnames for the specific data elements you'd like to appear on the report (Employee Social, Name, Employment Date) along with any fieldnames and values needed for screening statements, if needed.
-
Confirm that all the data elements needed for your report reside in the Active Current Status file. Note: If a report request needs data from more than one file, you'll need to use a JOIN or a MATCH.
After you've done this step, you can search the Common library to identify and copy a report request that meets your report needs. If you find one, great! If not, proceed to step 3.
Step 3 - Write Report and Test Commands
Armed with the information determined above, create a new report in your personal library, then test the commands in the online FOCUS environment. The commands below will identify the employment date (hire date) of current active and onleave employees. Screening statement are not needed to identify the desired employee groups (faculty, staff and management who are active or onleave) and the desired timeframe (current data) because the AC file does not have historical data, student data, or separations.
-
Refer to the Basic Reporting workbook and/or your FOCUS documentation for an overview of basic report structure and commands.
-
Refer to the quick guides in the CIRS User Manual for step-by-step instructions on creating and executing report requests.
-
Be sure to include a record limit to keep the costs down while you're testing and developing the report request.
Report Request:
|
Report Generated:
|
-* hire date (empdate) of current ee's
EX ac
TABLE FILE ac
print ac:empdate
BY AC:SSA
by ac:wname
if recordlimit eq 5
end
|
AC:SSA AC:WNAME AC:EMPDATE
------ -------- ----------
000-00-0000 KANGAROO, ANN 10/03/2005
111-11-1111 CAMEL, CHRIS 01/19/1999
222-22-2222 HIPPO, RON 08/29/1983
333-33-3333 APE, ETHEL 08/25/2003
444-44-4444 FROG, NANCY 01/24/2006
|
Step 4 - Verify The Output
Using a sample of the data, verify the results of your report request. For example, review the output to verify the data is accurate and formatted per your requirements. Note: This step is especially important if your report is using defined fields, and/or is matching data.
-
If you were using a very small record limit to check the syntax and formatting, you may need a larger sample for verification.
-
Use Compendium reports to help verify your results. Note: Your report totals will not match exactly the counts on the Compendium reports - just confirm your counts are close.
-
If you have access to PIMS, you can lookup employees on the HIST and CSUC inquiry screens to verify the data.
Step 5 - Final Disposition
After you've got the basic report working, decide on the final deposition of the report (print or download) and modify it accordingly. After you've made the modifications, remove the record limits and execute the report using Batch. Refer to the CIRS User Manual or the Basic Reporting workbook for more information on Batch execution.
-
If the report will be downloaded to a desktop PC, remove the record limit and add the ON TABLE HOLD AS permfile FORMAT LOTUS command. Where permfile is your PERMLRG, PERMSML or PERMRPT file. Additional formats are available, refer to the FOCUS documentation.
Report Request:
|
Hold File Generated:
|
-* hire date (empdate) of current ee's
EX ac
TABLE FILE ac
print ac:empdate
BY ac:SSA
by ac:wname
on table hold as permlrg format lotus
end
|
"000-00-0000","KANGAROO, ANN ", 10032005
"111-11-1111","CAMEL, CHRIS ", 01191999
"222-22-2222","HIPPO, RON ", 08291983
"333-33-3333","APE, ETHEL ", 08252003
"444-44-4444","FROG, NANCY ", 01242006
|
-
If the report will be printed, this is the point where you'll add formatting commands for a heading, a footing, new columns titles, etc. Test your report request changes with a record limit in the online environment before submitting using Batch.
Report Request:
|
Printed Report Generated:
|
-* hire date (empdate) of current ee's
EX ac
TABLE FILE ac
HEADING CENTER
"EMPLOYMENT DATE FOR CURRENT EE'S"
" "
print ac:empdate as 'employ,date'
BY ac:SSA AS 'SSA'
by ac:wname AS 'EMPLOYEE'
FOOTING
"REPORT NAME: EMPDATE"
end
|
EMPLOYMENT DATE FOR CURRENT EE'S
SSA EMPLOYEE EMPLOY DATE
--- -------- ----------
000-00-0000 KANGAROO, ANN 10/03/2005
111-11-1111 CAMEL, CHRIS 01/19/1999
222-22-2222 HIPPO, RON 08/29/1983
333-33-3333 APE, ETHEL 08/25/2003
444-44-4444 FROG, NANCY 01/24/2006
REPORT NAME: EMPDATE
|
See also: Sample Request: Original Hire Date For Current Employees