e-Services SQL Editor Form

The SQL Editor form is used to either update or create new reports. Name, description, access, folder name and connection information is added or edited.

Helpers are available for writing SQL including basic SQL commands, basic SELECT and Aggregate functions, and operators for use in WHERE clauses. "Insert Parameter" buttons enters the brackets for a parameter. View Results buttons are available for testing the SQL statement.

 

Navigating To

The SQL Editor form is navigated to from several links located on the Reports List Menu page and the New Reports List with Folders Menu page:

• Add Report. Clicking on this link will open a new SQL Editor form.
  
  
• Update. Clicking on this link will open the SQL Editor form for the associated report.
  

SQL Editor Form Division

There are two main areas of the SQL Editor form. The upper section is for entering the report's description information. The lower section is for developing the SQL statement.

 

Upper Section (Adding Report's Descriptive Information)

1. Name. Enter a Name for the report.
   
   Suggestion: Carefully name your reports. When selecting a name, visualize a list of 100 report links and your method of finding the report. Use the purpose of the report or the data accessed by the report as prefix with which you group your reports. The reports are sorted alphabetically. A new function allows for the reports to be listed within a folder.
   
   
   
2. Description. Enter a Description for the report.
   
   
   
3. Access. Click the radio button corresponding to level of access assigned to the report.
   

• For my personal use. Only you will have access to this report.
  
  
• For everyone in my department(s) Everyone in your department plus any departments that are selected in the next step will have access to this report.
  
  
  

4. Select, using the drop-down list, additional departments that will have access to this report.
   
   Suggestion: Hold the Shift key down to select a range of consecutively listed departments. Hold the CTRL key down and click on individual departments to select more than one.
   
   
   
5. Folder. Select from the "Lookup:" drop-down list the folder that the report is to be listed under. If a folder does not exist then type in a new folder name in the "Name:" field.
   
   
   
6. Connection. Enter in the name of the database that the report will be using.
   

Lower Section (Adding Report's SQL Statement)

The lower section is for entering the SQL statement for the report. You can directly type the SQL statement in the text field or there are several tools available to aid you in developing the SQL statement.

Note: See SQL Statements.

1. There are several links located at the top of the lower section that provide useful functions or helps:
   
   
   • SQL Builder GUI Editor. This link will open the SQL Builder GUI Editor in a new window.
     
     Note: See Using the SQL Builder GUI Editor.
     
     
     
   • Data Dictionary. This link will open the Data Dictionary. This page is useful for finding table and column information regarding the database.
     
     Note: See Data Dictionary.
     
     
     
   • Clear SQL Box. Clicking on this link will clear the text entered in the SQL Statement field. A window will open verifying that you want to perform the function.
     
     
     
   • ADM Clusters. This link will open a new window with the list of the Select Column Clusters Excel for developing Prospective Student audiences.
     
     Note: See Clusters.
     
     
     
   • ADM Snippets. This link will open a new window with the list of WHERE Snippets for developing Prospective Student audiences.
     
     Note: See Snippets.
     
     
     
   • CS Clusters. This link will open a new window with the list of the Select Column Clusters for developing Current Student audiences.
     
     
     
   • CS Snippets. This link will open a new window with the list of WHERE Snippets for developing Current Student audiences.
     
     
     
2. SQL Statement. Enter the SQL statement in this text field. You can either directly type the SQL statement, use the SQL Helpers to insert the SQL commands, functions and operators, or use the SQL Builder GUI Editor to develop the SQL statement and add it in this text field.
   
   Attention: Take care not to write SQL statements that will result in errors. Running SQL statements with errors will cause unnecessary performance drain on the database server.
   
   
   
3. Save SQL. Clicking on this button will save the Report with the SQL statement.
   
   
   
4. Preview (Browser). Clicking on this button will display the result of the SQL statement in an HTML format.
   
   Note: If your SQL statement contains parameters, the Preview buttons will not work since the statement requires additional data entry screens.
   
   
   
5. Preview (Excel). Clicking on this button will display the result of the SQL statement in Microsoft Excel.
   
   Note: If your SQL statement contains parameters, the Preview buttons will not work since the statement requires additional data entry screens.
   
   

 

SQL Helpers

SQL Helpers are located to the right of the lower section. These helpers provide quick insert of SQL commands, functions and operators.

Note: When selecting a command etc., the selection will be inserted in the SQL Statement text field at the end of the existing SQL statement. The helper inserts will be within brackets. These helper inserts will list the expected parameters. The desired parameter, in most cases, must replace the brackets and the helper inserts.

Suggestion: Helper Inserts can be turned off so that only the desired command/function/etc. will be inserted.

1. Tables/Views. Select, from the drop-down, the table that you would like to insert into the SQL command.
   
   
   
2. SQL Commands. Select, from the drop-down list, the desired SQL command. .
   
   
   
3. SQL Functions. Select, from the drop-down list, the desired SQL function. .
   
   
   
4. SQL Boolean Operators. Select, from the drop-down list, the desired boolean operator to be added to the SQL statement. .
   
   
   
5. SQL Comparison Operators. Select, from the drop-down list, the desired comparison operator to be added to the SQL statement.
   
   
   
6. Insert Parameter. Clicking on this button will insert brackets with the expected parameter. The desired parameter must be placed within the brackets.
   
   Note: See Entering Parameter Selections SQL Statements or Entering Menus for Parameter Selection in SQL Statements.
   
   
   
7. Turn Off Helper Inserts. Checking this box will stop the helper inserts from being inserted with the selected command/function/operator.

Using the SQL Builder GUI Editor

The SQL Builder GUI Editor is designed to aid the developer in quickly developing SQL statements.

Note: The SQL Builder GUI Editor is only available for the creation of new SQL statements. It cannot be used for editing existing statements.

1. Click the SQL Builder GUI Editor link. A pop-up window appears with a list of tables that you may access.
   
   
   
2. Click on a table name from the pop-up window.
   
   Note: Press the Control Key and click on the desired table names to select up to two tables.
   
   
   
3. Click View Fields. A list of fields appears at the top of the next page, and several rows appear for you to enter criteria to filter the report.
   
   Note: If two tables were selected, you must select Join Fields by clicking on the fields from the drop-down lists.
   
   Suggestion: Check the Distinct checkbox if only distinct (different) elements are desired in the return.
   
   
   
4. In the first criteria row, click the drop-down list to select a field to show and/or filter.
   
   Note: If you want every field in the table to display, select the *.
   
   
   
5. If you desire the field to show in the report, then select the Show checkbox.
   
   Note: If you selected the * for the first row, then you should turn-off the Show checkbox as the field will already be displayed on the report.
   
   
   
6. Select, from the drop-down list, the aggregate function to apply, if applicable.
   
   
   
7. Select, from the drop-down list, the desired Order By function (asc or dsc), if applicable.
   
   
   
8. Select an Operator you will use to identify the data in the field you selected.
   
   Suggestion: If you select the Like operator, you may use the SQL Wildcard character % to search for any part of the field containing the value.
   
   
   
9. Enter your criteria in the text field.
   
   Suggestion: To do a sub query, open another window (press the Control and the "N" keys) and create your new query. Copy and paste the query into the Criteria text box.
   
   
   
10. Repeat steps 4 and 9 until all of your filter criteria have been identified.
    
    
    
11. Click the View Results in Browser button to view the data in HTML.
    
    
    
12. Click the View Results in Excel button to view the data in Excel.
    
    Attention: If the first field in your SQL query contains only numbers, you will receive the error message "Excel has detected that 'sqlbuilder4_results.asp' is a SYLK file, but cannot load it.." Edit your SQL query and use a field containing text as the first field in your query.
    
    Suggestion: If one of your fields contains leading zeroes that you wish to preserve (for example, a zip code in a mail merge spreadsheet), you will need to reformat the column in Excel by clicking Format | Cells on the menu bar. In the category list, click Special. In the Type list, click Zip Code. Click Ok to continue.
    
    
    
13. Click the Back button in your browser and edit your criteria as necessary.
    
    
    
14. When you are satisfied with the results, click the Copy this SQL Statement to the Text Form button. The SQL is automatically entered into the SQL Statement text box.
    
    
    
15. Click the Save SQL button.
    
    
    
16. Click the Reports List link to return to the Reports list.

Entering Parameter Selections SQL Statements

The following instructions will aid the user in inserting parameter selections in their SQL statements.

Note: In order to use parameters, you need to save the report first.

1. Write the WHERE clause and test the SQL statement.
   
   Suggestion: Make sure there are no errors in the statement or in returning too many or too few results.
   
   
   
2. Replace the applicable WHERE value with the parameter statement. For example, if a date is your parameter, your WHERE might say: WHERE date_from = TO_DATE('12/31/2004','MM/DD/YYYY').
   
   Note: To insert the parameter, replace the date, '12/31/2004' with parameter developed in steps 3 - 5.
   
   
   
3. Enter brackets to surround your parameter, ex: [. . .]
   
   
   
4. Enter the label for your parameter, ex: [Date (ex: 12/31/2004)]
   
   Note: You do not need to include a colon after the label.
   
   Note: For text parameters, it is a good practice to include an example for the user to follow.
   
   
   
5. If there is a default value for your parameter, then enter two equal signs followed by the default value for your parameter, ex: [Date (ex: 12/31/2004)==2/29/2004]
   
   
   
6. Save your SQL statement.
   
   
   
7. Test the parameter functionality.

Entering Menus for Parameter Selection in SQL Statements

The following instructions will aid the user in inserting menus for parameter selection in SQL statements.

1. Write the WHERE clause and test the SQL statement.
   
   Suggestion: Make sure there are no errors in the statement or in returning too many or too few results.
   
   
   
2. Replace the applicable WHERE value with the parameter statement. For example, if a Greek Office is your parameter, your WHERE might say, WHERE activity_value = 'President'
   
   Note: To insert the parameter, replace the word, 'President' with the parameter developed in steps 3 - 9.
   
   
   
3. Enter brackets to surround your parameter, ex: [. . .]
   
   
   
4. Enter the label for your parameter, ex: [Greek Office]
   
   
   
5. After the label, enter two equal signs followed by the word "menu" in lower case letters, ex: [Greek Office==menu]
   
   
   
6. The menu tags are separated by double colons.
   
   Note: The parameter sequence is menu::ConnectionVariable::SQLStatement::DefaultValue/multiple
   
   
   
7. Enter the connection variable, ex: [Greek Office==menu::GreekInfoConn]
   
   
   
8. Enter the SQL statement, ex: [Greek Office==menu::GreekInfoConn::SELECT activity_value FROM xxcis_activity WHERE activity_type = 2967]
   
   
   
9. Enter the Default Value if applicable, ex: [Greek Office==menu::GreekInfoConn::SELECT activity_value FROM xxcis_activity WHERE activity_type = 2967::0]
   
   Note: If you want to allow the user to select multiple values, enter the word "multiple" in lowercase letters. Your WHERE clause should use an IN operator.
   
   Note: If you are going to allow the user to select multiple text values, then your parameter SQL statement should include the single quotes for the IN condition, ex: [Greek Office==menu::GreekInfoConn::SELECT""||activity_value||''", activity_value FROM xxcis_activity WHERE activity_type = 2967::0]
   
   
   
10. Save your SQL statement.
    
    
    
11. Test the parameter functionality.

Related Items

Report Engine Overview

CRM Procedures

Report Menu

Excel Tips

Data Dictionary