Missouri State University

Skip Navigation Links
Missouri State University

QDUG - Tips and Techniques

Please enable JavaScript.
This application will not function properly if JavaScript is disabled.
Argos
ACCESS
EXCEL

Argos tips

Datablock Designer Power Tips

TIP: Add a record count to the preview multi-column in your data block.First create the multi-column then change the property. Show Item Count. This will give you a total count of records at the bottom of the preview window.

TIP:  Using ADDRESS_BY_RULE - The ADDRESS_BY_RULE table works as a hierarchy of addresses for a person depending on a rule.

TIP:  Converting Time in Argos Reports To convert time from the following HH24MISS (161300) format to HH:Mi:SS AM/PM (4:13:00 PM) format, you can use the following in a calculated field to_char(to_date(tablename.columnname, 'hh24mi'), 'hh:mi AM') i.e. to_char(to_date(begin_time, 'hh24mi'), 'hh:mi AM')

TIP:  Joining Tables in Argos - When joining tables in Argos, it is best to join by the Person_UID rather than the ID where available. The Person_UID is an indexed field and will make your queries run alittle quicker.

TIP:  Outer Joining in the where clause - Situation - If you have a list of students and what to show only a certain type of Address, and some student may not have that type of Address, but you still want to list them on the report. You can outer join in the where clause. Do this in the GUI, make a calculated field and add in the field that you want to filter in this instance Address_type and add a (+) to the end. This will left outer join this field to your query. It will look like the picture below.


This will allow all students to be shown on the list but will give you the addresses of student with the Address Type specified.

TIP:  Registration and Enrolled Indicators - What is the definition of these indicators and how they are used in Banner. (Contributed by Kim Bell)

  • When using the Enrollment View in ODS: Students with a registered indicator (registered_ind) set to N means they were once registered but are not currently registered.
  • When using the Enrollment View in ODS: Students with a registered indicator (registered_ind) set to null means they were never registered for that term.

TIP:  Using Z_TELEPHONE_BY_RULE view - The Z_TELEPHONE_BY_RULE view works as a hierarchy of phone numbers for a person depending on a rule. TIP: Using Date Parameters in the select clause of a query in Argos: If you will be using a date parameter in the select clause of your query you must use the TO_DATE Function. This is not an Argos bug it has to do with our setup and how variables are used. However, you do not want to use the TO_DATE function in the where (condition) clause.

EXAMPLE:
Select to_date(:parm_dt_date, 'MM-DD-YYYY') from dual where sysdate = :parm_dt_date;

Access tips

TIP:   If you're using the SQL window and the SQL is not accepted by Access, then use the "passthru" query property to pass the SQL directly to the iSeries for processing. This can be found on the menu bar under Query -> SQL Specific

Excel Tips

TIP:   When you save the workbook, Excel saves a the query inside the file. When you reopen the file and click on one of the data cells, you can then edit the query that produced the data.

TIP:   If you are building a query using the graphical interface and need to use a left or right outer join, you can do this by double clicking the line between the two tables. When the dialog box for joins opens, you can select one of the other join types that MS Query supports. If you need to use an exception join, then you must open the SQL window and enter the join syntax directly.

TIP:   To use parameters in an Excel query, to the following: When the query is open, in the criteria window, instead of entering a value for a column you want to select on, enter a prompt string surrounded by square brackets, i.e., [Type a SSN]. When you run the query, a dialog window in appear with the prompt string you entered and a place for you to enter the value.

TIP:   The SQL window can be used to directly enter the SQL you want to execute, modify the SQL built using the graphical interface, or paste the SQL from a QM query. To copy the SQL from QM, display the query in QM, copy the SQL to the clipboard, open the SQL window in MS Query, and paste the copied SQL.

TIP:   When you save a query in MS Query, the default directory is hidden which means that it will not normally display when you look at your files and directories. The easiest was to find your saved MS Query queries is to click Start -> Search and look for files with a dqy extension, such as query1.dqy. Be sure to search hidden files and folders. Or you can save the queries in a location that is not hidden.