Add

 

In the Datasource type dialog box, select a desired data source type and click [OK] to create a new data source according to the system instructions.

Text file

Data of this type of data source comes from a text file.

Click  and select Text file and the following window appears:

Name

Source text file name.

 

Option

Select an option to be used in loading the text file with f.import() function.

 

File name

A file name can be an absolute path or a relative path which is relative to the path set in mainPath property in the configuration file raqsoftConfig.xml. Click  to select a text file on the pop-up to open.

 

Filter expression

A filtering expression, such as EID=10 and EID=id, in which id is the parameter defined in Edit -> Parameters, is used to filter data in a text file.

Separator

It lets you choose a separator used in a text file; default is TAB. Select one in the drop-down list, click Refresh fields button , and all fields will be listed.

 

Charset

The character set a file uses. There are four choices: default, GBK, UTF-8 and ISO-8859-1. The default is determined by the OS.

 

Data is sorted on keys

With this option selected, data will be automatically sorted by the primary key in ascending order. This design can make the computation faster.

 

Field

The field list displays a selected text file’s fields – by default all fields will be listed, where you can set a key field and perform GROUP BY aggregation over a certain field. The available aggregate operations are sum, count, getting max and min. The aggregates column will be appended at the end. Click  to add a field or  to delete one.

 

Note: When a primary key is defined, data will be automatically sorted by the key ascendingly to speed the data source connection. Correspondingly, the result view will be ordered by the primary key in ascending order. If there isn’t a primary key, the system will check whether the associative field values are distinct when performing the connection. If they are unique, they are eligible for being a primary key; if they are not but you proceed with the execution and preview, a prompt reading “The associative field isn’t the primary key!” will appear. In this case, you can open the Relation setting window and select Remove duplicate key values to delete the duplicates but retain the first values in the target field.

 

●Example: Create a text file source based on emp.txt.

1.Click  on the Datasource window to pop up the following Datasource type box:

2.Select Text file and click [OK] to open Text file dialog box:

3.Click  to select emp.txt, or enter the file’s relative path after File name.

4.Enter mgrid=12 after Filter expression to select employee records where mgrid is 12. Then set empid field as the primary key. Below is the settings:

5.Click [OK] to complete creating a text file data source.

Excel file

This type of data source comes from Excel files.

Click  and select Excel file on Datasouce type window to open Excel file dialog box:

Name

Source Excel file name.

File name

The file name can be an absolute path or a relative path which is relative to the path set in mainPath property in the configuration file raqsoftConfig.xml. Click  to select a text file on the pop-up to open.

 

Filter expression

A filtering expression, such as EID=10 and EID=id, in which id is the parameter defined in Edit -> Parameters, is used to filter data in an Excel file.

 

Sheet

You can select an Excel worksheet and load it. Default is the first worksheet.

 

Password

The password for accessing an Excel file.

 

Data is sorted on keys

With this option selected, data will be automatically sorted by the primary key in ascending order. This design can make the computation faster.

 

Field

The field list displays a selected text file’s fields – by default all fields will be listed, where you can set a key field and perform GROUP BY aggregation over a certain field. The available aggregate operations are sum, count, getting max and min. The aggregates column will be appended at the end.  Click  to add a field or  to delete one.

 

Note: When a primary key is defined, data will be automatically sorted by the key ascendingly. This will speed the connection to the data source and make the result view ordered by the primary key in ascending order. If there isn’t a primary key, the system will check whether the associative field values are distinct when performing the connection. If they are unique, they are eligible for being a primary key; if they are not but you proceed with the execution and preview, a prompt reading “The associative field isn’t the primary key!” will appear. In this case, you can open the Relation setting window and select Remove duplicate key values to delete the duplicates but retain the first values in the target field.

 

●Example: Create an Excel file data source based on score.xlsx.

1.Click  on the Datasource window to pop up the following Datasource type box:

2.Select an Excel file and click [OK] to open Excel file dialog box:

3.Click  to select emp.txt, or enter the file’s relative path after File name.

4.Select Sheet1 in the drop-down list after Sheet; default is the first worksheet.

5.Enter score>=90 after Filter expression to select student records where score is greater than 90, select Count as the Aggregate type for id field and delete subject field and score field. Below is the settings:

6.Click [OK] to complete creating an Excel file data source, where the number of students in each class who have scores equal to and greater than 9 is displayed.

Database table

With this data source type, it means the data source comes from a database table. Based on a table in a database, the system executes a simple SQL query to retrieve data meeting a certain condition to make a data source. The Data File Tool lets users select a database table and edit filtering expression on an easy-to-handle dialog box, and then automatically generates a SQL query based on the settings to get the desired data. This functionality is designed for SQL beginners.

 

Click, select Database table on Datasouce type box to open Excel file dialog box:

Name

Source database table name.

 

Data source

Select a data source connection from the drop-down list to get a data table. The data table originates from the data source connection configured in Tool -> Datasource.

 

Table

It lets you select a to-be-queried database table.

 

Add quotes to table

It determines whether a database table name is quoted.

Where

The filtering statement filters the selected database table. For example: 

ID=10

Or ID=?, in which the question mark “?” represents a parameter value defined in the parameter list;

Or ID=id, in which id is the parameter defined Under Params.

 

Quote type

There are three types of quotes – Single quotes, double quotes and MySQL.

 

Data is sorted on keys

With this option selected, data will be automatically sorted by the primary key in ascending order. This design can make the computation faster.

 

Params

It IS For setting parameter type and value, as shown below:

Field

The field list displays a selected text file’s fields – by default all fields will be listed, where you can set a key field and perform GROUP BY aggregation over a certain field. The available aggregate operations are sum, count, getting max and min. The aggregates column will be appended at the end.  Click  to add a field or  to delete one.

 

Note: When a primary key is defined, data will be automatically sorted by the key ascendingly. This will speed the connection to the data source and make the result view ordered by the primary key in ascending order. If there isn’t a primary key, the system will check whether the associative field values are distinct when performing the connection. If they are unique, they are eligible for being a primary key; if they are not but you proceed with the execution and preview, a prompt reading “The associative field isn’t the primary key!” will appear. In this case, you can open the Relation setting window and select Remove duplicate key values to delete the duplicates but retain the first values in the target field.

 

● Example: Create a data source based on database table EMPLOYEE.

1. Click  on Datasource window to pop up the following Datasource type box:

2.Select Database table and click [OK] to open Database table dialog box:

3.Select “demo” in the drop-down list of Data source and all tables in demo database will be listed in the drop-down list under Table:

4.Select EMPLOYEE table, enter filtering expression EID=? after Where, set EID as the primary key under Field and add a parameter under Params:

The filtering expression after Where can be EID=3, too.

Values under Params can also be parameters, like arg1, which are defined in Edit - > Parameters.

5.Click [OK] to complete creating a database table data source.

SQL

The data source is fed by a database table or joined database table through a SQL query containing a condition. Users don’t need to type SQL queries manually. Data File Tool provides an easy-to-handle wizard for selecting table and fields, setting filtering condition, performing connection, and choosing a grouping, filtering and sorting method. Based on these settings, the system will generate SQL queries automatically. Compared with a database table source, these SQL queries are complicated. This functionality is designed for skilled SQL users.

 

●Example: Create a SQL data source based on EMPLOYEE table and SALES table stored in demo database.

1.Click  on Datasource window to open the following Datasource type box:

2.Select SQL and click [OK] to open SQL dialog box:

3.Select “demo” in the drop-down list of Data source and click  to open the SQL editor window:

4.Choose a schema and select a table under the Available tables, click the right arrow to move it under Selected tables. Or you can double-click a table to select it.

5.On the Field tab, select a data table in the drop-down list and all its available fields will be listed below. Select a field and click the right arrow to transport it under the Selected fields; or you can double-click a table to select it. Click a selected field and then select a summarization method to perform aggregation.

6.On Where tab, click  to add a condition. For example, if you want to get data where DEPT is “Sales” and State is “California” or “Florida” from EMPLOYEE table, add these conditions under Where as below:

7.On the Join tab, click  to add a joining condition. For example, if you want to display DEPT and STATE fields in EMPLOYEE table and AMOUNT field in SALES table, you need to join the tables through a joining condition, which can be set under Join as below:

8.You can group data by adding grouping fields on Group tab. For example, to group result set by DEPT and STATE fields of EMPLOYEE table, first you need to select the data table and then double-click the two fields respectively to select them:

9.Having tab lets you add a filtering condition. For example, if you want to select summarized data where AMOUNT is greater than 100000, add a filtering condition as below:

10.On Sort tab, select to-be-sorted fields under Order via the right arrow:

11.On SQL tab, you can view the complete SQL query:

12.Click [OK] to finish editing the SQL query. Then click Refresh fields button  to load the resulting fields: