TRAVELTRAX DESKTOP REPORTING Training Guide v6.
TABLE OF CONTENTS Agenda...................................................................................................................................... 5 TRAVELTRAX Overview .......................................................................................................... 9 Agency ETL (DataMan) ............................................................................................................................. 9 Credit Card ETL (CreditCardMan) .........................................
Report 1 – Detail Report .......................................................................................................................... 49 Create Report ...................................................................................................................................... 50 Report Properties ................................................................................................................................. 50 Section Properties .......................................
Report 7 - Union .................................................................................................................................... 122 Query ................................................................................................................................................. 122 Excel Design Overview.......................................................................................................................... 124 Detach Excel Design...................................
Agenda Day One 1. Introductions 2. Course Objectives 3. TRAVELTRAX Overview 4. Review of the data flow 5. Understanding relational databases 6. Understanding the Data Dictionary 7. A Tour of TRAVELTRAX Desktop Reporting o Main Menu o Menu and Toolbar options 8. Running a Report 9. Profiles 10. Exercise on your own – build a Profile 11. Create a detailed Report o Properties o Query o Build a calculation o Filtering using Filter Assist 12. Create the design for a detailed report.
Day Two 1. Review of yesterday 2. Create a summary Report o Understand Query Calc o Table Frame Tool o Build a calculation in Design o Add a graph 3. Create a ranking Report o The Rank rules o Use calculated field from Data Dictionary 4. Create a Batch 5. Create a multi-rank Report o Group Band o Page Break 6. Customization o Building a Calculation in the Data Dictionary o Create or modify report periods o Category Editor 7. Lookups o Build a Lookup 8.
Day Three 1. Review of yesterday 2. Understanding Linking 3. Create a one-many linked Report o About DART sections o Rules of linking o Section filters vs. Global filters o Designing with “Format, Layout” 4. Create a one-many linked ranking Report 5. Create a one-one linked Report 6. Understanding Unions 7. Modify the one-one linked Report to add section 8. Working with the Data Model 9. Create a cross-tab Report 10. Directed exercises (report builds) Page 7 Confidential TRX, Inc.
Day Four 1. 2. 3. 4. 5. 6. 7. Review of yesterday Excel Designer Basic Excel formulas TRAVELTRAX Desktop Reporting and Excel (how they interact) Introduction to macros Build a two section Excel Design report with macros Review / Exercise on your own Day Five 1. TTX Portal Licensing Documentation Support Requests 2. Incidents 3. Review of yesterday 4. Cover remaining items if required 5. Practice, Practice, Practice 6. Final Review Page 8 Confidential TRX, Inc.
TRAVELTRAX Overview Agency ETL (DataMan) Agency ETL is the data management tool used to convert data from back-office systems into a format that TRAVELTRAX Desktop Reporting can recognize and use. The menu options in Agency ETL provide the user a high level of flexibility in customizing the way data is imported into TRAVELTRAX Desktop Reporting. Request Options This tab provides the ability to designate how daily car rates are derived, how the Advance Purchase Group data is calculated and Archive Options.
Credit Card ETL (CreditCardMan) Credit Card ETL is the data management tool you’ll use for credit card consolidation, to perform the following functions: 1. Extract data from credit card agency-provided transaction files. 2. Import credit card transactions to the TRAVELTRAX Client database. 3. Match Booking information (Air Ticket, Car and Hotel Reservations) to Credit Card Transactions for subsequent reporting by the TRAVELTRAX Client reporting application.
Process Scheduler (Auto Processor) Process Scheduler is a highly scalable system for securely transferring, processing, and tracking file based data in an automated, timely, and consistent manor. It includes a number of significant enhancements that further automate manual processes, validate, track, and troubleshoot data feeds. The Process Scheduler functions as both a set-up tool, for defining users and scheduled events, as well as for monitoring the progress of those events.
Hotel Name Normalization The Hotel Name Normalization tool is used to take all the different ways that a hotel property can be displayed in your data and change it into one standard hotel property name. The problem with hotel data is that it comes from many different sources and those sources don’t always display the hotel information the exact same way. Agency Data – will pull hotel information from the Hotel Table Card Data – will pull hotel information from the CCHotel and CCMerchant Tables.
Currency Converter (MoneyMan) Currency Converter is a tool that will allow a user to enter currency code tables in their TRAVELTRAX Desktop Reporting tables for specific dates. Page 13 Confidential TRX, Inc.
TTX Desktop Reporting (TravelMan) TRAVELTRAX Desktop Reporting is a data reporting tool. This tool allows your company to build your own reports, as well as build profiles, lookup lists and batches. You will also be able to customize things such as reporting periods, category names, and corporate structures. Page 14 Confidential TRX, Inc.
Data Quality Manager (QualityMan) Data Quality Manager is a state of the art database management program. It complements TRAVELTRAX Desktop Reporting by providing a user friendly tool to correct, modify, or append the data displayed on your report results. Data Quality Manager fixes data at the source, before you generate your reports, saving you the time and expense of constantly redoing your work.
Airline Contract Management TRX's Airline Contract Management program provides an end to end solution for companies to manage their airline contracts; from planning, performance evaluation and utilization to compliance and ROI measurement. The result is improved contracts and maximized savings through enhanced forecasting and negotiation, on-going optimization, monitoring and compliance auditing.
TTX Web Reporting (WebMan) TRAVELTRAX Web Reporting provides you the ability to access and run reports via the Internet. Reports can be run quickly to share throughout your organization or set up as automated report distributions. With Web Reporting access to your travel related information is limited only by your access to the Internet. Reports are returned to you in numerous formats, including Microsoft Excel, a Printable Document Format (PDF), Xcelsius dashboard (Adobe Flash).
TRAVELTRAX Portal The TRAVELTRAX Portal is a site for our clients to use to enter a support request when they have a problem with any of the TTX applications. The client can then track the incident to find the status and communicate with the TRX Helpdesk about the incident. Page 18 Confidential TRX, Inc.
Database Overview PNR 1.1HOWARD/JOHN S*110158-C85412 1 CO1687T 30JAN M SEAIAH*HK1 622A 1230P HRS /DCCO*ABQXNQ /E X 2 CO1079T 30JAN M IAHSAT*HK1 245P 351P HRS /DCCO*ABQXNQ /E O 3 CAR ZL 30JAN M HK1 SAT/03FEB/FCAR/ARR-4P/ID-78607 /DCZL 7471/CD-5555501/RET-5A/RG-¤USD39.50 UNL DY XD39.50 UNL XH13.17 UNL/AP-¤USD209.30 UNL 4DY 0HR 51.30MC/BS-26582183/RC-J2429/CF-6 70967723COUNT4 HHL ES HK1 SAT IN30JAN M-OUT03FEB 4NT 117734 EM /DCES BASSY SUITES RIVE 1A0CA1X -1/ 164.
RELATIONAL DATABASES TRAVELTRAX Desktop Reporting retrieves the data it displays on reports from a relational database. Within a database like Tman.db there exits TABLES where the data is stored. In TRAVELTRAX Desktop Reporting those tables might look similar to the screenshot below. These tables are similar to a spreadsheet in that the data is stored in a tabular format. Each column in a table is referred to as a DATA FIELD. Within each table you will normally find many data fields.
Primary Keys Every table contained in a relational database must have one or more fields that will uniquely identify a record. These unique fields are called the PRIMARY KEYS. Using our example above, the primary keys for the invoice detail table is made up of the RecordKey, IataNum, and SeqNum. RecordKey Record key is a unique code assigned by the system when the data is being imported. It is used to link together various tables such as invoices to transeg, car and hotel.
To understand the relationship of the Primary Keys in the above tables, let’s examine the first record in the invoice detail table. Notice that the Passenger Name field is Estroff/Alison M. When this individual booked her travel plans they called for air, car, and hotel reservations. Now look at the first record of the hotel and car tables. The Passenger Name field entries are Estroff/Alison. The middle initial M is missing. To the computer this is a different passenger.
Automated Global Travel Data Consolidation Page 23 Confidential TRX, Inc.
Entity Relational Diagram This diagram shows you the basic relationship structure of our 5.03 database. There can only be one client (code) per invoice so there is a one to one relationship between Client and Invoice Header (a client is the company, not the passenger). There is only one set of common remarks per client, so there can only be on set of common remarks per invoice header. The Invoice Detail table holds ticket data.
Configuring ODBC and BDE Overview Microsoft SQL Server Data is stored in databases. The data in a database is organized into the logical components visible to users. Each instance of SQL Server has one or more user databases. Your organization likely has only one user database containing all your post-ticketed data. When connecting to an instance of SQL Server, your connection is associated with a particular database on the server.
Layer Names When connecting to a client, you have three possible connections: a connection to the data layer, the meta layer, and the system layer. See below for an explanation of what each layer houses: Data Layer The data layer holds the clients actual data. Once their data is loaded and goes through Agency ETL, it is held on their data layer. Meta Layer The meta layer holds the Metadata. Metadata consists of the system files required for application operation.
Configure ODBC In this step you are creating the connection to the server as well as a connection to the client’s database on that server. 1. On your Desktop, click the BDE Administrator icon (if it’s not on your desktop, go to Start – Control Panel). 2. From the menu bar, select Object (or right-click on the Database tab) and select ODBC Administrator. If you don’t see these options when selecting Object on the menu bar, make sure that you’re on the Databases tab.
Configure ODBC continued 5. Select the driver for which you want to set up a data source - SQL Server. 6. Select the Finish button. 7. In the Create a New Data Source to SQL Server window, give the data source a name. Our standard naming convention is client’s name_ layer name_ Server number (example: MOOL_Data_899). If the data and meta layers are on the same server and have the same database name, you can create one connection for both of them.
Configure ODBC continued 11. There are two options under ‘How should SQL Server verify the authenticity of the login ID?’ Use the one you need for your company. Windows NT – This option will use your network login & password to check and see if you have permission to connect to the server you added. SQL Server authentication – This opens up the Login ID and Password fields to display (see screenshot below). This is the login and password to access the server you’re trying to connect to.
Configure ODBC continued 16. On the next screen, select Finish. 17. On the final screen, select the Test Data Source button. If the configuration is properly test up, you will be rewarded with a message that reads, ‘TESTS COMPLETED SUCCESSFULLY’. If you do not receive this message, review your steps to reconfigure the ODBC connection. If you need assistance, contact your network administrator or TRX Technical Support. 18.
Configure BDE Normally, your system is built with 3 BDE aliases as defaults: TMAN for the data layer, TMAN_META for the meta layer, and TMAN_SYSTEM for the system layer. You can use these BDE aliases and just point your connections to them, or you can create new BDE aliases for each of your layers. To create a BDE alias, follow the steps below: Create BDE Alias 1. In the BDE window, click on the Databases tab. You should see your ODBC aliases.
Configure BDE continued Make sure the settings for this BDE are correct by going through the following steps: Definitions of BDE 6. The new BDE alias name is now included in the list. Click on the BDE alias to display the definitions (settings). 7. On the Definition tab, change the parameters for the following:: BLOB SIZE = 1000 BLOBS TO CACHE = 1024 8. Repeat steps 6-7 for each BDE alias. 9. Select the Configuration tab. 10.
Configure BDE continued 12. On the Definition tab, change the parameters as follows: LOCAL SHARE = FALSE MAXBUFSIZE = 16384 MAXFILEHANDLES = 512 MEMSIZE = 205 MINBUFSIZE = 2048 SHAREDMEMLOCATION = 4E90 SHAREDMEMSIZE = 65536 13. From the Configuration tab, expand Configuration, Drivers, and Native by clicking the plus sign (+) to the left of each. 14. Select Paradox. 15.
Point BDE aliases to ODBC connections In order to connect to the correct server and database, your BDE aliases need to be pointed to the correct ODBC connections. Follow the steps below to point the BDE aliases to the correct ODBC connections for all layers needed: 1. Click on the BDE alias for your data layer (example: TMAN). 2. Click on the ODBC DSN in the right column. 3. Click the drop down and select the correct connection for the data layer for this client. 4. 5. 6. 7.
Create Reports Introduction TRAVELTRAX Desktop Reporting is a data reporting tool. This tool allows your company to build your own reports, as well as build profiles, lookup lists and batches. You will also be able to customize things such as reporting periods, category names, and corporate structures. During implementation, TRX will work with you to download and configure everything needed for this system to work.
Main Menu Overview The Main Menu is the primary operating screen for TRAVELTRAX Desktop Reporting. From it, you initiate most of the main functions within the program. It provides an easy to read platform that displays information in a standard windows format; customizable to your needs. The following information is provided to enhance the learning process within the TRAVELTRAX training environment.
Tool Bar The Tool Bar is a shortcut method of performing many of the functions associated with the main menu. Exit TRAVELTRAX – This closes TRAVELTRAX and returns to Windows. New – This displays a drop-down list to choose new Report, Profile, Batch, Lookup, List, Dictionary, and Script. Copy – Allows you to copy any highlighted Report, Profile, Batch, Lookup, List, Dictionary, and Script. Delete - Allows you to delete any highlighted Report, Profile, Batch, Lookup, List, Dictionary, and Script.
Toolbar continued The following seven icons allow you to display any combination of Reports, Profiles, Batches, Lookups, Lists, Dictionaries, and Scripts in the main window. Show Reports – Will display a list of all reports each user is allowed to see. Show Profiles – Will display a list of all profiles each user is allowed to see. Show Batch Requests – Will display a list of batch requests. Show Lookups – Will display a list of lookup tables.
Menu Bar The Menu Bar accomplishes many of the same functions as the Icon Bar, but requires more steps. However, there are other functions that are only available from the Menu Bar. A description of the functions that will be covered in training follows: File New – From the New icon, click on the drop-down menu to create the following: Reports Profiles Batches Lookups Lists Dictionaries Scripts Open, Copy, Delete 1. To open, copy, or delete a file (report, lookup, etc.), click on that file.
Menu Bar continued Tools Databases Use this window to configure the databases TRAVELTRAX is connected to. Normally, this only needs to be completed when you first install TRAVELTRAX. Page 40 Confidential TRX, Inc.
Tools continued System Setup The system setup screen is also completed the first time you setup TRAVELTRAX. Much of the information is used for product identification when you are coordinating with TRX. The Export Directory is used in conjunction with Batch Generator when the destination of the report results is other than screen, printer, or e-mail. Licensing This is used during the registration of your TRAVELTRAX software. When you need to register your software, 1. Click on Tools – Licensing 2.
Help This is a standard Windows help menu that will assist you in finding answers to your TRAVELTRAX questions. Right Clicking Options A. When you right click on a batch or a profile, TRAVELTRAX allows you to do the following: Edit – Brings up the window associated with the item you chose so you can make changes to it. Copy – Allows you to make an exact copy of the selected item. Delete – Removes the selected item from TRAVELTRAX. Export – Activates the export window. B.
Report Options When you right-click on a report, you get a list of options associated with Reports. Many, like Copy and Delete, have already been covered. Below are the other options: Edit Query – Opens the query window for the report you have selected so you can make changes to it. Edit Design – Opens an existing report design or allows you to create a new one if one doesn’t already exist. Delete Design – Deletes any existing design for the selected report.
Master Data Dictionary The Data Dictionary is used extensively throughout TRAVELTRAX. It contains fields from each of the tables in your database. These fields directly correspond to data element columns within the table. Your company’s Master Data Dictionary can be accessed from the Main Page in Desktop Reporting. Click on the Data Dictionary icon. You can then click on the Data Dictionary icon.
Profiles Overview The building of Profiles is critical to the generation of Reports in TRAVELTRAX. If you want to run reports for a specific client, agent, group, or your company as a whole, build a Profile for that client and/or group and/or company. A Profile can be defined with any field from the Data Dictionary: Client codes, agent sines, passenger names, employee numbers, region codes, location names, etc… Profiles can be defined with multiple values.
Profile Exercise continued 8. Right click on the column you just dropped CL007 into and select Edit Filter. 9. Type the SQL expression for one value. Type = ‘Arlington’ 10. Select OK. Page 46 Confidential TRX, Inc.
Profile Exercise continued Group Profile Let’s say your company does travel for many different corporate clients and you sell them a specific group of reports. You’d like a profile that will only access those corporate clients. Here is how to create a profile for more than one value. 1. Select the New icon and then Profile from the drop-down list. 2. When the New Profile window appears, type Corporate Clients then select OK. 3.
Reports Overview In this class, you will learn the process of building custom reports. In each report, you will learn different procedures and techniques important in building any report. The process of learning these reports is progressive. The techniques learned in each successive report are carried over to the next report. You will learn what the Report Properties are used for and what the Section Properties are used for. You will also learn how to add query fields and filters.
Report 1 – Detail Report The first report you will build will be a DETAIL report. It will display selected transaction information at the invoice level. In addition, you will learn to remove voided invoices as well as cruises and tours. After you have completed these steps, you will learn how to display your results in a custom design. A Detail Report will return one row in your report for every row it finds in the Database. Page 49 Confidential TRX, Inc.
Report 1 continued Create Report Here are the steps to create a new report: 1. Select the New icon. 2. Select Report from drop-down menu. The New Report pop-up window appears. 3. Enter a name for the new report: REPORT1 4. Select OK. Report Properties Report properties will set up what the customer will see if the report is going to be used on the TRAVELTRAX Web Reporting site. When creating your own reports, enter the appropriate title, category and description based on each report. 5. 6. 7. 8.
Report 1 continued Section Properties Section Properties will set up the date properties, links, unions, etc for each section of a report. Since a report can have more than one section when needed, these properties could possibly be different for the different sections. 10. From the menu bar, select Section then Properties (or right click on the Section 1 tab and choose Properties from the pop-up menu). 11. Date Field – In the floating data dictionary, scroll down and find the Issue Date field (ID006).
Report 1 continued Query Now you will be selecting the data elements you want to see in your report. You will drag and drop fields from the Data Dictionary to the Fields area of your query window. Query Fields 16.
Report 1 continued Calculations This is where a value is assigned to a calculated field. A value is normally some form of a mathematical function. In this exercise (Report1), we want to calculate the Amount Saved. This is accomplished by subtracting the Total Amount (ID029) from the Full Fare (ID035). Anytime you place a CAL field in your query it means that you are creating a calculation instead of just pulling up the data. You must complete the calculation or you will get an error when you run the report.
Report 1 continued Filters The next step is to filter the data you want displayed on your report. In a standard ticket report, you’ll want to remove voids and you need to select vendor types that will return airline and rail tickets only. You do this with filters. Exclude Voids 23. Locate the void indicator (VoidInd - ID007) and drag it to the first column in Filters. 24. Right-click on the field you pasted and select Edit Filter to bring up the Expression Builder. 25.
Report 1 continued Airline Vendor Only 28. Locate the Vendor Type (VendorType - ID018) and drag it to the second column of Filters. 29. Right-click on the field you pasted and select Edit Filter to bring up the Expression Builder. 30. Drag the operator in list to the Expression area (or you could just type IN). 31. Click between the parentheses and type: (‘BSP’, ‘NONBSP’) 32. Select OK to finish. You have now built the query and established filters. Now, save your work. IN (‘BSP’,’NONBSP’) 33.
Report 1 continued 34. Run the report to make sure you receive data. This is a good habit to get into after you create a report. You don’t want to go in and build a design that may take hours only to find that your data won’t pull up. Click the run report icon Select the output for the report and select the date range. Click OK. The report will run in Excel (Excel is the default when you have not created a design for your report). .
Report 1 continued Paradox Design Overview This is where you will design the look of the report. There are two ways to access the report design. Right-click on the name of the report from the Main Menu of TRAVELTRAX. If you have the query pulled up, follow the steps below: 1. Click on the Report Design icon. This means that you are in the EDIT DESIGN mode. 2. Select Paradox x.0. 3. Select OK. Page 57 Confidential TRX, Inc.
Bands Bands are Horizontal Bars which appear in the Report Design. There are three default bands: Report Band – Objects placed here print once at the beginning or ending of a report (header and footer). Page Band – Objects placed here print once per page (header and footer for each page). Record Band – This is the body of the report where all the data appears You can also add a band to the report. This is accomplished through a button called Add Group Band.
Paradox Overview continued Before we begin to create the design for Report 1, we need to cover some basics. The designer is where you layout the report. You choose fields from the query and put them in the design using a multitude of tools. These tools are very important for you to learn. If you forget the name of a specific tool, hover your mouse across the button bar (tool icons), and the name of the tool will appear as a hint. Let’s now define each of the tools you’ll be using in your report design.
Paradox Overview continued OLE Tool Use the OLE Tool to add a linked object to the report design. You can bring in charts and graphs from other software programs. As the linked object gets updated in the other programs, it will also change in your design. Field Tool Use the Field Tool to put fields from the Query in the report design. Each Query field is laid out individually using this object. Table Frame Tool Use the Table Frame Tool to add all of the fields from a section to your report design.
Paradox Overview continued Using Tools Click on the tool you want to use. Drag the mouse into the report and create the design you need from that tool. Practice Use the appropriate tools to create a box, a circle, text, and two fields. Status Bar Objects Every item in the design is considered an object: Tables, Fields, Lines, Text, Graphs, etc. Select an object by clicking on the Selection Arrow icon Click on the object in the report.
Paradox Overview continued Containership Containership has to do with the way objects interact with one another. As we stated before, every item on a report is an object. You can have objects within objects (this is where Containership comes into being). If you have one object within another object, the surrounding object by default contains the inner object. If one object is contained within another, you must click multiple times in order to get to the inner object.
Paradox Overview continued Containership Practice 1. Draw a large Box in the record band. 2. Draw an Ellipse inside the Box - By default, the Box will contain the Ellipse. 3. Draw a Line inside the Ellipse - By default, the line will have the handles. 4. Press [Esc]. - The Ellipse is selected. 5. Press [Esc] again. - The Box is selected. 6. Pressing [Esc] - Moves you back one level. 7. With handles around the Box, move the Box with the arrows of the Keyboard.
Paradox Overview continued Data Model The Data Model contains the query information, the date information, and some profile information. It will always have at least 3 tables listed on the right hand side of the screen: Sec1.db -You have to have at least one section for each report. If you right-click this button, a list of all the fields from this section will display. These are the data fields that can be entered into the design.
Paradox Overview continued Formulas A formula is a calculation that is defined in the design. Properties Properties are selections which can be defined for all objects in the design. Some properties are the font size, color, font style, word wrap, format, etc... Properties affect the way the data looks when the report is printed. Activate Property Menu Right-click on an object (or objects – if you want to do the same thing to multiple objects). Using the right mouse button means to Inspect an object.
Report 1 Design If you need the complete steps to create a design, please see the Desktop Reporting User Guide on the Portal. The steps below are specifically for this report example. 1. Data Model – Check the data model to see how many data fields are in this report (Format – Data Model). 2. Set Page Setup – This is where you select the orientation and size of the page as well as the margins. Use the steps below for this report: a. Select File – Page Setup from the Menu Bar. b. Choose Portrait. c.
Report 1 continued 6. Change View – This allows you to see the design better, it doesn’t change the size of the design. a. Select View (from Menu Bar). b. Select Zoom. c. Select Fit Width. 7. Create Fields a. Go into the Data Model (Format – Data Model – right click on Sect1.db button) to find out how many data fields you need for this report. b. Select the Field tool. c. Click in the upper left corner of the Record Band and drag a box to create one field. Make sure it is up against the left margin. 8.
Report 1 continued 9. Copy and Paste to create other similar fields. We could have drawn seven fields with the field too, unlabeled and set the properties for each field separately, but to save time and to make your work easier, use the copy feature. a. Select the field you just created (look for handles). b.
Report 1 continued 12. Align Fields - This process will: Space your fields equally across the page, align the top of all fields in a straight line, and make all fields the same thickness. a. Select all fields by one of the following methods: Edit Select All Highlight the Record Band by clicking on the band itself or clicking anywhere in the white space in the record band. Select Edit from the menu bar. Select Select All. Ctrl Key Hold the Ctrl Key down. Select each field.
Report 1 continued 13. Create Column Headers As the report stands now, we have the data on the page but no labels to indicate what each piece of data indicates. Our data will be displayed in columns, so we need a label above each column to make our report make sense a. Select the Text Tool. b. Click near the left hand margin in the Page Band where you want your text to begin. A place holder will appear on the Ruler. This is the starting point for the first header label. c.
Report 1 continued i. Align Headers – Align headers to each other and align each header to its corresponding data field in the record band. - Select all text boxes and align objects to bottom. - Deselect all text boxes. - Hold your Ctrl key and select the Issue Date text box and the Issue Date field. - Select the Align Objects left button and then the Align text left button. - Repeat this for each data field/header.
Report 1 continued 14. Create the Report Header and Footer Your company will most likely have a standard header and footer that they want to use for all reports. Instead of creating these headers and footers for every report, you should just find one of each and copy them into each report design. NOTES: - You should consider whether the header/footer is portrait or landscape so it matches the report. - Then you’ll find a report that contains the header and footer you need.
Report 1 continued Create Report header and footer continued a. Highlight the header of the report. To do this, click inside the Page band, but outside the actual header. b. Select Edit – Select All. This will select your header and footer. c. Since we only need the header, we need to deselect the footer (click Ctrl and click on the footer). d. From the menu bar, select Edit – Copy (you can also right-click and Copy). e. From the menu bar, select Window and click on your report.
Report 1 continued In this part of the exercise, we need to make our report more presentable. Notice the great amount of space between our data rows? This is caused by the space between the different bands in the design mode. Also, notice how that the Total Amount, Full Fare, and Low Fare don’t have dollar signs? How about the format for the Issue Date? We need to fix all this. To make changes to our design you must go back to the design mode.
Report 1 continued 1. Remove Space We can remove white space in a band just like we created more space within a band. o Highlight a band. Highlight the band and either move your mouse up until you see a double-arrow or move it down until you see one. o Once you see the double-arrow, click and drag up (or down). o Make sure you’re in the correct band or you could be creating white space in the wrong band. a. Highlight the band. Move the mouse up or down until you see double, vertical arrows. b.
Report 1 continued b. Header Labels too long for corresponding data. c. Click on the ISSUE DATE object (the header name). Click again till you get a flashing cursor. Click after the E in ISSUE. This allows you to use your keyboard. Select ENTER on your keyboard and put DATE on a second line. Repeat processes until all objects are lined up and centered. 3. Make Headers Bold a. Select Page Band. Select Edit – Select All (All headers have handles). b.
Report 1 continued 4. Add Totals to last page of report a. Select the Field Tool. b. In the Report Band, create a new field under, and aligned with, the Total column. Make space if needed. NOTE: Putting the totals in the Report Band will display the totals on the last page of the report, not on every page of the report. If you had wanted to put the totals on every page of the report, you would put them in the Page Band. c. Right-Click on the field and un-label it (Properties – Display Type – Edit). d.
Report 1 continued Align Fields (Totals) Repeating the process for aligning top and aligning left/right, align the fields in the Report with the fields in the Record Band. Band Finish a. View data and make final adjustments. Remember, your totals will be on the last page of the report. On the Paradox menu bar, click on the Last Page icon RESULTS 5. Change the format for the Issue Date For any date field, Paradox uses the Windows date as the default date. This includes the time and the date.
Profiles Unless you assign a report to a profile, it will not display on a Web Reporting site. There are two ways to assign a report to a profile: 1. Open a profile – You can open a profile and assign all the necessary reports to that profile. Use this option when you’ve created a new profile & need to assign several reports to it. 2. Right-click & Assign To – You can right-click on a report & Assign To the correct profile(s).
Report 2 – Summary Report Report two is a summary report. A summary report will summarize data instead of returning each row of data. This report will display car data summarized by car chain name. Filtering the data to remove voided car invoices will be required as well as building an appropriate design. Page 80 Confidential TRX, Inc.
Report 2 continued Create Report 1. Select the New icon and select Report from drop down menu. 2. Enter name for new report: REPORT2. Report Properties 1. Click Report on the Menu bar and select Properties. 2. Type Car Chain Summary in the Report Properties Title window. 3. Select OK when finished. Section Properties You can either click on Section, and then Properties on the menu bar or you can add the information in the Date Settings section (see screenshot below).
Report 2 continued Query 1. In the Description text box type Car Data. 2. Click on the Data Dictionary icon . Query Fields 3. Drag the following fields you want displayed in your report from the Data Dictionary into the FIELDS position of your DART Query window: CAR016 – ChainName CAL2 – Calc Number CAL2 – Calc Number CAL2 – Calc Number 4.
Report 2 continued Calculation Since you now have three undefined Calc Fields in your query, you’ll need to complete the calculations to make them valid. 9. Right click in the column and select Calculation then add the following calculations: 10. Car Days: CAR023 * CAR024 11. Total car cost: CAR023 * CAR024 * CAR027 Avg Daily Rate: The average daily rate will use the results from the Car Days and Total Car Cost calculations, so we have to create a different type of calculation.
Filters The next step is to filter out (exclude) voided car invoices. 12. Locate CAR008 (VoidInd) and drag it to the first column in Filters. 13. Right-click on the field you dropped it in and select Edit Filter to bring up the Expression Builder. 14. Drag the word equal to the Expression area (or type in an equals sign from your keyboard). 15. Type a single quote (apostrophe), an uppercase N, and close with another single quote. ‘N’ 16. Select OK to finish.
Report 2 continued Design 1. Select the Report Design icon from the DART window. 2. Set the Page Layout Page Layout - Select File from the Menu Bar. Select Page Setup. Choose Portrait. 3. Set the Report Layout Select Format – Layout. In the Style, select Tabular. Select OK at the bottom of the Layout window. Warning appears. Select OK - We want to update our design. NOTE: In lieu of using Design Layout, you could draw a table in the record band using your Table Frame tool.
Report 2 continued 5. Center the table. a. Using the line tool. Draw a line above the table from left to right margin. To draw a straight line, hold down the shift key while drawing the line. b. Holding down the shift key, select Line, Table, and both parts of the Heading. c. Select Format – Alignment - Align Center from the main bar. Everything should now be centered in your design. d. Delete the line since it’s no longer needed. 6. Make necessary adjustments to the table. a.
Report 2 Design continued 7. Align data and headers. 8. Make headers bold. a. b. c. d. Click on the header row until selected. Select Edit, Select All to get handles around all headers. Right-Click on one of the headers selected. Select Properties, Font, (Font Style) Bold. 9. Remove grid lines in the table. a. Select the Table. b. Right-click, select Properties – Grid – None. 10. Add totals. a.
Report 2 continued 12. Align totals horizontally. a. Select all fields in the totals row including the text field. b. Select Format, Alignment, Alight Top. 13. Make totals bold. a. With totals still selected, select on of the fields and right-click. b. Select Properties, Font, (Font Style) Bold. 14. Remove decimals from Car Days. a. Right-click on the field. b. Select Properties, Format, Integer. c. View data and make final adjustments Page 88 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 below the table within the record band. You may have to make room below the table first. b. Define the graph. Select the graph by right-clicking and selecting Defining Chart. You know you are addressing the graph if you have a large mouse pointer within the graph. A small pointer means you are addressing objects within the graph. c. Select the X Axis radio button. d. Select Car Company from Sect1.
Report 2 Design continued d. Alternate X-Axis labels to allow room for long names. Right-click on X-Axis (small pointer). Select Ticks, Alternate. e. Change bar color Right-Click on bar (small pointer). Select Color and then choose the new color. f. Change type of graph Right-Click on graph. Select Chart Type and choose from the list. After you’ve tried a few, select 2D rotated bar. 17. Save report Save the report & return to the main menu. This completes the design for Report2. .
Report 3 – Ranking Report Report three is a ranking report. Ranking requires you to enter a field called ranking, then enter the ranking information and then tell the system which data field you’re ranking. This report will display the top 10 City Pairs based on the Segment Value. We will filter the data to remove voided invoices and select the correct vendor types to display on the report. Page 91 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 number of segments flown to those city pairs. 1. Create a new report. Select the New icon from main menu. Select Report from the drop down menu. A pop-up window appears. Enter REPORT3 for the name of the report. Select OK. 2. Enter Report Property information. Click On Report then Properties from the menu bar.
Report 3 Continued 4. Designate Summary Types. Remember, in all queries that return summarized data, any field that returns a number must have a Summary Type Assigned. Right-click anywhere in the Number of Segments column and select Summary Type. Select Sum. Repeat for Segment Value. Ranking To rank something in a report, you have to add the Rank data field and it has to be the first data field on the left in your query. 5. We want the Top 10 city pairs based on the number of segments.
Report 3 Continued 6. Add Calculations to the report. In this report, we’ll have two calculated fields. A. City Pairs – We want the city pairs using the city name, not the code. The calculation you will be building is a SQL statement that will be adding together the Origin City Name and the Destination City Name. [Origin City]+'-'+[Destination City] [TS009]+’-‘+[TS026] Right click in City Pairs column. Select Calculation.
Report 3 Continued 5. Add a report header and footer in the Page Band. 6. Change the header for Level 1 to Rank and left align the text. 7. Adjust the column widths and header height as desired. 8. Center the data in the columns except for the Rank data field. Remember containership. a. Shrink the fields with the mouse pointer. Remember to leave it large enough to hold the number of characters expected. b. Move the fields to the right of center using your keyboard. Allow enough room for the largest number.
Create a Custom Calculation Field To create a calculation that will be saved in the Data Dictionary: 1. Click on the Main Data Dictionary icon (from the Main Menu). 2. In the left column under Data Dictionary, click on Calculations. 3. Then, right-click on Calculations and select New Calculations. 4.
Report 4 – Multi-level Ranking Report Report 4 is a multi-level ranking report. It will display the top 10 Travelers for the Top 5 Hotel. We will add a filter so that data will not pull from voided hotel invoices. Page 97 Confidential TRX, Inc.
Create Report 4 In this section, we’ll enter the report properties and section properties for Report 4. 1. Create a new report. Select the New icon from main menu. Select Report from the drop down menu. A pop-up window appears. Enter REPORT4 for the name of the report. Select OK. 2. Enter Report Property Information Click on Report then Properties from the menu bar. Title – Top Travelers by Hotel Category – Air Travel Description – Top 10 Travelers for the Top 5 Hotels Select OK. 3.
Report 4 continued Query In this section, you’ll be creating the query for this report. Think about the information you’re trying to get out of this report. You’re looking for the Top 10 Airlines for the Top 5 Travelers. Once you determine the results you need, then you’ll be able to determine what data fields need to be added to return that information. 1.
Report 4 continued Design 1. Start the Report Design. 2. Set up the Page Layout as Portrait. 3. Set up the Design Layout a. Select Format – Layout - Tabular. b. Remove the fields in the page band. 4. Add the Report Header and Footer in the Page Band. 5. Save the design and return to the TRAVELTRAX main menu. The organization of this report would be better if we broke out each client and provided a subtotal for each one. So, we’re going to separate the report by client.
Report 4 continued 1. Add Group Band – This will add another band to the design and put the Top Clients field in the new band. This will allow us to provide the data for each client separately instead of having the data repeat a. Select the Add Group Band tool. b. Highlight Level1 in the Field window. c. Select OK. d. View your report. It should be divided into multiple tables based on the Level1 rank field. Notice that the Level1 field in the table is now redundant so we can delete it.
Report 4 continued 5. In a blank row below the data field of the Top Clients’ column, click until the column turns black. 6. Hit the Delete key on your keyboard. 7. Repeat these steps for the Passenger Name column in the table. 8. Remove the grid lines from the table. 9. Move the table to the right as far as possible. 10. Table Adjustments Adjust column widths and header height as desired. 11. Align data in columns a. Shrink fields with your mouse pointer.
Report 4 continued 20. Add Final Totals. a. b. c. d. Previous totals are now sub-totals. Copy sub-totals fields (all at once). Paste in Report Band. Edit text in report totals as Final Totals. 21. Save your work. 22. Assign this report to 3 of your profiles. Page 103 Confidential TRX, Inc.
Linking Reports With multi-section reports, sections may have data from different tables in the database or data from the same table for different time periods. Linking is necessary in order to display this data in a meaningful format. Example: Suppose you wanted to produce a report that displayed a list of passengers, the amount of their tickets, and the air segments they flew.
One to Many Link Overview One to Many Link should be used when the linked sections’ result tables do not match (e.g. when there are multiple segments for each ticket row). One to Many Link Section 1 Section 2 Ticket Data Itinerary Data Result Table Result Table Invoice # Invoice # Segment # 1 1 2 2 3 3 1 2 3 1 2 1 If these sections were not linked, you would have two separate tables on the report showing separate sets of data. Each table could be a different size.
Data Dictionary Help Pages Type Origin City Code Destination City Code Origin City Name Destination City Name Origin Region Code Destination Region Code All Connections TS009 TS026 ORIG003 SDEST003 ORIG005 SDEST005 No Connections (NOX) TS009 TS037 ORIG003 XDEST003 ORIG005 XDEST005 No Connections - Minute Rule TS009 TS049 ORIG003 MDEST003 ORIG005 MDEST005 Market City Pairs (All Connections) TS034 TS035 SMORIG003 SMDEST003 SMORIG005 SMDEST005 Market City Pairs (NOX) TS045
Type Origin Region Name Destination Region Name Return Indicator (is not null) Segment Value Intl/Dom Ind (D or I ) Seg Miles Total Miles Selection Criteria All Connections ORIG006 SDEST006 TS036 TS030 TS027 TS031 TS032 None No Connections (NOX) ORIG006 XDEST006 TS048 TS041 TS038 TS042 TS043 TS037>A No Connections - Min Rule ORIG006 MDEST006 TS060 TS053 TS050 TS054 TS055 TS049>A Market City Pairs (All Connections) SMORIG006 SMDEST006 TS036 TS030 TS027 TS031 TS032
Tips for Segment Reporting: If you want to use the segment value that comes from your back office system you can choose that field from the TranSeg table. To prorate the segment value based on the segments mileage as compared to the total mileage use the following formula: SegmentMileage / TotalMileage x Amount - the amount can be the Ticket Price, Base Fare, etc.
Report 5 – Linking Report This report has ticket data and segment data. They need to be put in separate sections so that the ticket information is not repeated for each airline segment in the itinerary. 1. Create a new report and name it REPORT5. 2. Report Properties Title – Ticket Summary Category – Air Travel Description – Ticket summary including flight segments 3. Section Properties – Enter the following: Date Field – ID006 Date Period – Monthly Date Format – ODBC Date 4.
Report 5 continued Query 5. Enter the following data fields in section 1. ID001 – Record Key ID002 – IataNum ID003 – SeqNum ID006 – Issue Date ID015 – Ticket Number CAL4 – Passenger Name Right-click and create a calculation ID011+’/’+ID010 ID029 – Ticket Amount 6. Add Filters to Global filters by clicking on the Global radio button. Use the filters so only airline tickets are returned but voided tickets are not. 7.
Report 5 continued 10. Place the following data fields in the Section 2 Query: TS001 – Record Key TS002 – IataNum TS003 – SeqNum TS015 – Departure Date CAL4 – City Pair (TS009 +’ - ’+ TS026) 11. Link the Record Key, IataNum, and SeqNum query fields to those fields in section one. To do that, follow the steps below. Right-click the RecordKey data field and select Properties. Using the drop-down-arrow next to Link By, select RecordKey.
Report 5 continued 3. To build the design for this report, you can construct it manually by using the design tools on the icon bar or you can use Paradox’s automated design feature. We’ll introduce the automated feature here. If you didn’t delete the Record Key, IataNum, and SeqNum fields in the layout, do so now. We don’t need to see them in the report. They are only used when the query runs to link the data fields.
Report 5 Design continued At this point, your design should look like the following: To make our design look presentable, there are still a few things we need to do: 4. Move the Ticket Number field to the left margin and line up the other individual fields, in the order you want them, left to right. You might want to resize the Passenger Name field to make it fit easier. 5. Delete the headers in the table. 6. Select the table, right-click, select Properties.
Report 5 Design continued 7. Delete the 3 objects from the page band. We will enter our own headers later. 8. Shrink the city pair column in the table. 9. Move the table to the right margin and make sure it is higher than the other data fields. 10. Select all data fields (even the ones within the table), and make the text a size 8. 11. Adjust all data fields to the minimum height. 12.
Finished report Page 115 Confidential TRX, Inc.
Report 5 continued Create link in Paradox If your Data Model did not show the links, you can create them manually by following the steps outlined below. 1. Left-click and hold down the mouse button on the Sect1.db button. 2. Drag the mouse pointer down until it’s on top of Sect2.db and then release the left mouse button. If you do this properly, a Define Link window will appear (see below). 3. To establish the link, highlight Recordkey in the left window. Just above it a right facing arrow will appear.
Report 6 – One to One Link This will be a three section report with sections 2 and 3 linking back to section 1. Section 1 will be the master section containing the main ticket information; section 2 will be similar but will only include refunds, and section 3 will only include exchanges. Page 117 Confidential TRX, Inc.
Report 6 continued 1. Go to the Main Menu and create a new report called REPORT6. 2. Report Properties Title – Ticket Spend Summary Category – Spend Management Description – Ticket Spend summary includes ticket, refund, and exchange amounts. 3. Section 1 Properties Issue Date - ID006 Date Period – Month to Date Date Format – ODBC Date 4. Section Description – Ticket Data Query 5.
Report 6 continued 8. Copy Section 1 by clicking on this icon . Each of the three sections in this report will contain the same query fields so you can copy this section. 9. Section Properties – The only property you need to add is the link to section 1. The other properties were copied from section 1 & will remain the same. Link – Link section 2 to section 1. Do this by entering a 1 in the Link by section.
Report 6 continued rd Add a 3 section for the exchange information. 1. Copy this section. 2. Change the Section description to Exchanges. 3. Section 3 Properties do not change since the link was created in section 2 & we copied section 2. 4. Query Field – Change the name of the ID029 field to Exchange Amount. 5. Filters – Edit the exchange filter to = Y for yes and edit the refund filter back to = N for no. ID047 – Exchange Indicator Enter = ‘Y’ ID074 – Refund Indicator Enter = ‘N’ 6.
Unioning The unioning of sections is necessary in order to add data from subsequent sections into a previous section. You will not have a Union in every report. The type of report you want to build and the results you’re trying to achieve are the determining factors. In a union, Desktop Reporting takes the result set of one section and adds it to the result set of another section in order to get data from unlike sources into one result table.
Report 7 - Union In this report we want to find out the total amount that a client spent on air tickets, car, and hotel. We want one total for everything. We have a function called Unioning that will do this for us. Also, this is the first report we’ll be designing in Excel. 1. Create a new report. 2. Name it REPORT7. 3. Add Report Properties Title – Travel Spend Summary Category – Air Travel Description – Summary of spend per client includes air, car, and hotel spend.
Report 7 continued Query – Section 3 This section holds the hotel spend. 11. Add Section 3 Properties: Date Field: HTL007 Date Period: MTD Date Format: ODBC Date Union – Enter a 1 in the Union Section. We’re unioning section 3 with section 1 in order to add the hotel spend to the air spend per customer. Section 3 Description – Hotel Spend 12. Section 3 Query Fields: CL003 – Customer Name HTL035 – Hotel Cost. Sum it. 13.
Report 7 continued 21. From the query page, select the Report Design button. This will put you in the Edit Design mode. NOTE: If changes are made to the design when ‘Run’ or ‘Run with’ are selected, then the changes will not be saved. You need to be in the Edit design mode. 22. Below is the outline of an Excel Design. Excel Design Overview Selecting Excel as the design tool for your report is different than having Desktop Reporting do an Excel data dump.
Report 7 continued Rpthdng – This tab contains the data that comes from the profile. It contains the Profile ID, any headers that were entered into the profile, the report name, and any remarks that were entered in the profile. This data can be moved to the main sheet for the report to create a header for the report. Sect – Each section of a report will have its own tab in the Excel design that contains the data results from the query.
Report 7 continued Detach Excel Design You could design the entire report while attached to the TTX Desktop Reporting application, but we always detach the design from the tool so we can create multiple versions of the design if necessary. With Excel, if you create VBA and you make a mistake, you’ll probably lose the entire design and have to start over.
Report 7 continued 7. Reference the data you’ll need for your headers. In A1 through E1, type = Select the Rpthdng tab Select the Report title (cell 2E) and Enter In A2 through E2, type = Select the Rpthdng tab Select the profile name (2C) and Enter In A3 through E3, type = Select the Sect1DT tab Select the date range (2C) and Enter 8. Type in the column headers that you’ll need and format them You need Client, Total Spend, Air Spend, Hotel Spend, and Car Spend. 9.
Report 7 continued Re-attach Excel Design Since you created (or changed) the design outside the TTX Desktop Reporting tool, you’ll need to reattach your design to the tool. Follow the steps below to re-attach an Excel design: 14. Open Report 7 in TTX Desktop Reporting. 15. Select Edit Design from the main menu or click on the Report Design button if you opened the report and are on the query page (you have to be in the edit design mode). Use the ABC Corp profile and the default dates. 16.
Report 7 continued 20. Save As and save the report to C:\WINDOWS\Temp\TMANPRIV. 21. Instead of entering a name, select your original report and choose to overwrite when prompted. 22. Close Excel. This has reattached you design to TTX DR. The report with all your changes is now saved to the database. 23. To verify this, go back into TRAVELTRAX Desktop Reporting and run the report. Page 129 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 basic concepts of VBA code and you’ll learn how to apply those basic concepts to different report designs. Query You’re going to start looking at a report yourself to determine what fields are needed, whether the report is a summary report or a detailed report, how many sections are needed, and what data fields you need to put into each section.
Report 8 continued 5. Enter the data fields and filters necessary to create the report you see on the screen in class. Enter the data field numbers below for the following: Air Spend ______________ Number of Tickets ______________ Full Fare ______________ Low Fare ______________ 6. Add Filters – Using filter assist, add the filters we normally add for ticket data. Enter the data fields needed and the SQL for the filter.
_______________________________ _______________________________ Page 132 Confidential TRX, Inc.
Report 8 continued 13. Determine what columns/rows you’ll need and add the column/row headers. Since the column/row headers are static, you can just type them in. You will need to format the text (font size, font type). Add borders if necessary. 14. Think about the other things you’ll need to do to get the data from both sections into the Report sheet. Write them below: 15. Access VBA code using one of the following options: Right click any sheet and View Code.
Report 8 continued 17. Create a sub routine. Sub Name() – Type the word Sub then type in the name of the sub routine. You can name the sub routine anything you want. You’ll have to refer to this name later in the code. Example: Sub DoNotRun() The trainer will give you the VBA code for this report. Copy & paste it into this module. Page 134 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 the design tool. We’re going through all the steps a report developer would normally take when creating an Excel design. We hit on them briefly in the last report, but we’ll go into those steps more in depth during this report. Query 24. Create a new report called REPORT9. 25.
Report 9 continued 35. Close Excel. 36. From the Query page, select the Report Design button (or Tools – Report Design) and select Excel as the design tool. This is the Edit Design Mode. When you select Excel as the design tool, Desktop Reporting forces you to run the report, so select the default dates and the ABC Corp profile. Design When doing an Excel design using VBA, you may need to create many copies of the design just in case some of the code doesn’t work.
Report 9 continued 45. Select cell D2 in the Main sheet and reference the bookings from Sect2 for that first airline (you’ll have to find the airline name to get the cell reference). Type = Select the Sect2 sheet Select cell B(line nbr that the airline is on) Enter 46. Select cell E2 in the Main sheet and reference the bookings from Sect2 for that first airline (you’ll have to find the airline name to get the cell reference).
Report 9 continued 51. Create VBA code – Right click any tab/sheet at the bottom of the page and select View Code (you can also click on your Developer tab at the top of the page and select Visual Basic in the Code section). 52. In the left column under VBAProject, right-click and Insert – Module. This is where you’ll enter your VBA code. In class, we’re going to give you some code that you’ll be able to copy and paste into this module. We’ll also send the file to you so you can use later. 53.
Close out of Excel Page 139 Confidential TRX, Inc.
Report 9 continued 57. Go back to Desktop Reporting, and go to this report and Edit Design (Design Report). 58. When the report displays, close the workbook but NOT Excel (click the lower X in the upper-right corner). 59. Select File – Open and browse to the folder containing your modified Excel design. 60. Select your Excel report. 61. Prevent any macros from running and clear most, or all, data from each Section tab. 62. Save As and save the report to C:\WINDOWS\Temp\TMANPRIV. 63.
Batch Generator The Batch Generator is where you setup which profiles and associated reports you want to generate. It provides the means to produce one to hundreds of unattended reports in several formats. You can elect to output all or any combination of your reports in any combination of the following ways: Report Output Printer Screen E-Mail Compress using ZIP Standard output. Send to any available printer. Send to your computer monitor.
Create a Batch Exercise In this exercise you will learn how to create a new batch from which you can run existing reports. Later, this same batch will be used to generate the reports you will build in class. 1. Select the New icon and then select Batch from the drop-down list. 2. When the New Batch window appears, type MONTHLY then select OK. 3. Select ABC Corporation from Available Profiles. 4. Click on the right arrow to move ABC Corporation to the upper right window. 5.
Batch Generator Exercise continued 7. From the main batch window, select Batch, then Properties. 8. Select the Load Dates button to bring up a list of the Reporting Period labels for all reports in your batch. 9. If required, edit the Begin Date and End Date. (In class, leave the default dates.) 10. Click the Skip Failed Reports check box. If this is unchecked, no report after the failed report will run. 11. Click OK. 12.
Prompts This section will show you how to create prompts in a report. To give a user the ability to select specific data within a data field, you can create a prompt that allows them to enter the data or to choose the data from a list. Below are the different types of prompts that you can create along with an example and an explanation. When you enter a prompt into the data fields, it is mandatory for the user to select/add a value.
Pick List Prompt A Pick List prompt will display a list of values that the user can select from. This option allows the user to pick from the list or enter a value(s) manually. The list has to be created before doing the steps below. In the filter section, click and drag the data field that holds the value needed. Right-click in that column and select Pick List. Select List Object and the pick list box will activate. Select the list you need from the options that display. Click OK.
Pick List report: A Pick List report is a separate report you create to run a query and return data that will then be used to display a list that the user can select from. The advantage of using this is that it will only allow the user to select from a list of values that currently have data. Create Pick List Report Create a new report. Name it using a naming convention that will let everyone know this is a pick list report. Access the section properties and select Distinct.
Char Index Prompt The CHARINDEX function is used to search a character string for a specific character or set of characters. If the character string being searched contains the characters being searched for, then this function returns a non-zero integer value. This integer value is the starting location of where the character string being searched for is located within the string being searched.
SQL Statements SQL is used when creating calculations and filters. This section will show you the most common SQL statements used in reports. ISNULL The ISNULL function is used to replace NULL values with another value. Here is an example of an ISNULL statement: ISNULL([ID021],’Unknown’) ISNULL ([ID021] ,’Unknown’) SQL expression The data field to check for Null values (ID021 The replacement value for any results that are returned with a Null value.
Customization Within the Desktop Reporting tool, each company is able to customize certain things. They can create custom calculation fields, their own reporting periods, their own filters for filter assist, and their own lookups based on their individual needs. Below are some of the things within Desktop Reporting that they can customize. Calculations To create a calculation that will be saved in the Data Dictionary: 8. Click on the Data Dictionary icon. 9.
Filter Assist If you use the same filters over and over again in reports, you may want to put them into the Filter Assist so you don’t have to create them for each report. 1. You have to add the filters to your query page and enter the SQL statements. 2. Click on the Filter Assist button . It will display the box on the left, below. 3.
Reporting Periods This screen contains the master Reporting Periods for TRAVELTRAX. Batch Generator uses these reporting periods when you generate reports. Add Reporting Period Follow the instructions below to add a reporting period 1. Click on Tools. 2. Select Reporting Periods. 3. Select the Add icon. 4. Enter the Date Name. 5. Enter the Beginning Date and Ending Date. Add Copy Delete Delete Reporting Period If a report period is no longer needed, it can be deleted from the list. 1. 2. 3. 4.
Category Editor Use this screen to add, copy, or delete categories. You can create categories for both Profiles and Reports. Select Profiles or Reports from the Category Type drop-down list before you add, copy, or delete a category. Add a Category 1. Select Tools. 2. Select Category Editor. 3. Select the Category Type (Profiles or Reports). 4. Select the New icon. 5. Enter the category name. 6. Select OK. 7. Select File – Save. 8. Select the Exit icon. Delete a Category 1. 2. 3. 4. 5. 6. 7. 8.
Lookups The Lookup option allows you to build a new field based on an existing field in the Data Dictionary. One reason to do this is to provide meaning to coded fields within your database. For example, the Domestic/International field could have the following codes: D, I, T, and F. If you want to see the words Domestic and International instead of the codes, you would build a Lookup. The code D would be set up to be Domestic and the codes I, T, and F would be set up to be International.
Building a Lookup continued 6. Drag ID025 - InternationalInd to the left window. 7. Click the Add values for the topmost field icon. Add Values to Lookup By selecting load values, you will retrieve a distinct list of values from the database that define the International Indicator. (A blank row can also be a value.) 8. Complete the Text column with D for Domestic and I for International. 9. Select OK. Page 154 Confidential TRX, Inc.
Building a Lookup continued Completed Lookup Your Lookup window should now look like the figure below. 10. Select the Exit Lookups icon to save your work and to return to the Main Menu. Page 155 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 used anywhere you enter a SQL expression such as Calculations or Filters in reports or in profiles. Please see the steps below to create a new list. Build a List Exercise 1. Select the New icon (or File – New ) and select List. 2. Enter the name of the list (example: Preferred Airlines). 3. Enter a description of the list. Page 156 Confidential TRX, Inc.
4. Click the Add Value button and enter one value. Repeat this step for every value that needs to be added. 5. When the list is complete, either click on List – Save, or click on the Exit List Editor button and it will prompt the user to save the list. How to Use a List To use the list, either within a calculation or a filter, you will have the Expression Builder box open. Enter the other parts of the SQL statement and then click on the name of the list and drag it into the expression box.
Import/Export You can export or import reports, profiles, lookups, scripts, batches, lists, and dictionaries to/from your environment to another environment. External clients can use this to send TRX things that may not be working. We would then import them into our environment to see what the problem might be. Export You can export any object so that it can be transferred to someone else’s TRAVELTRAX, or to create a back-up copy of reports, profiles, batches, etc. 1. From the main menu, choose Tools. 2.
Import Choose this option to Import TRAVELTRAX Reports, Profiles, Batches, etc. that were exported from your or another person’s copy of TRAVELTRAX. You must select the directory where the files are located before you can begin the import process. Page 159 Confidential TRX, Inc.
Portal The TRAVELTRAX Portal gives you the ability to license your TRAVELTRAX software, review incidents, submit new Support requests, and download documentation. The site is designed to help you quickly find the information you are looking for and to help you manage your Support incidents. Home Page The Homepage of the TRAVELTRAX Portal gives general information on TRAVELTRAX. Page 160 Confidential TRX, Inc.
Licensing The licensing section allows you to re-license your TRAVELTRAX software. To use this tool, please follow the instructions below. 1. Click on the Licensing tab. 2. Get the challenge code from the software you need to re-license. Each piece of software below has a licensing option within the application. If your license has expired, you can click on that option and it will give you a challenge code. 3. Enter the challenge code in the appropriate section for the software you need to re-license.
Documentation In this section you will find documents that cover various TRAVELTRAX subjects. Click on the name of the document to download it. Page 162 Confidential TRX, Inc.
Create a Support Request Below are the steps to take when you need to create a TRAVELTRAX Incident/Ticket. These tickets should be created when you are having problems with any TRAVELTRAX application. 1. Access the TRAVELTRAX Portal: https://portal.traveltrax.com. 2. Enter your email address and password. This will display the TRAVELTRAX Portal home page. Page 163 Confidential TRX, Inc.
Create a Support Request continued 3. Click on the Support Request tab. This will display the Support Request page with your contact information pre-filled according to your login. If any contact information needs to be changed, please make the changes in the appropriate boxes. 4. Click the drop down area and select the area of customer support that best applies to your situation. 5. Enter the subject for your support request. Page 164 Confidential TRX, Inc.
Create a Support Request continued 6. Enter a summary of your problem/issue in the Comments’ box. Put in as much detail as possible so the problem can be researched and resolved. 7. If more detail is needed (such as screenshots, word documents, etc.), please enter the file(s) by clicking on the Browse button, selecting the file(s) and clicking the Upload button. 8. Once all information is entered, click on the Submit button.
View an Incident Once a support request form has been entered, the user may check the status of the incident by following the steps below: 1. Click on the Incident tab. 2. Enter the Incident number. 3. Click on the Go button. This will display the incident information which includes the following: The date this incident was last updated. The status of this incident. The target completion date or comments. The date this incident was created. The title of the incident when it was submitted.
Search for an Incident If you do not have the incident number, you can search for any incidents that have been created by anyone in your company. Follow the steps below to search for an incident: 1. Click on the Incident Search tab. 2. Enter one or more of the following (this will depend on what information you have to search with/for): Search Comments – enter the search comments to pull up an incident that includes those comments. Date Range – enter a date range (start & end date).
Report Examples Page 168 Confidential TRX, Inc.
Page 169 Confidential TRX, Inc.
Page 170 Confidential TRX, Inc.
Page 171 Confidential TRX, Inc.
