Indiana University
University Information Technology Services
  
What are archived documents?
Login>>
Login

Login is for authorized groups (e.g., UITS, OVPIT, and TCC) that need access to specialized Knowledge Base documents. Otherwise, simply use the Knowledge Base without logging in.

Close

In the IUIE, in a data extract, what parameters are available?

In the IUIE, you may manipulate a number of parameters in order to run a precise data extract. Below are tables describing these various query options and buttons.

Note: The exact parameters available will depend upon the report object selected.

Query options

Columns to be included Under "Select Columns to Be Included", you may choose which columns should appear in the output. Selecting All Columns returns all columns. Selecting Selected Columns: allows you to select individual columns to return by checking them.
Include column names Under "Include Column Names?", choose Yes or No, depending upon whether or not you want the names of the selected columns to be included in the first row of the output. (The default selection is Yes.)
Maximum number of rows to return You may specify a maximum number of rows to return in order to restrict the number of rows read from the database. This is useful for testing and debugging queries that might normally return a large number of rows. The precise meaning of the phrase "maximum number of rows to return" is as follows: read rows from the table that satisfy the SQL WHERE clause, assigning sequential row numbers until the "Maximum Number of Rows to Return" value has been met.
Return distinct rows only When Return Distinct Rows Only is checked, no duplicate rows are included in the output.

For example, to find the names of the organization managers for a particular campus, you would need to use the account/organization data group under the Financial Data Groups section. This table contains the information you need, but it is repeated for each account in every organization. Since you need only information about the organizations, select the appropriate columns (i.e., organization name, organization code, organization manager) and check Return Distinct Rows Only. Then each organization will appear only once, instead of being repeated for each account.
Output destination Under "Select an Output Destination", you may choose where the query output will go:
  • Wait for Output is the default radio button. Your query will run in the foreground until finished, and the output will go to your browser screen.

  • Send Output to Completed Reports Folder allows a query to execute in the background, which allows you to continue working within IUIE without waiting for a query to finish. The report output goes to the Completed Reports folder.

  • Send Output to Printer allows you to specify a four-character Enterprise printer ID to which to send report output.
Output format Under "Select the Output Format", you may choose between multiple formats. Web Browser (for displaying output in a browser) and Spreadsheet (for loading the output into an alternative viewer, such as Excel) are common choices.

Web Browser is best suited for quick lookups on small data sets where no further data manipulation will be required. Spreadsheet returns data in a format readable by Microsoft Excel. This option is best when you expect a large amount of output, or you expect to modify or manipulate it. You may need to configure your browser to automatically access Microsoft Excel when returning spreadsheet-formatted data for the first time.
Selection criteria Selection criteria, often called data extract parameters, are the heart of your search. The values supplied are incorporated into the WHERE clause of the generated SQL SELECT statement, thus restricting the rows returned by the query.

A sample parameter line contains the following elements:

  • The column description is linked to metadata about the column, including the datatype, size, and what datagroups contain it.

  • A column value field allows you to enter one or more values, separated by commas.

  • A Valid Values button, if present, allows selection of values for the column.

  • A Wildcards Allowed link, if present, denotes that wildcards are allowed in the "Column Values" field and makes available documentation about the use of wildcards. For more information on wildcards, see In the IUIE, what wildcards and logical operators can I use?
Include SQL query text If you check Include SQL Query Text with Output, the SQL query built from the parameter values you supply will be listed on your output.
Retain Leading Zeros of Character Columns Check this option to retain leading zeros when outputting to the XSL format. This is especially useful when dealing with University IDs.

Buttons

Run Run executes your query against the database.
Novice The Novice button displays the screen without the "Additional Criteria", "Record Delimiter", and "Field Delimiter" selection fields.
Advanced The Advanced button displays three additional query options not present on the "Novice" screen:
  • "Additional Criteria": You can add additional clauses to this text area to further refine your output.

  • "Field Delimiter": This option allows you to specify the character(s) placed between fields in a query result. This delimiter should be a character or characters not contained within any field values. The default field delimiter is a tab ("Tab" selection). Other recommended values are the tilde ( ~ ) and the comma ( , ). For Microsoft Excel, UITS recommends the tab.

  • "Record Delimiter": This option allows specification of the character(s) placed between records in a query result. This delimiter should be a character or characters not contained within any record values. The default record delimiter is a newline ("Newline" selection); UITS recommends this for Microsoft Excel. Another recommended value is Carriage Return + Line Feed (CR/LF). Other selections are rarely used except in special situations.
Valid Values The Valid Values button displays a screen of values valid for the current field.
Save Settings The Save Settings button saves a data extract or template form as a shortcut in My Catalog. For more information on the Save Settings button, see In the IUIE, how can I save my settings as a shortcut in My Catalog?
Reset Query The Reset Query button erases changes on the current screen.
Mailing If you have been authorized to use mailing functionality, you will see a Mailing button as well. For information on mailing functions, see In the IUIE, how can I use mailing functions in a data extract?
Run Against Test Authorized testers, Data Managers, developers, and IUIE Admins can use the Run Against Test button to run the production version of the datagroup against test data located on DSS1TST.

This is document aljt in domain all.
Last modified on January 08, 2009.

Comments/Questions/Corrections

Use this form to offer suggestions, corrections, and additions to the Knowledge Base. We welcome your input!

If you are affiliated with Indiana University and would like assistance with a specific computing problem, please use the Ask a Consultant form, or contact your campus Support Center.

Contact Information

Note: We will reply to your comment at this address. If your message concerns a problem receiving email, please enter an alternate email address.