Welcome to the Advantage Custom Reporting Toolkit

The Custom Reporting Toolkit makes it possible for you to access and create reports from the information in your Advantage database.

Although it is helpful to be familiar with Microsoft Access, it is not required. Even beginner users will be able to develop simple queries and reports. However, if you are new to Microsoft Access, you may want to attend our Ad-Hoc Reporting with Advantage seminar to help familiarize yourself with this new and more flexible reporting environment. Visit our web site at http://www.deltek.com/ to obtain a training schedule.

Because the Advantage Custom Reporting Toolkit interfaces with both Microsoft Access and Microsoft Excel, support for this product has been limited to general installation questions.

All other questions about use of the Advantage Custom Reporting Toolkit or Microsoft Access should be directed to the Business Intelligence Group at 888/380-6700. This assistance is provided on a time and materials basis.

What do you want to do?

Install the Custom Reporting Toolkit

Create and use queries

Select data

Create and use reports


Ad-Hoc Reporting with Advantage Seminar

If you are unfamiliar with Microsoft Access you may want to attend our Ad-Hoc Reporting with Advantage seminar or attend a general Microsoft Access class. The seminar is a two-day course designed to provide you with the knowledge necessary to produce custom Advantage reports and invoices using Microsoft Access.

At the completion of this course, you will be able to:

•	Identify which Advantage tables contain the most useful data for reporting purposes, and understand the data stored within these tables.

•	Identify how different Advantage tables relate to each other; that is, identify which columns need to be joined to produce accurate reports.

•	Construct basic and complex queries using Microsoft Access to manipulate and report on Advantage data.

•	Design Advantage reports and Billing Templates using the Custom Reporting Toolkit, Microsoft Access, and the Billing Template Editor.


Overview of Installing the Advantage Custom Reporting Toolkit to a Network

The installation program installs a Microsoft Access 2002/2003 (AdvRTK80.mdb) version of the Custom Reporting Toolkit when you install the Advantage software. By default, the software is installed locally, in the directory where you install the Advantage software.

If...	Then...
You are a single-user client and your Advantage database is on your local drive.	You should keep the database in the local drive.
You are a single or multi-user client, your Advantage database is on the network, and you want the database local for security reasons.	You should keep the database in the local drive.
You are a single-user client and you want Project Managers to be able to run reports based on Advantage data in your database.	You should move the database to a network drive.
You are a multi-user client and you want multiple employees to have access to, create, and run custom reports	You should move the database to a network drive.


Installing the Advantage Custom Reporting Toolkit to a Network

To install the toolkit on the network:

1.	Open Windows Explorer or File Manager.

2.	Create a new directory on the network in which to copy the Custom Reporting Toolkit. You only need to perform this step if your database is not on the network. If your database is on the network, you should copy the file into the folder containing your database.

3.	Access the directory in which you installed the Advantage software.

4.	Select the file named AdvRTK80.mdb (for Microsoft Access 2002/2003). Do not copy all of these files to the network directory.

5.	Select Edit, Copy from the Windows Explorer or File Manager main menu.

6.	Move the file to the directory created in step 2.

7.	Open Advantage. Select Programs, Advantage [version], Advantage from the Start menu.

8.	Select Utilities, Security Setup from the Advantage main menu. The system displays the Security Maintenance form.

9.	Click on the Privileges tab.

10.	Ensure that the Custom reporting toolkit field is set to Full Access for those security workgroups that should have access to the Custom Reporting Toolkit.

11.	Exit Advantage.

12.	Double-click on the file named AdvRTK80.mdb in the network directory in which you copied the file. The system displays the Database Wizard form. If this form does not appear automatically, click on the Database Wizard button.

13.	Select the first option to Connect to an existing Advantage database and click on the Next button.

14.	Enter the path of your Advantage database in the Link File (AdvLink.mdb) field and click on the Link button. The system links the Custom Reporting Toolkit to your Advantage database. You can now use the Custom Reporting Toolkit.

 Notes


Creating a New Query Using the Toolkit

The first step in using the toolkit is to create a new query. Queries let you view and analyze data. Queries are the main source of data for reports.

You create queries by selecting rows/columns of data from your Advantage tables. When doing this, you may want to refer to the Advantage Data Dictionary or the Field Definitions included later in this file. These sources of information are helpful when determining the data your query should collect.

Once you develop a query, you have the following options:

•	Preview the data in datasheet view. This does not require any knowledge of Microsoft Access or Excel, and is recommended if you want to view information quickly, without any need for formatting.

•	Export and format the data in Microsoft Excel. For users that do not have a strong knowledge of Microsoft Access, we recommend this option.

•	Format the data in Microsoft Access. To move beyond the simple reports generated for you by the system, you will need to have knowledge of Microsoft Access.

You do not have to create a custom report in Microsoft Access using your new query. Remember, queries are just a means to collect the data. The report is a more sophisticated means to view the information, if desired.

Queries are the most important aspect of the toolkit. The queries you create are the backbone of all the custom data you store and export to Microsoft Access or Excel. Please contact the Business Intelligence Group if you are having problems with your queries or reports.

What do you want to do?

Review the general steps to create a new query


General Steps to Create a New Query

To create a new query:

1.	Plan the information you want to retrieve so that you do not retrieve unnecessary information.

2.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form. Perform the following steps:

3.	Select the data category from which you want to create a new query from the Tables section of this form.

4.	Select the fields to include in the query.

5.	Specify the order in which the selected fields will appear when you run the query.

6.	Set up any selection criteria that will limit the amount of data retrieved by the query using the Selection button.

7.	Review the new query, add or remove desired fields, and finalize the new query.

 Notes

 Related Topics


Using a Supplied Data Category to Create a New Query

To create your custom query, you can select from a list of defined data categories, containing fields in your Advantage database. We supply these data categories, listed below, to group information by type for easier access. Using data categories helps ensure that report information is related, focused, and organized.

Data Category for Field Definitions	Sample Query Instructions
General Ledger	Creating a sample general ledger query
Employee	Creating a sample employee query
Project Summary	Creating a sample project summary query
Project Labor	Creating a sample project labor query
Unposted Timesheets	Creating a sample unposted timesheets query.
Project Expenses	Creating a sample project expenses query
Clients	Creating a sample clients query
Accounts Receivable	Creating a sample accounts receivable query
Vendors	Creating a sample vendors query
Accounts Payable	Creating a sample accounts payable query
Payroll	Creating a sample payroll query
 Note

 Related Topics


General Ledger Data Category Field Definitions

The following describes the fields associated with the Chart of Accounts and Expense Detail tables. The system selects data in the following ways:

•	If you select fields from the Chart of Accounts table, the system retrieves rows from this table only.

•	If you select fields from the Expense Detail table, the system retrieves rows from this table only.

•	If you select fields from both of these tables, the system retrieves rows from the Expense Detail table along with the selected fields from the associated row in the Chart of Accounts table.

If you select the Consolidate data by selected fields option, the rows of data retrieved will be consolidated by the combination of selected fields. See also Consolidating Data by the Selected Fields.

Field	Description	Table	Field Name
Account Number	The account number for this transaction.	ED	edAccount
Account Name	The account name as defined in Account Table Maintenance.	CA	caName
Sort/Select 1	The value entered as the first select code in Account Table Maintenance.	CA	caSelect1
Sort/Select 2	The value entered as the second select code in Account Table Maintenance.	CA	caSelect2
Sort/Select 3	The value entered as the third select code in Account Table Maintenance.	CA	caSelect3
Profit Center	The profit center for the general ledger transaction as determined by the project entered in data entry.	ED	edProfCtr
Period	The period in which the general ledger transaction was posted (for example, 199805).	ED	edPeriod
Posting Seq	The posting sequence number for the general ledger transaction.	ED	edPostSeq
Trans Type	The transaction type for the general ledger transaction.	ED	edTransType
Sub Type	The transaction type for the general ledger transaction.	ED	edSubType
Reference Number	The reference number assigned to the general ledger transaction during data entry.	ED	edRefNo
Date	The date entered for the general ledger transaction during data entry (for example, 01/01/98).	ED	edDate
Bank Code	The bank code (if any) entered for the general ledger transaction during data entry.	ED	edBankCode
Project	The project entered for the general ledger transaction during data entry.	ED	edProject
Task	The task (if any) entered for the general ledger transaction during data entry.	ED	edTask
Description 1	The first description entered for the general ledger transaction during data entry.	ED	edDesc1
Description 2	The second description entered for the general ledger transaction during data entry.	ED	edDesc2
Amount	The amount entered for the general ledger transaction during data entry.	ED	edAmount
Cash-basis Amount	The cash-basis amount for the general ledger transaction.	ED	edCBAmount
Auto Entry	Y or N displays to indicate whether the transaction was the result of an implicit entry?	ED	edAutoEntry


Creating a Sample Query Based on the General Ledger Data Category

Objective:

Create a new query, General Ledger Sample Query that collects the Account Number, Account Name, and Amount for all accounts in your Advantage database.

To create the General Ledger Sample Query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the General Ledger button in the Tables section of the Query Builder form. A list of general ledger-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Account Number

•	Account Name

•	Amount

The system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter General Ledger Sample Query as the name of the query, and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a report based on the query created, as desired.

 Related Topics


Creating a Sample Report Based on a General Ledger Data Category Query

To create a report based on the General Ledger Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample general ledger query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Account Balances Report as the name of the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Employee Data Category Field Definitions

The following describes the fields associated with the Employee, Time Analysis Setup, and Labor Detail tables. The system selects data in the following ways:

•	If you select fields from the Employee table, the system retrieves rows from this table only.

•	If you select fields from the Labor Detail table, the system retrieves rows from this table only.

•	If you select fields from the Employee and Labor Detail (or any other combination) of these tables, the system retrieves rows from the Labor Detail table along with the selected fields from the associated row in the Employee table.

If you select the Consolidate data by selected fields option, the rows of data retrieved will be consolidated by the combination of selected fields. See also Consolidating Data by the Selected Fields.

Field	Description	Table	Field Name
Employee Number	The employee number as defined in Employee Table Maintenance.	EM	emEmployee
Last Name	The employee’s last name as defined in Employee Table Maintenance.	EM	emLastName
First Name	The employee’s first name as defined in Employee Table Maintenance.	EM	emFirstName
Job Cost Rate	The employee’s hourly job cost rate as defined in Employee Table Maintenance. (Not to be confused with the payroll rate.)	EM	emJobCostRate
Job Cost Type	The employee’s job cost type as defined in Employee Table Maintenance.	EM	emJobCostType
Overtime Pct	The employee’s overtime percentage as defined in Employee Table Maintenance.	EM	emJCOvtPct
Target Ratio	The employee’s target ratio as defined in Employee Table Maintenance.	EM	emTargetRatio
Hours Per Day	The employee’s typical hours per day as defined in Employee Table Maintenance.	EM	emHoursPerDay
Hire Date	The employee’s hire date as defined in Employee Table Maintenance.	EM	emHireDate
Raise Date	The employee’s raise date as defined in Employee Table Maintenance.	EM	emRaiseDate
Termination Date	The date on which the employee was terminated.	EM	emTerminationDate
Status	The employee’s status as defined in Employee Table Maintenance.	EM	emStatus
Employee Type	The employee’s type as defined in Employee Table Maintenance.	EM	emType
Profit Center	The employee’s profit center as defined in Employee Table Maintenance.	EM	emProfCtr
Billing Category	The employee’s billing category as defined in Employee Table Maintenance.	EM	emBillingCategory
SSN	The employee’s social security number as defined in Employee Table Maintenance.	EM	emSSN
Address Line 1	The first line of the employee’s street address as defined in Employee Table Maintenance.	EM	emAddress1
Address Line 2	The second line of the employee’s street address as defined in Employee Table Maintenance.	EM	emAddress2
Address Line 3	The third line of the employee’s street address as defined in Employee Table Maintenance.	EM	emAddress3
City	The employee’s city as defined in Employee Table Maintenance.	EM	emCity
State	The employee’s state as defined in Employee Table Maintenance.	EM	emState
ZIP	The employee’s zip code as defined in Employee Table Maintenance.	EM	emZIP
Country	The employee’s country as defined in Employee Table Maintenance.	EM	emCountry
Phone	The employee’s phone number as defined in Employee Table Maintenance.	EM	emPhone
FAX	The employee’s fax number as defined in Employee Table Maintenance.	EM	emFAX
Email	The employee’s email address as defined in Employee Table Maintenance.	EM	emEMail
Sort/Select 1 <variable name>	The value entered as the first select code (if in use) in Employee Table Maintenance.	EM	emSelect1
Sort/Select 2 <variable name>	The value entered as the second select code (if in use) in Employee Table Maintenance.	EM	emSelect2
Sort/Select 3 <variable name>	The value entered as the third select code (if in use) in Employee Table Maintenance.	EM	emSelect3
Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) in Employee Table Maintenance.	EM	emSelect4
Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) in Employee Table Maintenance.	EM	emSelect5
Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) in Employee Table Maintenance.	EM	emSelect6
Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) in Employee Table Maintenance.	EM	emSelect7
Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) in Employee Table Maintenance.	EM	emSelect8
Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) in Employee Table Maintenance.	EM	emSelect9
Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) in Employee Table Maintenance.	EM	emSelect10
Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) in Employee Table Maintenance.	EM	emSelect11
Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) in Employee Table Maintenance.	EM	emSelect12
Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) in Employee Table Maintenance.	EM	emSelect13
Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) in Employee Table Maintenance.	EM	emSelect14
Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) in Employee Table Maintenance.	EM	emSelect15
Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) in Employee Table Maintenance.	EM	emSelect16
Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) in Employee Table Maintenance.	EM	emSelect17
Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) in Employee Table Maintenance.	EM	emSelect18
Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) in Employee Table Maintenance.	EM	emSelect19
Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) in Employee Table Maintenance.	EM	emSelect20
Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) in Employee Table Maintenance.	EM	emSelect21
Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) in Employee Table Maintenance.	EM	emSelect22
Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) in Employee Table Maintenance.	EM	emSelect23
Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) in Employee Table Maintenance.	EM	emSelect24
Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) in Employee Table Maintenance.	EM	emSelect25
Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) in Employee Table Maintenance.	EM	emSelect26
Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) in Employee Table Maintenance.	EM	emSelect27
Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) in Employee Table Maintenance.	EM	emSelect28
Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) in Employee Table Maintenance.	EM	emSelect29
Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) in Employee Table Maintenance.	EM	emSelect30
Memo	Any additional notes entered for the employee.	EM	emMemo
Project	The project to which the employee posted labor as entered in data entry.	LD	ldProject
Task	The task (if any) to which the employee posted labor as entered in data entry.	LD	ldTask
Labor Code	The labor code to which the employee posted labor as entered in data entry.	LD	ldLaborCode
Trans Type	The type of the labor transaction as defined by the type of data entry.	LD	ldTransType
Date	The date entered for the labor transaction during data entry.	LD	ldDate
Period	The period in which the labor transaction was posted.	LD	ldPeriod
Posting Seq	The posting sequence number for this labor transaction.	LD	ldPostSeq
Regular Hours	The regular hours posted to the project for the labor transaction.	LD	ldRegHrs
Overtime Hours	The overtime hours posted to the project for the labor transaction.	LD	ldOvtHrs
Regular Amount	The regular amount posted to the project for the labor transaction.	LD	ldRegAmt
Overtime Amount	The overtime amount posted to the project for the labor transaction.	LD	ldOvtAmt
Special Overtime Hours	The special overtime hours posted to the project for the labor transaction.	LD	ldSpecialOvtHrs
Special Overtime Amount	The special overtime amount posted to the project for the labor transaction.	LD	ldSpecialOvtAmt
Special Overtime Percent	The employee’s special overtime percentage as defined in Employee Table Maintenance.	LD	ldSpecialOvtPct
Special Overtime Rate	The employee’s effective special overtime rate at the time that the transaction was posted.	LD	ldSpecialOvtRate
Billing Extension	The billing extension for this labor transaction.	LD	ldBillExt
Comment	The comment entered for this labor transaction.	LD	ldComment
Billing Status	The billing status for the labor transaction.	LD	ldBillStatus


Creating a Sample Query Based on the Employee Data Category

Objective:

Create a new query, Employee Sample Query that retrieves all employees who worked on a specific project (for example, 97005.00).

To create a sample employee query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Employees button in the Tables section of the Query Builder form. A list of employee-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	First Name

•	Last Name

•	Project

•	Regular Hours

•	Overtime Hours

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Selection button. The system displays the Employee Selection form.

5.	Click on the Advanced button on the Employee Selection form, and enter the following in the Advanced box:

(LD.ldProject = ‘9700500’)

Where 9700500 is the project number for which to retrieve information. This step retrieves all records from the LD (Labor Detail) table where the project number is 97005.00.

6.	Click on the OK button. The system returns you to the Query Builder form.

7.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

8.	Select File, Close to exit Preview mode and return to the Query Builder form.

9.	Click on the Save Query button. The system displays the Save Query form.

10.	Enter Employee Sample Query as the name for the query, and click on the OK button. A message appears indicating the query has been saved.

11.	Click on the OK button again to return to the Query Builder form.

12.	Click on the Close button to exit the Query Builder form.

13.	Create a sample employee report, as desired.

 Related Topics


Creating a Sample Report Based on an Employee Data Category Query

To create a report based on the Employee Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample employee query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Employee Hours Worked on City Hall Project as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Project Summary Data Category Field Definitions

The following describes the fields associated with individual projects or tasks. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Project Number	Project number.	PR	prProject
Project Name	Project name.	PR	prName
Task Number	Task number.	PR	prTask
Task Name	Task name.	PRTask	prName
Project Type	The project’s type as defined in Project Table Maintenance.	PR	prType
Task Type	The task type for this project (either Yes or No) as defined in Project Table Maintenance.	PR	prTaskType
Principal	The employee number for the principal assigned to the project in Project Table Maintenance.	PR	prPrincipal
Project Manager	The employee number for the project manager assigned to the project in Project Table Maintenance.	PR	prProjMgr
Client	The client number of the client assigned to the project in Project Table Maintenance.	PR	prClient
Profit Center	The profit center of the project.	PR	prProfCtr
Compensation	The project’s total fee as defined in Project Table Maintenance.	PR	prFee
Reimb Allowance	The project’s reimbursable allowance as defined in Project Table Maintenance.	PR	prReimbAllowance
Consultant Fee	The project’s consultant fee as defined in Project Table Maintenance.	PR	prConsultFee
Bdgt OH Rate	The project’s budgeted overhead rate as defined in Project Table Maintenance.	PR	prBudOHRate
Status	The status assigned to the project in Project Table Maintenance.	PR	prStatus
Revenue Type	The project’s revenue method as defined in Project Table Maintenance.	PR	prRevType
Mult/Amt	The multiplier or amount to use with revenue generation for this project as defined in Project Table Maintenance.	PR	prMultAmt
Unit Table	The default unit table to use for the project as defined in Project Table Maintenance.	PR	prUnitTable
Start Date	The expected start date for the project as defined in Project Table Maintenance.	PR	prStartDate
End Date	The expected end date for the project as defined in Project Table Maintenance.	PR	prEndDate
Pct Compl	The estimated overall percent complete for the project as defined in Project Table Maintenance.	PR	prPctComp
Labor Pct Compl	The estimated labor percent complete for the project as defined in Project Table Maintenance.	PR	prLabPctComp
Exp Pct Compl	The estimated expense percent complete for the project as defined in Project Table Maintenance.	PR	prExpPctComp
Bill By Default	The decision whether or not charges to this project should be billed to the client	PR	prBillByDefault
Billable Message	The decision whether or not you want to receive a warning or an error message when entering an account that is incompatible with your selection in the Bill By Default field.	PR	prBillableWarning
ADP Special Project	The decision on whether or not this project is an ADP special project.	PR	prADPSpecial
ADP Reg Hrs Code	The codes designed by your company and ADP for processing special tasks during regular work hours.	PR	prADPRegCode
ADP Reg Hrs Field ID	The bucket (storage file) where the system stores regular hours entered for special tasks.	PR	prADPRegFieldID
ADP Ovt Hrs Code	The codes designed by your company and ADP for processing special tasks during overtime work hours.	PR	prADPOvtCode
ADP Ovt Hrs Field ID	The bucket (storage file) where the system stores overtime hours entered for special tasks.	PR	prADPOvtFieldID
Budgeted Labor Code Validation	The level of access the system should permit you for the specified project when entering codes in the Labor Code fields in Timekeeper.	PR	prBudgetedFlag
Budgeted Levels	The levels on which the system validates entries in the Labor Code fields in Timekeeper.	PR	prBudgetedLevels
Sort/Select 1 <variable name>	The value entered at the first select code (if in use) in Project Table Maintenance.	PR	prSelect1
Sort/Select 2 <variable name>	The value entered at the second select code (if in use) in Project Table Maintenance.	PR	prSelect2
Sort/Select 3 <variable name>	The value entered at the third select code (if in use) in Project Table Maintenance.	PR	prSelect3
Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) in Project Table Maintenance.	PR	prSelect4
Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) in Project Table Maintenance.	PR	prSelect5
Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) in Project Table Maintenance.	PR	prSelect6
Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) in Project Table Maintenance.	PR	prSelect7
Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) in Project Table Maintenance.	PR	prSelect8
Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) in Project Table Maintenance.	PR	prSelect9
Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) in Project Table Maintenance.	PR	prSelect10
Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) in Project Table Maintenance.	PR	prSelect11
Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) in Project Table Maintenance.	PR	prSelect12
Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) in Project Table Maintenance.	PR	prSelect13
Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) in Project Table Maintenance.	PR	prSelect14
Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) in Project Table Maintenance.	PR	prSelect15
Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) in Project Table Maintenance.	PR	prSelect16
Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) in Project Table Maintenance.	PR	prSelect17
Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) in Project Table Maintenance.	PR	prSelect18
Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) in Project Table Maintenance.	PR	prSelect19
Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) in Project Table Maintenance.	PR	prSelect20
Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) in Project Table Maintenance.	PR	prSelect21
Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) in Project Table Maintenance.	PR	prSelect22
Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) in Project Table Maintenance.	PR	prSelect23
Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) in Project Table Maintenance.	PR	prSelect24
Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) in Project Table Maintenance.	PR	prSelect25
Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) in Project Table Maintenance.	PR	prSelect26
Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) in Project Table Maintenance.	PR	prSelect27
Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) in Project Table Maintenance.	PR	prSelect28
Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) in Project Table Maintenance.	PR	prSelect29
Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) in Project Table Maintenance.	PR	prSelect30
Memo	Any comments entered for the project in Project Table Maintenance.	PR	prMemo
Task Principal	The name of the principal assigned to the task in Project Table Maintenance.	PRTask	prPrincipal
Task Project Manager	The name of the project manager assigned to the task in Project Table Maintenance.	PRTask	prProjMgr
Task Client	The name of the client assigned to the task in Project Table Maintenance.	PRTask	prClient
Task Profit Center	The profit center of the task.	PRTask	prProfCtr
Task Compensation	The task’s total fee as defined in Project Table Maintenance.	PRTask	prFee
Task Reimb Allowance	The task’s reimbursable allowance as defined in Project Table Maintenance.	PRTask	prReimbAllowance
Task Consultant Fee	The task’s consultant fee as defined in Project Table Maintenance.	PRTask	prConsultFee
Task Bdgt OH Rate	The task’s budgeted overhead rate as defined in Project Table Maintenance.	PRTask	prBudOHRate
Task Status	The status assigned to the task in Project Table Maintenance.	PRTask	prStatus
Task Revenue Type	The task’s revenue method as defined in Project Table Maintenance.	PRTask	prRevType
Task Mult/Amt	The multiplier or amount to use with revenue generation for this task as defined in Project Table Maintenance.	PRTask	prMultAmt
Task Start Date	The expected start date for the task as defined in Project Table Maintenance.	PRTask	prStartDate
Task End Date	The expected end date for the task as defined in Project Table Maintenance.	PRTask	prEndDate
Task Pct Compl	The estimated overall percent complete for the task as defined in Project Table Maintenance.	PRTask	prPctComp
Task Labor Pct Compl	The estimated labor percent complete for the task as defined in Project Table Maintenance.	PRTask	prLabPctComp
Task Exp Pct Compl	The estimated expense percent complete for the task as defined in Project Table Maintenance.	PRTask	prExpPctComp
Task Bill by Default	The decision whether or not charges to this task should be billed to the client	PRTask	prBillByDefault
Task Billable Message	The decision whether or not you want to receive a warning or an error message when entering an account that is incompatible with your selection in the Bill By Default field.	PRTask	prBillableWarning
Task ADP Special Task	The decision on whether or not this task is an ADP special task.	PRTask	prADPSpecial
Task ADP Reg Hrs Code	The codes designed by your company and ADP for processing special tasks during regular work hours.	PRTask	prADPRegCode
Task ADP Reg Hrs Field ID	The bucket (storage file) where the system stores regular hours entered for special tasks.	PRTask	prADPRegFieldID
Task ADP Ovt Hrs Code	The codes designed by your company and ADP for processing special tasks during overtime work hours.	PRTask	prADPOvtCode
Task ADP Ovt Hrs Field ID	The bucket (storage file) where the system stores overtime hours entered for special tasks.	PRTask	prADPOvtFieldID
Task Budgeted Labor Code Validation	The level of access the system should permit you for the specified task when entering codes in the Labor Code fields in Timekeeper.	PRTask	prBudgetedFlag
Task Budgeted Levels	The levels on which the system validates entries in the Labor Code fields in Timekeeper.	PRTask	prBudgetedLevels
Task Sort/Select 1 <variable name>	The value entered at the first select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect1
Task Sort/Select 2 <variable name>	The value entered at the second select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect2
Task Sort/Select 3 <variable name>	The value entered at the third select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect3
Task Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect4
Task Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect5
Task Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect6
Task Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect7
Task Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect8
Task Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect9
Task Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect10
Task Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect11
Task Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect12
Task Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect13
Task Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect14
Task Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect15
Task Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect16
Task Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect17
Task Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect18
Task Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect19
Task Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect20
Task Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect21
Task Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect22
Task Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect23
Task Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect24
Task Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect25
Task Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect26
Task Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect27
Task Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect28
Task Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect29
Task Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect30
Task Memo	Any comments entered for the task in Project Table Maintenance	PRTask	prMemo
Cur Labor Hours	Current labor hours for the project.	RtkProjectLaborSummary	[Cur labor hours]
YTD Labor Hours	Year-to-date labor hours for the project.	RtkProjectLaborSummary	[JTD labor hours]
JTD Labor Hours	Job-to-date labor hours for the project.	RtkProjectLaborSummary	[YTD labor hours]
Cur Labor Cost	Current labor amount for the project.	RtkProjectLaborSummary	[Cur labor Cost]
JTD Labor Cost	Job-to-date labor amount for the project.	RtkProjectLaborSummary	[JTD labor cost]
YTD Labor Cost	Year-to-date labor amount for the project.	RtkProjectLaborSummary	[YTD labor cost]
Cur Labor Bill Ext	Current labor billing extension for the project.	RtkProjectLaborSummary	[Cur Labor Bill Ext]
YTD Labor Bill Ext	Year-to-date labor billing extension for the project.	RtkProjectLaborSummary	[YTD Labor Bill Ext]
JTD Labor Bill Ext	Job-to-date labor billing extension for the project.	RtkProjectLaborSummary	[JTD Labor Bill Ext]
Cur Reimb Cons Cost	Current reimbursable consultant amount for the project.	RtkProjectExpSummary	[Cur Reimb Cons Cost]
YTD Reimb Cons Cost	Year-to-date reimbursable consultant amount for the project.	RtkProjectExpSummary	[YTD Reimb Cons Cost]
JTD Reimb Cons Cost	Job-to-date reimbursable consultant amount for the project.	RtkProjectExpSummary	[JTD Reimb Cons Cost]
Cur Reimb Other Cost	Current other reimbursable amounts for the project.	RtkProjectExpSummary	[Cur Reimb Other Cost]
YTD Reimb Other Cost	Year-to-date other reimbursable amounts for the project.	RtkProjectExpSummary	[YTD Reimb Other Cost]
JTD Reimb Other Cost	Job-to-date other reimbursable amounts for the project.	RtkProjectExpSummary	[JTD Reimb Other Cost]
Cur Dir Cons Cost	Current direct consultant amount for the project.	RtkProjectExpSummary	[Cur Dir Cons Cost]
YTD Dir Cons Cost	Year-to-date direct consultant amount for the project.	RtkProjectExpSummary	[YTD Dir Cons Cost]
JTD Dir Cons Cost	Job-to-date direct consultant amount for the project.	RtkProjectExpSummary	[JTD Dir Cons Cost]
Cur Dir Cons Cost	Current other direct amounts for the project.	RtkProjectExpSummary	[Cur Dir Other Cost]
YTD Dir Other Cost	Year-to-date other direct amounts for the project.	RtkProjectExpSummary	[YTD Dir Other Cost]
JTD Dir Other Cost	Job-to-date other direct amounts for the project.	RtkProjectExpSummary	[JTD Dir Other Cost]
Cur Ind Cost	Current indirect amount for the project.	RtkProjectExpSummary	[Cur Ind Cost]
YTD Ind Cost	Year-to-date indirect amount for the project.	RtkProjectExpSummary	[YTD Ind Cost]
JTD Ind Cost	Job-to-date indirect amount for the project.	RtkProjectExpSummary	[JTD Ind Cost]
Cur Reimb Cons Bill Ext	Current reimbursable consultant billing extension for the project.	RtkProjectExpSummary	[Cur Reimb Cons Bill Ext]
YTD Reimb Cons Bill Ext	Year-to-date reimbursable consultant billing extension for the project.	RtkProjectExpSummary	[YTD Reimb Cons Bill Ext]
JTD Reimb Cons Bill Ext	Job-to-date reimbursable consultant billing extension for the project.	RtkProjectExpSummary	[JTD Reimb Cons Bill Ext]
Cur Reimb Other Bill Ext	Current reimbursable other billing extension for the project.	RtkProjectExpSummary	[Cur Reimb Other Bill Ext]
YTD Reimb Other Bill Ext	Year-to-date reimbursable other billing extension for the project.	RtkProjectExpSummary	[YTD Reimb Other Bill Ext]
JTD Reimb Other Bill Ext	Job-to-date reimbursable other billing extension for the project.	RtkProjectExpSummary	[JTD Reimb Other Bill Ext]
Cur Dir Cons Bill Ext	Current direct consultant billing extension for the project.	RtkProjectExpSummary	[Cur Dir Cons Bill Ext]
YTD Dir Cons Bill Ext	Year-to-date direct consultant billing extension for the project.	RtkProjectExpSummary	[YTD Dir Cons Bill Ext]
JTD Dir Cons Bill Ext	Job-to-date direct consultant billing extension for the project.	RtkProjectExpSummary	[JTD Dir Cons Bill Ext]
Cur Dir Other Bill Ext	Current direct other billing extension for the project.	RtkProjectExpSummary	[Cur Dir Other Bill Ext]
YTD Dir Other Bill Ext	Year-to-date direct other billing extension for the project.	RtkProjectExpSummary	[YTD Dir Other Bill Ext]
JTD Dir Other Bill Ext	Job-to-date direct other billing extension for the project.	RtkProjectExpSummary	[JTD Dir Other Bill Ext]
Cur Ind Bill Ext	Current indirect billing extension for the project.	RtkProjectExpSummary	[Cur Ind Bill Ext]
YTD Ind Bill Ext	Year-to-date indirect billing extension for the project.	RtkProjectExpSummary	[YTD Ind Bill Ext]
JTD Ind Bill Ext	Job-to-date indirect billing extension for the project.	RtkProjectExpSummary	[JTD Ind Bill Ext]
Cur Fee Billing	Current fee billing for the project.	RtkProjectExpSummary	[Cur Fee Billing]
YTD Fee Billing	Year-to-date fee billing for the project.	RtkProjectExpSummary	[YTD Fee Billing]
JTD Fee Billing	Job-to-date fee billing for the project.	RtkProjectExpSummary	[JTD Fee Billing]
Cur Cons Billing	Current consultant billing for the project.	RtkProjectExpSummary	[Cur Cons Billing]
YTD Cons Billing	Year-to-date consultant billing for the project.	RtkProjectExpSummary	[YTD Cons Billing]
JTD Cons Billing	Job-to-date consultant billing for the project.	RtkProjectExpSummary	[JTD Cons Billing]
Cur Reimb Billing	Current reimbursable billing for the project.	RtkProjectExpSummary	[Cur Reimb Billing]
YTD Reimb Billing	Year-to-date reimbursable billing for the project.	RtkProjectExpSummary	[YTD Reimb Billing]
JTD Reimb Billing	Job-to-date reimbursable billing for the project.	RtkProjectExpSummary	[JTD Reimb Billing]
Cur Other Billing	Current other billing for the project.	RtkProjectExpSummary	[Cur Other Billing]
YTD Other Billing	Year-to-date other billing for the project.	RtkProjectExpSummary	[YTD Other Billing]
JTD Other Billing	Job-to-date other billing for the project.	RtkProjectExpSummary	[JTD Other Billing]
JTD Total Billing	Job-to-date total billings for the project.	RtkProjectExpSummary	[JTD Tot Billing]
Cur Total Billing	Current total billings for the project.	RtkProjectExpSummary	[Cur Tot Billing]
YTD Total Billing	Year-to-date total billings for the project.	RtkProjectExpSummary	[YTD Tot Billing]
Cur Revenue	Current revenue for the project.	RtkProjectExpSummary	[Cur Revenue]
YTD Revenue	Year-to-date revenue for the project.	RtkProjectExpSummary	[YTD Revenue]
JTD Revenue	Job-to-date revenue for the project.	RtkProjectExpSummary	[JTD Revenue]
Cur Received	Current received for the project.	RtkProjectExpSummary	[Cur Received]
YTD Received	Year-to-date received for the project.	RtkProjectExpSummary	[YTD Received]
JTD Received	Job-to-date received for the project.	RtkProjectExpSummary	[JTD Received]
Cur OH	Current overhead for the project.	RtkProjectOH	[CurOH]
YTD OH	Year-to-date overhead for the project.	RtkProjectOH	[YTD  OH]
JTD OH	Job-to-date overhead for the project.	RtkProjectOH	[JTD OH]


Creating a Sample Query Based on the Project Summary Data Category

Objective:

Create a new query, Project Summary Sample Query that retrieves year-to-date labor for all regular projects in your database.

To create a sample project summary query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Project Summary button in the Tables section of the Query Builder form. A list of project summary-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Project Number

•	Project Name

•	YTD Labor Hours

•	YTD Labor Cost

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Selection button. The system displays the Project Selection form.

5.	Select the Regular option from the drop-down list in the Type field on the Project Selection form.

6.	Select the Selection based on task values option to return only tasks that meet the criteria specified in the previous step.

7.	Click on the OK button. The system returns you to the Query Builder form.

8.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

9.	Select File, Close to exit Preview mode and return to the Query Builder form.

10.	Click on the Save Query button. The system displays the Save Query form.

11.	Enter Project Summary Sample Query as the name for the query, and click on the OK button. A message appears indicating the query has been saved.

12.	Click on the OK button again to return to the Query Builder form.

13.	Click on the Close button to exit the Query Builder form.

14.	Create a report based on the query created, as desired.

 Related Topics


Creating a Sample Report Based on a Project Summary Data Category Query

To create a report based on the Project Summary Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample project summary query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Year-to-date Labor by Project Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Project Labor Data Category Field Definitions

The following describes the fields associated with individual labor transactions. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Project Number	The project number to which the amount was posted.	PR	prProject
Project Name	The name of the project name to which the amount was posted.	PR	prName
Task Number	The task number to which the amount was posted.	PR	prTask
Task Name	The name of the task to which the amount was posted.	PRTask	prName
Project Type	The project’s type as defined in Project Table Maintenance.	PR	prType
Task Type	The task type for this project.	PR	prTaskType
Principal	The name of the principal assigned to the project in Project Table Maintenance.	PR	prPrincipal
Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PR	prProjMgr
Client	The name of the client assigned to the project in Project Table Maintenance.	PR	prClient
Status	The status assigned to the project in Project Table Maintenance.	PR	prStatus
Profit Center	The profit center of the project.	PR	prProfCtr
Sort/Select 1 <variable name>	The value entered at the first select code (if in use) in Project Table Maintenance.	PR	prSelect1
Sort/Select 2 <variable name>	The value entered at the second select code (if in use) in Project Table Maintenance.	PR	prSelect2
Sort/Select 3 <variable name>	The value entered at the third select code (if in use) in Project Table Maintenance.	PR	prSelect3
Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) in Project Table Maintenance.	PR	prSelect4
Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) in Project Table Maintenance.	PR	prSelect5
Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) in Project Table Maintenance.	PR	prSelect6
Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) in Project Table Maintenance.	PR	prSelect7
Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) in Project Table Maintenance.	PR	prSelect8
Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) in Project Table Maintenance.	PR	prSelect9
Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) in Project Table Maintenance.	PR	prSelect10
Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) in Project Table Maintenance.	PR	prSelect11
Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) in Project Table Maintenance.	PR	prSelect12
Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) in Project Table Maintenance.	PR	prSelect13
Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) in Project Table Maintenance.	PR	prSelect14
Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) in Project Table Maintenance.	PR	prSelect15
Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) in Project Table Maintenance.	PR	prSelect16
Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) in Project Table Maintenance.	PR	prSelect17
Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) in Project Table Maintenance.	PR	prSelect18
Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) in Project Table Maintenance.	PR	prSelect19
Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) in Project Table Maintenance.	PR	prSelect20
Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) in Project Table Maintenance.	PR	prSelect21
Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) in Project Table Maintenance.	PR	prSelect22
Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) in Project Table Maintenance.	PR	prSelect23
Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) in Project Table Maintenance.	PR	prSelect24
Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) in Project Table Maintenance.	PR	prSelect25
Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) in Project Table Maintenance.	PR	prSelect26
Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) in Project Table Maintenance.	PR	prSelect27
Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) in Project Table Maintenance.	PR	prSelect28
Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) in Project Table Maintenance.	PR	prSelect29
Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) in Project Table Maintenance.	PR	prSelect30
Task Principal	The name of the principal assigned to the project in Project Table Maintenance.	PRTask	prPrincipal
Task Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PRTask	prProjMgr
Task Client	The name of the client assigned to the project in Project Table Maintenance.	PRTask	prClient
Task Status	The status assigned to the project in Project Table Maintenance.	PRTask	prStatus
Task Profit Center	The profit center of the project.	PRTask	prProfCtr
Task Sort/Select 1 <variable name>	The value entered at the first select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect1
Task Sort/Select 2 <variable name>	The value entered at the second select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect2
Task Sort/Select 3 <variable name>	The value entered at the third select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect3
Task Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect4
Ta4k Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect5
Task Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect6
Task Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect7
Task Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect8
Task Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect9
Task Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect10
Task Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect11
Task Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect12
Task Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect13
Task Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect14
Task Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect15
Task Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect16
Task Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect17
Task Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect18
Task Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect19
Task Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect20
Task Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect21
Task Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect22
Task Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect23
Task Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect24
Task Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect25
Task Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect26
Task Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect27
Task Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect28
Task Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect29
Task Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect30
Labor Code	The labor code to which the amount was posted.	LD	ldLaborCode
Employee Number	The employee who posted amount to this project/task/labor code combination.	LD	ldEmployee
Employee Name	The name of the employee who posted amount to this project/task/labor code combination.	LD	ldName
Trans Type	The transaction type for this labor transaction.	LD	ldTransType
Date	The date entered for this labor transaction during data entry.	LD	ldDate
Period	The period in which this labor transaction was posted.	LD	ldPeriod
Posting Seq	The posting sequence number for this labor transaction.	LD	ldPostSeq
Regular Hours	The regular hours posted for this labor transaction.	LD	ldRegHrs
Overtime Hours	The overtime hours posted for this labor transaction.	LD	ldOvtHrs
Regular Amount	The regular amount posted for this labor transaction.	LD	ldRegAmt
Overtime Amount	The overtime amount posted for this labor transaction.	LD	ldOvtAmt
Special Overtime Hours	The special overtime hours posted for this labor transaction	LD	ldSpecialOvtHrs
Special Overtime Amount	The special overtime amount posted for this labor transaction.	LD	ldSpecialOvtAmt
Special Overtime Percent	The employee’s special overtime percentage as defined in Employee Table Maintenance.	LD	ldSpecialOvtPct
Special Overtime Rate	The employee’s effective special overtime rate at the time that the transaction was posted.	LD	ldSpecialOvtRate
Billing Extension	The billing extension for this labor transaction.	LD	ldBillExt
Comment	The comment for this labor transaction.	LD	ldComment
Billing Status	The billing status for the labor transaction.	LD	ldBillStatus
Suppress Billing	Suppress the billing for this labor transaction, Yes or No.	LD	ldSuppressBill


Creating a Sample Query Based on the Project Labor Data Category

Objective:

Create a new query, Project Labor Sample Query that retrieves the total amount of hours written-off by project manager.

To create a sample project labor query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Project Labor button in the Tables section of the Query Builder form. A list of project labor-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Project Manager

•	Billing Status

•	Regular Hours

•	Overtime Hours

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Selection button. The system displays the Project Selection form.

5.	Click on the Advanced button on the Project Selection form and enter the following in the Advanced box:

(LD.ldBillStatus = ‘X’)

This statement will return all values that were written off.

6.	Select the Selection based on tasks value option to include only the tasks for projects that meet the project criteria specified in the previous step.

7.	Click on the OK button. The system returns you to the Query Builder form.

8.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

9.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

10.	Click on the Save Query button. The system displays the Save Query form.

11.	Enter Project Labor Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

12.	Click on the OK button again to return to the Query Builder form.

13.	Click on the Close button to exit the Query Builder form.

14.	Create a sample project labor report, as desired.

 Related Topics


Creating a Sample Report Based on a Project Labor Data Category Query

To create a report based on the Project Labor Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample project labor query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Write-Offs by Project Manager Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Unposted Timesheets Data Category Field Definitions

The following describes the fields associated with individual labor transactions. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

If you select the Consolidate data by selected fields option, the rows of data retrieved will be consolidated by the combination of selected fields. See also Consolidating Data by the Selected Fields.

Field	Description	Table	Field Name
Project Number	The project number to which the amount was posted.	PR	prProject
Project Name	The name of the project name to which the amount was posted.	PR	prName
Task Number	The task number to which the amount was posted.	PR	prTask
Task Name	The name of the task to which the amount was posted.	PRTask	prName
Project Type	The project’s type as defined in Project Table Maintenance.	PR	prType
Task Type	The task type for this project.	PR	prTaskType
Principal	The name of the principal assigned to the project in Project Table Maintenance.	PR	prPrincipal
Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PR	prProjMgr
Client	The name of the client assigned to the project in Project Table Maintenance.	PR	prClient
Status	The status assigned to the project in Project Table Maintenance.	PR	prStatus
Profit Center	The profit center of the project.	PR	prProfCtr
Sort/Select 1 <variable name>	The value entered at the first select code (if in use) in Project Table Maintenance.	PR	prSelect1
Sort/Select 2 <variable name>	The value entered at the second select code (if in use) in Project Table Maintenance.	PR	prSelect2
Sort/Select 3 <variable name>	The value entered at the third select code (if in use) in Project Table Maintenance.	PR	prSelect3
Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) in Project Table Maintenance.	PR	prSelect4
Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) in Project Table Maintenance.	PR	prSelect5
Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) in Project Table Maintenance.	PR	prSelect6
Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) in Project Table Maintenance.	PR	prSelect7
Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) in Project Table Maintenance.	PR	prSelect8
Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) in Project Table Maintenance.	PR	prSelect9
Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) in Project Table Maintenance.	PR	prSelect10
Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) in Project Table Maintenance.	PR	prSelect11
Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) in Project Table Maintenance.	PR	prSelect12
Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) in Project Table Maintenance.	PR	prSelect13
Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) in Project Table Maintenance.	PR	prSelect14
Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) in Project Table Maintenance.	PR	prSelect15
Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) in Project Table Maintenance.	PR	prSelect16
Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) in Project Table Maintenance.	PR	prSelect17
Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) in Project Table Maintenance.	PR	prSelect18
Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) in Project Table Maintenance.	PR	prSelect19
Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) in Project Table Maintenance.	PR	prSelect20
Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) in Project Table Maintenance.	PR	prSelect21
Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) in Project Table Maintenance.	PR	prSelect22
Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) in Project Table Maintenance.	PR	prSelect23
Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) in Project Table Maintenance.	PR	prSelect24
Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) in Project Table Maintenance.	PR	prSelect25
Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) in Project Table Maintenance.	PR	prSelect26
Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) in Project Table Maintenance.	PR	prSelect27
Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) in Project Table Maintenance.	PR	prSelect28
Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) in Project Table Maintenance.	PR	prSelect29
Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) in Project Table Maintenance.	PR	prSelect30
Task Principal	The name of the principal assigned to the project in Project Table Maintenance.	PRTask	prPrincipal
Task Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PRTask	prProjMgr
Task Client	The name of the client assigned to the project in Project Table Maintenance.	PRTask	prClient
Task Status	The status assigned to the project in Project Table Maintenance.	PRTask	prStatus
Task Profit Center	The profit center of the project.	PRTask	prProfCtr
Task Sort/Select 1 <variable name>	The value entered at the first select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect1
Task Sort/Select 2 <variable name>	The value entered at the second select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect2
Task Sort/Select 3 <variable name>	The value entered at the third select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect3
Task Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect4
Ta4k Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect5
Task Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect6
Task Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect7
Task Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect8
Task Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect9
Task Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect10
Task Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect11
Task Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect12
Task Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect13
Task Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect14
Task Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect15
Task Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect16
Task Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect17
Task Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect18
Task Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect19
Task Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect20
Task Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect21
Task Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect22
Task Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect23
Task Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect24
Task Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect25
Task Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect26
Task Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect27
Task Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect28
Task Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect29
Task Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect30
Transaction File	The name of the unposted transaction file including the labor amounts.	RtkUnpostedTime	FileName
Labor Code	The labor code to which the amount was posted.	RTKuUnpostedTime	LaborCode
Employee Number	The employee who posted amount to this project/task/labor code combination.	RTKuUnpostedTime	Employee
Employee Name	The name of the employee who posted amount to this project/task/labor code combination.	RTKuUnpostedTime	Name
Date	The date entered for this labor transaction during data entry.	RTKuUnpostedTime	TransDate
Regular Hours	The regular hours posted for this labor transaction.	RTKuUnpostedTime	RegHrs
Overtime Hours	The overtime hours posted for this labor transaction.	RTKuUnpostedTime	OvtHrs
Special Overtime Hours	The special overtime hours posted for this labor transaction.	RTKuUnpostedTime	SpecialOvtHrs
Comment	The comment for this labor transaction.	RTKuUnpostedTime	TransComment


Creating a Sample Query Based on the Unposted Timesheets Data Category

Objective:

Create a new query, Unposted Timesheets Sample Query that retrieves all employees who worked on a specific project (for example, 97005.00).

To create a sample unposted timesheets query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Unposted Timesheets button in the Tables section of the Query Builder form. A list of unposted employee timesheet-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Project

•	Employee Name

•	Labor Code

•	Regular Hours

•	Overtime Hours

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter Unposted Timesheets Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a sample unposted timesheets report, as desired.

 Related Topics


Creating a Sample Report Based on an Unposted Timesheets Data Category Query

To create a report based on the Unposted Timesheets Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample unposted timesheets query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Timesheet Edit List as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Project Expenses Data Category Field Definitions

The following table reviews fields that are associated with individual expense transactions. Also included in this table, is the Advantage table from which this information is obtained and the field name as it appears in the table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Project Number	The project number to which the amount was posted.	PR	prProject
Project Name	The name of the project to which the amount was posted.	PR	prName
Task Number	The task number to which the amount was posted.	PR	prTask
Task Name	The name of the task to which the amount was posted.	PRTask	prName
Project Type	The project’s type as defined in Project Table Maintenance.	PR	prType
Task Type	The task type for this project.	PR	prTaskType
Principal	The name of the principal assigned to the project in Project Table Maintenance.	PR	prPrincipal
Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PR	prProjMgr
Client	The name of the client assigned to the project in Project Table Maintenance.	PR	prClient
Status	The status assigned to the project in Project Table Maintenance.	PR	prStatus
Profit Center	The profit center of the project.	PR	prProfCtr
Sort/Select 1 <variable name>	The value entered at the first select code (if in use) in Project Table Maintenance.	PR	prSelect1
Sort/Select 2 <variable name>	The value entered at the second select code (if in use) in Project Table Maintenance.	PR	prSelect2
Sort/Select 3 <variable name>	The value entered at the third select code (if in use) in Project Table Maintenance.	PR	prSelect3
Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) in Project Table Maintenance.	PR	prSelect4
Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) in Project Table Maintenance.	PR	prSelect5
Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) in Project Table Maintenance.	PR	prSelect6
Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) in Project Table Maintenance.	PR	prSelect7
Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) in Project Table Maintenance.	PR	prSelect8
Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) in Project Table Maintenance.	PR	prSelect9
Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) in Project Table Maintenance.	PR	prSelect10
Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) in Project Table Maintenance.	PR	prSelect11
Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) in Project Table Maintenance.	PR	prSelect12
Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) in Project Table Maintenance.	PR	prSelect13
Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) in Project Table Maintenance.	PR	prSelect14
Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) in Project Table Maintenance.	PR	prSelect15
Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) in Project Table Maintenance.	PR	prSelect16
Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) in Project Table Maintenance.	PR	prSelect17
Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) in Project Table Maintenance.	PR	prSelect18
Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) in Project Table Maintenance.	PR	prSelect19
Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) in Project Table Maintenance.	PR	prSelect20
Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) in Project Table Maintenance.	PR	prSelect21
Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) in Project Table Maintenance.	PR	prSelect22
Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) in Project Table Maintenance.	PR	prSelect23
Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) in Project Table Maintenance.	PR	prSelect24
Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) in Project Table Maintenance.	PR	prSelect25
Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) in Project Table Maintenance.	PR	prSelect26
Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) in Project Table Maintenance.	PR	prSelect27
Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) in Project Table Maintenance.	PR	prSelect28
Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) in Project Table Maintenance.	PR	prSelect29
Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) in Project Table Maintenance.	PR	prSelect30
Task Principal	The name of the principal assigned to the project in Project Table Maintenance.	PRTask	prPrincipal
Task Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PRTask	prProjMgr
Task Client	The name of the client assigned to the project in Project Table Maintenance.	PRTask	prClient
Task Status	The status assigned to the project in Project Table Maintenance.	PRTask	prStatus
Task Profit Center	The profit center of the project.	PRTask	prProfCtr
Task Sort/Select 1 <variable name>	The value entered at the first select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect1
Task Sort/Select 2 <variable name>	The value entered at the second select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect2
Task Sort/Select 3 <variable name>	The value entered at the third select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect3
Task Sort/Select 4 <variable name>	The value entered at the fourth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect4
Ta4k Sort/Select 5 <variable name>	The value entered at the fifth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect5
Task Sort/Select 6 <variable name>	The value entered at the sixth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect6
Task Sort/Select 7 <variable name>	The value entered at the seventh select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect7
Task Sort/Select 8 <variable name>	The value entered at the eighth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect8
Task Sort/Select 9 <variable name>	The value entered at the ninth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect9
Task Sort/Select 10 <variable name>	The value entered at the tenth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect10
Task Sort/Select 11 <variable name>	The value entered at the 11th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect11
Task Sort/Select 12 <variable name>	The value entered at the 12th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect12
Task Sort/Select 13 <variable name>	The value entered at the 13th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect13
Task Sort/Select 14 <variable name>	The value entered at the 14th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect14
Task Sort/Select 15 <variable name>	The value entered at the 15th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect15
Task Sort/Select 16 <variable name>	The value entered at the 16th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect16
Task Sort/Select 17 <variable name>	The value entered at the 17th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect17
Task Sort/Select 18 <variable name>	The value entered at the 18th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect18
Task Sort/Select 19 <variable name>	The value entered at the 19th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect19
Task Sort/Select 20 <variable name>	The value entered at the 20th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect20
Task Sort/Select 21 <variable name>	The value entered at the 21st select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect21
Task Sort/Select 22 <variable name>	The value entered at the 22nd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect22
Task Sort/Select 23 <variable name>	The value entered at the 23rd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect23
Task Sort/Select 24 <variable name>	The value entered at the 24th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect24
Task Sort/Select 25 <variable name>	The value entered at the 25th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect25
Task Sort/Select 26 <variable name>	The value entered at the 26th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect26
Task Sort/Select 27 <variable name>	The value entered at the 27th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect27
Task Sort/Select 28 <variable name>	The value entered at the 28th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect28
Task Sort/Select 29 <variable name>	The value entered at the 29th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect29
Task Sort/Select 30 <variable name>	The value entered at the 30th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect30
Account	The account number to which to charge this expense amount.	ED	edAccount
Account Name	The name of the account to which to charge this expense amount.	CA	caName
Trans Type	The transaction type for this expense transaction.	ED	edTransType
Date	The date entered for this expense transaction during data entry.	ED	edDate
Period	The period in which this expense transaction was posted.	ED	edPeriod
Posting Seq	The posting sequence number for this expense transaction.	ED	edPostSeq
Reference Number	The reference number for this expense transaction.	ED	edRefNo
Description 1	The first description for the expense transaction.	ED	edDesc1
Description 2	The second description for the expense transaction.	ED	edDesc2
Amount	The amount of the expense transaction.	ED	edAmount
Billing Extension	The billing extension of the expense transaction.	ED	edBillExt
Billing Status	The billing status of the expense transaction.	ED	edBillStatus
Suppress Billing	Suppress the billing for this expense transaction, Yes or No.	ED	edSuppressBill


Creating a Sample Query Based on the Project Expenses Data Category

Objective:

Create a new query, Project Expense Sample Query that retrieves all employees who worked on a specific project (for example, 97005.00).

To create a sample project expenses query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Project Expenses button in the Tables section of the Query Builder form. A list of project expense-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Project Name

•	Project Number

•	Amount

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter Project Expenses Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a sample project expenses report, as desired.

 Related Topic


Creating a Sample Report Based on a Project Expenses Data Category Query

To create a report based on the Project Expenses Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample project expense query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Expenses by Project Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Client Data Category Field Definitions

The following table describes the fields associated with individual clients. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Client Number	The client number as specified in Client Table Maintenance.	CL	clClient
Client Name	The client name as specified in Client Table Maintenance.	CL	clName
Addressee	The addressee label for this client as specified in Client Table Maintenance.	CL	clAddressee
Address Line 1	The first line of the client’s address as defined in Client Table Maintenance.	CL	clAddress1
Address Line 2	The second line of the client’s address as defined in Client Table Maintenance.	CL	clAddress2
Address Line 3	The third line of the client’s address as defined in Client Table Maintenance.	CL	clAddress3
City	The client’s city as specified in Client Table Maintenance.	CL	clCity
State	The client’s state as specified in Client Table Maintenance.	CL	clState
ZIP	The client’s zip code as specified in Client Table Maintenance.	CL	clZIP
Country	The client’s country as specified in Client Table Maintenance.	CL	clCountry
Contact	The contact at the client as specified in Client Table Maintenance.	CL	clContact
Phone	The client’s telephone number as specified in Client Table Maintenance.	CL	clPhone
FAX	The client’s FAX number as specified in Client Table Maintenance.	CL	clFAX
Email	The client’s e-mail as specified in Client Table Maintenance.	CL	clEMail
Select Code 1	The value entered as the first select code in Client Table Maintenance.	CL	clSelect1
Select Code 2	The value entered as the second select code in Client Table Maintenance.	CL	clSelect2
Select Code 3	The value entered as the third select code in Client Table Maintenance.	CL	clSelect3
Memo	Any additional notes for the client.	CL	clMemo
Project Number	The project number to which this client is assigned.	PR	prProject
Project Name	The name of the project to which this client is assigned.	PR	prName


Creating a Sample Query Based on the Client Data Category

Objective:

Create a new query, Client Sample Query, that retrieves contact information for all of the clients in your database.

To create a sample client query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Clients button in the Tables section of the Query Builder form. A list of client-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Client Name

•	Contact

•	Phone

•	FAX

•	Email

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter Client Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a sample client report, as desired.

 Related Topics


Creating a Sample Report Based on a Client Data Category Query

To create a report based on the Client Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample client query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Client Contact Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Accounts Receivable Data Category Field Definitions

The following table describes the fields associated with individual invoice transactions. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Project Number	The project number.	PR	prProject
Project Name	The name of the project.	PR	prName
Task Number	The task number.	PR	prTask
Task Name	The name of the task.	PRTask	prName
Project Type	The project’s type as defined in Project Table Maintenance.	PR	prType
Task Type	The task type for this project (either Yes or No).	PR	prTaskType
Principal	The name of the principal assigned to the project in Project Table Maintenance.	PR	prPrincipal
Project Manager	The name of the project manager assigned to the project in Project Table Maintenance.	PR	prProjMgr
Client	The name of the client assigned to the project in Project Table Maintenance.	PR	prClient
Status	The status assigned to the project in Project Table Maintenance.	PR	prStatus
Profit Center	The profit center of the project.	PR	prProfCtr
Sort/Select 1 <variable name>	The value entered as the first select code (if in use) in Project Table Maintenance.	PR	prSelect1
Sort/Select 2 <variable name>	The value entered as the second select code (if in use) in Project Table Maintenance.	PR	prSelect2
Sort/Select 3 <variable name>	The value entered as the third select code (if in use) in Project Table Maintenance.	PR	prSelect3
Sort/Select 4 <variable name>	The value entered as the fourth select code (if in use) in Project Table Maintenance.	PR	prSelect4
Sort/Select 5 <variable name>	The value entered as the fifth select code (if in use) in Project Table Maintenance.	PR	prSelect5
Sort/Select 6 <variable name>	The value entered as the sixth select code (if in use) in Project Table Maintenance.	PR	prSelect6
Sort/Select 7 <variable name>	The value entered as the seventh select code (if in use) in Project Table Maintenance.	PR	prSelect7
Sort/Select 8 <variable name>	The value entered as the eighth select code (if in use) in Project Table Maintenance.	PR	prSelect8
Sort/Select 9 <variable name>	The value entered as the ninth select code (if in use) in Project Table Maintenance.	PR	prSelect9
Sort/Select 10 <variable name>	The value entered as the tenth select code (if in use) in Project Table Maintenance.	PR	prSelect10
Sort/Select 11 <variable name>	The value entered as the 11th select code (if in use) in Project Table Maintenance.	PR	prSelect11
Sort/Select 12 <variable name>	The value entered as the 12th select code (if in use) in Project Table Maintenance.	PR	prSelect12
Sort/Select 13 <variable name>	The value entered as the 13th select code (if in use) in Project Table Maintenance.	PR	prSelect13
Sort/Select 14 <variable name>	The value entered as the 14th select code (if in use) in Project Table Maintenance.	PR	prSelect14
Sort/Select 15 <variable name>	The value entered as the 15th select code (if in use) in Project Table Maintenance.	PR	prSelect15
Sort/Select 16 <variable name>	The value entered as the 16th select code (if in use) in Project Table Maintenance.	PR	prSelect16
Sort/Select 17 <variable name>	The value entered as the 17th select code (if in use) in Project Table Maintenance.	PR	prSelect17
Sort/Select 18 <variable name>	The value entered as the 18th select code (if in use) in Project Table Maintenance.	PR	prSelect18
Sort/Select 19 <variable name>	The value entered as the 19th select code (if in use) in Project Table Maintenance.	PR	prSelect19
Sort/Select 20 <variable name>	The value entered as the 20th select code (if in use) in Project Table Maintenance.	PR	prSelect20
Sort/Select 21 <variable name>	The value entered as the 21st select code (if in use) in Project Table Maintenance.	PR	prSelect21
Sort/Select 22 <variable name>	The value entered as the 22nd select code (if in use) in Project Table Maintenance.	PR	prSelect22
Sort/Select 23 <variable name>	The value entered as the 23rd select code (if in use) in Project Table Maintenance.	PR	prSelect23
Sort/Select 24 <variable name>	The value entered as the 24th select code (if in use) in Project Table Maintenance.	PR	prSelect24
Sort/Select 25 <variable name>	The value entered as the 25th select code (if in use) in Project Table Maintenance.	PR	prSelect25
Sort/Select 26 <variable name>	The value entered as the 26th select code (if in use) in Project Table Maintenance.	PR	prSelect26
Sort/Select 27 <variable name>	The value entered as the 27th select code (if in use) in Project Table Maintenance.	PR	prSelect27
Sort/Select 28 <variable name>	The value entered as the 28th select code (if in use) in Project Table Maintenance.	PR	prSelect28
Sort/Select 29 <variable name>	The value entered as the 29th select code (if in use) in Project Table Maintenance.	PR	prSelect29
Sort/Select 30 <variable name>	The value entered as the 30th select code (if in use) in Project Table Maintenance.	PR	prSelect30
Task Principal	The principal assigned to the task.	PRTask	prPrincipal
Task Project Manager	The project manager assigned to the task.	PRTask	PrProjMgr
Task Client	The client assigned to this task.	PRTask	PrClient
Task Status	The status of the task.	PRTask	PrStatus
Task Profit Center	The profit center of the task.	PRTask	PrProfCtr
Task Sort/Select 1 <variable name>	The value entered as the first select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect1
Task Sort/Select 2 <variable name>	The value entered as the second select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect2
Task Sort/Select 3 <variable name>	The value entered as the third select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect3
Task Sort/Select 4 <variable name>	The value entered as the fourth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect4
Ta4k Sort/Select 5 <variable name>	The value entered as the fifth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect5
Task Sort/Select 6 <variable name>	The value entered as the sixth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect6
Task Sort/Select 7 <variable name>	The value entered as the seventh select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect7
Task Sort/Select 8 <variable name>	The value entered as the eighth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect8
Task Sort/Select 9 <variable name>	The value entered as the ninth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect9
Task Sort/Select 10 <variable name>	The value entered as the tenth select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect10
Task Sort/Select 11 <variable name>	The value entered as the 11th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect11
Task Sort/Select 12 <variable name>	The value entered as the 12th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect12
Task Sort/Select 13 <variable name>	The value entered as the 13th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect13
Task Sort/Select 14 <variable name>	The value entered as the 14th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect14
Task Sort/Select 15 <variable name>	The value entered as the 15th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect15
Task Sort/Select 16 <variable name>	The value entered as the 16th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect16
Task Sort/Select 17 <variable name>	The value entered as the 17th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect17
Task Sort/Select 18 <variable name>	The value entered as the 18th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect18
Task Sort/Select 19 <variable name>	The value entered as the 19th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect19
Task Sort/Select 20 <variable name>	The value entered as the 20th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect20
Task Sort/Select 21 <variable name>	The value entered as the 21st select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect21
Task Sort/Select 22 <variable name>	The value entered as the 22nd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect22
Task Sort/Select 23 <variable name>	The value entered as the 23rd select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect23
Task Sort/Select 24 <variable name>	The value entered as the 24th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect24
Task Sort/Select 25 <variable name>	The value entered as the 25th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect25
Task Sort/Select 26 <variable name>	The value entered as the 26th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect26
Task Sort/Select 27 <variable name>	The value entered as the 27th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect27
Task Sort/Select 28 <variable name>	The value entered as the 28th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect28
Task Sort/Select 29 <variable name>	The value entered as the 29th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect29
Task Sort/Select 30 <variable name>	The value entered as the 30th select code (if in use) for this task in Project Table Maintenance.	PRTask	prSelect30
Invoice Number	The invoice number.	ED	EdInvoice
Invoice Date	The date of the invoice.	AR	ARInvoiceDate
Trans Type	The transaction type for this invoice transaction.	ED	edTransType
Trans Date	Date referenced on the transaction posting.	ED	edDate
Fees	The fee amount specified on the invoice.	None	Calculated Field
Reimbursables	The reimbursable amount specified on the invoice.	None	Calculated Field
Taxes	The tax amount specified on the invoice.	None	Calculated Field
Interest	The interest amount specified on the invoice.	None	Calculated Field
Other	All other amounts specified on the invoice.	None	Calculated Field
Total	The total amount specified on the invoice.	None	Calculated Field


Creating a Sample Query Based on the Accounts Receivable Data Category

Objective:

Create a new Accounts Receivable Sample Query that retrieves total outstanding invoice amounts by client.

To create a sample accounts receivable query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Accounts Receivable button in the Tables section of the Query Builder form. A list of accounts receivable-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Project Number

•	Project Name

•	Client

•	Total

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter Accounts Receivable Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a sample accounts receivable report, as desired.

 Related Topics


Creating a Sample Report Based on an Accounts Receivable Data Category Query

To create a report based on the Accounts Receivable Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample accounts receivable query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Total Accounts Receivable by Client Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Vendor Data Category Field Definitions

The following table describes the fields that are associated with individual vendors. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Vendor Number	The vendor number as defined in Vendor Table Maintenance.	VE	veVendor
Vendor Name	The vendor name as defined in Vendor Table Maintenance.	VE	veName
Sort Name	The vendor sort name as defined in Vendor Table Maintenance.	VE	veSortName
Address	The address code(s) for the vendor.	VEA	veaAddress
Address Line 1	The first line of the vendor’s address as defined in Vendor Table Maintenance.	VEA	veaAddress1
Address Line 2	The second line of the vendor’s address as defined in Vendor Table Maintenance.	VEA	veaAddress2
Address Line 3	The third line of the vendor’s address as defined in Vendor Table Maintenance.	VEA	veaAddress3
Address Line 4	The fourth line of the vendor’s address as defined in Vendor Table Maintenance.	VEA	veaAddress4
City	The vendor’s city as defined in Vendor Table Maintenance.	VEA	veaCity
State	The vendor’s state as defined in Vendor Table Maintenance.	VEA	veaState
ZIP	The vendor’s zip code as defined in Vendor Table Maintenance.	VEA	veaZIP
Country	The vendor’s country as defined in Vendor Table Maintenance.	VEA	veaCountry
Contact	The contact at the vendor as defined in Vendor Table Maintenance.	VEA	veaContact
Phone	The vendor’s phone number as defined in Vendor Table Maintenance.	VEA	veaPhone
FAX	The vendor’s FAX number as defined in Vendor Table Maintenance.	VEA	veaFAX
Email	The vendor’s email address as defined in Vendor Table Maintenance.	VEA	veaEMail
Vendor Type	The vendor’s type as defined in Vendor Table Maintenance.	VE	veType
Status	The vendor’s status as defined in Vendor Table Maintenance.	VE	veStatus
1099 Req	Is a 1099 required for this vendor (Options are 0=No or –1=Yes).	VE	ve1099Req
Federal ID	For vendor’s requiring 1099s, the federal identification number.	VE	veFedID
Pay Terms	The default payment terms specified for this vendor in Vendor Table Maintenance.	VE	vePayTerms
Disc Pct	The discount percentage to be applied to this vendor as specified in Vendor Table Maintenance.	VE	veDiscPct
Disc Period	The discount period for this vendor as specified in Vendor Table Maintenance.	VE	veDiscPeriod
Disc Code	The discount code for this vendor as specified in Vendor Table Maintenance.	VE	veDiscCode
Overhead Account	The account to use for overhead projects for this vendor as specified in Vendor Table Maintenance.	VE	veOHAccount
Regular Account	The account to use for regular projects for this vendor as specified in Vendor Table Maintenance.	VE	veRegAccount
Profit Center	The profit center for this vendor as specified in Vendor Table Maintenance.	VE	veProfCtr
Checks Per Voucher	Are there separate checks for this vendor for each voucher?	VE	veCheckPerVoucher
Paid This Year	The 1099 amount for the vendor this year.	VE	veThisYear1099
Paid Last Year	The 1099 amount for the vendor from the previous year.	VE	veLastYear1099
Select Code 1	The value entered as the first select code in Vendor Table Maintenance.	VE	veSelect1
Select Code 2	The value entered as the second select code in Vendor Table Maintenance.	VE	veSelect2
Select Code 3	The value entered as the third select code in Vendor Table Maintenance.	VE	veSelect3
Memo	Any additional notes for this vendor.	VE	veMemo


Creating a Sample Query Based on the Vendor Data Category

Objective:

Create a new query, Vendor Sample Query, that lists 1099 amounts by vendor.

To create a sample vendor query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Vendors button in the Tables section of the Query Builder form. A list of vendor-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Vendor Number

•	Vendor Name

•	Paid Last Year

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter Vendor Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a sample vendor report, as desired.

 Related Topics


Creating a Sample Report Based on a Vendor Data Category Query

To create a report based on the Vendor Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample vendor query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter 1099 Amounts by Vendor Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Accounts Payable Data Category Field Definitions

The following table describes the fields associated with individual accounts payable transactions. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Vendor Number	The vendor number.	VE	veVendor
Vendor Name	The name of the vendor.	VE	veName
Sort Name	The vendor sort name as defined in Vendor Table Maintenance.	VE	veSortName
Vendor Type	The vendor’s type as defined in Vendor Table Maintenance.	VE	veType
Status	The vendor’s status as defined in Vendor Table Maintenance.	VE	veStatus
Select Code 1	The value entered as the first select code in Vendor Table Maintenance.	VE	veSelect1
Select Code 2	The value entered as the second select code in Vendor Table Maintenance.	VE	veSelect2
Select Code 3	The value entered as the third select code in Vendor Table Maintenance.	VE	veSelect3
Profit Center	The profit center for this vendor.	VE	veProfCtr
Voucher	The voucher number for this accounts payable transaction.	ED	edVoucher
Line	The voucher line number for this accounts payable transaction.	ED	edLine
Date	The date of the accounts payable transaction.	VO	voDate
Invoice Date	The invoice date for this accounts payable transaction.	VO	voInvoiceDate
Invoice	The invoice number for this accounts payable transaction.	VO	voInvoice
Bank Code	The bank code for this accounts payable transaction.	VO	voBankCode
Liab Code	The liability code for this accounts payable transaction.	VO	voLiabCode
Payment Terms	The payment terms for this voucher.	VO	voPayTerms
Payment Date	The payment date for this voucher.	VO	voPaymentDate
Period Paid	The period in which this voucher was paid.	VO	voPaidPeriod
Project	The project number for this accounts payable transaction.	ED	edProject
Task	The task for this accounts payable transaction.	ED	edTask
Account	The account number for this accounts payable transaction.	ED	edAccount
Trans Type	The transaction type.	ED	edTransType
Reference Number	The reference number for this accounts payable transaction.	ED	edRefNo
Description 1	The first description for this accounts payable transaction.	ED	edDesc1
Description 2	The second description for this accounts payable transaction.	ED	edDesc2
Billed Invoice	The invoice on which this accounts payable transaction appeared on.	ED	edBilledInvoice
Amount	The amount for this accounts payable transaction.	ED	edAmount
Discount	The discount applied to this accounts payable transaction.	ED	edDiscount


Creating a Sample Query Based on the Accounts Payable Data Category

Objective:

Create a new query, Accounts Payable Sample Query that retrieves Vouchers for a specific vendor (for example, 0000000001 Speedy Blueprint).

To create a sample accounts payable query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Accounts Payable button in the Tables section of the Query Builder form. A list of accounts payable-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Vendor Number

•	Vendor Name

•	Voucher

•	Line

•	Amount

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Selection button. The system displays the Vendor Selection form.

5.	Enter the appropriate vendor number for which to collect information in the Vendor Number field on the Project Selection form.

6.	Click on the OK button. The system returns you to the Query Builder form.

7.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

8.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

9.	Click on the Save Query button. The system displays the Save Query form.

10.	Enter Accounts Payable Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

11.	Click on the OK button again to return to the Query Builder form.

12.	Click on the Close button to exit the Query Builder form.

13.	Create a sample accounts payable report, as desired.

 Related Topics


Creating a Sample Report Based on an Accounts Payable Data Category Query

To create a report based on the Accounts Payable Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample accounts payable query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Vouchers by Vendor Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Payroll Data Category Field Definitions

The following describes the fields associated with individual payroll transactions. Also listed is the Advantage table from which this information is obtained, and the field name as it appears in the Advantage table. This information will be useful when setting up custom selection criteria for your queries.

Field	Description	Table	Field Name
Employee Number	Employee number.	EMP	empEmployee
Last Name	The employee’s last name.	EM	emLastName
First Name	The employee’s first name.	EM	emFirstName
Employee Type	The employee’s type as defined in Employee Table Maintenance.	EM	emType
Status	The employee’s status as defined in Employee Table Maintenance.	EM	emStatus
Profit Center	The profit center for this employee.	EM	emProfCtr
Sort/Select 1 <variable name>	The value entered as the first select code (if in use) in Employee Table Maintenance.	EM	emSelect1
Sort/Select 2 <variable name>	The value entered as the second select code (if in use) in Employee Table Maintenance.	EM	emSelect2
Sort/Select 3 <variable name>	The value entered as the third select code (if in use) in Employee Table Maintenance.	EM	emSelect3
Sort/Select 4 <variable name>	The value entered as the fourth select code (if in use) in Employee Table Maintenance.	EM	emSelect4
Sort/Select 5 <variable name>	The value entered as the fifth select code (if in use) in Employee Table Maintenance.	EM	emSelect5
Sort/Select 6 <variable name>	The value entered as the sixth select code (if in use) in Employee Table Maintenance.	EM	emSelect6
Sort/Select 7 <variable name>	The value entered as the seventh select code (if in use) in Employee Table Maintenance.	EM	emSelect7
Sort/Select 8 <variable name>	The value entered as the eighth select code (if in use) in Employee Table Maintenance.	EM	emSelect8
Sort/Select 9 <variable name>	The value entered as the ninth select code (if in use) in Employee Table Maintenance.	EM	emSelect9
Sort/Select 10 <variable name>	The value entered as the tenth select code (if in use) in Employee Table Maintenance.	EM	emSelect10
Sort/Select 11 <variable name>	The value entered as the 11th select code (if in use) in Employee Table Maintenance.	EM	emSelect11
Sort/Select 12 <variable name>	The value entered as the 12th select code (if in use) in Employee Table Maintenance.	EM	emSelect12
Sort/Select 13 <variable name>	The value entered as the 13th select code (if in use) in Employee Table Maintenance.	EM	emSelect13
Sort/Select 14 <variable name>	The value entered as the 14th select code (if in use) in Employee Table Maintenance.	EM	emSelect14
Sort/Select 15 <variable name>	The value entered as the 15th select code (if in use) in Employee Table Maintenance.	EM	emSelect15
Sort/Select 16 <variable name>	The value entered as the 16th select code (if in use) in Employee Table Maintenance.	EM	emSelect16
Sort/Select 17 <variable name>	The value entered as the 17th select code (if in use) in Employee Table Maintenance.	EM	emSelect17
Sort/Select 18 <variable name>	The value entered as the 18th select code (if in use) in Employee Table Maintenance.	EM	emSelect18
Sort/Select 19 <variable name>	The value entered as the 19th select code (if in use) in Employee Table Maintenance.	EM	emSelect19
Sort/Select 20 <variable name>	The value entered as the 20th select code (if in use) in Employee Table Maintenance.	EM	emSelect20
Sort/Select 21 <variable name>	The value entered as the 21st select code (if in use) in Employee Table Maintenance.	EM	emSelect21
Sort/Select 22 <variable name>	The value entered as the 22nd select code (if in use) in Employee Table Maintenance.	EM	emSelect22
Sort/Select 23 <variable name>	The value entered as the 23rd select code (if in use) in Employee Table Maintenance.	EM	emSelect23
Sort/Select 24 <variable name>	The value entered as the 24th select code (if in use) in Employee Table Maintenance.	EM	emSelect24
Sort/Select 25 <variable name>	The value entered as the 25th select code (if in use) in Employee Table Maintenance.	EM	emSelect25
Sort/Select 26 <variable name>	The value entered as the 26th select code (if in use) in Employee Table Maintenance.	EM	emSelect26
Sort/Select 27 <variable name>	The value entered as the 27th select code (if in use) in Employee Table Maintenance.	EM	emSelect27
Sort/Select 28 <variable name>	The value entered as the 28th select code (if in use) in Employee Table Maintenance.	EM	emSelect28
Sort/Select 29 <variable name>	The value entered as the 29th select code (if in use) in Employee Table Maintenance.	EM	emSelect29
Sort/Select 30 <variable name>	The value entered as the 30th select code (if in use) in Employee Table Maintenance.	EM	emSelect30
Pay Rate	The employee’s pay rate as defined in Employee Table Maintenance.	EMP	empPayRate
Pay Type	The employee’s pay type as defined in Employee Table Maintenance.	EMP	empPayType
Ovt Pct	The employee’s overtime percentage as defined in Employee Table Maintenance.	EMP	empPayOvtPct
W2 Year	The W-2 year for the payroll run.	CFGPostControl	W2Year
W2 Quarter	The W-2 quarter for the payroll run.	CFGPostControl	W2Quarter
Period	The period for the payroll run.	EMPD	empdPeriod
Posting Seq	Posting sequence number.	EMPD	empdPostSeq
Reg Hrs	Regular hours charged by the employee.	EMPD	empdRegHrs
Reg Hrs Override	The regular hours entered as an override during a payroll processing session.	EMPD	empdRegHrsOverride
Ovt Hrs	Overtime hours charged by the employee.	EMPD	empdOvtHrs
Ovt Hrs Override	The overtime hours entered as an override during a payroll processing session.	EMPD	empdOvtHrsOverride
Special Ovt Hours	Special overtime hours charged by the employee.	EMPD	empdSpecialOvtHrs
Special Ovt Hours Override	The special overtime hours entered as an override during a payroll processing session.	EMPD	empdSpecialOvtHrsOverride
Reg Pay	Regular pay for the employee.	EMPD	empdRegPay
Ovt Pay	Overtime pay for the employee.	EMPD	empdOvtPay
Special Ovt Pay	Special Overtime pay for the employee.	EMPD
Other Pay	Other pay for the employee.	EMPD	empdOtherPay
Other Pay Override	The other pay entered as an override during a payroll processing session.	EMPD	empdOtherPayOverride
Check Number	Check number for the employee’s pay check.	EMPD	empdCheck
Withholding Code	Withholding code.	EMPWD	empwdCode
Withholding Method	Withholding method.	EMPWD	empwdMethod
Withholding Amt/Pct	Withholding amount/percent, depending on the method used.	EMPWD	empwdAmtPct
Withholding Limit	Withholding limit.	EMPWD	empwdLimit
Withholding Gross Pay Basis	Withholding gross pay basis.	EMPWD	empwdGrossPayBasis
Withholding Amount	Withholding amount for the withholding code.	EMPWD	empwdAmount
Withholding Override	The withholding amount entered as an override during a payroll processing session.	EMPWD	empwdOverride


Creating a Sample Query Based on the Payroll Data Category

Objective:

Create a new query, Payroll Sample Query, that retrieves withholding codes and amounts for all the employees in your database.

To create a sample payroll query:

1.	Click on the Create New Query button from the main Advantage Custom Reporting Toolkit form. The system displays the Query Builder form.

2.	Click on the Payroll button in the Tables section of the Query Builder form. A list of payroll-related fields appears in the Available Fields section of the form.

3.	Select the following fields in the Available Fields section of this form:

•	Employee Number

•	First Name

•	Last Name

•	Withholding Code

•	Withholding Amount

When you do this, the system displays these field names in the Selected Fields section of this form.

4.	Click on the Preview button to view the information obtained by the query. The system displays a form, in datasheet view, listing the information obtained from your Advantage database.

5.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

6.	Click on the Save Query button. The system displays the Save Query form.

7.	Enter Payroll Sample Query as the name for the query and click on the OK button. A message appears indicating the query has been saved.

8.	Click on the OK button again to return to the Query Builder form.

9.	Click on the Close button to exit the Query Builder form.

10.	Create a sample payroll report, as desired.

 Related Topics


Creating a Sample Report Based on a Payroll Data Category Query

To create a report based on the Payroll Sample Query:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the sample payroll query you previously created.

3.	Click on the Report button. The system displays the Report Builder form.

4.	Enter Employee Withholdings Report as the name for the report in the Report Title field.

5.	Define whether the report should be portrait or landscape.

6.	Click on the OK button. The system creates a report based on the information provided in the query and on the Report Builder form.

You can modify the report at any time. If you are unfamiliar with designing reports in Microsoft Access and you would like to modify the report, you should refer to the Online Help Topics for Microsoft Access. Or, you can contact the Business Intelligence Group if you are having problems creating your reports.


Selecting the Fields to Include in a Query

Once you select the table from which to build a query, you must select the fields to include in the query. You can select as many fields from a data category as desired. However, you cannot select fields from two different data categories.

To select the fields to include in the query, refer to the following guidelines:

To select...	Do this...
one field	Click on the desired field, and then click on the Add button. Or, double-click on the desired field.
multiple, sequential fields	Hold down the <Shift> key and click on the first and last field in the range. When all desired fields are selected, click on the Add button. Or, hold down the left mouse button and drag the mouse over the desired fields to include and then click on the Add button.
multiple, non-sequential fields	Hold down the <Ctrl> key and click on each individual field to include. When all desired fields are selected, click on the Add button. Or, double-click on each individual field you want to include.
 Notes


Consolidating Data by the Selected Fields

When you select the fields to include in a query, the system retrieves each individual detail line that matches the criteria and fields you specified for the query. In other words, the query may display multiple lines for the same item (account, employee, project). For this reason, you can select to consolidate the data onto one line by clicking on the Consolidate data by selected fields option from the main Query Builder form.

For example, assume you are generating an Account Balances report and you select the following fields for the query:

•	Account Number

•	Account Name

•	Amount

If you do not have the Consolidate data by selected fields option selected, the query will generate a list such as the following:

In this example, notice how multiple lines appear for accounts 101.00 and 111.00. These lines represent the detail in your database at the current time.

If you want the amounts on these lines consolidated onto one line, select the Consolidate data by selected fields option. When you regenerate this query, the query will generate a list such as the following:


Excluding Formatting in Queries

When you select the fields to include in a query, the system retrieves and formats each individual detail line that matches the criteria and fields you specified for the query. In other words, the query may display thousands of lines of data based on the query. This process of retrieving and formatting can take a long time on large databases. For this reason, you can select to exclude formatting from the query by clicking on the Exclude formatting for projects, accounts, etc. option from the main Query Builder form. By turning this feature on, you are greatly reducing the time it takes to run the query.

Excluding formatting has the following results:

•	Account numbers will not include the delimiter.

•	Project numbers will not include the delimiter(s).

•	Profit center codes will not include the delimiter.

•	Accounting periods will be formatted as 199908 (yyyymm).

•	Numeric values may not be rounded to two decimals.

Example


Example for Excluding Formatting in Queries

Assume you are generating an Account Balances report and you select the following fields for the query:

•	Account Number

•	Account Name

•	Amount

If you do not have the Exclude formatting for projects, accounts, etc. option selected, the query will generate a list such as the following:

In this example, notice how the accounts appear with decimals 101.00 and 111.00. The toolkit has taken the time to format the accounts due to your specifications at installation.

If you have the Exclude formatting for projects, accounts, etc. option selected, the query will generate a list such as the following:


Selection Criteria

When you select the fields to include in a query, the system retrieves all the information in your Advantage database that has a value in any of the selected fields. This may be useful for a database list, but is not so useful if you are trying to get the labor costs for a specific project. Therefore, you can set up selection criteria to further limit the information retrieved by the query. Depending on the data category you are on, you can set up certain criteria.

You select the data to include on your reports by clicking on the Selection button. The information on this tab differs slightly for each reporting routine, however, the functionality is basically the same. The type of information that differs is as follows:

•	The fixed selection criteria located at the top of this form. This information includes project, employee, account, or vendor specific data depending on the query type.

Selection Criteria Section

•	The sort/select codes as defined in Sort/Select Setup Maintenance. This information includes project, employee, account, or vendor specific sort/select codes.

Profit Center and Sort Selects Section

•	The specific key field selection section at the bottom of this form. You can select specific projects, employees, accounts, or vendors based on the query type.

Specific Key Field(s) Selection Section

•	The Selection based on task values option at the bottom right of this form. This option allows you to select only the tasks for a project that match the criteria entered. This option is available for the following project related Data Categories: Accounts Receivable, Project Expenses, Project Labor, Project Summary, and Unposted Timesheets.

Example

 Notes


Setting Up Advanced Selection Criteria to Limit the Amount of Data Retrieved
Setting Up Advanced Selection Criteria to Limit Data Retrieved

The Advanced window on the account, employee, project, client, and vendor selection forms let you enter additional criteria that limits the data retrieved by the query. You should enter Select statements in this window in order to choose information that cannot be chosen by the fields on the selection form.

Example

You should also be very familiar with the Advantage Data Dictionary, and have a complete understanding of how data is stored, prior to entering a statement in the Advanced window. If you need assistance in creating Advanced statements, you can contact the Business Intelligence Group. We also offer an Ad-Hoc Reporting with Advantage Seminar that reviews this information in further detail.

 Note


Sorting the Fields Included in the New Query

Once you have defined the fields to include in the new query, you can define the order in which the fields will appear. By default, the query (and report, if created) will display the fields in the order in which they appear in the Selected Fields section of the form.

To move a field up or down in the list, highlight the field to reposition, and then click on one of the following buttons:

	Moves the selected field up one line in the Selected Fields section of this form.
	Moves the selected field down one line in the Selected Fields section of this form.

You can also simultaneously select multiple fields to move up and down. To do this, select the fields to move and click on the up or down button.

 Notes


Previewing, Exporting, Saving, and/or Closing the Query

Once you have completed the new custom query, you can perform the following options:

Option	Description
Preview the results of the query	You should use this as an ongoing step to ensure that the data you are accessing is appropriate. When you click on this button, the system runs the query and displays the results in Microsoft Access datasheet view. It is useful to preview the query after you select the fields, sort the display of the fields in the query, and define selection criteria.
Export the query to Microsoft Excel	You should use this as a final step in accessing the data. When you click on this button, the system runs the query, and then exports the data to Microsoft Excel. You can use Microsoft Excel to manipulate, report, and/or save the spreadsheet. Exporting to Microsoft Excel is appropriate if you want the data in a spreadsheet format, but you want to add special formatting or include subtotals and totals.
Save the query	Click on the Save Query button once you have decided that the query is collecting the appropriate information and displaying it as desired. When you click on this button, the system displays a form on which you can specify the query name.  Notes
Close the query	Click on the Close button after you have saved your query (if desired). When you click on this button, the system returns you to the main Advantage Custom Reporting Toolkit form.


Previewing the Data in Datasheet View

While you are on the Query Builder form, you can click on the Preview button as you are modifying the query and the selection criteria to preview the data. The benefits of using this feature are:

•	You will be able to see the effect of your changes immediately. The data collected is displayed in datasheet form, which you can sort, print, or search.

•	You do not need to learn Microsoft Access or Excel. You can click on the Run Saved Query button from the main Custom Reporting Toolkit form, select the query, and click on the Preview button to view the most up-to-date data in datasheet view.

Even if you plan on exporting the data and formatting it in Microsoft Access or Excel, you should use this option on an ongoing basis when building your queries to ensure that the data you are accessing is appropriate.

Good times to preview the query are after you select the fields, after you define selection criteria, and after you sort the fields in the query.


Exporting and Formatting the Data in Microsoft Excel

Once your query has been finalized, you can choose to export the data to Microsoft Excel. In Microsoft Excel, you format your data using the Microsoft Excel feature. Some of the benefits are:

•	Data is exported in spreadsheet format. No automatic formatting is applied to the data.

•	You can manipulate the data, analyze it, create charts, and use the many other features of Microsoft Excel.

•	You can merge data with user-defined information not stored in Advantage.

There are many more benefits to using Excel, including your familiarity with the tool.


Formatting the Data in Microsoft Access

The main purpose of the Advantage Custom Reporting Toolkit is to create custom reports based on queries you create from the data categories. Additionally, you can also use the queries to preview data or export it to Microsoft Excel.

•	See also Creating a New Report Using the Toolkit.

•	If you need assistance in creating a report, please contact the Business Intelligence Group.

•	If you would like to learn more about creating custom reports using Microsoft Access and the Advantage Custom Reporting Toolkit, we offer an Ad-Hoc Reporting with Advantage Seminar.

 Note


Running a Saved Query in the Toolkit

You can run a query at any time after creating it by clicking on the Saved Queries button from the main Advantage Custom Reporting Toolkit form. When you click on this button the system displays the form shown below.

To find out more about the options on this form, click on the desired button (or area) on the graphic.


General Steps to Create a New Report Using the Toolkit

Once you have your query created, it is time to create a new report. Only one query can be used when creating a report.

To create a new report:

1.	Click on the Create New Report button from the main Advantage Custom Reporting Toolkit form. The system displays the Saved Queries form.

2.	Select the query to use when creating the new report.

3.	Click on the Report button. The system opens the report in design mode.

4.	Enter a report title and desired layout as prompted. The report is created.

5.	Modify any desired information on the report.

6.	Save the report.

7.	Close the report.

8.	Run the saved report.


Selecting the Query to Use for the Report

When you click on the Create New Report button, the system displays the Saved Queries form. This form lists all of the queries you have created using the Advantage Custom Reporting Toolkit. Select the desired query to use for the report.

What’s Next?

Create the report.


Creating the Report

Click on the Report button. The system opens the report in Microsoft Access design mode. When the system creates the report, it uses the following Advantage formatting and conventions:

Report Section	Includes
Report Header	No data is included in this section.
Page Header	• The name of your firm, as entered in System Setup Maintenance, in Arial 9-point, bold font. • Day and date on which the report was generated, in Arial 10-point font. • Time at which the report was generated, in Arial 10-point font. • The names of the fields from the query included on the report, in Arial 8-point, bold font.
Detail	The data generated by the query for each field included on the report, in Arial 8-point font.
Page Footer	The word Page and the page number, in Arial-8 point font.
Report Footer	• Totals for all amount fields included on the report, in Arial-8 point, bold font. • ** End of Report ** prints, in Arial-8 point, bold font.

What’s Next?

Modify the report information in design view.


Modifying Report Information in Design View

Once the report is open in design mode, you can modify it as desired. Some of the information you may choose to modify is listed below:

•	The arrangement of the fields. If you do this, ensure that you are moving the heading, detail, and total lines (if applicable) so that the information remains aligned.

•	The size of the fields. The system automatically sizes the fields for you based, on the number of fields you have included on the report. You may have to adjust the size of certain fields so that amounts/text is not truncated on the finished report.

•	Other miscellaneous formatting items (such as font size, font, alignment, spacing).

•	Add label boxes, if needed.

What’s Next?

Save the report.


Saving and Closing the Report

When you have completed formatting the report as desired, you can save and close the report.

•	To save a report, select the Save option from the File menu. Enter the name for the report, and click on the OK button.

•	To close out of design mode of the report, select the Close option from the File menu.

The next step is to run the saved report. See Running a saved report in the toolkit for more information.


Running a Saved Report in the Toolkit

You can re-run a report at any time after creating it by clicking on the Saved Reports button from the main Advantage Custom Reporting Toolkit form. When you click on this button the system displays the following form.

To find out more about the options on this form, click on the desired button (or area) on the graphic displayed below:

 Note


Changing the Reporting Period Using the Toolkit

The Advantage Custom Reporting Toolkit lets you run your custom reports in all prior open, and closed periods.

To change the reporting period:

1.	Click on the Change Period button from the main Advantage Custom Reporting Toolkit form. When you do this, the system displays the Period Selection form.

2.	Select the period for which you want to generate the custom report.

3.	Click on the OK button. Advantage opens the selected period.

 Notes


Updating Sort/Select Information

Sort/Select fields in Advantage are completely user-defined. For this reason, you need to let the toolkit know when you add a new field or delete a field.

To update sort/select information:

•	Click on the Update Sort/Select Info button from the initial Advantage Custom Reporting Toolkit form.

The update process should be performed on a regular basis to prevent errors in your queries, as well as to ensure that you are creating queries with the most up-to-date information in your database.


Viewing Database Information Using the Toolkit

Warning!	The information displayed in the database window is all the information in your Advantage database. If you modify any of the original data in this window, problems will occur with your Advantage application. Please be sure to only select and modify the data that you have created. Modifying the information that we have provided can cause serious problems with the Advantage application.
When you click on the Database Window button, the system displays a form listing all the tables, queries, forms, reports, macros, and modules in the Advantage Custom Reporting Toolkit. Through this window, you also have access to all the tables and table data from your Advantage database.

Advanced users will be able to use this portion of the toolkit to create advanced queries and reports not attainable through the normal Toolkit functions. Use the data dictionary to find the tables that include the information you want to access in the creation of the custom report.

If you need assistance in creating custom reports or queries, please contact the Business Intelligence Group.


Preview the query

Preview the results of the query in Microsoft Access datasheet view. This view provides you with the data that is generated by the query, given the defined fields and selection criteria.


Export the query to Microsoft Excel

Export the query to Microsoft Excel. This view provides you with the data that is generated by the query in a Microsoft Excel spreadsheet. You will then be able to use Microsoft Excel functionality to manipulate the data.


Create a report based on the query

Create a Microsoft Access report based on the information in the query. The report format defaults to the standard Advantage report format so that your custom reports look similar to your standard Advantage reports.

See also, creating a new report using the toolkit.


Edit the query in design view

Open the query in Microsoft Access design view. This view lets you view the tables that make up the query, as well as the fields included in the query. You can add fields and sort criteria using this view.

More importantly, you can create more complex queries using this type of view by adding tables to the query and setting up relationships between the tables. This is only recommended for advanced Microsoft Access users.


Delete the query

Delete the current, highlighted query.


Viewing saved queries

Provides you with a list of all the saved queries in the Advantage Custom Reporting Toolkit. Highlight the desired query.


Close

Closes the Saved Queries and/or Saved Reports form and returns you to the main Advantage Custom Reporting Toolkit form.


Save the query under a new name

Saves the existing query under a new name. This feature is useful if you want to duplicate the query or if you named it incorrectly and want to rename it.


Preview the report

Print preview the report in Microsoft Access. When you do this, the system runs the query to get the most up-to-date information for the displayed report.


Print the report

Print the report. When you do this, the system runs the query to get the most up-to-date information for the printed report.


Edit the report in design view

Open the report in Microsoft Access design view. This view lets you move the fields on the report, enlarge the fields, or add other desired information.


Delete the report

Delete the current, highlighted report.


Viewing saved reports

Provides you with a list of all the saved reports in the Advantage Custom Reporting Toolkit. Highlight the desired report.


Save the report under a new name

Saves the existing report under a new name. This feature is useful if you want to duplicate the report or if you named it incorrectly and want to rename it.


Contacting our Technical Consultants

For assistance in using the Custom Reporting Toolkit, please contact the Business Intelligence Group at 617.492.4410. This consulting will be provided on a time and materials basis.

In addition, we also offer an Ad-Hoc Reporting with Advantage Seminar.


Tips on Using the Custom Reporting Toolkit

•	Write your own queries for more efficiency.

•	Become experienced with SQL Server (then use report builder to layout your reports for you).

•	Turn on hidden objects after viewing database window.

•	Review the Function References provided in this document. They can help you perform many functions quickly and easily.


Using Date and Period Routines to Create a Custom Report

Here are the Date and Period functions you can use in the Custom Reporting Toolkit:

Public Function GetAsOfDate() As String

Returns a string of the form  As of June 30, 1998  where the date is the ending date of the current period.

To use, set the control source of a text box control to:

=GetAsOfDate()

Public Function GetForThePeriod()

Returns a string of the form  For the period June 1, 1998 – June 30, 1998  where the dates are the beginning and ending dates of the current period.

To use, set the control source of a text box control to:

=GetForThePeriod()

Public Function GetForPeriodEnding(Optional PdEnd)

Returns a string of the form  For the period ending June 30, 1998  where the date is either a date passed to the function, or if no date is supplied, the ending date of the current period.

To use, set the control source of a text box control to:

=GetForPeriodEnding()

or

=GetForPeriodEnding(#7/31/98#)

Public Function GetActivePeriod As Long

Returns a long integer value of the form yyyymm representing the current fiscal period.

Used in queries to filter period sensitive data:

Where ED.edPeriod = GetActivePeriod()

Public Function GetActiveFY() As Long

Returns a long integer value of the form yyyy00 representing the current fiscal period.

Used in queries to filter period sensitive data:

Where ED.edPeriod >= GetActiveFY()

Public Function GetActiveFYStart() As Long

Returns a long integer value of the form yyyy00 representing the beginning of the current fiscal year.

Used in queries to filter period sensitive data:

Where ED.edPeriod >= GetActiveFYStart()

Public Function GetActiveFP() As Long

Returns a long integer value of the form yyyymm representing the beginning of the current fiscal period.

Used in queries to filter period sensitive data:

Where ED.edPeriod >= GetActiveFP()


Using Chart of Account Routines to Create a Custom Report

Here are the Chart of Account functions you can use in the Custom Reporting Toolkit:

Public Function GetFirstLiability() As String

Returns a string representing the first account in the liability range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstLiability()

or

Where ED.edAccount >= GetFirstLiability() And ED.edAccount < GetFirstNetWorth()

Public Function GetFirstNetWorth() As String

Returns a string representing the first account in the net worth range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstNetWorth()

or

Where ED.edAccount >= GetFirstNetWorth() And ED.edAccount < GetFirstRevenue()

Public Function GetFirstRevenue() As String

Returns a string representing the first account in the revenue range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstRevenue()

or

Where ED.edAccount >= GetFirstRevenue() And ED.edAccount < GetFirstReimbCons()

Public Function GetFirstReimbCons() As String

Returns a string representing the first account in the reimbursable consultant range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstReimbCons()

or

Where ED.edAccount >= GetFirstReimbCons() And ED.edAccount < GetFirstReimbOther()

Public Function GetFirstReimbOther() As String

Returns a string representing the first account in the reimbursable other range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstReimbOther()

or

Where ED.edAccount >= GetFirstReimbOther() And ED.edAccount < GetFirstDirectCons()

Public Function GetFirstDirectCons() As String

Returns a string representing the first account in the direct consultant range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstDirectCons()

or

Where ED.edAccount >= GetFirstDirectCons() And ED.edAccount < GetFirstDirectOther()

Public Function GetFirstDirectOther() As String

Returns a string representing the first account in the direct other range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstDirectOther()

or

Where ED.edAccount >= GetFirstDirectOther() And ED.edAccount < GetFirstIndirect()

Public Function GetFirstIndirect() As String

Returns a string representing the first account in the indirect range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstIndirect()

or

Where ED.edAccount >= GetFirstIndirect() And ED.edAccount < GetFirstOtherCharges()

Public Function GetFirstOtherCharges() As String

Returns a string representing the first account in the other charges range as defined in System Setup.

Used in queries to filter general ledger or expense data:

Where ED.edAccount >= GetFirstOtherCharges()


Using Formatting Routines to Create a Custom Report

Here are the Formatting functions you can use in the Custom Reporting Toolkit:

Public Function FormatAccount(Account) As String

Returns a string representing a formatted account number, in other words, with delimiters inserted.

To use, set the control source of a text box control to:

=FormatAccount(ED.edAccount)

Public Function FormatProfCtr(ProfCtr) As String

Returns a string representing a formatted profit center, in other words, with delimiters inserted.

To use, set the control source of a text box control to:

=FormatProfCtr(ED.edProfCtr)

Public Function FormatProject(Project) As String

Returns a string representing a formatted project number, in other words, with delimiters inserted.

To use, set the control source of a text box control to:

=FormatProject(ED.edProject)

Public Function FormatPeriod(Period) As String

Returns a string of the form mm/yyyy representing a formatted fiscal period.

To use, set the control source of a text box control to:

=FormatPeriod(ED.edPeriod)

Public Function FormatShortDate As Date

Formats dates according to the Advantage short date format.

To use, set the control source of a text box control to:

=FormatShortDate(PR.prStartDate)

Public Function FormatMediumDate As Date

Formats dates according to the Advantage medium date format.

To use, set the control source of a text box control to:

=FormatMediumDate(PR.prStartDate)

Public Function FormatLongDate As Date

Formats dates according to the Advantage long date format.

To use, set the control source of a text box control to:

=FormatLongDate(PR.prStartDate)

Public Function FormatStdDate As Date

Formats dates according to the format mmm-yyy.

To use, set the control source of a text box control to:

=FormatStdDate(PR.prStartDate)


Using Miscellaneous Routines to Create a Custom Report

Here are the Miscellaneous functions you can use in the Custom Reporting Toolkit:

Public Function GetFirmName()

Returns a string containing the firm’s name as defined in SystemSetup.

To use, set the control source of a text box control to:

=GetFirmName()

Public Function GetPctrLabel(ProfCtr) As String

Returns a string containing the label for the specified profit center as defined in Individual Profit Center Setup.

To use, set the control source of a text box control to:

=GetPctrLabel(‘BOAR’)


Microsoft Access Class Agenda
Ad-Hoc Reporting with Advantage Seminar - Agenda

•	On the first day, you will begin to learn Microsoft Access by becoming familiar with the basic functions of the program. You will also explore how and where the data is stored within Advantage. This entails reviewing the Advantage Data Dictionary.

•	On the second day, you will focus on the tables you will commonly use to create Ad-Hoc Reports and Billing templates and you will build Ad-Hoc reports and Billing Templates from scratch. The techniques used to create these items will assist you in creating many other Advantage Ad-Hoc Reports and Templates.


Example - Setting Up Advanced Selection Criteria

Objective:

Determine those employees who worked on project 97005.00 – City Hall.

To determine those employees who worked on project 97005.00:

1.	Click on the Create New Query button.

2.	Select to use the Employee data category as the basis of your query.

3.	Select the following fields:

•	Employee Number

•	Last Name

•	First Name

•	Project

4.	Click on the Selection button. Notice that a field does not exist in which you may specify a project number.

5.	Click on the Advanced button. The system activates the Advanced window.

6.	Enter the following statement in the Advanced window:

(LD.ldProject = ‘9700500’)

 Note

7.	Click on the OK button to return to the Query Builder form.

8.	Click on the Preview button to preview your results. Note that the system only selects employees who charged time to project 97005.00 – City Hall.

9.	Select File, Close from the Access main menu to exit Preview mode and return to the Query Builder form.

10.	Click on the Save Query button. The system displays the Save Query form.

11.	Enter the name for the query, and click on the OK button. A message appears indicating the query has been saved.

12.	Click on the OK button again to return to the Query Builder form.

13.	Click on the Close button to exit the Query Builder form.


Example for Selecting Based on Task Value

Assume you want to retrieve all of the projects or tasks for which William Apple is the project manager. You have the following projects set up in your database:

Project	Project Manager
1999014.00	William Apple
	001	James Bartlett
	002	Grace Cohen
	003	William Apple
	004	William Apple
	005	James Bartlett
1999020.00	James Bartlett
	001	James Bartlett
	002	William Apple

If you do not select the Selection based on task values, Advantage prints the report displaying only project 1999014.00, where William Apple is the project manager for the project. Even though William Apple is the project manager for one of the tasks in project 1999020.00, this project is not included on the report.

If you select the Selection based on task values, Advantage prints the report displaying tasks 003 and 004 for project 1999014.00 and task 002 for project 1999020.00, where William Apple is the project manager for one or more of the tasks for the project.


Notes about Installing the Custom Reporting Toolkit

•	Prior to installing the toolkit, ensure that all toolkit users are using the same version of Microsoft Access.

•	The Backup/Restore program will not backup this .mdb file. You must back it up manually.

•	In a network environment, if your System Administrator does not have your network set up to perform regular backups, you must also backup this file manually. The Backup/Restore program will not backup this .mdb file.


Notes about Creating Queries

•	Once you create the query, you can either export the data to Microsoft Excel, and/or save the new query.

•	When you save a query, the system does not save the actual data the query returns. The system simply saves a mechanism for accessing the desired data. Re-running a saved query will always return the most current data from your database.

•	You can create a new custom report based on the query you just created (and saved) using Microsoft Access.


Notes about Selecting Fields

•	As you select the fields to include in the query, they appear in the Selected Fields window on the Query Builder form.

•	Remember that if you select too many fields they may not fit on the report, causing the system to truncate amounts or other text.


Notes about Selection Criteria for Queries

•	Selection criteria is very useful if you want the same query to retrieve information for different criteria. For example, assume you want to retrieve project labor by project manager. First, you would create the desired query and set up the selection criteria to retrieve the project labor for a specific project manager. Then, you could make a copy of the query, save it under a different name, and change the project manager in the selection criteria.

You can also set up advanced selection criteria to further limit the rows retrieved by the query. For more information, see the topic titled Setting up advanced selection criteria to limit data retrieved .

•	When you define selection criteria, the Selection button appears in red to indicate that selection criteria has been defined for this query.

•	See also Specifying Selection Values.


Note about Setting Up Advanced Selection Criteria

When you define selection criteria, the Selection button appears in red to indicate that selection criteria has been defined for this query.


Note about Sorting Fields Included on the New Query

When you create a report using the toolkit, the system automatically formats the fields based on their position in this list. That is, the top field displays first on the report, the second field displays to the right of the first field, and so on.


Notes about Saving the Query

•	Once you save the query and exit the Query Builder form, you can no longer modify the query in the Query Builder. All modifications to the query will need to be made using Microsoft Access (outside the toolkit).

•	When you save a query, you are not saving the information that is gathered by the query. Each time you run a saved query, the query will retrieve the information currently reflected in your database.


Notes about Formatting the Data in Microsoft Access

•	Once you save the query and exit the Query Builder form, you can no longer modify the query in the Query Builder. All modifications to the query will need to be made using Microsoft Access (outside the toolkit).

•	When you save a query, you are not saving the information that is gathered by the query. Each time you run a saved query, the query will retrieve the information currently reflected in your database.


Note about Running the Report in the Toolkit

If Microsoft Access is set up to show Hidden Objects (on the View tab of the Options form), you will also be able to view two report templates. If you can see these report templates, it is imperative that you do not delete or modify these templates in any way. Doing so will cause your Advantage Reporting Toolkit to not function properly and you will have to reinstall the toolkit.


Notes about Changing the Reporting Period Using the Toolkit

•	Regardless of the period you are in, you always have access to all of the queries you have created.

•	When you initially start the Toolkit, the system displays the Period Selection form so you can specify the period. The Toolkit does not automatically open to the same period in which you are running the Advantage software.


Note about Using Data Categories

When you select the appropriate data category from which to build your query, a list of fields display in the Available Fields section of the Query Builder form.


Note about Step 6

You are probably wondering to yourself why the statement in step 6 works. After all, the project table is PR, not LD. However, employee labor detail is stored in the LD table. In other words, when an employee enters a timesheet, the labor and project numbers are stored together in the LD table, not the PR table.