DataView Custom Report Writer DataView Custom Report Writer
Introduction
The DataView Custom Report Writer is a powerful, simple to use tool that allows the creation of custom reports within the accounting system. Custom reports may be viewed on screen, printed, or "exported," where they can be used in spreadsheets or word processors. Because the accounting system has the capacity to run multiple companies, DataView reports may be saved and shared with other companies. (They do not need to be recreated for each company.)
Having Someone Else Create Custom Reports
The software developer can be contracted to create custom reports in DataView. Also, both can provide assistance (billed separately from Technical Support Agreements, etc.) in trouble-shooting formula errors, etc.
Definitions
Basic Definitions
Each custom report is based on information already saved in the accounting system. Every bit of information, every transaction, every profile, and bit of history is saved in the form of lists called Files. For example: a list of employees is the Employee File. Files are composed of separate information or distinct transactions called Records. Every employee has an Employee Record. Each record includes several parts of information called Fields. An employee record will have an Employee Name Field and several other fields such as the employee's home address, social security number, phone, and deductions. Fields may contain Numeric data or Alphanumeric data (Text). Numeric data is a value, such as an amount or a percentage. Alphanumeric data (Text) may include letters and numbers.
Files Available by Module
In order to make report definitions very simple to create, DataView uses fields from existing report formats or View Types as a starting place. When a new report needs to be created, it is only a matter of going to the Accounting Module (Accounts Payable, Payroll, Job Costing, etc.) and then selecting the View Type most likely to contain the fields to be needed in the new custom report. The following are the Accounting Modules and View Types used by DataView:
General Ledger
Transaction Report
Detail by Account
Check Registers
Chart of Accounts Listing
Trial Balance
Accounts Payable
Posted Invoice Analysis
Posted Invoice Detail
Invoice Payment Detail
Vendor Lists
Contact Manager
Contact Listing
Activity Listing
Sales Orders
Quote Listing
Quote Detail
Sales Order Listing
Sales Order Detail
Accounts Receivable
AR Invoice Listing
AR Invoice Detail Listing
Receipt Listing
Job Status Reports
Customer Lists
Customer Ship-to Addresses
Payroll
Employee Listing
Earnings Detail
Employment History
Deduction Detail
Timesheet Proof Listing
Timesheet Detail
Federal Earnings
State Earnings
Local Earnings
Standard Deductions
Union Transactions
State Setup
Deduction Setup
Union Fringes
Union Listing
Union Tables
Job Costing
Job Cost Analysis Report
Job Summary Report
Job Cost Detail Listing
Job Listing
Cost Code Listing
Change Order Listing
Change Order Detail
Job Billing Phases
Equipment Costing
Equipment Analysis Reports
Equipment Detail Reports
Equipment Listing
Cost Category Listing
Usage Log History
Purchase Orders
Purchase Order Listing
Purchase Order Detail
Inventory
Item Listing
Cash Register
Cash Register Sale Listing
Cash Register Detail Listing
Miscellaneous
Notes
Utilities
Post Log
Field Selection
When creating a custom report, DataView presents a selected list of Fields used in the Records of a File. Using the lists in DataView's Custom Report Writer, fields can be selected for custom reports.
Record Filtering
If a File includes Records that are not needed in a custom report, DataView has the ability to exclude those Records using formulas called Filters. (A filter may be synonymous with or part of a Query, for those that prefer that term.) A Filter can be used to select only Records containing certain data and/or exclude Records containing certain data. When a Filter is set up, a set of conditions is defined. The Filter is sometimes called a Conditional Statement. For example: a Filter can be used to exclude all employees under the age of 55, to create a list of employees that might be interested in a retirement planning seminar sponsored by the company. Or, a Filter can be used to exclude all married employees for a list of single employees. Only the Records meeting the Conditions of the Filter that are selected.
Record Sorting
Using DataView's sorting tool, Records can be sorted from the highest value to the lowest or vice versa. The simple click of a mouse resorts the data immediately.
Calculated Fields
In addition to selecting Fields from the accounting system files, DataView can use formulas to calculate other Fields. For instance, if the report needs to show the age of an employee, a formula can be created that subtracts the employee's birth date from today's date and expresses the result in years. Calculated Fields must follow very specific formatting requirements to work properly, so each format is shown on the screen. Calculations may also contain conditions, such as doing a calculation only when certain Conditions exist. These are called Conditional Formulas.
When a Condition is met, a value of "1" is given. When a Condition is not met, a value of "0" is given. To make a Conditional Formula work, define the Condition and multiply the result by the formula. If the condition is met, the formula will calculate the correct number by multiplying it by "1". If the condition is not met, the result will be zero because the formula will be multiplied by "0". When a formula is completed, it can be tested by printing the preliminary report to screen. If there is an error, the formula can be corrected before going on.
DataView uses standard formulas and "syntax" (formula structures). Anyone capable of creating formulas in typical spread sheets, simple databases, etc. will find DataView's functions and capacities to be very familiar.
Defining a DataView Custom Report, Step by Step
Step 1
Step One is to gain access to DataView and select a View Type. There are two ways to do this. The first way is by clicking on the DataView icon located at the top of the screen. The DataView icon is the the accounting system icon with a small magnifying glass over it. Click on the icon and the following screen comes into view.
Notice that all of the Accounting Modules and all of their corresponding View Types are presented.
The second way to access DataView is from the Reports Menu in any of the Accounting Modules. The General Ledger DataView menu looks like this:
Notice that only the View Types for the General Ledger are available from the General Ledger Module.
As with all Reports, a menu of completed reports comes into view. In the bottom left corner there are two small icons. The first icon on the left adds the highlighted report to the My Favorites list of reports. The second icon makes a duplicate copy of the highlighted report.
Step 2
Step Two is to select the Fields that will be used in the custom report.
Arrows for Adding/Removing Fields
Each Left/Right arrow has a function in selecting or deselecting a Field:
Arrow to the Right moves a highlighted field into the Fields Used list.
Arrow to the Left moves a highlighted field from the Fields Used list.
Double Arrow to the Right moves all fields into the Fields Used list.
Double Arrow to the Left moves all fields from the Fields Used list
Each Up/Down arrow has a function in relocating a Field to a position on a report:
Arrow Up with a '1' in the top left corner moves a highlighted field up one line.
Arrow Down with a '1' in the bottom right corner moves a highlighted field down one line.
Double Arrow Up moves a highlighted field to the top of the list. This field will be the first column on the new report.
Double Arrow Down moves a highlighted field to the bottom of the list. This field will be the last column on the new report.
Limit on Number of Columns
Each DataView listing can consist of up to 20 Character columns and up to 20 Numeric columns.
Step Three is to Add Calculated Field(s) and Running Totals, if appropriate. This is an area where lots of flexibility is built into DataView. The process requires the completion of a formula that can include Field Names, Arithmetic Operators/Calculator Pad, Functions, and Constants. The formula standards and formats in DataView are the same as those used in most spread sheets. If it is necessary to call the Dealer or Technical Support for assistance creating a Calculated Field for a custom report, their services will be billed at hourly rates.
To add a calculated field, click the Calculated Field button. This window will be displayed.
Use this window to create a calculation for the value of the new field. This operation is defined in the following paragraphs.
Field Name (List Box)
To select a Field Name, double click on it and it will appear in the Formula for Calculated Field area.
Arithmetic Operators/Calculator Pad
To add an arithmetic function or a number to the Formula, click on the Calculator Pad. The keys of the Number Pad correspond to the functions in common spread sheets such as Microsoft Excel.
These Functions are available for the Filtering process (explained later), so not all of the Functions may be appropriate for Calculated Fields. Click once on any of the Functions and the symbol for that function will appear beneath the Functions Column. (Click twice and the Function will appear in the Formula area at the bottom of the screen.) If the Function requires a formatted formula, the format of that formula will appear beneath the Functions Column, ready to be completed. Note that all fields must have brackets, such as in "{Field Name}" surrounding the name of the field. A complete list of the Functions and their formats follow:
Abs will show up in the formula as Absolute Value - ABS(field)
Action: Prints the absolute value of a number, the value of a number when its sign (plus or minus) is removed.
Example: ABS({Cost Variance}) [Prints the value of "5,000" for positive or negative 5,000 Cost Variances]
Also see: Int or Round
Action: Selects records with a dual condition, first condition AND second condition.
Example: {Received to Date} > 5000 and {Cost Code Description} contains "concrete" [Selects records where the amount Received to Date is greater than 5,000 AND the Cost Code Description contains the word "concrete" anywhere in it] Note: Text must be enclosed with quotation marks.
Example: {Transaction Date} >= DATE(4,1,09) AND {Transaction Date} <= DATE(4,10,09) [selects all records with a transaction date between 4/1/09 and 4/30/09].
Also see not, List, or or
Choose will show up in the formula as CHOOSE(expression,value,value,value,condition)
expression: An arithmetic expression which determines which value to use. When this expression is solved, the answer must be zero or a positivie number.
value: A variable, constant, or expression for the procedure to return.
condition: A logical expression which determines which of the two value parameters to return. If no truevalue or falsevalue parameters are present, one (1) is returned when the expression is true, and zero (0) is returned when the expression is false.
The CHOOSE procedure evaluates the expression or condition and returns the appropriate value. If the expression resolves to a positive number, that number selects the corresponding value parameter for the CHOOSE procedure to return. If the expression evaluates to an out-of-range number, then CHOOSE returns the last listed value.
When the condition evaluates as true (1), then CHOOSE returns the first listed value. When the condition evaluates to false, then CHOOSE returns the second listed value.
If no values are present, CHOOSE returns one (1) for true, and zero (0) for false.
The CHOOSE function is used to return a value based on some attributes of an extisting field. It is useful in DataView because it can produce a conditional result in a one-line formula. Let's say you want to create a field that contains the difference between the values in two of your fields, but you don't want a negative number. So if the difference comes out to be negative, you want zero. If we map this out in a simple IF THEN statement it would look something like this:
IF {Invoice Amount} - {Retention Amount} >= 0 THEN {MyNewField} = {Invoice Amount} - {Retention Amount} ELSE {MyNewField} = 0.
Using the CHOOSE function, you can do it this way:
CHOOSE ( {Invoice Amount} - {Retention Amount} >= 0 , {Invoice Amount} - {Retention Amount} , 0 )
Examples:
CHOOSE (4,'A','B','C','D','E') returns 'D', or the 4th value
CHOOSE (2 > 1,'A','B') returns 'A'. 2 > 1 evaluates to True(1), so the function returns the first value
CHOOSE (1 > 2,'A','B') returns 'B'. 1 > 2 evaluates to False(0), so the function returns the second value
CHOOSE (1 > 2) evaluates to false(0) and since there are no values listed, the function returns zero
CHOOSE ({Invoice Date} % 7 + 1,'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') This will return the day of the week of the {Invoice Date} value
CHOOSE ({Invoice Date} % 7 + 1,'Sun','Mon','Tue','Wed','Thu','Fri','Sat') This will do the same, but return an abbreviated day
CHOOSE ({Invoice Date} % 7 % 6 + 1,'Weekend','Weekday')
CHOOSE (INRANGE({Invoice Date} % 7,1,5),'Weekend','Weekday')
CHOOSE (INLIST({Gender},'M','F'),'Male','Female','Unknown')
Action: Selects records with certain text anywhere within a text field.
Example with a text field: {Cost Code Description} contains "concrete" [Selects records where the Cost Description Description contains the word "concrete" anywhere in it] Note: Text must be enclosed with quotation marks.
Also see is equal to
Date will show up as DATE(mm,dd,yy)
Action: Prints the date in mm/dd/yy format.
Example: Date(11,24,08) [Means November 24, 2008]
Also see Day , Month , Year, or Today
Day will show up as DAY(date field)
Action: Prints only the "day" of a date.
Example: DAY({Transaction Date}) [Prints "22" when the date is 11/22/98]
Also see Date , Month , Year, or Today
Int will show up in the formula as Integer of - INT(field)
Action: Prints the amount to the left of the decimal of any positive number, ignoring the decimal or bumping negative numbers up to the next integer
Example: INT({Amount}) [Prints "1,500.00" when the number is 1500.99]
Example: INT({Amount}) [Prints "15.00" when the number is -14.59]
Also see Abs or Round
is equal to (will show in the formula up as = )
Action: Selects records with a value equal to a defined amount or a second field.
Example with a numeric field: {Received to Date} = 5000 [Selects records where the amount Received to Date is 5,000]
Example with a numeric field: {Received to Date} = {Cost to Date} [Selects records where the amount Received to Date equals the amount of Cost to Date]
Example with a date field: {Transaction Date} = DATE(11, 22, 98) [Selects records where the date is November 22, 1998]
Example with a text field: {Workers Comp Craft Code} = "A" [Selects records where the Workers Craft Code is an A] Note: Text must be enclosed with quotation marks.
Also see contains, is greater than, or is less than.
is greater than (will show up in the formula as > )
Action: Selects records with a value greater than a defined amount or greater than a second field.
Example with a numeric field: {Received to Date} > 5000 [Selects records with a value over 5,000]
Example with a numeric field: {Received to Date} > {Cost to Date} [Selects records where Received to Date has a value greater than Cost to Date]
Example with a date field: {Transaction Date} > DATE(11, 22, 98) [Selects records where the date is after November 22, 1998]
Example with a text field: {Workers Comp Craft Code} > "R" [Selects records where Workers Comp Craft Code is greater than the letter R] Note: Text must be enclosed with quotation marks.
Also see contains, is equal to, or is less than
is less than (will show up in the formula as < )
Action: Selects records with a value less than a defined amount or less than a second field.
Example with a numeric field: {Received to Date} < 5000 [Selects records with a value less than 5,000]
Example with a numeric field: {Received to Date} < {Cost to Date} [Selects records where Received to Date is less than Cost to Date]
Example with a date field: {Transaction Date} < DATE(11, 22, 98) [Selects records where the date is before November 22, 1998]
Example with a text field: {Workers Comp Craft Code} < "M" [Selects records where the Workers Comp Craft Code is "A" to "L". Note: Text must be enclosed with quotation marks.
Also see contains, is equal to, or is greater than
Left will show up in the formula as LEFT(field, characters)
Action: Selects records where the specified number of left characters in a field meet a condition.
Example with a text field: LEFT({Workers Comp Craft Code},1) = "F" [Selects records where the first character of the Workers Comp Craft Code is "F"]
Example with a text field: LEFT({Workers Comp Craft Code},3) = "FRA" [Selects records where the first three characters of the Workers Comp Craft Code is "FRA"] Note: Text must be enclosed with quotation marks.
Also see Right or Mid
Length will show up in the formula as Length - LEN(field)
Action: Prints a value, which is the number of characters in a text field.
Example: LEN({Subcontractor Name}) [Prints the number "16" when the name is "Plumber's Helper"]
Also see Left, Right or Mid
List will show up in the formula as LIST(field,"value","value", "value")
Action: Selects records with more than one possible value. This is a variation on the OR condition, useful when there are more than two values being considered.
Example: LIST({Job Number}, 101, 108, 745) [Selects records where the Job Number is 101 OR 108 OR 745]
Also see and or or
Lower will show up in the formula as Lower Case -LOWER(field)
Action: Converts text in a selected field to all Lower Case letters.
Example: :LOWER({Subcontractor Name}) [Prints "plumber's helper" when the name is "Plumber's Helper"]
Also see Upper
Mid will show up in the formula as MID(field, start,(characters))
Action: Selects records where a specified number of characters in the middle of a field meet a condition.
The location of the character(s) or the starting place is number of characters from the left side of the field.
The number of characters to be evaluated, including the starting character, is referred to as "characters".
Example: MID({Cost Code Description}, 4, 2) = "LE" looks at the 4th and 5th letters (or characters) in the Cost Code Description field. (It starts at the 4th character and includes two characters.) [Selects records where the 4th character is "L" and the 5th character is "E" in the Cost Code Description field, such as in the word "SEALER"] Note: Text must be enclosed with quotation marks.
Month will show up in the formula as MONTH(date field)
Action: Prints only the "month" of a date.
Example: MONTH({Transaction Date}) [Prints "11" when the date is 11/22/98]
Also see Date , Day, Year or Today
Action: Selects records that do not meet a defined condition.
Example with a numeric field: {Received to Date} not = 5000 [Selects records where the amount Received to Date is not 5,000]
Example with a text field: {Workers Comp Craft Code} not = "R" [Selects records where the Workers Comp Craft Code is not "R"} Note: Text must be enclosed with quotation marks.
Also see and, List, or or
Action: Selects records with a dual condition, first condition OR second condition.
Example: {Received to Date} > 5000 or {Cost Code Description} contains "concrete" [Selects records where the amount Received to Date is greater than 5,000 OR the Cost Code Description contains the word "concrete" anywhere in it] Note: Text must be enclosed with quotation marks.
Example: {Transaction Date} < DATE(4,1,09) OR {Transaction Date} > DATE(4,10,09) [excludes all records with a transaction date between 4/1/09 and 4/30/09].
Also see and, List, or not
Right will show up in the formula as RIGHT(field, characters)
Action: Selects records where the specified number of right characters in a field meet a condition.
Example with a text field: RIGHT({Workers Comp Craft Code},1) = "X" [Selects records where the last character of the Workers Comp Craft Code is "X"] Note: Text must be enclosed with quotation marks.
Example with a text field: RIGHT({Workers Comp Craft Code},3) = "ERS" [Selects records where the last three characters of the Workers Comp Craft Code is "ERS"] Note: Text must be enclosed with quotation marks.
Also see Left or Mid
Round will show up in the formula as ROUND(field,places)
Action: Prints the rounded amount of a number, to a selected place.
Example: ROUND({Amount, .01}) [Prints "7.56" when the number is 7.5627]
Example: ROUND({Amount, .1}) [Prints "5,647.60" when the number is 5,647.56]
Example: ROUND({Amount, 1}) [Prints "5,648.00" when the number is 5,647.56]
Example: ROUND({Amount, 10}) [Prints "5,650.00" when the number is 5,647.56]
Example: ROUND({Amount, 100}) [Prints "5,600.00" when the number is 5,647.56]
Example: ROUND({Amount, 1000}) [Prints "6,000.00" when the number is 5,647.56]
Also see: Abs or Int
Upper will show up in the formula as Upper Case - UPPER(field)
Action: Converts text in a selected field to all Upper Case letters.
Example: UPPER({Subcontractor Name}) [Prints "PLUMBER'S HELPER" when the name is "Plumber's Helper"]
Also see Lower
Year will show up in the formula as YEAR(date field)
Action: Prints only the "year" of a date.
Example: YEAR({Transaction Date}) [Prints "2009" when the date is 11/22/09]
Also see Date , Day, Month or Today
The Constants are also shared with the Filtering process, so not all of the Constants may be appropriate for Calculated Fields. Click once on any of the Constants and an explanation of that Constant will appear beneath the Constants Column. (Click twice and the Constant will appear in the Formula area at the bottom of the screen in its proper format, ready to be completed.) A complete list of the Constants and their formats follow:
CCL is Cost Code Length
Example: CCL will print "6" when length of the Cost Code is 6.
CCMethod means Completed Cost Method
Example: CCMethod will print "1" when the Estimated Completed Cost Method is set up as "Computer Calculates by Percent Complete".
Example: CCMethod will print "2" when the Estimated Completed Cost Method is set up as "User Enter Final Completion Cost".
Example: CCMethod will print "3" when the Estimated Completed Cost Method is set up as "User Enters Additional Cost to Complete".
CurFP means Current Fiscal Period
Example: CurFP will select records with the Current Fiscal Period
Example: (CurFP - 1) will select records with a Fiscal Period which is 1 fiscal period less than the Current Fiscal Period.
CurEarn means Current Earning Account
Prints the GL Account Number assigned to Current Earnings.
Example: CurEarn will print the "39999" if the GL Account Number of the Current Earnings Account is set up as 39999.
DepSeg means GL Account Department Segment
Prints the number of digits in the Department Segment of the GL Account Number.
Example: DepSeg{GL Account} will print "2" if the number of digits set up in Department Segment of GL Accounts is set up as 2.
JNL means Job Number Length
Prints the number of digits in the Job Number Field.
Example: JNL will print "7" if the length of the Job Number is 7.
MAcct means Missing Accounts
Prints the GL Account Number assigned to Missing Accounts.
Example: MAcct will print "99999" if GL Account Number for Missing Accounts Account is set up as 99999.
RetEarn is Retained Earnings Account
Prints the GL Account Number assigned to Retained Earnings.
Example: RetEarn will print "39000" if the GL Account Number of the Retained Earnings Account is set up as 39000.
Seg1 means GL Account Segment 1 Digits
Prints the number of digits set up in the First Segment of the GL Account Numbering System.
Example: Seg1{GL Account} will print "5" if the number of digits set up in First Segment of GL Accounts is set up as 5.
Seg2 means GL Account Segment 2 Digits
Prints the number of digits set up in the Second Segment of the GL Account Numbering System.
Example: Seg2{GL Account} will print "2" if the number of digits set up in Second Segment of GL Accounts is set up as 2.
Also see Seg1 or Seg3
Seg3 means GL Account Segment 3 Digits
Prints the number of digits set up in the Third Segment of the GL Account Numbering System.
Example: Seg3{GL Account} will print "3" if the number of digits set up in Third Segment of GL Accounts is set up as 3
Also see Seg1 or Seg2.
Prints Today's Date.
Example: Today will print "11/22/08" when today's date is November 22, 2008.
Example: Today + 1 will print "11/23/08" when today's date is November 22, 2008.
Also see Date , Day, Month or Year.
Once the Calculated Field value formula has been established, click the OK button and the Change Field Attributes (Step 4 below) window will appear for creating a Column Name and other attributes.
Press the Add Running Total button and the following screen comes into view.
Select the Column for the Running Total from the dropdown list and press the OK button.
Step Four is to Change Field Attributes, if appropriate. There are three different types of Fields that may be included in a custom report, a Text Field, a Numeric Field, and a Date Field. Each has its own options. If a Text Field is selected, the following screen comes into view:
Formatting a Text Field
Column Heading
This is the title that will appear at the top of the heading on each page when displayed on screen or printed.
Justification
When the Field Type is a Text Field, there is only one attribute to set, Justification. Should the data be justified to the left, the right, or centered? Use the radio button to select the format. Text fields are traditionally justified to the left.
When a Numeric Field Type is selected, the following screen comes into view.
Column Heading
This is the title that will appear at the top of the heading on each page when displayed on screen or printed. Tip: Change the name promptly so that it is not overlooked. The Column Heading also serves as a reminder as to what the Calculated Field is trying to accomplish.
Justification
Should the data be justified to the left, the right, or centered? Numbers are traditionally justified to the Right. Use the radio button to select the format.
Formatting
Decimal Places
Format the number of decimal places the number will display. Be aware of how many decimals the data has in its unformatted state. If a two decimal amount is formatted with 9 decimals, it adds 7 zeros, wasting valuable space and adding nothing to the report.
Percent Sign (Check Box)
If the numeric field being formatted is a percentage, a Percent Sign can be added by checking this box.
Dollar Sign (Check Box)
If a numeric field is being formatted as a dollar amount, a Dollar Sign can be added by checking this box.
Commas (Check Box)
If the value of a numeric field exceeds 999, commas can be added by checking this box. If the box is not checked, spaces will be used instead of commas. Commas are conventional, but spaces often make a report easier to read.
Blank if Zero (Check Box)
If a numeric field has values of zero, a list of zeros can be distracting. Check this box to NOT print zeros when they occur.
Print Column Total (Check Box)
If a numeric field is being formatted, a total can be added at the bottom of the column. Check this box to have a total printed.
Special Option for Totaling Calculated Percentage Fields
If this is a Calculated Field, an additional option will appear when the "Print Column Total" box is checked.
Total Column Values
If this radio button is selected, the value of the column total will be the sum of all the values in the column.
Calculate
If this radio button is selected, the same formula used to calculate the individual values in this column will be used to calculate the total. For example, if this column is a Percentage and the formula for calculating this column value is {Amount1}/{Amount2}, the column total will be calculated in this manner: (Amount1 Column Total) / (Amount2 Column Total).
True/False Field (Check Box)
If an 'either/or' field is being formatted, what actually prints can be formatted to print in three unique ways. True/False formats are ideal for special fields with limited information, referred to as 'either/or fields', such as Reconciled/Not Reconciled, Paid/Not Paid.
The accounting system stores data in these "either/or" fields as '1' for True and '0' for False. For example: Paid Invoices: 1 for Yes, 0 for No. Rather than have a column with zeros and ones, the field can be formatted to print True/False, Yes/No, or with a Checkmark if True by selecting a Radio Button.
Formatting a Date Field
Date Format
Format the date, selecting from the drop down list of possible formats. Check the Blank if No Date Check Box (covered in the picture below by the list of Date Formats) to leave blanks where the data has no date.
Step 5
Step Five is to Filter out the records that will not be needed. There are two choices for creating a record filter. The Simple Filter is easy to use and very powerful. The Extended Filter allows for more complex filtering formulas.
Simple Filter
Select Simple Filter and these fields will be displayed:
The list box shows any filter elements that have already been entered. To add a new filter element or change an existing one, click the Add or Change button. This window is displayed:
If this is the first element being entered, the AND/OR selection box will not be displayed; otherwise, select whether this element will be and AND or an OR statement.
Field Name
Select the Field Name for this filter element from the drop-down list.
Operator
Select the Operator for this filter element from the drop-down list. Depending on the type of operator selected, a different set of fields will be displayed at the bottom of the window. Some operators, when chosen, will cause a "Constant" checkbox to be displayed. For example, if the "Transaction Date" field is selected and the "is equal to" operator is selected, the user can choose to either check the "Constant" box and select a constant (such as "Today's Date") or uncheck the "Constant" box and pick a date using the calendar lookup.
Click the OK button to save this filter element.
Filter Display Box
As filter elements are added, the actual filter statement will be displayed in the Filter Display Box at the bottom of the window.
Extended Filter
When "Extended Filter" is selected, the screen looks very much like the screen for creating Calculated Fields. The only exception is the Clear Filter button, which is used to clear the Filter formula and start new. Hint: When a complicated formula is finished, copy it into a word processor and print it out. Check it to make sure all of the left parenthesis are matched with right parenthesis and that parenthesis separate all individual functions. In short, validate the formula for errors before running a new report. This can be a time consuming process. If it is necessary to call the Dealer or Technical Support for assistance creating a Filter Formula for a custom report, their services will be billed at hourly rates.
Go to Step 3 for detailed instructions on each of the Functions and Constants.
Step Six is to create the Sort definition. Click on the Sort Records tab and the following screen comes into view:
Arrows for Adding/Removing Fields
Each Left/Right Arrow has a function in selecting or deselecting a Field:
Arrow to the Right moves a highlighted field into the Fields Used list.
Arrow to the Left moves a highlighted field from the Fields Used list.
Double Arrow to the Left moves all fields from the Fields Used list
Arrows for Changing Sort Priorities
Each Up/Down Arrow has a function in relocating a Field to a position in the Sort Priority. Those at the top are sorted first. Those that follow are secondary sorts under the preceding sort.:
Arrow Up with a '1' in the top left corner moves a highlighted field up one line in Sort Priority.
Arrow Down with a '1' in the bottom right corner moves a highlighted field down one line in Sort Priority.
Double Arrow Up moves a highlighted field to the top of the Sort Priority.
Double Arrow Down moves a highlighted field to the bottom of the Sort Priority.
+/- or Ascending/Descending (button)
Click this button to change the sort order for the highlighted field from Plus to Minus, Ascending (lowest to highest value) or to Descending (lowest to highest value.)
Click this button to have the custom report print a heading for the highlighted sort field. If a Heading for each Cost Code is wanted, click here. Tip: When a column will have its data repeated numerous times, it may be better to have that data print only when it first appears and then when it changes after that. To set this up, add that column to the Sort Order and click the Heading button. Note: The repeated data will show on the Screen version of the report, but not the Printed version.
Click this button to have the custom report print a subtotal for the highlighted sort field. If a Subtotal for each Cost Code is wanted, click here.
Click this button to have the printed report move to a new physical page after this subtotal..
Step 7
Step Seven is to adjust the Print Setup. Click on the Print Setup tab and the following screen comes into view:
Paper Size (radio button)
Select either Letter Size paper (8 1/2 by 11), Legal Size paper (8 1/2 by 14), or Ledger Size paper (11 by 17).
Orientation (radio button)
Select either Portrait (Upright printing) or Landscape (Sideways printing).
Double Space Report (Check Box)
Check this box to double space the lines of the report. This may be of value when using small fonts or reports with many columns.
Print Report Filter (Check Box)
If this box is checked the full text of the report filter will be included at the bottom of the report.
When this button is pressed, the following screen comes into view.
No Logo on Report
No Logo or company information will be printed.
Center
Use this setting to print the logo centered at the top of the report.
Left
Use this setting to print the logo to the left of the report title at the top of the report.
Logo File Name
Press the lookup button and select the location of the desired logo file.
The Readability Bars button lets the user select a different color for the readability bars. There is the option to choose gray, blue, green, or yellow bars. Try adding different bars to a report and see what the preview of the printed report looks like until the desired report is seen.
Step 8
Step Eight is to press the OK button. The new Custom Report will begin to filter, calculate, and sort the records. This may take only seconds or several minutes, depending on the number or records it has to read, the filtering required, the calculations to make, and the sorting to be done. Timing is a function of the speed of the computer. When the process is done, a preliminary Custom Report will come into view.
If a formula or sort definition contains an error, the following screen will come into view:
If this error message comes up, copy each problem formula into a word processor and check it for validity. Make sure all of the left parenthesis are matched with right parenthesis and that parenthesis separate all individual functions. This can be a time consuming process, but it is necessary. If it is necessary to call the Dealer or Technical Support for assistance creating a Filter Formula for a custom report, their services will be billed at hourly rates.
Ignore (button)
Click on the Ignore button to go ahead with the Custom Report process, ignoring the fact that at least one formula will give inaccurate or incomplete information.
Cancel (button)
Click on the Cancel button to stop the Report process.
Step 9
Step Nine is doing the finishing touches, modifications, font selection, colors, and final testing. All of this is done on screen before a single page is printed. The preliminary Custom Report may look as follows:
There are two adjustments that can be made to the report using only the mouse:
Resizing Columns: To resize a column, click on the column divider (the line between columns) and drag the divider to create a wider or narrower column.
Instant Sorting: To instantly sort a column, click on the column header (the name of the column) and the column is automatically sorted by that column in ascending order (lowest to highest value). Click on it again and the column is automatically sorted by that column in descending order (highest to lowest value). If a column is sorted, either using the instant sorting or by specifying a preset sort, a Λ OR V will display beside the name of the sorted column. The V indicates a descending sort and the Λ indicates an ascending sort.
Modify View (button)
Click this button to go back to the prior steps to modify Calculations, Filters, or Sorts. Tip: This is a good time to check on the Print Setup. The ideal report matches the paper it is printed on, with neither too much white space or too little (crowded columns).
Change Font (button)
Click on this button to access the standard Font and Color options which are available in typical word processors and spread sheets. Tip: Be careful not to select fonts that are too large because of the size limitations of the screen. Also be careful not to select fonts that are too small because they are difficult to read. Fonts require self discipline, the plain ones are easiest to read. Color is only useful if a color printer is being used.
Restore Font (button)
Click this button to undo what was done after clicking on the Change Font button.
Reset Sort (button)
If one or more of the column headings has been clicked to perform an Instant Sort as described above, click this button to restore the sorting native to this view.
Print View (button)
Press this button to print the report to screen. This tests how it will actually look before it is printed.
Export View (button)
Click this button to save the report in a file to be exported to other companies or shared with others. Note: This exports the actual data. The export option allows the following options
Export File Name
This is the same as saving any file using any word processor or spread sheet. A path and file name need to be entered here.
Export File Type (radio button)
Select either Comma Delimited or Tab Delimited (for later import into a spread sheet) or Fixed Length Fields (for later import into a word processor).
Export Headings (Check Box)
Check this box to include the Headings in the exported data.
Export Totals (Check Box)
Check this box to include the Totals in the exported data.
Preview Export File (Check Box)
Check this box to preview the exported data in Note Pad when it has been created. The data (the new Custom Report) in Note Pad can be Selected and Copied to the Clipboard. Open a Spread Sheet and the data can be easily Pasted into it.
Open with Excel (button)
Click this button to open a copy of this view using Microsoft Excel. The system will immediately export a copy of the data and open the copy using Excel.
View Highlighted Record (button
Click this button to view more detail about the highlighted line item. An overview screen concerning the selected item will be displayed.
Import/Export Views (DataView Reports)
Once a custom report has been defined using DataView, it can be saved for later use. A saved report can also be imported into another company's data and used there.
Export This View (button)
Click on the Export This View button to create a text file description of the currently highlighted DataView Report that can be used to re-create this report in another installation of the accounting software. The file can be immediately emailed if desired. A standard file saving Windows dialog box will appear, allowing the user to save the file to any location. The file extension for exported views is .dvx, and should not be changed. After the file is saved, the following question will appear.
If Yes is selected, the following standard email editor will be displayed with the exported file attached. For more information about sending email using this window, refer to the Contact Manager: Activities help topic.
Import Views from External Source (button)
Click on the Import Views button to copy DataView reports from an external source. The following selector will appear.
Click this button to import DataView reports from a different company into the data for this company. The following window will appear.
Select Source (button)
Click on the Select Source button to locate DataView reports saved under other folders (for other companies.)
Clicking this button will allow an exported DataView Report text file to be imported. A standard Windows file open dialog box will appear.
Find the desired DataView Export (.dvx) file and click the Open button. The exported DataView report will be imported into this company data set.
