Report Assistant for Microsoft Dynamics™ SL Time and Expense for Projects Module
PPM6-PTE0-RA00000 RPMN-PTE0-RA00650 Last Revision: March 14, 2006
PPM6-PTE0-RA00000 RPMN-PTE0-RA00650 Last Revision: March 14, 2006 Disclaimer This document may contain technical inaccuracies or typographical errors. Any documentation with respect to Microsoft Business Solutions products is provided for information purposes only and does not extend or modify the limited warranty extended to the licensee of Microsoft Dynamics™ SL software products. The names of companies, products, people, and/or data used in window illustrations and sample output are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted. The forms and policies presented in this manual may not be the most current available. Please contact the Training Group at Microsoft Business Solutions for the most up-todate information. This manual has been developed for Microsoft Dynamics™ SL 6.5. Copyright Manual copyright © 2006 Microsoft Corporation. All rights reserved. Your right to copy this documentation is limited by copyright law and the terms of the software license agreement. As the software licensee, you may make a reasonable number of copies or printouts for your own use. Making unauthorized copies, adaptations, compilations, or derivative works for commercial distribution is prohibited and constitutes a punishable violation of the law. Trademark Microsoft, ActiveX, Excel, FRx, FrontPage, MapPoint, Outlook, SharePoint, Visual Basic, Visual Studio, Windows, Windows NT and Windows Server are either registered trademarks or trademarks of Microsoft Corporation, FRx Software Corporation, or their affiliates in the United States and/or other countries. FRx Software Corporation is a subsidiary of Microsoft Corporation. Crystal Reports is a registered trademark of Business Objects SA. The names of actual companies and products mentioned herein may be trademarks or registered marks - in the United States and/or other countries - of their respective owners. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
Table of Contents How to use this Manual
1
Introduction Manual Sections
2 2
Sort and Select Statements
5
Overview Select Statements Field Operator Value Boolean Sort Statements Field Sort Type Sort Ascending Page Break Total Break Possible Values Chart
Data Field Illustrations Overview Timecard Entry (TM.TCE.00) Timecard Entry-Additional Information (TM.TCE.01) Timecard Entry-Additional Info (TM.TCE.01) – Customization Manager Timecard with Rate/Amount Entry (TM.TEA.00) Timecard with Rate/Amount Entry-Additional Info (TM.TEA.01) Timecard with Rate/Amount Entry-Additional Info –Customization Manager Project Timesheet Entry (TM.PTE.00) Project Timesheet -Additional Info (TM.PTE.01) – Customization Manager Project Timesheet-Units of Production Entry (TM.PTE.02) Project Timesheet-Units of Production Entry – Customization Manager Project Timesheet Entry with Rate/Amount Entry (TM.PTA.00) Project Timesheet with Rate/Amount Entry – Customization Manager Project Timesheet with Rate/Amount Entry-Units of Production (TM.PTA.02) Proj Timesheet with Rate/Amt Entry (TM.PTA.02)–Customization Manager
6 6 7 7 11 15 16 17 18 18 18 19 21
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
Timecard Correction (TM.COR.00) Travel & Expense Report Entry (TM.ENT.00) Travel & Expense Report Entry (TM.ENT.00) – Customization Manager Travel & Expense Report Entry-Additional Info (TM.ENT.01) Travel & Expense Report Entry-Additional Info– Customization Manager Expense Report Review & Approval (TM.ERA.00) Company Expense Reconciliation (TM.CER.00) Employee Position/Rate Maintenance (TM.EPJ.00) Employee Position/Rate Maintenance (TM.EPJ.00)–Customization Manager Expense Type Maintenance (TM.ETM.00) Prevailing Wage Maintenance (TM.PRE.00) Prevailing Wage Maintenance (TM.PRE.00) – Customization Manager Union Rate Maintenance (TM.URE.00) Union Rate Maintenance (TM.URE.00) - Customization Manager Week Maintenance (TM.WEM.00) Workers’ Compensation Maintenance (TM.WKM.00)
Report Information
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
55
Overview 56 Time Card Report - Screen TM.010.00 57 Labor Expense Posting Rpt/Recap - Screen TM.020.00 57 Labor Distribution by Chg/Home Sub - Screen TM.030.00 58 Employee Utilization - Screen TM.040.00 59 Employee Labor Distribution by Chg/Home Sub - Screen TM.050.00 59 Certified Payroll Rpt - Screen TM.060.00 60 Project Timesheet Report - Screen TM.080.00 61 GL Labor Reconciliation Report - Screen TM.100.00 61 Travel and Expense Report - Screen TM.410.00 62
Index
63
HOW TO USE THIS MANUAL
What you will learn in this section: In this section you will learn about the main sections in this Microsoft Dynamics™ SL Report Assistant manual. We will discuss the differences between sort and select statements. We will review possible values that can be used for both sort and select statement fields. We will also be reviewing basic information about the data field illustrations for each module data entry and maintenance screen. You will learn about basic module report information: master table, sort order, report name as stored in Crystal, and sort/select field examples.
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Introduction Flexible reporting is a very important part of a company’s ability to effectively operate their business. This manual is intended to assist you with flexible reporting in the Microsoft Dynamics™ SL Time and Expense for Projects module. It may also be of use during creation or modification of reports in the Crystal Report writer.
Manual Sections The manual is divided into three parts: Sort and Select Statements - The first section describes the differences between sort statements and select statements. It also describes each of the parameters that can be used in a sort or select statement and gives examples of how to use them. This section should be a good reference for users struggling with deciding which operator to use, or whether they need a select statement or a sort statement. At the end of this section, a Possible Values Chart has been included. The chart displays values stored in Microsoft Dynamics™ SL for some of the more common fields in the Time and Expense for Projects module. For example, time card type values, time card status values, employee status values, etc. When performing sort and select statements, you may not be sure of the values for some of these types of fields. The section displays some common fields with their possible values. Data Field Illustrations - The second section contains a picture of data entry screens and maintenance screens for the Time and Expense module. There is a reference for each field in the screen noting the table and field where the data from the field is stored in the Microsoft Dynamics™ SL data files, as noted through the Customization Manager module. For example, when entries are made in the Timecard Entry screen, the data entered in the Account field is stored in the Pjlabdet file, in a field named gl_acct. The illustration with the Timecard Entry screen in this section includes a reference to the Account field, noting the value for the field is stored in Pjlabdet.gl_acct. When printing a report, it is not always easy to determine the field to be used in a sort or select statement. You may know the field in which the data was entered in a screen, but are unsure what the field is called and the table in which the field is stored. With the information in this section, you can quickly determine the entry needed in the Sort or Select Field. Report Information - The third section contains pertinent information about each report in the Time and Expense for Projects module. The following information is contained in the section: •
2
Page
Master table - When using sort/select statements for printing reports, it is important to know the master table name. If the field chosen for the sort/select statement is from the master table, the
How to Use This Manual
•
•
•
result can be much faster report processing and more accurate results. Sort order - If a report is sorted differently than designed, report totals and subtotals may be inaccurate. Knowing the fields the report currently uses for the sort order may help you produce accurate reports when using sort statements. Report name as stored in Crystal - If you need to access the report to modify or copy it, it is important to have the report names. Many reports have multiple formats available, which makes it more difficult to determine the correct report to retrieve. Sort/select field examples - A table is included with sort/select fields for many of the more commonly used report filters for each report.
Page
3
S O R T A N D S E L E C T S TAT E M E N T S
What you will learn in this section: In this section you will learn about Microsoft Dynamics™ SL’s Select statements, used to limit the records included on reports. We will discuss Microsoft Dynamics™ SL’s Sort statements and their fields: Field, Sort Type, Sort Ascending, Page Break, Total Break.
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Overview Each of Microsoft Dynamics™ SL’s report screens include a Sort tab and a Select tab which allow you to enter parameters used to limit the information included on the reports. This section is intended to assist you with using those Sort and Select options. What is the difference between a Sort statement and Select statement? Select statements limit the records to be included in a report. For example, if you enter a Select statement to include only one employee in a report, only records meeting that criterion will be included in the report. If no records are found to match the Select parameters entered, the report will still print, but without data. Sort statements modify the sort order of the report. The report will still contain all of the data that is in the original report, but the Sort statement will change the order of the records when they are printed. You can create multiple Sort and Select statements in one report. The Sort and Select tabs appear the same for all reports. However, the available fields for Sort/Select statements are different, depending on the tables included in the report. When creating Sort and Select statements, be aware that the results might not always be what you expect. Reports are written with specific grouping criteria and sort orders. When other fields are entered for Sort or Select parameters, the report may not print or total properly, although data may still be printed. Section 3 of this manual details the master table and default sort order for each report.
Select Statements As noted earlier, a Select statement is used to limit the records to be included in a report. A Select statement is similar to a Filter. With the Select tab, you will enter fields, operators and values that will make up the filter controlling which records will be included in the report. The Select tab contains multiple rows, with four (4) fields per row. These four fields include: Field, Operator, Value and Boolean. These fields are discussed below, along with examples.
6
Page
Sort and Select Statements
FIGURE 1-1: SELECT TAB
Field The Field area is used to enter the table and field name to be used to filter the records in the report. When in the Field area, you can use Microsoft Dynamics™ SL’s F3 inquiry function to view a list of available tables and fields for the report. Sections 2 and 3 of this guide can also be used as a reference for field names. Note The list of available fields may include fields that are not used in the report. If you use a field in the Select statement that is not used in the report, you may get a blank report. See Section 3 for examples of fields commonly used for Sort/Select statements for each report.
Occasionally, you may need to add a field to the Field list to be used in a Select statement. This can be done by adding the ROI SELECT function to the report through Crystal Reports. Refer to Knowledge Base article 867089 for information on this process. Note ROI stands for Report Options Interpreter.
Operator The Operator field controls the type of filter to be applied to the records when the system is gathering data for the report. The available Operators and examples of their use are described below.
Page
7
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
FIGURE 1-2: SELECT TAB
Begins with The ‘Begins with’ operator is useful when you need a report for a group with a common factor. For example, if you need a report listing Employees where Employee IDs begin with ‘E0’, the following statement could be used.
Field
Operator
Value
Boolean
Pjemploy.employee
Begins With
E0
And
Between Use ‘Between’ when the report should only include records between a beginning and ending range. In the value field, separate the upper and lower values with a comma. In the following example, the report will include data for Employees where the Employee IDs are E01244 through E01255, inclusive.
Field
Operator
Value
Boolean
Pjemploy.employee
Between
E01244,E01255
And
Note When using between, the report includes records matching the beginning and ending values entered (i.e. E01244 and E01255 will be included in the report).
Contains The ‘Contains’ operator is used to select records, which have a specified value anywhere within a field. In the following example, the statement used would result in a report including only those Employees that have a ‘01’ in their ID.
8
Field
Operator
Value
Boolean
Pjempoly.employee
Contains
01
And
Page
Sort and Select Statements Equal Use ‘Equal’ for an exact match. In the following example, only data for Employee ‘E01244’ will be included in the report.
Field
Operator
Value
Boolean
Pjemploy.employee
Equal
E01244
And
Greater than ‘Greater than’ is used to include records larger (higher) than the value entered. In the following example, only Employees with a higher date greater than 1/28/00 will be included in the report. (Also see ‘Greater than or equal to’ below.)
Field
Operator
Value
Boolean
Pjemploy.date_hired
Greater than
1/28/00
And
Greater than or equal to ‘Greater than or equal to’ is used to include records containing the value entered, as well as records greater than the value. In the following example, employee hired dates 1/28/00 will be included in the report, along with hired dates higher than 1/28/00.
Field
Operator
Value
Boolean
Pjemploy.date_hired
Greater than or equal to
1/28/00
And
In The ‘In’ operator works similarly to the ‘Equal’ operator, but allows you to enter multiple items in the Value field. In the example below, Employee IDs E01244, E01246, and E01265 will be included in the report. If there are no records for one of the values entered, it will simply be left off the report - records for the other two values will still print.
Field
Operator
Value
Pjemploy.employee
In
E01244,E01246,E01265 And
Boolean
Is NULL Using the ‘Is NULL’ operator will include records on the report for which the selected field is empty. In the example below, the report will include Employees for which no hired date has been entered in the hired date field. When using ‘Is NULL’, the Value field must be left blank.
Field
Operator
Pjemploy.date_hired
Is NULL
Value
Boolean And
Page
9
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module Is not NULL ‘Is not NULL’ works the opposite of the ‘Is NULL’ operator. It is used to include items on a report where a specified field has been filled in. In the example below, the report will include Employees for which a hired date has been entered in the hired date field.
Field
Operator
Pjemploy.date_hired
Is not NULL
Value
Boolean And
Less than The ‘Less than’ operator is the opposite of the ‘Greater than’ operator, and is used to select records for which a specified field is less than the amount in the Value field. In the example below, an Employee report will include hired dates less than 3/24/00. Any hired dates of 3/24/00 or later will not be included on the report.
Field
Operator
Value
Boolean
Pjemploy.date_hired
Less than
3/24/00
And
Less than or equal to The ‘Less than or equal to’ operator works the same as the ‘Less than’ operator, but will also include records with a value equal to the amount in the Value field. In the example below, an Employee report will include hired dates of 3/24/00 or less.
Field
Operator
Value
Boolean
Pjemploy.date_hired
Less than or equal to
3/24/00
And
Not between The ‘Not between’ operator is used to print a report for values outside a particular range. For example, if you needed a report showing Employees with a hired date less than 3/24/00 or more than 4/24/00, the following statement would be used.
Field
Operator
Value
Boolean
Pjemploy.date_hired
Not between
3/24/00,4/24/00
And
Note When using Not Between, the report does not include records matching the beginning and ending values entered (i.e. Employee’s with hired dates of 3/24/00 or 4/24/00 will not be included in the report).
10
Page
Sort and Select Statements Not contains The ‘Not contains’ operator is used to exclude data from a report with broad specifications. For example, if you wanted to exclude all the “Environmental” jobs from the Project List (PA.170.00), the following statement could be used. Note that any job with “Environmental” in the description will be excluded from the report.
Field
Operator
Value
Boolean
Pjproj.project_desc
Not contains
‘Environmental’
And
Not equal The ‘Not equal’ operator is used to exclude specific data from a report. In the example below, the report will be printed excluding Employees where the hired date is 1/1/00.
Field
Operator
Value
Boolean
Pjemploy.date_hired
Not equal
1/1/00
And
Not in The ‘Not in’ operator works similarly to the ‘Not equal’ operator, but allows you to enter multiple items in the Value field. In the example below, the report will be printed to exclude Employees where the hired date is 1/1/00 or 12/30/00.
Field
Operator
Value
Boolean
Pjemploy.date_hired
Not in
1/1/00,12/30/00
And
Value The Value field is used to enter data that Microsoft Dynamics™ SL should use to compare with when selecting records to be included in the report. The previous section included many examples for the Value field. However, there are additional functions available. Note The Value field is limited to seventy (70) characters.
Page
11
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
FIGURE 1-3: SELECT TAB Note The Possible Values section on Page 21 contains helpful information about values for True and False fields, Active and Inactive fields, checkboxes, etc.
Wildcards Microsoft Dynamics™ SL supports the use of wildcards in the value field of select statements. In the following example, the report would include all Employees where the Employee IDs begins with ‘E012’ (i.e. E01244, E01245, E01246 etc.)
Field
Operator
Value
Boolean
Pjemploy.employee
Equal
E012??
And
A question mark ( ? ) is used when one character is being replaced. In the above example, Employees with a Employee ID of ‘E012445’ would not be included in the report. When multiple characters need to be looked at, an asterisk (*) is used. By changing the value in the previous example to the one below, Microsoft Dynamics™ SL would return all transactions with Employee IDs starting with ‘E01244’, regardless of the Employee ID length.
Field
Operator
Value
Boolean
Pjemploy.employee
Equal
E012*
And
12
Page
Sort and Select Statements Date Values When using Date Values, you need to include the forward slashes in the date. Quotes are not necessary. The month and day may be entered with or without leading zeroes, and the year may be entered with 2 or 4-characters.
Field
Operator
Value
Boolean
Pjemploy.date_hired Pjemploy.date_hired
Greater than Less than
01/30/2000 2/7/00
And And
Dashes and Other Special Characters Many fields in Microsoft Dynamics™ SL may contain dashes or other special characters that require special consideration when entering values for Sort/Select statements. Sometimes the values must be entered with the special characters and other times they do not. The governing factor is whether the special characters must be entered during regular data entry. For example, if you have subaccounts which have been set up with multiple segments, the segments may be separated with dashes or other characters. During data entry, the system fills in the separator for you. When making an entry for subaccount 05-25, you need only type 0525 in the data entry screen. In this case, if you want to use subaccount 05-25 as a value for a Select statement, the entry will be 0525, without the dash. In other instances, the special characters may be required. For example, it is possible to set up Employee IDs with only one segment and then use dashes in the ID field when creating Employee IDs. In this case, since the dash is not a separator between segments but is instead a part of the ID, the dash must be entered both during data entry and when used in a Select statement.
Page
13
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module For items such as phone numbers, zip codes and social security numbers, the system supplies the special characters during data entry, so the characters should not be entered in Select statements. (The one exception to this is date fields, discussed above.) In general, the way to test this is to open a screen where the item would be entered and determine whether the dashes or other special characters are required to be entered during data entry. If you do not have to enter them during data entry, then do not enter them in the Select statement (once again, with the exception of date fields, discussed above).
Fiscal Periods Special care must be taken when using fiscal periods in Select statements. In many of the report screens, there are fields used to specify the fiscal periods to be included in the report. The fiscal period(s) entered in the report screen will be used to select the data based on the period to post for the transaction. In the Select tab, parameters can be entered to select data based on period entered, period closed, period to post, etc. If the report screen includes fields for restricting the periods to be reported, and you enter a select statement for period to post with a different period to post, the report will be blank, as it will not be possible for any of the data to meet both criteria. For example, the Project Transactions report has a field in the report screen to specify fiscal period. If you enter 01-2001 in both fields, the report will include only transactions posted to 01-2001. If you then enter the following select statement in the Select tab, the report would be blank:
Field
Operator
Value
Boolean
Pjtran.fiscalno
Equal
200102
And
However, the following Select statement would be allowable. Although it is based on a field with a fiscal period value, it is searching for items entered in other periods.
Field
Operator
Value
Boolean
Pjtran.fiscalno
Less than
200101
And
Note that while fiscal periods are displayed in screens and reports as month and then year (i.e. 06-2001), they are stored in the data files as year, then month. Because of this, they must be entered in Select statements as shown in the examples above.
14
Page
Sort and Select Statements Boolean Often, you will need to enter more than one select statement, in order to print only the needed information on the report. The Boolean value defines how the multiple select statements are to be used together. There are two options in this field, which are described below.
FIGURE 1-4: SELECT TAB
•
AND - When AND is used for the Boolean value, each record to be included in the report must match the values for both of the select statements. In the example below, the resulting report would include an Employee with a hired date of ‘1/31/00’.
Field
Operator
Value
Boolean
Pjemploy.employee Pjemploy.date_hired
Equal Equal
E01244 1/31/00
And And
•
OR - When OR is used for the Boolean value, records will be included in the report if they meet the specifications of one of the select statements. In the example below, records will be included in the report even if the Employee does not have a hired date of ‘1/31/00’.
Field
Operator
Value
Boolean
Pjemploy.employee Pjemploy.date_hired
Equal Equal
E01244 1/31/00
And Or
Page
15
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module Boolean values can be used to create select statements with multiple rows of select statements, which allows you to narrow report results as much as needed. For example, assume you need an Employee report showing Employee ID ‘E01246’, with a hired date Equal to ‘1/31/00’, and a subaccount equal to 01000-CD-00-00-1. Printing the Employee Utilization report, using the following Select statements, would give you this information.
Field
Operator
Value
Boolean
Pjemploy.employee Pjemploy.date_hired Pjemploy.gl_subacct
Equal Equal Equal
E01244 1/31/00 01000CD00001
And And And
Sort Statements The Sort tab contains multiple rows, with five (5) fields per row. When creating Sort statements, the following five fields are used: Field, Sort Type, Sort Ascending, Page Break and Total Break. These fields are discussed below, along with examples.
FIGURE 1-5: SORT TAB
Often times a report will contain multiple sort fields. When creating a Sort statement for a report containing multiple sort fields, you can help ensure more accurate results by entering the same number of sort fields as in the original report. Sometimes this also means leaving some of the sort fields in the order they already are in the report.
16
Page
Sort and Select Statements Field The sort Field is used to enter the table and field name on which you want the report to base the sort. For example, the Employee Utilization report, Summary format, is designed to sort by Subaccount, Employee Name and ID, and Project ID. If you want to sort the report by the Supervisor, you would enter the following Sort statement.
Field
Sort Type
Sort Ascending Page Break
Total Break
Pjemploy.manager1
Group Field
Checked
Blank
Blank
FIGURE 1-6: EMPLOYEE UTILIZATION REPORT WITH DEFAULT SORT
Page
17
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
FIGURE 1-7: EMPLOYEE UTILIZATION REPORT WITH SORT ON SUPERVISOR HINT: Because the report’s first sort is Subaccount, the second sort is Employee Name and ID, and the third sort is Period Ending Date, and the fourth sort is Project ID, you want to sort on Supervisor, entering the 1st sort to remain as the Subaccount, change the 2nd sort to the Supervisor, the 3rd sort to remain as the Period Ending Date, and the fourth sort to remain as Project ID, helps ensures accurate results. Note When in the Field area, you can use Microsoft Dynamics™ SL’s F3 inquiry function to view a list of available tables and fields for the report. Sections 2 and 3 of this guide can also be used as a reference for field names.
Sort Type Sort Type identifies the type of field identified in Field. Group Field indicates that the field is a report group that allows page and total breaks. Sort Field indicates that the report data sorts by the values in the field, within existing groups. Groups are always processed first, followed by sort fields.
Sort Ascending The Sort Ascending field is used to specify whether the values will be sorted in ascending or descending order. To sort in ascending order, check the box in this field. To sort in descending order, leave the checkbox in this field blank.
Page Break Checking the box in the Page Break field will cause the report to begin a new page whenever the field in the Sort Field changes. The Page Break option is useful for reports where you want one employee per page.
18
Page
Sort and Select Statements For example, let’s say you want to print the Employee Utilization report, and you want to sort by Employee ID with a Page Break by Employee ID. By entering the following Sort statement, Microsoft Dynamics™ SL would print one Employee on the first page, the second Employee on the next page, and so on. Figure 8 displays the Employee Utilization with a Page Break by Employee ID.
Field
Sort Type
Sort Ascending Page Break
Total Break
Pjemploy.employee
Group Field
Checked
Blank
Checked
FIGURE 1-8: EMPLOYEE UTILIZATION WITH PAGE BREAK ON EMPLOYEE ID
Total Break The Total Break is used to change the total grouping of a report. There are two important factors to keep in mind when creating Total Breaks. First, the report must already have total rows included in the report. If the report is not written with a total row, the following message appears when you try to process a report with a Total Break.
FIGURE 1-9: TOTAL BREAK MESSAGE
The second thing to keep in mind is you need to specify a sort order for the field or the Total Break will not work. Usually you will specify the field for the Total Break to be the 1st sort order. For example, the Employee Utilization report currently prints documents in order by Employee Name and then by Employee ID. Figure 9 displays the Page
19
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module standard Employee Utilization report. By changing the sort to be first by Employee Id, then by Subaccount and third by Hired Date, and indicating a Total Break on both the Employee Id and Subaccount fields, the Employee Utilization will now total documents by Employee ID as displayed in Figure 10.
Sort Order
Field
Sort Ascending
Page Break
Total Break
1st 2nd 3rd
Pjemploy.employee Pjemploy.gl_subacct Pjemploy.date_hired
Checked Checked Checked
Checked Blank Blank
Checked Checked Blank
CAUTION: The Total Break does not always work properly therefore you should review the report carefully if you have selected a Total Break.
FIGURE 1-10: EMPLOYEE UTILIZATION WITH DEFAULT SORT
FIGURE 1-11: EMPLOYEE UTILIZATION WITH SORT AND TOTAL BREAK ON EMPLOYEE ID 20
Page
Sort and Select Statements
Possible Values Chart The Value that is stored in a field might not always be obvious to the end user. The following chart is intended to assist with values that are stored in some of the Accounts Payable fields. The Value/Format field is the value you would enter in the Value field of a Select statement.
Field
Value/Format
Employee Status Active Hold One Time Purge Date fields Period fields such as period-to- post, period-entered, etc. Fiscal Year fields Time Card Type All Unposted Missing Rejected In Process Completed Timesheet Posted Batch Status: On Hold Balanced (release later status) Released - Not posted to GL Released - Posted to GL Released - Doesn’t effect GL Partially Released Voided Deleted
A H O P 01/05/2000 or 01/05/00 200103 2001 A U M R I C T P H B U P C S V D
TABLE 1: TIME AND EXPENSE POSSIBLE VALUES
Microsoft Dynamics™ SL stores several fields as True/False fields using the values of ‘1’ for True and ‘0” for False. Some of these are described below: • •
When a value is a True or False value, such as the Released field, Microsoft Dynamics™ SL stores True as a ‘1’ and False as a ‘0’. If a field has a value of Yes or No, such as Approval Required in Billing Information Maintenance (BI.BMM.00), Microsoft Dynamics™ SL stores Yes with a value of ‘Y’ and No with a value of ‘N’.
Page
21
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module •
22
Page
The External Module fields in Project Maintenance (PA.PRJ.00), the value is stored as a ‘A’ if the checkbox is checked, and the value is stored as ‘I’ if the checkbox is not checked.
D ATA F I E L D I L L U S T R AT I O N S
What you will learn in this section: In this section you will learn about the fields in Time and Expense data entry and maintenance screen: their field names and the table and field where they are stored in the Microsoft Dynamics™ SL data files.
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Overview The information in this section contains a screen illustration of the data entry screens and most maintenance screens in the Time and Expense for Projects module. The information in this section can be very useful when performing Sort and Select statements, writing reports in Crystal Reports, using SQL statements to verify information, or using ODBC. Each screen is displayed with a callout box indicating the table and field name where the data is stored, as noted in the FieldName field on the Property Window of the Customization Manager module. Some data is stored in multiple tables and fields. Additional tables and fields may be noted in the callout box, however, there may still be additional tables or fields where the data is stored. Note Some fields within data entry screens and maintenance screens are calculated or temporary fields specifically for the screen, and are not fields that are actually stored in the database. These types of fields have been designated with the text ‘Calculated’ in the following data field illustrations. Because these fields are calculated or temporary, they will not appear in sort/select inquiry lists or Crystal Reports tables or field lists. Note Some fields within the data entry and maintenance screens are variable fields that are specific to a screen, and are not fields that are actually stored in the database. These types of fields have been designated with the text ‘Variable’ in the following data field illustrations. Because these fields are variable or temporary, they will not appear in sort/select inquiry lists or Crystal Reports tables or field lists. Note Some fields within the data entry and maintenance screens are related to a Code Type. Code Types are logical tables that are defined in Code Type Maintenance (PA.COT.00) and maintained in PJCODE. Values for each Code Type are entered in Code File Maintenance (PA.CFM.00) and maintained in PJCONTRL.
24
Page
Data Field Illustrations
Timecard Entry (TM.TCE.00) Pjlabhdr.employee
Pjemploy.emp_name Pjlabhdr.le_type
Pjlabdet.ld_id08
Pjlabdet.labor_class_cd Pjlabhdr.pe_date
Pjlabdet.shift
Pjemploy.emp_name
Pjlabdet.day5_hr1 Pjlabhdr.le_status
Pjlabhdr.docnbr
Pjlabdet.project
Pjlabhdr.le_ido5
Pjlabhdr.le_id06
Pjlabhdr.approver Pjlabdet.gl_acct Pjlabdet.work_type
Pjlabdet.ld_desc Pjlabdet.gl_subacct Pjlabdet.pjt_entity
Pjlabdet.union_cd
Pjlabdet.cpnyid_chrg
Pjlabdet.ld_status
Pjlabdet.ld_id10
Pjlabdet.day7_hr1
Pjlabdet.day4_hr1
Pjlabdet.day1_hr1
Pjlabdet.day6_hr1
Pjlabdet.ld_id03
Pjlabdet.total_hrs Pjpent.pjt_entity_desc
Pjlabdet.day2_hr1 Pjlabdet.work_comp_cd Pjlabdet.day3_hr1
Pjproj.project_desc Calculated
Calculated
Pjlabdet.ld_id19 Calculated
Calculated
Calculated
Calculated
Page
25
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Timecard Entry-Additional Information (TM.TCE.01) Pjlabhdr.le_id02
26
Page
Data Field Illustrations
Timecard Entry-Additional Info (TM.TCE.01) – Customization Manager Pjlabhdr.le_id01
Pjlabhdr.le_id03
Pjlabhdr.le_id05
Pjlabhdr.le_id07
Pjlabhdr.le_id04
Pjlabhdr.le_id10
Pjlabhdr.le_id08
Pjlabhdr.le_id09
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
Page
27
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Timecard with Rate/Amount Entry (TM.TEA.00) Pjlabdet.shift
Pjemploy.emp_name
Pjlabhdr.le_type
Pjlabdet.ld_id08
Pjlabdet.labor_class_cd
Pjlabdet.ld_status
Pjemploy.emp_name
Pjlabhdr.pe_date
Pjlabhdr.employee
Pjlabhdr.le_status
Pjlabhdr.docnbr Pjlabhdr.le_id05 Pjlabhdr.approver
Pjlabdet.project Pjlabhdr.le_id06 Pjlabdet.gl_acct
Pjlabdet.ld_desc
Pjlabdet.union_cd Pjlabdet.work_type Pjlabdet.earn_type_id
Pjlabdet.pjt_entity Pjlabdet.cpnyid_chrg
Pjlabdet.day7_hr1
Pjlabdet.ld_id10 Pjlabdet.ld_id03 Pjlabdet.day1_hr1
Pjlabdet.day6_hr1 Pjlabdet.total_hrs Pjlabdet.total_amount Pjpent.pjt_entity_desc
Pjlabdet.day2_hr1 Pjlabdet.work_comp_cd Pjlabdet.rate_source Pjlabdet.gl_subacct Pjlabdet.day3_hr1 Pjlabdet.ld_id06
28
Page
Calculated Calculated Pjlabdet.ld_id19 Pjproj.project_desc
Pjlabdet.ld_id07
Pjlabdet.day5_hr1
Calculated
Pjlabdet.day4_hr1
Calculated
Calculated Calculated
Data Field Illustrations
Timecard with Rate/Amount Entry-Additional Info (TM.TEA.01) Pjlabhdr.le_id02
Page
29
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Timecard with Rate/Amount Entry-Additional Info –Customization Manager Pjlabhdr.le_id01
Pjlabhdr.le_id03
Pjlabhdr.le_id04
Pjlabhdr.le_id07
Pjlabhdr.le_id05
Pjlabhdr.le_id010
Pjlabhdr.le_id08
Pjlabhdr.le_id09
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field.
30
Page
Data Field Illustrations
Project Timesheet Entry (TM.PTE.00) Pjemploy.emp_name Pjtimhdr.preparer_id
Pjproj.project_desc Pjtimhdr.multi_emp_sw
Pjtimhdr.shift
Pjpent.pjt_entity_desc
Pjtimhdr.start_time
Pjtimdet.tl_id17
Pjtimhdr.docnbr
Pjtimhdr.th_date Pjemploy.emp_name Pjtimhdr.th_status
Pjtimhdr.crew_cd
Pjtimhdr.end_time Pjtimdet.shift Pjtimdet.union_cd
Pjtimhdr.th_comment Pjtimhdr.project
Pjtimdet.work_type Pjtimdet.cet_pay_sw Pjtimdet.group_code
Pjtimdet.project Pjtimdet.pjt_entity
Pjtimdet.equip_id Pjtimdet.equip_uom Pjtimdet.equip_rate_cd
Pjtimdet.employee Pjtimdet.labor_class_cd Pjtimdet.gl_acct Pjtimdet.cpnyid_chrg
Pjtimdet.equip_units Pjtimdet.tl_id20
Pjtimdet.gl_subacct
Calculated
Pjproj.project_desc Pjpent.pjt_entity_desc Pjemploy.emp_name
Calculated
Pjequip.equip_desc
Pjtimdet.elapsed_time Pjtimdet.reg_hours Pjtimdet.end_time Pjtimdet.ot2_hrs Pjtimdet.start_time Pjtimdet.work_comp_cd Pjtimdet.ot1_hours
Calculated Calculated
Page
31
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Project Timesheet -Additional Info (TM.PTE.01) – Customization Manager Pjtimhdr.th_id01
Pjtimhdr.th_id05
Pjtimhdr.th_id04
Pjtimhdr.th_id02
Pjtimhdr.th_id03
Pjtimhdr.th_id06
Pjtimhdr.th_id10
Pjtimhdr.th_id07
Pjtimhdr.th_id09
Pjtimhdr.th_id08
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
32
Page
Data Field Illustrations
Project Timesheet-Units of Production Entry (TM.PTE.02) Pjuopdet.up_date_id
Pjuopdet.pjt_entity Pjuopdet.prod_uom
Pjuopdet.project
Pjuopdet.up_id08 Pjuopdet.up_id09 Pjuopdet.prod_units
Pjuopdet.up_id10
Pjproj.project_desc
Pjpent.pjt_entity_desc
Pjuopdet.prod_qty
Page
33
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Project Timesheet-Units of Production Entry – Customization Manager Pjuopdet.up_id04
Pjuopdet.up_id05
Pjuopdet.up_id06
Pjuopdet.up_id07 Pjuopdet.up_id01
Pjuopdet.up_id08
Pjuopdet.up_id02
Pjuopdet.up_id09 Pjuopdet.up_id10
Pjuopdet.up_id03
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
34
Page
Data Field Illustrations
Project Timesheet Entry with Rate/Amount Entry (TM.PTA.00) Pjtimhdr.preparer_id Pjtimhdr.docnbr
Pjemploy.emp_name
Pjtimhdr.shift
Pjtimhdr.multi_emp_sw
Pjpent.pjt_entity_desc Pjtimdet.shift
Pjproj.project_desc
Pjtimdet.tl_id17
Pjemploy.emp_name Pjtimhdr.th_date Pjtimhdr.start_time
Pjtimhdr.crew_cd Pjtimhdr.th_comment
Pjtimhdr.end_time
Pjtimhdr.project
Pjtimhdr.th_status
Pjtimhdr.pjt_entity
Pjtimdet.union_cd
Pjtimdet.tl_date
Pjtimdet.tl_id18
Pjtimdet.project
Pjtimdet.labor_amt
Pjtimdet.pjt_entity
Pjtimdet.equip_id
Pjtimdet.employee
Pjtimdet.equip_uom
Pjtimdet.labor_class_cd
Pjtimdet.equip_rate_cd
Pjtimdet.gl_acct
Pjtimdet.equip_units
Pjtimdet.cpnyid_chrg
Pjtimdet.tl_id20
Pjtimdet.gl_subacct
Calculated
Pjproj.project_desc
Calculated
Pjpent.pjt_entity_desc Pjtimdet.start_time Pjemploy.emp_name Pjequip.equip_desc
Pjtimdet.end_time Pjtimdet.elapsed_time Pjtimdet.reg_hours
Calculated Pjtimdet.tl_id16
Pjtimdet.work_type
Pjtimdet.labor_rate Pjtimdet.cet_pay_sw Pjtimdet.ot1_hoursPjtimdet.group_code Pjtimdet.ot2_hrs
Calculated Pjtimdet.work_comp_cd Pjtimdet.earn_type_id
Page
35
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Project Timesheet with Rate/Amount Entry – Customization Manager Pjtimhdr.th_id01
Pjtimhdr.th_id05
Pjtimhdr.th_id04
Pjtimhdr.th_id02
Pjtimhdr.th_id03
Pjtimhdr.th_id06
Pjtimhdr.th_id10
Pjtimhdr.th_id07
Pjtimhdr.th_id09
Pjtimhdr.th_id08
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
36
Page
Data Field Illustrations
Project Timesheet with Rate/Amount Entry-Units of Production (TM.PTA.02) Pjuopdet.up_date_id
Pjuopdet.pjt_entity
Pjuopdet.prod_uom
Pjuopdet.project
Pjuopdet.up_id08 Pjuopdet.up_id09 Pjuopdet.prod_units
Pjuopdet.up_id10
Pjproj.project_desc
Pjpent.pjt_entity_desc
Pjuopdet.prod_qty
Page
37
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Proj Timesheet with Rate/Amt Entry (TM.PTA.02)–Customization Manager Pjuopdet.up_id04
Pjuopdet.up_id05 Pjuopdet.up_id06
Pjuopdet.up_id07
Pjuopdet.up_id01
Pjuopdet.up_id08
Pjuopdet.up_id02 Pjuopdet.up_id03
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
38
Page
Data Field Illustrations
Timecard Correction (TM.COR.00) Pjlabhdr.employee
Pjemploy.emp_name
Pjlabhdr.docnbr
Pjlabhdr.le_type
Pjlabhdr.pe_date
Pjlabhdr.le_status
Pjlabhdr.docnbr
Page
39
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Travel & Expense Report Entry (TM.ENT.00) Pjexphdr.status_2
Pjexpdet.desc_detail
Pjemploy.emp_name
Pjexphdr.tripnbr
Pjexphdr.desc_hdr Pjexphdr.advance_amt
Pjexphdr.employee Pjexphdr.report_date Pjexphdr.approver Pjexphdr.docnbr
Pemploy.emp_name
Pjexpdet.exp_date
Pjexphdr.status_1
Pjexpdet.exp_type
Pjexpdet.td_id01
Pjexpdet.project
Pjexpdet.CpnyId_chrg
Pjexpdet.pjt_entity Pjexpdet.units Pjexpdet.gl_subacct Pjexpdet.rate Pjexpdet.payment_cd Pjexpdet.amt_employ Pjexpdet.amt_company
Pjexpdet.status
40
Page
Pjemploy.em_id07
Calculated
Calculated
Calculated
Data Field Illustrations
Travel & Expense Report Entry (TM.ENT.00) – Customization Manager
Pjexpdet.td_id02 Pjexpdet.td_id03 Pjexpdet.td_id04 Pjexpdet.td_id05 Pjexpdet.td_id10
Pjexpdet.td_id06 Pjexpdet.td_id07 Pjexpdet.td_id08
Pjexpdet.td_id09
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
Page
41
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Travel & Expense Report Entry-Additional Info (TM.ENT.01) Pjexphdr.te_id01
Pjexphdr.te_id03
42
Page
Pjexphdr.te_id02
Data Field Illustrations
Travel & Expense Report Entry-Additional Info– Customization Manager Pjexphdr.te_id06
Pjexphdr.te_id04
Pjexphdr.te_id05
Pjexphdr.te_id10
Pjexphdr.te_id07
Pjexphdr.te_id09
Pjexphdr.te_id08
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
Page
43
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Expense Report Review & Approval (TM.ERA.00) Pjexphdr.employee
Pjexphdr.report_date
Pjemploy.emp_name
Pjexphdr.te_id02
Pjexphdr.status_1 Pjexphdr.te_id03
Pjexphdr.status_2
Pjexphdr.desc_hdr
Pjexphdr.docnbr
44
Page
Pjexphdr.tripnbr
Pjexphdr.advance_amt
Calculated
Calculated
Data Field Illustrations
Company Expense Reconciliation (TM.CER.00) Variable
Variable
Pjexpdet.exp_date
Pjexptyp.desc_exp Pjexpdet.amt_employ
Pjexpdet.amt_company
Pjexpdet.desc_detail
Pjexpdet.td_id03 Pjemploy.emp_name
Calculated
Calculated
Calculated
Page
45
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Employee Position/Rate Maintenance (TM.EPJ.00) Pjemploy.employee
Pjemppjt.effect_date
Pjemppjt.labor_class_cd
Pjemppjt.project
Pjemppjt.ep_id05
Pjemppjt.labor_rate
Pjemppjt.ep_id06
Pjemppjt.ep_id03
46
Page
Pjemppjt.ep_id04
Pjemppjt.ep_id02
Pjemppjt.ep_id01
Pjemploy.emp_name
Data Field Illustrations
Employee Position/Rate Maintenance (TM.EPJ.00)–Customization Manager
Pjemppjt.ep_id10
Pjemppjt.ep_id09
Pjemppjt.ep_id08
Pjemppjt.ep_id07
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
Page
47
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Expense Type Maintenance (TM.ETM.00) Pjexptyp.exp_type
Pjexptyp.units_flag
48
Page
Pjexptyp.desc_exp
Pjexptyp.default_rate
Pjexptyp.gl_acct
Data Field Illustrations
Prevailing Wage Maintenance (TM.PRE.00) Pjcode.code_value
Pjcode.code_value_desc
Pjcode.data3
Pjcode.data1
Pjwagepr.pw_id16
Pjwagepr.labor_class_cd
Pjwagepr.group_code
Pjwagepr.labor_rate
Pjwagepr.pw_id01
Page
49
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Prevailing Wage Maintenance (TM.PRE.00) – Customization Manager Pjwagepr.pw_id10 Pjwagepr.pw_id11 Pjwagepr.pw_id12 Pjwagepr.pw_id13 Pjwagepr.pw_id14
Pjwagepr.pw_id02
Pjwagepr.pw_id15
Pjwagepr.pw_id03
Pjwagepr.pw_id17
Pjwagepr.pw_id05
Pjwagepr.pw_id18 Pjwagepr.pw_id04 Pjwagepr.pw_id09
Pjwagepr.pw_id19 Pjwagepr.pw_id08
Pjwagepr.pw_id07
Pjwagepr.pw_id06
Pjwagepr.pw_id20
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
50
Page
Data Field Illustrations
Union Rate Maintenance (TM.URE.00) Pjwageun.labor_class_cd
Pjwageun.work_type
Pjcode.code_value
Pjcode.code_value_desc
Pjwageun.effect_date
Pjwageun.labor_rate
Pjwageun.un_id01
Page
51
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Union Rate Maintenance (TM.URE.00) - Customization Manager Pjwageun.un_id13 Pjwageun.un_id13 Pjwageun.un_id02
Pjwageun.un_id13
Pjwageun.un_id03 Pjwageun.un_id14 Pjwageun.un_id04 Pjwageun.un_id05
Pjwageun.un_id15
Pjwageun.un_id06
Pjwageun.un_id16
Pjwageun.un_id07 Pjwageun.un_id17 Pjwageun.un_id08 Pjwageun.un_id09
Pjwageun.un_id18 Pjwageun.un_id10
Pjwageun.un_id20
Pjwageun.un_id19
Note Project Controller provides additional fields to allow storage of user-defined data. These fields are called ID fields and are very similar to the standard Microsoft Dynamics™ SL User1 through User8 fields. The major difference between the ID fields and the User fields is that the ID fields are activated and added to screens without the Customization Manager module. ID Maintenance (PA.IDM.00) determines whether the field is visible, the caption associated with the field, and how validation is performed on the data entered into the field. Note Some of the ID fields are reserved for system use and may not be defined or used by the user for any other purpose. In some cases, reserved ID fields already have specific definitions, which are noted. In other cases, the ID fields are reserved for future use.
52
Page
Data Field Illustrations
Week Maintenance (TM.WEM.00) Pjweek.we_date
Pjweek.we_num
Pjweek.comment
Pjweek.period_num
Pjweek.fiscalno
Page
53
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Workers’ Compensation Maintenance (TM.WKM.00) Pjcode.code_value
Pjcode.data2
54
Page
Pjcode.code_value_desc
Pjcode.data1
R E P O R T I N F O R M AT I O N
What you will learn in this section: In this section you will learn the pertinent information about each report in the Time and Expense module: master table, sort order, report name as stored in Crystal, sort/select field examples.
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module
Overview The information in this section lists each report in the Project Controller module, along with the following information: Report Formats and Report Names This section lists the name of the report format from the Report Format field of the report screen. Next to the Report Format is the name of the Report as it is stored in Crystal Reports. Master Table Often times when you are looking at the inquiry list within the sort or select field of a report, you will see the same field in more than one table. For example, the project ID field (project) is in several tables. If you are entering a sort or select statement based on an field which is contained in multiple tables, your report will usually process faster if you use the field from the master table. This section gives you the master table (or view table) that is used in the report . Note A View is a ‘virtual table’ whose contents are defined by a SQL query statement. A View appears like a table with columns and rows of data, but does not exist in the database as a stored set of data values. All of the reports that are written with a View, rather than a master table, begin with the letter V. For purposes of sort and select statements, a View is just like any other table in a report and can be accessed in sort and select statements.
Default Sort Fields This section lists the sort fields, in the same order as they are in the report. If different fields are selected when sorting a report, the totals on the report may no longer be accurate. When running a report with a Sort statement, use this section of the manual to see which fields the report currently uses as sort fields to help you ensure your sort field is a good choice. Common Sort Examples At the bottom of each report there is a list of some of the common sort and select fields you might use in the report, along with the possible values for the field listed. Note Special care must be used when entering IDs which may have more than one segment, such as Subaccount Numbers, as well as when entering dates and fiscal periods. If you experience problems with these items in Sort/Select statements, please review the sections on Date Values, Dashes and Other Special Characters, or Fiscal Periods.
56
Page
Report Information
Time Card Report - Screen TM.010.00 Report Formats
Report Name
Master Table
Default Sort Fields
Time Card Report
TM010
Pjlabhdr
Pjemploy.emp_name Pjlabhdr.pe_date Pjlabhdr.docnbr
The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Employee ID Employee Name Week (Period) Ending Date Document Number
Pjlabdet.project Pjlabdet.pjt_entity Pjlabhdr.employee Pjemploy.emp_name Pjlabhdr.pe_date Pjlabhdr.docnbr
Valid Project ID Valid Task ID Valid Employee ID Valid Employee Name 09/27/00 or 09/27/2000 Valid Document Number
Labor Expense Posting Rpt/Recap - Screen TM.020.00 Report Formats
Report Name
Master Table
Default Sort Fields
Labor Expense Posting Report
TM020
Pjlabdis
Labor Expense Posting Recap
TM020
Pjlabdis
Pjlabdis.cpnyid_chrg Pjlabdis.gl_subacct Pjlabdis.gl_acct Pjlabdis.project Pjlabdis.pjt_entity Pjemploy.emp_name Pjlabdis.employee Pjlabdis.pe_date Pjlabdis.cpnyid_chrg Pjlabdis.gl_subacct Pjlabdis.gl_acct Pjlabdis.project Pjlabdis.pjt_entity Pjemploy.emp_name Pjlabdis.employee Pjlabdis.pe_date
Page
57
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Employee ID Employee Name Week (Period) Ending Date Company Charged Account Number Subaccount Number Account Category
Pjlabdis.project Pjlabdis.pjt_entity Pjlabdis.employee Pjemploy.emp_name Pjlabdis.pe_date Pjlabdis.cpnyid_chrg Pjlabdis.gl_acct Pjlabdis.gl_subacct Pjlabdis.acct
Valid Project ID Valid Task ID Valid Employee ID Valid Employee Name 09/27/00 or 09/27/2000 Valid Company Valid Account Number Valid Subaccount Number Valid Account Category
Labor Distribution by Chg/Home Sub - Screen TM.030.00 Report Formats
Report Name
Master Table
Default Sort Fields
Labor Distribution By Chg Sub
TM030
Pjlabdis
Labor Distribution By Home Sub
TM030
Pjlabdis
@wSortcpnyid* @wSortsubacct* pjlabdis.gl_acct pjlabdis.project Pjlabdis.pjt_entity Pjemploy.emp_name Pjlabdis.employee Pjlabdis.pe_date @wSortcpnyid @wSortsubacct pjlabdis.gl_acct pjlabdis.project Pjlabdis.pjt_entity Pjemploy.emp_name Pjlabdis.employee Pjlabdis.pe_date
NOTE: The @ symbol indicates a formula field created in the Crystal Report Writer. You cannot use formula fields in Sort or Select statements.
The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Employee ID Employee Name Account Number Subaccount Number Account Category
Pjlabdis.project Pjlabdis.pjt_entity Pjlabdis.employee Pjemploy.emp_name Pjlabdis.gl_acct Pjlabdis.gl_subacct Pjlabdis.acct
Valid Project ID Valid Task ID Valid Employee ID Valid Employee Name Valid Account Number Valid Subaccount Number Valid Account Category
58
Page
Report Information
Employee Utilization - Screen TM.040.00 Report Formats
Report Name
Master Table
Default Sort Fields
0 Employee Utilization
TM040
Pjlabdis
pjlabdis.home_subacct @employeenameandnum pjlabdis.pe_date pjlabdis.project
NOTE: The @ symbol indicates a formula field created in the Crystal Report Writer. You cannot use formula fields in Sort or Select statements.
The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Employee ID Employee Name Account Number Subaccount Number Account Category
Pjlabdis.project Pjlabdis.pjt_entity Pjlabdis.employee Pjemploy.emp_name Pjlabdis.gl_acct Pjlabdis.gl_subacct Pjlabdis.acct
Valid Project ID Valid Task ID Valid Employee ID Valid Employee Name Valid Account Number Valid Subaccount Number Valid Account Category
Employee Labor Distribution by Chg/Home Sub - Screen TM.050.00 Report Formats
Report Name
Master Table
Default Sort Fields
Employee Labor Dist By Chg Sub
TM050
Pjlabdis
Employee Labor Dist By Home Sub
TM050
Pjlabdis
@wSortcpnyid @wSortsubacct Pjemploy.emp_name Pjlabdis.employee pjlabdis.gl_acct pjlabdis.project Pjlabdis.pjt_entity pjlabdis.pe_date @wSortcpnyid @wSortsubacct Pjemploy.emp_name Pjlabdis.employee pjlabdis.gl_acct pjlabdis.project Pjlabdis.pjt_entity pjlabdis.pe_date
NOTE: The @ symbol indicates a formula field created in the Crystal Report Writer. You cannot use formula fields in Sort or Select statements.
Page
59
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Employee ID Employee Name Account Category Account Number Subaccount Number
Pjlabdis.project Pjlabdis.pjt_entity Pjlabdis.employee Pjemploy.emp_name Pjlabdis.acct Pjlabdis.gl_acct Pjlabdis.gl_subacct
Valid Project ID Valid Task ID Valid Employee ID Valid Employee Name Valid Account Category Valid Account Number Valid Subaccount Number
Certified Payroll Rpt - Screen TM.060.00 Report Formats
Report Name
Master Table
Default Sort Fields
Certified Payroll Report
TM060
Pjlabdis
Pjlabdis.pe_date Pjlabdet.project Pjlabdis.employee Pjlabdis.labor_class_cd Pjlabdis.dl_id03 Pjlabdis.docnbr Pjlabdis.linenbr Pjlabdis.dl_id08 Pjlabdis.hrs_type Pjlabdis.status_2
The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Period End Date Labor Class Employee ID Employee Name
Pjlabdis.project Pjlabdis.pjt_entity Pjlabdis.pe_date Pjlabdis.labor_class_cd Pjlabdis.employee Pjemploy.emp_name
Valid Project ID Valid Task ID 01/01/2000 or 01/01/00 Valid Labor Class Valid Employee ID Valid Employee Name
60
Page
Report Information
Project Timesheet Report - Screen TM.080.00 Report Formats
Report Name
Master Table
Default Sort Fields
Project Timesheet Labor Report
TM080L
Pjtimhdr
Project Timesheet Equipment Report Project Timesheet UOP Report
TM080E
Pjtimhdr
Pjtimhdr.docnbr @employeenameandnum Pjtimdet.tl_date Pjtimdet.project Pjtimdet.pjt_entity Pjtimdet.labor_class_cd Pjtimdet.linenbr Pjtimhdr.docnbr
TM080U
Pjtimhdr
Pjtimhdr.docnbr
NOTE: The @ symbol indicates a formula field created in the Crystal Report Writer. You cannot use formula fields in Sort or Select statements.
The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Document Number Project ID Project ID Employee ID Labor Class Timesheet Date
Pjtimhdr.docnbr Pjtimdet.project Pjtimdet.pjt_entity Pjtimdet.employee Pjlabdis.labor_class_cd Pjtimdet.tl_date
Valid Document Number Valid Project ID Valid Task ID Valid Employee ID Valid Labor Class 01/01/2000 or 01/01/00
GL Labor Reconciliation Report - Screen TM.100.00 Report Formats
Report Name
Master Table
Default Sort Fields
GL Labor Recon Detail Report
TM100D
Pjlabdis
GL Labor Recon Summary Report
TM100S
Pjlabdis
pjlabdis.gl_acct pjlabdis.gl_subacct pjlabdis.dl_id15 pjlabdis.employee pjlabdis.gl_acct pjlabdis.gl_subacct pjlabdis.dl_id15 pjlabdis.employee
Page
61
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Project ID Task ID Period End Date Labor Class Employee ID Employee Name Account Category Account Number Subaccount Number
Pjlabdis.project Pjlabdis.pjt_entity Pjlabdis.pe_date Pjlabdis.labor_class_cd Pjlabdis.employee Pjemploy.emp_name Pjlabdis.acct Pjlabdis.gl_acct Pjlabdis.gl_subacct
Valid Project ID Valid Task ID 01/01/2000 or 01/01/00 Valid Labor Class Valid Employee ID Valid Employee Name Valid Account Category Valid Account Number Valid Subaccount Number
Travel and Expense Report - Screen TM.410.00 Report Formats
Report Name
Master Table
Default Sort Fields
Travel & Expense Report
TM410
Pjexphdr
Pjexphdr.docnbr
The following table shows fields typically used for Sort/Select parameters for this report:
Sort/Select Item
Field Name
Format / Possible Values
Document Number Project ID Task ID Employee ID Employee Name
Pjexphdr.docnbr Pjtimdet.project Pjtimdet.pjt_entity Pjtimdet.employee Pjemploy.emp_name
Valid Document Number Valid Project ID Valid Task ID Valid Employee ID Valid Employee Name
62
Page
INDEX
Report Assistant for Microsoft Dynamics™ SL – Time and Expense for Projects Module No entries
64
Page
