Profit Tools Reporting, Tag Map

Data Dictionary of Commonly Used Tables


Profit Tools supports several methods of reporting and exporting of data from within the application. For advanced reporting we can also provide read-only access to commonly used tables for custom reporting with third party reporting tools such as Access or Crystal Reports.

The most basic reporting can be done from any screen in Profit Tools that provides a Grid View of the data. With any Grid View displayed on the screen a user can right-click on any open area in the grid view and receive options to Filter, Sort, Find or Export the data. These filters and sorts cannot be saved and will be cleared and reset once the data window is closed. This technique is most useful for a single ad hoc report or export of the data.

The next step in Profit Tools reporting is to create a Custom Excel or Word Report Template. These types of templates can be created by Profit Tools for an hourly charge, or can be created by any Profit Tools user who has write access to the \Profit Tools\Template folder on the server and a basic knowledge of data reporting. Custom reports can be created using a PowerSoft Report, Microsoft Word or Microsoft Excel to create report templates. The PSR report offers the most flexibility in reporting, but requires Sybase reporting software to create or modify the report. Word and Excel based reports offer a compromise between features and flexibility and can be updated without special Sybase software. These templates are used at various points within the Profit Tools software to generate reports relevant to that screen or module.

The last option for Profit Tools reporting is for custom third party reporting directly from the data in the database. This type of reporting provides the greatest flexibility, but requires a working good knowledge of the third party reporting tool, understanding of basic database reporting methods, and information about the Profit Tools Data Structure. Profit Tools can offer training and assistance with the Profit Tools data structure, but cannot offer assistance with the third party software used for the reporting. This is also the method generally used to provide a direct interface from Profit Tools to another third party application.

For users who have a basic knowledge of data reporting this document provides the Tag Map information and selected Data Dictionary information necessary to assist in each of these types of reporting.

Grid View Reporting


Grid view Reporting is simply the ability to further Sort or Filter information in an existing Profit Tools Grid View. This information can then be Printed or Exported to a document. The example shown below is from the Equipment Summary screen, but this can be done from any screen in Profit Tools displaying the Grid View. Some locations in Profit Tools that support this feature are: Billing, Shipment Summary, Search Results, Load Builder, Equipment Summary, Itinerary, Equipment Lease Types, Route Manager and Company Search.

Sort

Sorting can be accomplished by selecting Sort from the right-click menu and choosing the criteria to sort by. Additional sort criteria can be added by clicking the Add button. Sort items can be sorted by Ascending or Descending by the use of the Ascending check box.

Filter

Filtering can be accomplished by selecting Filter from the right-click menu and choosing the criteria to filter by. Additional filter criteria can be added by clicking the Add button. Each additional filter can be added using the And or the Or operator. And operations will tend to restrict the results, where Or operations will tend to expand the results.

Print

Select Print from the right-click menu to bring up the Windows Print dialog to print the current view

Export

The information in the Grid View can be saved and exported by choosing Export from the right-click menu. This information can be saved in a variety of formats, with Excel being the most common.

Custom Excel Report Templates

Custom Export Reports are generated using a Microsoft Excel spreadsheet. Profit Tools uses this spreadsheet as a template for information like Driver and Equipment reports. The template uses a list of item tags to link data from the Profit Tools program. Profit Tools populates each of the tags placed in the spreadsheet template with the corresponding information. Once the spreadsheet is created and populated, you have the option of saving the report as an Excel document. You can also edit any of the cells before printing.

Using Tags in Excel Spreadsheets

When using Excel base reports the report will include four basic components, Headings, a Topic Tag, Data Tags and an End Tag. Headings are text labels to describe the data below. The Topic Tag should be placed in a cell in the upper left side of the spreadsheet, and needs to be on a line by itself and indicates the type of report you are generating. The End Tag is placed in a cell on the lower right side of the spreadsheet to indicate the end of the data in a record.

Here is a simple Driver Itinerary report using an Excel template:

Driver Name / Unit Number / Total Miles / Hours / # of Shipments / Revenue
<use.itinerary>
<itineraryfor> / <powerunitlist> / <totalmiles> / <itineraryhours> / <shipments> / <freightrevenue> / <endtag>

This report is generally formatted as:

Text Heading 1 / Text Heading 2 / Text Heading 3
<Topic Tag>
<Data Tag 1> / <Data Tag 2> / <Data tag 3> / <End Tag>

Headings

Column headings must be put by themselves on the line or lines above the Topic Tag. The headings can be plain or formatted text and will be placed at the top of the report the same way they appear in the template.

Topic Tag

The Topic Tag is a control tag and indicates the general Topic or type of information you are reporting on. See the chart at the end of this document for information about individual topics. The Topic Tag provides the reporting engine information about what table or tables the data will be coming from. Topic tags will immediately follow any text Heading that you have entered and will not be printed with the report. The Topic used will be applied to all of the Data Tags immediately following the Topic Tag. Excel based reports are used to report on a single topic only. Topic Tags are in the format <use.topic> where ’topic’ is any of the valid topics referenced in the Tag List.

Data Tags

The Data Tags indicate the actual data that will be displayed in the report. The Data Tags will follow immediately after the Topic Tag. Each data tag will typically appear in an individual cell with a single row containing all of the Data for a single record of the report. The series of Data Tags (a record) will be immediately followed by an End Tag. Data tags are in the format <datatag> where ‘datatag’ is any valid Tag from the Tag Map.

End Tag

The End Tag is a control tag and indicates the end of the current data set. The End Tag is indicated by <endtag> and is only used to indicate the end of a record and will not be printed.

Custom Word Report Templates

Custom Word templates are typically accessed from the Document Selection window in Profit Tools. These templates are frequently used for documents like: Delivery Receipts, Custom Invoices, Quote Template, and Rate Confirmations. These custom reports are generated using a Microsoft Word document as a template for the report. The template uses a series of item tags to link data from the report to the Profit Tools database. Profit Tools populates each of the tags placed in the template with the corresponding information. Some of this information is retrieved directly from the database, and other Tags correspond to derived information that is calculated by Profit Tools as the report is generated.

Here is a simple Delivery report using a Word template:

Page Formatting

Word Templates can contain any standard Word page formatting on the report or document. Near the top of the document before any Data Tags the main Topic Tag needs to appear. Data Tags for this topic are considered In Topic and do not have to have the topic explicitly defined in the tag. In topic tags may appear any where within the document. Data Tags that are out of topic will need to have the topic explicitly defined in the tag. All out of topic tags must appear with tables and all tags within any single table must be for the same topic. An ending tag is not needed within the word document because the size of the table defines the end of the row.

Topic Tag

The Topic Tag is a control tag and indicates the main Topic or type of information you are reporting on within the current report. See the chart at the end of this document for information about individual topics. The Topic Tag provides the reporting engine information about what table or tables the data will be coming from. Topic tags will not be printed in the report and need to preceed any Data Tags. The Topic used will be applied to all of the In Topic Data Tags following the Topic Tag. Word based reports can be used to report on single or multiple topics, but there can only be one Topic Tag per report. Topic Tags are in the format <use.topic> where ’topic’ is any of the valid topics referenced in the Tag List.

Data Tags

The Data Tags indicate the actual data that will be displayed in the report. The Data Tags will follow after the Topic Tag. Data Tags for the topic defined in the Topic Tag are considered In Topic and may appear anywhere on the page. In Topic Data Tags do not need to explicitly define the topic within the tag. Data tags that display information for a different topic from the Topic Tag are considered out of topic. Data Tags that are Out of topic will need to have the topic explicitly defined in the tag. All out of topic tags must appear within tables and all tags within any single table must be for the same topic. This is frequently referred to as a sub report. Within the tables each data tag will typically appear in an individual cell, with a single row containing all of the data for a single record of the report.
Data Tags can follow any of the following formats:

In Topic <datatag>

Out of Topic <topic.datatag>

Sub Tag <topic.subtopic.subtag>

Where:

‘datatag’ is any valid data tag from the Tag Map

‘topic’ is any of the valid data topics referenced in the Tag List

‘subtopic’ is any data tag that references a Sub-topic

‘subtag’ is any of the data tags in the sub topic

Formatting of Tags

Special formatting for tags can be applied by adding a semicolon and then the format information after the tag name, but before the closing tag marker. Formatting can be applied to Character Tags, Numeric Tags, and Date/Time tags.

Character Tags

For character values the format can contain two masks. The first mask is the format for the data; the second mask is the format for a null value (no value in the database- not to be confused with zero or space).

Numeric Tags

For numeric value tags the format can have up to four different parameters. The parameters are for positive , negative, zero, and NULL values in that order. If you’re using multiple format values, you will need to delimit the segments with the ‘|’ character (shift - \) to avoid ambiguity with the tag delimiters. Numeric tags can contain 0 or # as numeric place holders as well as percent signs, decimal points, parentheses, and spaces which will display as entered in the mask. Use at least one 0

Formats

# Any number

0 A required number; a number will display for every 0 in the mask

‘%’ ‘.’ ‘()’ ‘ ‘ display as entered in the mask.

0.00

#,##0

#,##0.00

$#,##0;($#,##0)

$#,##0;-$#,##0

$#,##0.00;($#,##0.00)

##0%

##0.00%

0.00E+00

Examples

<datetag;Format=0.00|-0.00 >

<datetag;Format=0.00|(0.00)|0.00|N/A >

<datetag;Format=$#,##0|($#,##0)|0.00|N/A>

Note: Do not create formatting with only a space as this could cause formatting problems with the format. Use an actual value like ‘empty’, ‘none’ or ‘N/A’.

Date Tags

Date tags can be formatted by adding a semicolon and then the format information within the tag markers For date values the format can contain two masks. The first mask is the format for the data; the second mask is the format for a null value (no value in the database- not to be confused with zero or space).

Formats

d Day with no leading zero

dd Day with leading zero

ddd Day name abbreviation

dddd Day Name

m Month with no leading zero

mm Month with leading zero

mmm Month name abbreviation

mmmm Month name

yy Two digit year

yyyy Four digit year

d-mmm-yy 30-Jan-98

dd-mmmm 30-January

mmm-yy Jan-98