User Defined Reporting “Basics”

1. User Defined Reporting “Basics”

2. Welcome ! DataQuest User Defined Reporting Basics. In this presentation you will learn the location of the Data Definition File (DDF), how to print the DDF, how to create and print User Defined Reports (UDR’s) as well as the basics of Sorting, Sub-Totaling and Filtering the data to be printed using search criteria.

3. User Defined Reporting is a feature included in several of your DataQuest Applications. Accounts Receivable, Business License, Fixed Assets, General Ledger, Inventory Control, Payroll, Utility Billing, Vehicle Maintenance and Work Orders - to name a few. The fundamentals of User Defined Reporting are basically the same throughout all of the DataQuest Applications. For the purposes of this presentation UDR’s with the Payroll application will be demonstrated.

4. The Data Definition File (DDF) and it’s location. The DDF is basically a list of the data fields that are available to print on, sort by or filter by when using UDR’s. The DDF varies by Application but is similar in each application in several ways including how you access it, where it’s located and how it’s used. The DDF can be accessed in each Applications “Office Manger” or “System Manager” section. It can also be accessed thru the use of specified Function Key options while creating and printing UDR’s. NOTE – Most DataQuest Applications have several thousand individual data fields within them. Many of these fields are available for printing on UDR’s. Unfortunately some are not. If you can’t locate a data field that you would like to print on the DDF contact DataQuest Support. We will be glad to help you determine if that data field is available or not.

5. Data Definition File (DDF) Select option #9 – Office Manager from the Payroll Master Menu to locate the Payroll DDF Handler NOTE – In other DataQuest Applications this option may be named System Manager and the option number will vary by application.

6. Data Definition File (DDF) Select option #19 – DDF Handler from the Office Manager menu NOTE – in other DataQuest Applications the option number will vary.

7. Data Definition File (DDF) A key to successful user defined reporting is understanding the DDF for the application. We recommend that you select the (P) Print prompt above to get a hard copy printout of the DDF, study it and become familiar with the available DDF fields. You can also use the (I) Inquiry prompt to view the DDF on the screen. The (C ), (U), and (D) prompts can be used to Create, Update or Delete DDF Records but we strongly suggest you consult with DataQuest Support before using these options.

8. Data Definition File (DDF) Once you select the (P) Print prompt you will be asked for the printer number to print the listing to.

9. Data Definition File (DDF) To print the complete DDF Listing leave the Begin Code blank and enter 9999 in the End Code. Report Order (C ) will sort the list by DDF Code Number and the (A) Alpha option will sort the list Alphabetically by DDF Description.

10. Date: 8/01/2003 City of UserReporting Page: 1 Time: 10:33 Data Definition Listing Id: XX9930 User: THMS DDF # Description Report Format Begin End Type Record 10 Record Count 9 734 734 UN 1 20 Payroll Key XX-XXXXXXXXX 1 11 AN 1 21 Pay Key XX 1 2 AN 1 22 Employee # ZZZZZZZZ9 3 11 UN 1 25 Location XXX 701 703 AN 1 27 Payroll Number Z9 265 266 UN 1 30 Name XXXXXXXXXXXXXXXXXXXXXXXXX 704 733 AN 1 40 Address XXXXXXXXXXXXXXXXXXXXXXXXX 40 64 AN 1 50 Address (Second Line) XXXXXXXXXXXXXXXXXXXXXXXXX 65 89 AN 1 60 City State XXXXXXXXXXXXXXXXXXXXXXXXX 90 114 AN 1 70 Zip Code XXXXX-XXXX 115 123 AN 1 80 Social Security Number XXX-XX-XXXX 124 132 AN 1 90 Plan Date 99/99/9999 199 202 DA 1 92 Plan CC 99 199 199 CP 1 93 Plan MM 99 201 201 CP 1 94 Plan DD 99 202 202 CP 1 95 Plan YY 99 200 200 CP 1 100 Evaluation Date 99/99/9999 206 209 DA 1 102 Evaluation CC 99 206 206 CP 1 103 Evaluation MM 99 208 208 CP 1 104 Evaluation DD 99 209 209 CP 1 105 Evaluation YY 99 207 207 CP 1 Data Definition File (DDF) This slide shows a sample of the Payroll DDF Listing by Code. The complete listing is actually several pages long.

11. Creating a User Defined Report This next section will demonstrate how to create a very basic User Defined Report

12. Accessing the User Defined Reporting menu. To access the UDR Menu select option 54 User Reports from the Payroll Master Menu Note – the option number will vary by application.

13. User Report Options From the User Reports menu you can Define Reports and Print Reports. To create a new UDR select option 1 Define Reports. This same option can be used to Update, Inquire or Delete UDR’s as shown by the CUID prompt on the next slide.

14. Creating a User Defined Report To create a UDR type C to select Create Mode from the C U I D prompt. The other prompts U I D can be used to Update, Inquire or Delete UDR’s once they have been created. NOTE – You must complete 2 screens to create a UDR. Screen 01 defines things like the report number, name, sorting, sub-totaling, etc and once you complete screen 01 you will be presented with screen 02 where you define the data fields to print on the reports detail lines.

15. Creating a User Defined Report (Screen 01) The top section of the User Report Definition screen 01 (shown above) ask a few questions that we will discuss in detail over the next few slides. Group – The Group field can be used to categorize reports and can be given any identifier that works for your situation. The Group could be your Initials or PAY to designate this group deals with reports pertaining to salaries. Up to 99 reports can be created for each Group. It will accept any 3 digit combination of letters or numbers. Report Number – The report number is a numeric, two-digit field which means you can number your report using any number from 1 – 99. Access Code – S, G, U System, Group, User (more details on next slide)

16. Access Code A UDR created with: S (system access) will allow any user that has access to the application (Payroll in this example) to Print the report. G (group access) Group access gathers reports similar in content. It can be used in the Print Menu to display reports for printing by group. Groups can be used to designate reports by department or other categories as you desire. U (user access) will limit access to the UDR to the individual user that created it.

17. Creating a User Defined Report (Screen 01) Report Name – is what will be shown in the print menu box. In other words, it is how you will identify this report when selecting reports to print from the Print Menu. It will not actually print on the report itself. You will also see the report number on the Print Menu. Major Heads – Two lines are available for the report heading. Enter the title of the report and any other text you wish to print each time the report is printed. You will have an opportunity to type in another line of (this time only heading) when you actually print the report from the Print menu.

18. Creating a User Defined Report (Screen 01) Characters per Line – This field determines the number of characters printed per line and the maximum number is 132. Lines per Page – This field determines the number of lines printed on each page. The default of 60 will work for most printers with 11 inch paper that are printing at 6 lines per inch or with 8.5 inch paper printing at 8 lines per inch. You may adjust the default up or down to get the results you need. Spacing – This determines the line spacing. It defaults to 1 which produces single spaced lines on the report. You can select 2 for double spacing or 3 for triple spacing. Print Underlines on each Line ? - If you enter Y here each line will be underlined.

19. Creating a User Defined Report (Screen 01) Sorting Information – Up to five levels of sorting are available for each report. With UDR’s you can sort the details printed on the report by any of the available DDF fields. Commonly used sorts are by Employee Name or Number. An example of a two level sort would be to print the records sorted by Employee Name or Number within Location. If you do not specify a DDF field to sort by the records will sort by the primary key to the record which is the employee number in the case of the Payroll application. Use of the sorting information will be demonstrated later in this presentation. Top of Page – can be used to generate a page break when the data changes. A good example of how to use this would be if you wanted each Location printed on a separate page.

20. Creating a User Defined Report (Screen 01) Sub-total when the following changes - can be used to produce up to 5 levels of totals as the data changes for the DDF field specified. A good example of using Sub-totals would be if you wanted totals by Location. An example of a 2 level sub-total could be if you wanted totals by Employee and Location. Sub-totals by Location will be demonstrated later in this presentation. Headings for Sub-totals – is used to identify your sub-total. What ever you enter as the Heading will print beside that sub-total on the report. F2..For Valid Report Fields – can be used to display on the screen the DDF Listing. Once displayed you can scroll forward or backward to locate DDF fields. This option is available on both screen 01 and 02 when creating UDR’s.

21. Creating a User Defined Report (Screen 02) When you complete screen 01 the next screen 02 will be displayed. On this screen you can specify up to 10 DDF fields to print on the UDR. You simply enter the DDF Field numbers in the Fl/Cd location and when you do the DDF Description and Detail Format will be displayed. The DDF Description will be used as the default Heading for that column of data. You can edit the Description if necessary and your changes will print on the report (but will not change the description in the DDF). NOTE the F2 Valid Report Fields option can be used here as well.

22. Creating a UDR - (Screen 02) This screen shows screen 02 completed by entering 4 DDF fields to print. Notice how the DDF fields selected display at the lower portion of the screen to detail how they will look when printed on the report. The Print positions used and Positions available information allows you to keep your detail line within the limits you specified as the Characters per Line (on screen 01). The Maximum allowed is 132 characters per line. These fields automatically update as you add or remove DDF fields from this screen.

23. Printing a User Defined Report Once a User Defined Report has been defined, you must return to the User Reports menu and select the Print Reports option to print the report.

24. Printing a User Defined Report Report Menu to View – relates to the access code the report was created for. U = user, D = Department or Group, S = System. The default is U which will limit the reports displayed to those created by the user. Specify S (System) to select reports designated as System reports by other users on the Payroll system. Specify D (Dept) to select reports by Group. If the list of available reports is very long you might find it helpful to use the F5.. To ENTER help option. This will move the screen pointer to the list of reports displayed and you can scroll forward or backward thru the list to select the desired report.

25. Printing a User Defined Report Report Name - Once you select a report to print the Report Name will be displayed on the next screen. This screen will ask for a Beginning and Ending Employee # to print which you can use to limit your results or you can leave blank to include all employee numbers. Records to read: can be used to search your database a specified number of records at a time. If you enter 100 here the program will stop after it reads each set of 100 records, display some information detailing the number of records to be printed at that point and give you the option to stop and print them or continue with the next 100 records. You can leave this blank to read the entire file with no interruptions. Additional Heading – can be used to include a heading for this run only which will print in addition to the two lines of heading that you can enter when you create the report.

26. Printing a User Defined Report Once you complete the Additional Heading field it will ask you to Enter Printer Number. The F3.. Search criteria option will be demonstrated later in this presentation. This option is used to define filters for selecting exactly the data you wish to print. The F4.. To view/edit report info – can be used to view or edit the report definition. The Searching on:, Records read and selected fields will be populated automatically as the report is running.

27. Date: 8/01/2003 City of UserReporting Page: 1 Time: 12:24 Employee Information List Id: PP1601 User: THMS Employee # Location ---------Name----------- Birthdate 100 001 JASPER, BOBBY B. 10/17/1938 101 001 JESSIE, JAMES L 09/06/1946 102 001 JOHNSON, KONAN 03/31/1947 103 001 BOOKS, MARY ALLICE 05/08/1947 104 001 OVERTON, PAULA R 04/11/1948 210 002 HOUSE, BRICK A. 11/05/1943 211 002 KENMORE, JUDY P. 08/17/1948 212 001 TOOKERS, WANDA P. 03/10/1950 213 002 JENKINS, DONNIE A. 08/08/1951 214 002 TREVORS, RICK ARNOLD 04/25/1956 215 002 WOOLIE, ANNELLE P. 11/26/1958 Printing a User Defined Report Once the printer number is entered the report will generate. Notice the results above include the Report Headings, Column Headings and DDF Fields we requested to print. Also notice they are sorted by Employee #.

28. User Defined Reporting This next section will demonstrate taking the very basic UDR created in section 1 and adding a DDF field to the print detail line as well as specifying a 2 level sort and subtotal. Sorting and Sub-Totaling work together to make a reports information much more useful. UDR’s are also very flexible in that once created you can easily modify the report to add or remove other DDF fields as needed.

29. Updating a User Defined Report To Update/Modify a User Defined Report enter the Define Reports option from the User Reports menu.

30. Updating a User Defined Report At the CUID prompt select U (Update) mode and enter the Report Number to modify. While in update mode I added sorting information to make the report sort by Location and then by Birth date. I also entered Sub-total information to make the report Sub-total by Location with a Heading of Location Total. Next I completed the screen and responded Y to go to the Report Format screen.

31. Updating a UDR (Screen 02) While on screen 02 I entered thru the 4 DDF fields that were originally setup on the report and in the next available Fl/Cd slot I added DDF field 3020 Annual Salary. The print positions used and available are automatically updated showing my changes. Next I completed the screen.

32. Printing a User Defined Report Once a User Defined Report has been defined, you must return to the User Reports menu and select the Print Reports option to print the report.

33. Printing a User Defined Report Report Menu to View – relates to the access code the report was created for. U = user, D = Department or Group, S = System. The default is U which will limit the reports displayed to those created by the user. Specify S (System) to select reports designated as System reports by other users on the Payroll system. Specify D (Dept) to select reports by Group. If the list of available reports is very long you might find it helpful to use the F5.. To ENTER help option. This will move the screen pointer to the list of reports displayed and you can scroll forward or backward thru the list to select the desired report.

34. Printing a User Defined Report Report Name - Once you select a report to print the Report Name will be displayed on the next screen. This screen will ask for a Beginning and Ending Employee # to print which you can use to limit your results or you can leave blank to include all employee numbers. Records to read: can be used to search your database a specified number of records at a time. If you enter 100 here the program will stop after it reads each set of 100 records, display some information detailing the number of records to be printed at that point and give you the option to stop and print them or continue with the next 100 records. You can leave this blank to read the entire file with no interruptions. Additional Heading – can be used to include a heading for this run only which will print in addition to the two lines of heading that you can enter when you create the report.

35. Printing a User Defined Report Once you complete the Additional Heading field it will ask you to Enter Printer Number. The F3.. Search criteria option will be demonstrated later in this presentation. This option is used to define filters for selecting exactly the data you wish to print. The F4.. To view/edit report info – can be used to view or edit the report definition. The Searching on:, Records read and selected fields will be populated automatically as the report is running.

36. Date: 8/01/2003 City of UserReporting Page: 1 Time: 13:03 Employee Information List Id: PP1601 User: THMS Employee # Location ---------Name----------- Birthdate Annual Salary 100 001 JASPER, BOBBY B. 10/17/1938 25,000.00 101 001 JESSIE, JAMES L 09/06/1946 15,000.00 102 001 JOHNSON, KONAN 03/31/1947 30,000.00 103 001 BOOKS, MARY ALLICE 05/08/1947 20,000.00 104 001 OVERTON, PAULA R 04/11/1948 35,000.00 Location Total 125,000.00 -------------------------------------------------------------------------- 210 002 HOUSE, BRICK A. 11/05/1943 10,000.00 211 002 KENMORE, JUDY P. 08/17/1948 50,000.00 212 002 TOOKERS, WANDA P. 03/10/1950 20,000.00 213 002 JENKINS, DONNIE A. 08/08/1951 30,000.00 214 002 TREVORS, RICK ARNOLD 04/25/1956 25,000.00 215 002 WOOLIE, ANNELLE P. 11/26/1958 20,000.00 Location Total 155,000.00 -------------------------------------------------------------------------- Grand Totals 280,000.00 -------------------------------------------------------------------------- Printing a User Defined Report Once the printer number is entered the report will generate. Notice the report is now sorted by Location and within Location the employee records are sorted by Birth date. The Annual Salary information is now printing with Sub-totals by Location and Grand Totals.

37. User Defined Reporting – Basics Section 3 – Filtering with Search Criteria This next section will demonstrate using the Search Criteria option to filter the data to be included on the UDR. Filtering the data using Search Criteria is how you manage to limit the information on the report to exactly what you need to see. For Example – if you only want to list employees from one location that make more than 20,000.00 annual salary.

38. Using Search Criteria to Filter the Data To define Search Criteria for a UDR select the Print Reports menu. Notice that the Search Criteria is defined when printing the report rather than as part of the report definition.

39. Printing a User Defined Report Report Menu to View – relates to the access code the report was created for. U = user, D = Department or Group, S = System. The default is U which will limit the reports displayed to those created by the user. Specify S (System) to select reports designated as System reports by other users on the Payroll system. Specify D (Dept) to select reports by Group. If the list of available reports is very long you might find it helpful to use the F5.. To ENTER help option. This will move the screen pointer to the list of reports displayed and you can scroll forward or backward thru the list to select the desired report.

40. Using Search Criteria to Filter the Data Once you have selected your report from the Print Menu the F3 Search Criteria option is available. Press F3 to display the search criteria definition screen.

41. Using Search Criteria to Filter the Data You can define search criteria (or data filters) using any of the available DDF fields and to view the DDF Listing on the screen you can use the F5 for valid categories option. Possible Conditions – The 6 possible search conditions are displayed in the upper right corner of the screen. The first two work with DDF fields that are Alphanumeric (type AN from the DDF Listing) The last 4 work with all other DDF types.

42. Using Search Criteria to Filter the Data In the bottom section of the search criteria screen you are asked the Category, Condition and Value. These 3 work together to define a data filter. Category – Is the DDF Field that you wish to filter the records by. Condition – this is where you enter one of the 6 possible conditions mentioned on the previous slide. Value – this is what your filter/condition is compared to when determining the records to print.

43. Using Search Criteria to Filter the Data Note - you can define up to 5 filters for each User Defined Report. Once the search criteria is defined it becomes a part of the report and will be retained for future report runs. Search criteria is retained by user so in the case of (S) access - System type reports that may be shared by many users within the application, search criteria defined by one user will not become a part of the same report for another user. Each user must define their own search criteria in the case of shared reports. In our sample below we have defined search criteria to include Location = 002 and Annual Salary > 20000.00.

44. Using Search Criteria to Filter the Data Once you complete the Search Criteria definition screen you will be returned to the previous screen and notice the Search indicator that is now displayed in the upper left corner of the screen. This can serve as a reminder or flag for future runs that you have defined search criteria data filters for this report. Once you complete this screen and specify the printer number the report will be generated using your search criteria.

45. Date: 8/01/2003 City of UserReporting Page: 1 Time: 13:44 Employee Information List Id: PP1601 User: THMS Employee # Location ----------Name----------- Birthdate Annual Salary 211 002 KENMORE, JUDY P. 08/17/1948 50,000.00 213 002 JENKINS, DONNIE A. 08/08/1951 30,000.00 214 002 TREVORS, RICK ARNOLD 04/25/1956 25,000.00 Location Total 105,000.00 -------------------------------------------------------------------------- Grand Totals 105,000.00 -------------------------------------------------------------------------- Using Search Criteria to Filter the Data Notice that the report now includes only those employee records from Location 002 that have Annual Salary (greater than) 20,000.00. The Location and Grand Totals are adjusted according to the records that are now included on the report.

46. The END Note: You can close this window to return to LGDPC’s How-To Presentations Page.