Data Sets
Data sets are custom reports formed by querying the RDM Repository using SQL.
Any SQL functions and clauses for selecting data can be used, like AVG,COUNT, SUM, JOIN.
For example, a bank might be interested in the number of branches by region or number of products offered by each branch.
| After creating a data set and deploying the configuration in the web application, the administrator needs to set the permissions for it. |
Create a data set
To configure a data set:
-
Navigate to RDM Logical Model > Data sets.
-
Right-click Data sets and select New Data set.
-
In the General tab, fill in the attributes:
Field Required Description Dataset name
Yes
Technical name of the data set written to the database.
Some names are reserved and cannot be used. See Reserved Words and Keywords. Dataset label
Yes
Name of the data set as shown in the web application.
Description
No
Free text describing the data set.
-
Switch to the SQL Query tab and specify the SQL query that generates this data set.
-
Switch to the Columns tab and specify the attributes of the generated table (based on the SQL query):
Field Required Description Name
Yes
Technical name of the column (or its alias) as written in the SQL query.
Label
Yes
Name of the data set as shown in the web application.
Type
Yes
Data type of the column.
Format
No
Value display format; works the same way as for domains. See Domains.
-
Switch to the Order columns tab and specify one or more attributes by which the results are sorted.
SQL query syntax
While the general format of the query and SQL functions are used, there are a few requirements to the SQL queries used for generating data sets:
-
The resulting column names should conform to the naming of rules of the database used: the safest way is to use letters, numbers, and the underscore (
_). This can be easily achieved by using the SQL aliases:as. -
Table names should be specified as follows:
$table_[actual_table_name]$. For example, for tableCITY,$table_CITY$should be used.
select r.name as region, count(b.code) as branch_num
from $table_BRANCH$ b
left join $table_CITY$ c on b.city=c.name
left join $table_REGION$ r on c.province=r.abbrev
group by r.nameWas this page useful?