Example 8
Introduction
Previous examples demonstrated how to use Argos tools to visually create SQL queries.
There are times when you already have an SQL query that you would like to enter into Argos without having to type in the query manually. This is possible using the Free Type SQL Editor which exists under the Report Query Tab. In the figure below the pacifier icon on the lower right is used to launch the Free Type SQL Editor.
Figure 92 –The Free Type icon within the Report Query –Design tab
Entering the Free Type Query
Clicking the Free Type icon launches the Free Type Editor shown below. Either type-in your query or paste it into the right hand window shown below.
Figure 93 – The Free Type Editor
Free Type/Visual Design toggle
After selecting the Free Type query by clicking this icon, the icon changes to the hardhat/hammer (Visual Design) icon. If you subsequently choose to create your query within the Visual Designer, click the hardhat/hammer icon to launch the Visual Designer (however the contents within the Free Type query will then be lost).
Type (lower left)
Allows you to highlight a field and change the data type from the drop down list.
abc
Clicking this icon displays a list of System Variables that can be added to your query.
Operators
The operators at the bottom of the window allow you to add logical operations to your query.
Validate the Query
Click the Commit button to save the query. After committing, you will be prompted to validate the query by Refreshing the Fields. Click the Refresh Fields icon to create the fields. Argos will populate the window on the left with the fields that it found in the SQL (see figure below). This step is required for Argos to validate that the query matches the database that it is connected to. You can click the Green test button to run the query and verify that it works properly in Argos.
Figure 94 –Validating the query
Apply Field Security
If you wish to apply security to individual fields, this can be accomplished by clicking the Field Security icon. The following dialog box will appear in which you can apply Allow/Deny privileges to Argos users or groups for each selected field. The figure below shows that security is to be applied to the “salary” field, which is highlighted.
Figure 95 –Applying field security
Filter and Sort the Query in CSV, Banded, and Extract Reports
When creating CSV, Banded, and Extract Reports, the person creating the report can make use of the Filters and Sort Tabs (see figure below) to modify the query results obtained
Refresh Fields icon
The commit and Test buttons
Remember to Commit(Save) your work and test the query as you make changes.
Field Security icon
Figure 96 –The Filters and Sort tabs used for Report Design
When creating Filters and Sorts, Argos adds SQL WHERE and ORDER BY statements to the SQL existing in the DataBlock.
Since Free Type queries are not created by Argos, Argos needs to be guided as to where the WHERE and ORDER BY statements should be added to the SQL query. This is handled by special characters added to the query. The special characters are added by clicking the
“Insert Special” icon within the Free Type Editor. The use of special characters is discussed in the next section.
If the Filter or Sort tab is selected during the design of the report, and a Free Type query exists in the DataBlock, then the message in the figure below will direct you to add the special characters to the Free Type query.
Figure 97 –Reminder to add special characters when using Free Type Query
Adding special characters for filtering in CSV, Banded, and Extract Reports If you feel that your CSV, Banded, or Extract report will require the use of a filter, then return to the Free Type Query Editor and add a special character into the query where the filter (WHERE statement) should be placed.
In this example, the Employees table in the sample database is used. A filter will be applied to the salary in the CSV/Banded/Extract report, therefore the WHERE clause needs to be added to the end of the query. Therefore, position the cursor on the first blank line at the end of the query, and click the Insert Special icon. This brings up the “Insert Special Sequence” dialog box shown below.
Select “New Filter”, then click the OK button.
Insert Special icon
Figure 98 –Adding special characters for filtering
The string “--$newfilter” is inserted at the end of the query. This string serves as a placeholder such that when the Filter tab is selected when creating the CSV/Banded /Extract report, the WHERE clause that the Filter creates will replace the -$newfilter string.
Note the location of the special character string in the figure below.
If your Free Type query already has a WHERE statement, (and you are adding a second filter through the report design), then select “Add Filter” instead of “New Filter. This will prevent Argos from replacing the existing WHERE statement and will add the second WHERE statement.
Figure 99 –The Free Type query after adding special characters for filtering You can now use the Filter tab when designing your CSV, Banded, or Extract Report.
Adding special characters for Sorting in CSV,Banded, and Extract Reports If you desire to replace an ORDER BY statement in your Free Type SQL with a sort criteria specified in the CSV/Banded/Extract report design, this is done in a similar fashion as described above for Filters.
$newfilter
This special character must be placed at the appropriate location within the query since it will be replaced by the WHERE statement created by the CSV/Banded/Extract Report design.
This example contains a Free Type query with an ORDER BY statement to sort employees by salary as shown in the figure below. If you want to override the sort specified in the Free Type query by the sort defined in the report design, then special characters must be added to the Free Type query. The ORDER BY statements to be replaced must be surrounded by Begin Order and End Order special characters as shown below. These special characters are added by clicking the Insert Special icon.
Figure 100 –The Free Type Query after adding special characters for sorting
Position the cursor at the desired location, then insert the special character strings and the ORDER BY statement. SQL statements between the --$beginorder and --$endorder statements will be replaced by the ORDER BY statement created as a result of the sort criteria specified in the CSV/Banded/Extract Report Design.
Edit ADO Properties
This option allows you to change the way Argos is communicating with the database.
Consult your MAPS Administrator before changing ADO properties.
Summary
Use of special characters within Free Type queries is required if the CSV, Banded, or Extract Report Filters or Sorts the query results provided by the DataBlock. The special characters must be placed at the appropriate location in the query such that Argos can replace the special characters with WHERE or ORDER BY statements from the report design.
$beginorder and $endorder
These special characters must be placed at the appropriate location within the query and surround the ORDER BY statement. The statements surrounded will be replaced by the ORDER BY statement created by the CSV/Banded/Extract Report design.