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, what parameters are available for a data extract?

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

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 check individual columns to return.
Include column names Under "Include Column Names?", choose Yes (default) or No to specify whether the names of the selected columns appear in the first row of the output.
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.
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.

    The Push To option allows you to send the results of your report to another user. This output works the same way as sending to Completed Reports; however, it allows you to select one or more other users to send a completed report to as well; see In the IUIE, how do I push a report to another user?

  • 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 alternate 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 particular datagroups that 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 using 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 zeroes when outputting to the XSL format. This is especially useful when dealing with University IDs.

Buttons

Run Executes your query against the database
Novice Displays the screen without the "Additional Criteria", "Record Delimiter", and "Field Delimiter" selection fields
Advanced 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 Displays a screen of values valid for the current field
Save Settings Saves a data extract or template form as a shortcut in My Catalog; see In the IUIE, what are shortcuts, and how do I save settings as a shortcut in the Master Catalog?
Reset Query Erases changes on the current screen
Mailing Provides additional options if you have been authorized to use mailing functionality; see In the IUIE, how can I use mailing functions in a data extract?
Run Against Test Allows authorized testers, Data Managers, developers, and IUIE Admins to 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 December 20, 2011.

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.