MTD 117-530A User's Guide

TRAVELTRAX DESKTOP REPORTING Training Guide v6.5 March 26, 2013

Page 10 Confidential TRX, Inc. Credit Card ETL (CreditCardMan) Credit Card ETL is the data management tool you’ll use for credit card consolidat

Page 100 Confidential TRX, Inc. Report 4 continued Design 1. Start the Report Design. 2. Set up the Page Layout as Portrait. 3. S

Page 101 Confidential TRX, Inc. Report 4 continued 1. Add Group Band – This will add another band to the design and put the Top Clients field

Page 102 Confidential TRX, Inc. Report 4 continued 5. In a blank row below the data field of the Top Clients’ column, click until the column t

Page 103 Confidential TRX, Inc. Report 4 continued 20. Add Final Totals. a. Previous totals are now sub-totals. b. Copy sub-totals fields (a

Page 104 Confidential TRX, Inc. Linking Reports With multi-section reports, sections may have data from different tables in the database or data

Page 105 Confidential TRX, Inc. One to Many Link Overview One to Many Link should be used when the linked sections’ result tables do not match (

Page 106 Confidential TRX, Inc. Data Dictionary Help Pages Type Origin City Code Destination City Code Origin City Name Destination City

Page 107 Confidential TRX, Inc. Type Origin Region Name Destination Region Name Return Indicator (is not null) Segment Value Intl/Dom Ind

Page 108 Confidential TRX, Inc. Tips for Segment Reporting: If you want to use the segment value that comes from your back office system you ca

Page 109 Confidential TRX, Inc. Report 5 – Linking Report This report has ticket data and segment data. They need to be put in separate section

Page 11 Confidential TRX, Inc. Process Scheduler (Auto Processor) Process Scheduler is a highly scalable system for securely transferring, proce

Page 110 Confidential TRX, Inc. Report 5 continued Query 5. Enter the following data fields in section 1.  ID001 – Record Key  ID002 – I

Page 111 Confidential TRX, Inc. Report 5 continued 10. Place the following data fields in the Section 2 Query:  TS001 – Record Key  TS002 –

Page 112 Confidential TRX, Inc. Report 5 continued 3. To build the design for this report, you can construct it manually by using the design t

Page 113 Confidential TRX, Inc. Report 5 Design continued At this point, your design should look like the following: To make our design loo

Page 114 Confidential TRX, Inc. Report 5 Design continued 7. Delete the 3 objects from the page band. We will enter our own headers later. 8

Page 115 Confidential TRX, Inc. Finished report

Page 116 Confidential TRX, Inc. Report 5 continued Create link in Paradox If your Data Model did not show the links, you can create them manua

Page 117 Confidential TRX, Inc. Report 6 – One to One Link This will be a three section report with sections 2 and 3 linking back to section 1.

Page 118 Confidential TRX, Inc. Report 6 continued 1. Go to the Main Menu and create a new report called REPORT6. 2. Report Properties  Ti

Page 119 Confidential TRX, Inc. Report 6 continued 8. Copy Section 1 by clicking on this icon . Each of the three sections in this report wi

Page 12 Confidential TRX, Inc. Hotel Name Normalization The Hotel Name Normalization tool is used to take all the different ways that a hotel pr

Page 120 Confidential TRX, Inc. Report 6 continued Add a 3rd section for the exchange information. 1. Copy this section. 2. Change the Sect

Page 121 Confidential TRX, Inc. Unioning The unioning of sections is necessary in order to add data from subsequent sections into a previous sec

Page 122 Confidential TRX, Inc. Report 7 - Union In this report we want to find out the total amount that a client spent on air tickets, car, an

Page 123 Confidential TRX, Inc. Report 7 continued Query – Section 3 This section holds the hotel spend. 11. Add Section 3 Properties:  Date

Page 124 Confidential TRX, Inc. Report 7 continued 21. From the query page, select the Report Design button. This will put you in the Edit Des

Page 125 Confidential TRX, Inc. Report 7 continued Rpthdng – This tab contains the data that comes from the profile. It contains the Profile I

Page 126 Confidential TRX, Inc. Report 7 continued Detach Excel Design You could design the entire report while attached to the TTX Desktop Rep

Page 127 Confidential TRX, Inc. Report 7 continued 7. Reference the data you’ll need for your headers.  In A1 through E1, type =  Select

Page 128 Confidential TRX, Inc. Report 7 continued Re-attach Excel Design Since you created (or changed) the design outside the TTX Desktop Rep

Page 129 Confidential TRX, Inc. Report 7 continued 20. Save As and save the report to C:\WINDOWS\Temp\TMANPRIV. 21. Instead of entering a name

Page 13 Confidential TRX, Inc. Currency Converter (MoneyMan) Currency Converter is a tool that will allow a user to enter currency code tables i

Page 130 Confidential TRX, Inc. Report 8 In this report, you’ll learn how to create an Excel design using VBA code within Excel. You will learn

Page 131 Confidential TRX, Inc. Report 8 continued 5. Enter the data fields and filters necessary to create the report you see on the screen i

Page 132 Confidential TRX, Inc.  _______________________________  _______________________________

Page 133 Confidential TRX, Inc. Report 8 continued 13. Determine what columns/rows you’ll need and add the column/row headers.  Since the co

Page 134 Confidential TRX, Inc. Report 8 continued 17. Create a sub routine.  Sub Name() – Type the word Sub then type in the name of the sub

Page 135 Confidential TRX, Inc. Report 9 This is a report to show the Airline Spend for Month to Date and Year to Date. We will select Excel as

Page 136 Confidential TRX, Inc. Report 9 continued 35. Close Excel. 36. From the Query page, select the Report Design button (or Tools – Repo

Page 137 Confidential TRX, Inc. Report 9 continued 45. Select cell D2 in the Main sheet and reference the bookings from Sect2 for that first a

Page 138 Confidential TRX, Inc. Report 9 continued 51. Create VBA code – Right click any tab/sheet at the bottom of the page and select View C

Page 139 Confidential TRX, Inc.  Close out of Excel

Page 14 Confidential TRX, Inc. TTX Desktop Reporting (TravelMan) TRAVELTRAX Desktop Reporting is a data reporting tool. This tool allows your c

Page 140 Confidential TRX, Inc. Report 9 continued 57. Go back to Desktop Reporting, and go to this report and Edit Design (Design Report).

Page 141 Confidential TRX, Inc. Batch Generator The Batch Generator is where you setup which profiles and associated reports you want to genera

Page 142 Confidential TRX, Inc. Create a Batch Exercise In this exercise you will learn how to create a new batch from which you can run existin

Page 143 Confidential TRX, Inc. Batch Generator Exercise continued 7. From the main batch window, select Batch, then Properties. 8. Select t

Page 144 Confidential TRX, Inc. Prompts This section will show you how to create prompts in a report. To give a user the ability to select spec

Page 145 Confidential TRX, Inc. Pick List Prompt A Pick List prompt will display a list of values that the user can select from. This option

Page 146 Confidential TRX, Inc. Pick List report: A Pick List report is a separate report you create to run a query and return data that will

Page 147 Confidential TRX, Inc. Char Index Prompt The CHARINDEX function is used to search a character string for a specific character or set of

Page 148 Confidential TRX, Inc. SQL Statements SQL is used when creating calculations and filters. This section will show you the most common S

Page 149 Confidential TRX, Inc. Customization Within the Desktop Reporting tool, each company is able to customize certain things. They can cre

Page 15 Confidential TRX, Inc. Data Quality Manager (QualityMan) Data Quality Manager is a state of the art database management program. It com

Page 150 Confidential TRX, Inc. Filter Assist If you use the same filters over and over again in reports, you may want to put them into the Filt

Page 151 Confidential TRX, Inc. Reporting Periods This screen contains the master Reporting Periods for TRAVELTRAX. Batch Generator uses these

Page 152 Confidential TRX, Inc. Category Editor Use this screen to add, copy, or delete categories. You can create categories for both Profile

Page 153 Confidential TRX, Inc. Lookups The Lookup option allows you to build a new field based on an existing field in the Data Dictionary. On

Page 154 Confidential TRX, Inc. Building a Lookup continued 6. Drag ID025 - InternationalInd to the left window. 7. Click the Add values for

Page 155 Confidential TRX, Inc. Building a Lookup continued Completed Lookup Your Lookup window should now look like the figure below. 10

Page 156 Confidential TRX, Inc. Lists This option allows you to build a list of data that can be used to build SQL expressions. Lists can be u

Page 157 Confidential TRX, Inc. 4. Click the Add Value button and enter one value. Repeat this step for every value that needs to be added.

Page 158 Confidential TRX, Inc. Import/Export You can export or import reports, profiles, lookups, scripts, batches, lists, and dictionaries to/

Page 159 Confidential TRX, Inc. Import Choose this option to Import TRAVELTRAX Reports, Profiles, Batches, etc. that were exported from your or

Page 16 Confidential TRX, Inc. Airline Contract Management TRX's Airline Contract Management program provides an end to end solution for co

Page 160 Confidential TRX, Inc. Portal The TRAVELTRAX Portal gives you the ability to license your TRAVELTRAX software, review incidents, subm

Page 161 Confidential TRX, Inc. Licensing The licensing section allows you to re-license your TRAVELTRAX software. To use this tool, please fol

Page 162 Confidential TRX, Inc. Documentation In this section you will find documents that cover various TRAVELTRAX subjects. Click on the name

Page 163 Confidential TRX, Inc. Create a Support Request Below are the steps to take when you need to create a TRAVELTRAX Incident/Ticket. Thes

Page 164 Confidential TRX, Inc. Create a Support Request continued 3. Click on the Support Request tab. This will display the Support Request

Page 165 Confidential TRX, Inc. Create a Support Request continued 6. Enter a summary of your problem/issue in the Comments’ box. Put in as m

Page 166 Confidential TRX, Inc. View an Incident Once a support request form has been entered, the user may check the status of the incident by

Page 167 Confidential TRX, Inc. Search for an Incident If you do not have the incident number, you can search for any incidents that have been cr

Page 168 Confidential TRX, Inc. Report Examples

Page 169 Confidential TRX, Inc.

Page 17 Confidential TRX, Inc. TTX Web Reporting (WebMan) TRAVELTRAX Web Reporting provides you the ability to access and run reports via the In

Page 170 Confidential TRX, Inc.

Page 171 Confidential TRX, Inc.

Page 18 Confidential TRX, Inc. TRAVELTRAX Portal The TRAVELTRAX Portal is a site for our clients to use to enter a support request when they hav

Page 19 Confidential TRX, Inc. Database Overview PNR 1.1HOWARD/JOHN S*110158-C85412 1 CO1687T 30JAN M SEAIAH*HK1 622A 1230P HRS /DCCO*ABQXNQ

Page 2 Confidential TRX, Inc. TABLE OF CONTENTS Agenda...

Page 20 Confidential TRX, Inc. RELATIONAL DATABASES TRAVELTRAX Desktop Reporting retrieves the data it displays on reports from a relational dat

Page 21 Confidential TRX, Inc. Primary Keys Every table contained in a relational database must have one or more fields that will uniquely ident

Page 22 Confidential TRX, Inc. To understand the relationship of the Primary Keys in the above tables, let’s examine the first record in

Page 23 Confidential TRX, Inc. Automated Global Travel Data Consolidation

Page 24 Confidential TRX, Inc. Entity Relational Diagram This diagram shows you the basic relationship structure of our 5.03 database.  The

Page 25 Confidential TRX, Inc. Configuring ODBC and BDE Overview Microsoft SQL Server Data is stored in databases. The data in a database is or

Page 26 Confidential TRX, Inc. Layer Names When connecting to a client, you have three possible connections: a connection to the data layer, th

Page 27 Confidential TRX, Inc. Configure ODBC In this step you are creating the connection to the server as well as a connection to the client’s

Page 28 Confidential TRX, Inc. Configure ODBC continued 5. Select the driver for which you want to set up a data source - SQL Server. 6. Sele

Page 29 Confidential TRX, Inc. Configure ODBC continued 11. There are two options under ‘How should SQL Server verify the authenticity of the lo

Page 3 Confidential TRX, Inc. Report 1 – Detail Report ...

Page 30 Confidential TRX, Inc. Configure ODBC continued 16. On the next screen, select Finish. 17. On the final screen, select the Test Data

Page 31 Confidential TRX, Inc. Configure BDE Normally, your system is built with 3 BDE aliases as defaults: TMAN for the data layer, TMAN_META

Page 32 Confidential TRX, Inc. Configure BDE continued Make sure the settings for this BDE are correct by going through the following steps: De

Page 33 Confidential TRX, Inc. Configure BDE continued 12. On the Definition tab, change the parameters as follows:  LOCAL SHARE = FALSE 

Page 34 Confidential TRX, Inc. Point BDE aliases to ODBC connections In order to connect to the correct server and database, your BDE aliases ne

Page 35 Confidential TRX, Inc. Create Reports Introduction TRAVELTRAX Desktop Reporting is a data reporting tool. This tool allows your compa

Page 36 Confidential TRX, Inc. Main Menu Overview The Main Menu is the primary operating screen for TRAVELTRAX Desktop Reporting. From it, you

Page 37 Confidential TRX, Inc. Tool Bar The Tool Bar is a shortcut method of performing many of the functions associated with the main menu.

Page 38 Confidential TRX, Inc. Toolbar continued The following seven icons allow you to display any combination of Reports, Profiles, Batches,

Page 39 Confidential TRX, Inc. Menu Bar The Menu Bar accomplishes many of the same functions as the Icon Bar, but requires more steps. However

Page 4 Confidential TRX, Inc. Report 7 - Union ...

Page 40 Confidential TRX, Inc. Menu Bar continued Tools Databases Use this window to configure the databases TRAVELTRAX is connected to

Page 41 Confidential TRX, Inc. Tools continued System Setup The system setup screen is also completed the first time you setup TRAVELTRAX. Mu

Page 42 Confidential TRX, Inc. Help This is a standard Windows help menu that will assist you in finding answers to your TRAVELTRAX questions.

Page 43 Confidential TRX, Inc. Report Options When you right-click on a report, you get a list of options associated with Reports. Many, like

Page 44 Confidential TRX, Inc. Master Data Dictionary The Data Dictionary is used extensively throughout TRAVELTRAX. It contains fields from ea

Page 45 Confidential TRX, Inc. Profiles Overview The building of Profiles is critical to the generation of Reports in TRAVELTRAX. If you want t

Page 46 Confidential TRX, Inc. Profile Exercise continued 8. Right click on the column you just dropped CL007 into and select Edit Filter.

Page 47 Confidential TRX, Inc. Profile Exercise continued Group Profile Let’s say your company does travel for many different corporate clients

Page 48 Confidential TRX, Inc. Reports Overview In this class, you will learn the process of building custom reports. In each report, you will

Page 49 Confidential TRX, Inc. Report 1 – Detail Report The first report you will build will be a DETAIL report. It will display selected tran

Page 5 Confidential TRX, Inc. Agenda Day One 1. Introductions 2. Course Objectives 3. TRAVELTRAX Overview 4. Review of the data flow

Page 50 Confidential TRX, Inc. Report 1 continued Create Report Here are the steps to create a new report: 1. Select the New icon. 2. Selec

Page 51 Confidential TRX, Inc. Report 1 continued Section Properties Section Properties will set up the date properties, links, unions, etc fo

Page 52 Confidential TRX, Inc. Report 1 continued Query Now you will be selecting the data elements you want to see in your report. You will

Page 53 Confidential TRX, Inc. Report 1 continued Calculations This is where a value is assigned to a calculated field. A value is normally so

Page 54 Confidential TRX, Inc. Report 1 continued Filters The next step is to filter the data you want displayed on your report. In a standard

Page 55 Confidential TRX, Inc. Report 1 continued Airline Vendor Only 28. Locate the Vendor Type (VendorType - ID018) and drag it to the secon

Page 56 Confidential TRX, Inc. Report 1 continued 34. Run the report to make sure you receive data. This is a good habit to get into after you

Page 57 Confidential TRX, Inc. Report 1 continued Paradox Design Overview This is where you will design the look of the report. There are two

Page 58 Confidential TRX, Inc. Bands Bands are Horizontal Bars which appear in the Report Design. There are three default bands:  Report Ba

Page 59 Confidential TRX, Inc. Paradox Overview continued Before we begin to create the design for Report 1, we need to cover some basics. The

Page 6 Confidential TRX, Inc. Day Two 1. Review of yesterday 2. Create a summary Report o Understand Query Calc o Table Frame Tool o Buil

Page 60 Confidential TRX, Inc. Paradox Overview continued OLE Tool Use the OLE Tool to add a linked object to the report design. You can brin

Page 61 Confidential TRX, Inc. Paradox Overview continued Using Tools  Click on the tool you want to use.  Drag the mouse into the repo

Page 62 Confidential TRX, Inc. Paradox Overview continued Containership Containership has to do with the way objects interact with one another.

Page 63 Confidential TRX, Inc. Paradox Overview continued Containership Practice 1. Draw a large Box in the record band. 2. Draw an Ellips

Page 64 Confidential TRX, Inc. Paradox Overview continued Data Model The Data Model contains the query information, the date information, and s

Page 65 Confidential TRX, Inc. Paradox Overview continued Formulas A formula is a calculation that is defined in the design. Properties

Page 66 Confidential TRX, Inc. Report 1 Design If you need the complete steps to create a design, please see the Desktop Reporting User Guide on

Page 67 Confidential TRX, Inc. Report 1 continued 6. Change View – This allows you to see the design better, it doesn’t change the size of the

Page 68 Confidential TRX, Inc. Report 1 continued 9. Copy and Paste to create other similar fields. We could have drawn seven fields with the

Page 69 Confidential TRX, Inc. Report 1 continued 12. Align Fields - This process will: Space your fields equally across the page, align the to

Page 7 Confidential TRX, Inc. Day Three 1. Review of yesterday 2. Understanding Linking 3. Create a one-many linked Report o About DART

Page 70 Confidential TRX, Inc. Report 1 continued 13. Create Column Headers As the report stands now, we have the data on the page but no label

Page 71 Confidential TRX, Inc. Report 1 continued i. Align Headers – Align headers to each other and align each header to its corresponding da

Page 72 Confidential TRX, Inc. Report 1 continued 14. Create the Report Header and Footer Your company will most likely have a standard header

Page 73 Confidential TRX, Inc. Report 1 continued Create Report header and footer continued a. Highlight the header of the report. To do this

Page 74 Confidential TRX, Inc. Report 1 continued In this part of the exercise, we need to make our report more presentable. Notice the great

Page 75 Confidential TRX, Inc. Report 1 continued 1. Remove Space We can remove white space in a band just like we created more space within

Page 76 Confidential TRX, Inc. Report 1 continued b. Header Labels too long for corresponding data.  Click on the ISSUE DATE object (the h

Page 77 Confidential TRX, Inc. Report 1 continued 4. Add Totals to last page of report a. Select the Field Tool. b. In the Report Band,

Page 78 Confidential TRX, Inc. Report 1 continued Align Fields (Totals)  Repeating the process for aligning top and aligning left/right, alig

Page 79 Confidential TRX, Inc. Profiles Unless you assign a report to a profile, it will not display on a Web Reporting site. There are two w

Page 8 Confidential TRX, Inc. Day Four 1. Review of yesterday 2. Excel Designer 3. Basic Excel formulas 4. TRAVELTRAX Desktop Reporting and

Page 80 Confidential TRX, Inc. Report 2 – Summary Report Report two is a summary report. A summary report will summarize data instead of return

Page 81 Confidential TRX, Inc. Report 2 continued Create Report 1. Select the New icon and select Report from drop down menu. 2. Enter nam

Page 82 Confidential TRX, Inc. Report 2 continued Query 1. In the Description text box type Car Data. 2. Click on the Data Dictionary i

Page 83 Confidential TRX, Inc. Report 2 continued Calculation Since you now have three undefined Calc Fields in your query, you’ll need to comp

Page 84 Confidential TRX, Inc. Filters The next step is to filter out (exclude) voided car invoices. 12. Locate CAR008 (VoidInd) and drag it t

Page 85 Confidential TRX, Inc. Report 2 continued Design 1. Select the Report Design icon from the DART window. 2. Set the Page Layout  P

Page 86 Confidential TRX, Inc. Report 2 continued 5. Center the table. a. Using the line tool. Draw a line above the table from left to righ

Page 87 Confidential TRX, Inc. Report 2 Design continued 7. Align data and headers. 8. Make headers bold. a. Click on the header row until

Page 88 Confidential TRX, Inc. Report 2 continued 12. Align totals horizontally. a. Select all fields in the totals row including the text fie

Page 89 Confidential TRX, Inc. Report 2 Design continued 15. Add a Graph a. Using the Chart tool, draw a box about the same size as the table

Page 9 Confidential TRX, Inc. TRAVELTRAX Overview Agency ETL (DataMan) Agency ETL is the data management tool used to convert data from back-

Page 90 Confidential TRX, Inc. Report 2 Design continued d. Alternate X-Axis labels to allow room for long names.  Right-click on X-Axis (

Page 91 Confidential TRX, Inc. Report 3 – Ranking Report Report three is a ranking report. Ranking requires you to enter a field called ranking

Page 92 Confidential TRX, Inc. Create Report In this section, you’ll be creating a new report that will rank the top 10 city pairs based on the

Page 93 Confidential TRX, Inc. Report 3 Continued 4. Designate Summary Types. Remember, in all queries that return summarized data, any field

Page 94 Confidential TRX, Inc. Report 3 Continued 6. Add Calculations to the report. In this report, we’ll have two calculated fields. A.

Page 95 Confidential TRX, Inc. Report 3 Continued 5. Add a report header and footer in the Page Band. 6. Change the header for Level 1 to R

Page 96 Confidential TRX, Inc. Create a Custom Calculation Field To create a calculation that will be saved in the Data Dictionary: 1. Click o

Page 97 Confidential TRX, Inc. Report 4 – Multi-level Ranking Report Report 4 is a multi-level ranking report. It will display the top 10 Trave

Page 98 Confidential TRX, Inc. Create Report 4 In this section, we’ll enter the report properties and section properties for Report 4. 1. Crea

Page 99 Confidential TRX, Inc. Report 4 continued Query In this section, you’ll be creating the query for this report. Think about the infor