Tips and Techniques

How can I display data from two fields together (i.e. putting a first name from one field together with the last name from another field, with a space between the two names)?

1. Use a plus sign (+) between the two fields in the selection definition to combine the fields.
2. Use a blank space with quotation marks (' ') between two fields to show a space in the results.
3. For example, if you have John in the first_name field and Smith in the last_name field you can combine the fields like this; first_name + ' ' + last_name to display John Smith.

How can I limit the number of characters of data displayed or used in a selection (i.e. display only the first two character college number from the full six character OUC number)?

1. Use the Substring function with the field name in the selection definition to display only part of the field.
2. The format for the definition is as follows:
   SUBSTRING(FIELDNAME , starting_character_position , number_of_characters)
3. For example, to change the 6 digit OUC number 653201 to display only the college number which is in the first two positions of the OUC; use SUBSTRING(OUC,1,2) to display 65.

How can I select all transactions after a date in the middle of the year, and not get any prior year transactions? The date field is formatted with month, day, and year (MMDDYY) and when I select dates after June, 1996 (>063096), I also get transactions from 123195.

1. Use the Substring function. with the date field in the selection definition to define only the year and month characters.
2. The format for the substring definition is as follows:
   SUBSTRING(FIELDNAME , starting_character_position , number_of_characters).
3. For example, to display only the year and month characters from the date field (MMDDYY), which also includes the day characters, define the following in the selection criteria:
   SUBSTRING(DATE,5,2)+SUBSTRING(DATE,1,2).
   This will display the date '123196' as '9612', and the date '123195' as '9512', and the date '070196' as '9607'.
4. Then select all records greater than '9606'.

How can I convert the results of a Q+E query into a text file without headings (i.e. create a file of transaction records for processing on the mainframe)?

1. Format the query results exactly the way you want each field to look to match the record layout, including the correct spacing between fields.
2. From the menu bar select the File/Save As... option.
3. On the Save As panel, select TextFile in the Destination field, enter the new file name (i.e. query1.txt) in the File Name field, and enter the appropriate Drive and Directory for saving the file.
4. DO NOT select Use Headings for Field Names, and then click the Options... button.
5. On the Text Save Options Panel select ANSI, Fixed Length Values, and DO NOT enter any Delimiter Character; then click OK on both panels.
6. The resulting saved text file is ready to be passed to the mainframe for processing. Contact ACS to coordinate the next step in this process.

How can I create a BrioQuery, when a database, which is needed for the query, is not included in the Open Catalog Extension (OCE)?

1. See the steps for customizing an OCE.

How can I re-size the column in a BrioQuery Pivot Table, when the data does not fit and is displayed as #######?

1. Point the cursor at the top of the column; in the area above the data and/or header label. Click the mouse button once and a line should appear along the right side of the column. If the line is not there, you have pointed to high or to low in the column. Try again until the line, along the right side, appears.
2. When the line is visible, you can select the Format/Squeeze option from the menu bar and automatically size the column to fit the widest data in the column. You can also use the mouse with the double arrow to move the line to any size you desire.

Q+E - How to Guide

Q+E Query

BrioQuery - How to Guide

BrioQuery - Advanced Features Tour

BrioQuery

RETURN TO DATA EXTRACTS - OVERVIEW