3.2. Data in QuickDesigners and Maps

3.2.1. Select a Data Source

QuickDesigner Reports, QuickDesigner Charts, Online Maps and SVG Maps use the same interface to manage data - Data Source Dialog . The Data Source Dialog allows end users to select, filter, and present data without mastering database structures, and all with zero client download. For full control of data sources, please see Section 3.1 - Data in Organizer.

The Data Source Dialog prompts you to select the data registry and the data source that you would like to use. A user must have read privileges to one of the registries defined in the Organizer. For more about creating and managing data registries, please see Section 3.1.1 - Managing Data Registries.

Select the data registry from the drop-down menu in the upper part on the left. The pane bellow displays the content of the selected registry. Select a data source you want to use.

Data Source Dialog

Once a data source is selected, you can see records of the source in the right-hand side View DataSheet pane. If you select a DataView as the data source, you will be able to create a new DataView query in the DataView Builder in the right pane. You can also select an existing DataView query and modify it in the right pane.

3.2.2. Queries

Queries work in conjunction with Data Views. Data Views allow an administrator to pre-select tables and fields from a database and create a local view from which end users can run queries. For more information about data views and setup, please see Section 3.1.3.3 - Data Views.

If you select either a data view or a data view query as the data source, you will be taken to Data Source Dialog query interface in the right-hand pane where you can create or modify a query based on the data view.

3.2.2.1. Select Fields

If you select a data view as the data source, the DataView Builder will open in the right pane allowing you to select fields from the view for your query.

Select Columns Dialog

A select list for each heading in the view is generated on the left-hand side of the DataView Builder. To select fields for the query, click on a field on the left-hand side. The selected column will be added to the dialog on the right-hand side. To remove fields, you can select the columns from the right-hand side and click the Remove button.

3.2.2.1.1. Build Columns

You can also create computed columns from the Choose Columns dialog. To create a computed column, click the Build Formula button. This will launch the Formula Builder interface in a new window.

Formula Builder Window

The right-hand side of the Formula Builder contains a list of fields in the view, as well as the database functions. The buttons at the bottom are available operators and conditionals. The Boolean and conditional operators are more commonly used for building conditions which is covered in Section 3.2.2.2 - Set Conditions.

Once you have finished constructing the field, click OK to close the Formula Builder and return to the Choose Columns window where the computed field is added to the Selected Columns list. You can edit the formula by selecting it in the list and clicking the Modify Formula button.

You can also create an alias for any expression column by selecting it and clicking the Rename button. This will bring up a new window for you to specify the alias.

Rename Dialog

Once you have finished selecting the columns you would like to use, click the Next button to set conditions.

3.2.2.1.2. Querying Encrypted Data

Suppose you have a database that has encrypted fields, e.g. social security numbers, tax ID, passport numbers, etc in a tax payer database or immigration database. How do you allow authorized users of your (ERES) system to make ad hoc reports or view reports online so that they can see the actual field values? The other concern is that you do not want any users of your system to know the encryption/decryption key but you want them to be able to see the decrypted data.

ERES provides an advanced feature that allows you to run QuickDesigner Reports and Menu Page/report URL with automatic decryption enabled.

To do this, you need to do two things:

You need to create an XML file that gives the database URL, database driver, name of column to be decrypted, and the function to be applied when the data is being retrieved. An example file is as follows:

<?xml version="1.0"?>
<!DOCTYPE ReplaceColumnInfoList SYSTEM "QBReplaceColumnInfoList.dtd">
<ReplaceColumnInfoList>
      <ReplaceColumnInfo>
            <Driver>com.mysql.jdbc.Driver</Driver>
            <URL><![CDATA[jdbc:mysql://prodigy:3306/CAIT]]></URL>
            <TableName>user</TableName>
            <Pair>
                  <ColumnName>user.SSN</ColumnName>
                  <ReplaceValue><![CDATA[cast(AES_DECRYPT(user.SSN,'1111111111111111') as CHAR)]]></ReplaceValue>
            </Pair>
      </ReplaceColumnInfo>
</ReplaceColumnInfoList>

The DTD file used is shown below.

<?xml encoding="US-ASCII"?>
<!ELEMENT ReplaceColumnInfoList (ReplaceColumnInfo*)>
<!ELEMENT ReplaceColumnInfo (Driver, URL, TableName, Pair*)>
<!ELEMENT Pair (ColumnName, ReplaceValue)>
<!ELEMENT Driver (#PCDATA)>
<!ELEMENT URL (#PCDATA)>
<!ELEMENT TableName (#PCDATA)>
<!ELEMENT ColumnName (#PCDATA)>
<!ELEMENT ReplaceValue (#PCDATA)>

In this example, we are using a MySQL database, the table name is user, the encrypted column is SSN. When a query is run, the function AES_DECRYPT(user.SSN, '1111111111111111') will replace user.SSN wherever it may appear in the SQL statement. The function AES_DECRYPT is the decryption function in MySQL. The key for decrypting the data is '1111111111111111' in this example.

You need to set the path to the XML file in the Admin Console (Server Options tab, Security Options category - see Section 1.4.1.3 - Server Options for more details) and then restart the ERES server.

Once this is set up, when you run a report in Menu Page, using generated report URL or generating reports in QuickDesigner Reports, you will be able to see the real (decrypted) values of the encrypted columns.

You can find the example XML and DTD files shown above under the <ERESInstallDir>/help/examples/DataDecryption/ directory. You will also find a script that you can use to generate some sample data in MySQL.

When you run a report (in Menu Page, QuickDesigner Reports or with report URL) with the following query,

select user.firstName, user.lastName, user.SSN from user;

you will see the following result set:

firstName	lastName	SSN
John1	Smith1	1111
John2	Smith2	2222
John3	Smith3	3333
John11	Smith11	11111

This query below,

select user.firstName, user.lastName, user.SSN from user where SSN LIKE '1%';

should produce the following result in your report.

firstName	lastName	SSN
John1	Smith1	1111
John11	Smith11	11111

You can specify multiple fields in the xml file. To add another field from the same table, simply add another <pair> element. To add a field from another table or datasource, add a full <ReplaceColumnInfo> element. This feature also supports sub-reports and drill-downs. Any field in these components matching the information in the xml will also be decrypted.

	Note
	If you are entering SQL for your query, you must use the full name for the field in order for this feature to work. For example, `SELECT user.SSN FROM user` works, but `SELECT SSN FROM user` will not.

3.2.2.2. Set Conditions

After selecting fields, or if you select a data view query as the data source, the conditions window opens allowing you to set aggregation, grouping, and filtering for the selected columns.

Conditions Dialog

Each selected column is listed along with several options:

Actions:

Show Column Data: This will bring up a new window with column data.

Show Column Data

Condition Wizard: This will bring up a dialog allowing you to set basic conditions from a field. The first dialog allows you to select whether you would like to enter values or select them from a list.

Condition Wizard

Once you have selected the condition, a new dialog opens allowing you to set the values for the condition.

Condition Value Dialog

Select the values that you would like and click OK. The condition will automatically be added to the Condition field.

Build Condition: This will bring back up the Formula Builder, allowing you to construct an expression for the condition.

Condition:

This allows you to type a condition for the column.

Sort:

This allows you to indicate if the column should be sorted. You can select whether to sort in ascending or descending order.

Sort Order:

This allows you to specify the priority (order) for sorting columns.

Aggregation:

This allows you to specify any grouping or aggregation in the query.

Show:

This option indicates whether the column should be visible or not.

3.2.2.2.1. Parameterized Queries

Data Source Dialog also allows you to define query parameters. Parameters are defined in the same way as with other query interfaces - using the ":" character. You can define parameters directly in the conditions field, using the convention of :ParameterName to define a parameter.

Parameters can also be defined in the Formula Builder when building conditions. To define a parameter this way, click the Build Condition button to open the Formula Builder, click the Parameter button to insert a parameter. This will bring up a new dialog allowing you to enter the parameter name.

Specifying a Parameter in Formula Builder

For more information about query parameters, please see Section 3.1.3.2.2 - Parameterized Queries.

3.2.2.2.1.1. Initialize Query Parameters

Once you have specified parameters in the query, they will need to be initialized. You can initialize parameters by clicking the Initialize Parameters button. The initialization dialog will also open if you preview the query by clicking on the View DataSheet tab or if you click on the OK button closing Data Source Dialog.

Initialize Parameter Dialog

The drop-down list at the top of the dialog indicates which parameter is currently being initialized. You can access and set the options for each parameter by selecting it from this list.

The following options are available for each parameter:

Map to database column:

This allows you to specify a column from the database whose values will be used for the parameter input. Selecting this option modifies the parameter prompt that you will get when running the report or chart. If you map the parameter to a database column, you will be prompted with a drop-down list of distinct values from which to select a parameter value. If you do not map, you will have to type in the specific parameter value.

Initialize Parameter Dialog

Map to database function:

Mapping a parameter to a database function is very similar to mapping to a column. This feature allows you to map to the results of any function from your database. For example, suppose you have the following condition in your query:

Year({Orders.OrderDate}) IN (:OrderYear)

In the initialize parameter dialog, check the Map to database function box, select the function from the list and enter the parameter value for your function. The default value and data type will be automatically updated

Initialize Parameter Dialog

For certain databases, you may notice that the functions available do not specify the parameter types for functions. In this case, there will only be one function parameter field and it will be longer than normal. Selecting a column from the drop down list will be appended at the end of the field rather than replacing the existing text. You must enter all parameter values for the selected function, separating values with commas. Once you click away from this field, the default value field will be filled with function results and the data type will be automatically determined.

Default Value:

This allows you to specify a default value for the parameter. Although you do not have to specify a default value, it is recommended that you do so.

Date Variable:

This option is only available when the parameter is not mapped to a database column or function, and is only intended for parameters with variable type date/time. When you click this button, the following panel will pop up, listing all the supported keywords.

Enter Date Variable Dialog

This dialog allows you to select one of the three keywords: CurrentDate, CurrentTime, and CurrentDateTime. You may add or subtract units of time from the current date/time, allowing you to have a dynamic date range. For example, a report may have the following default values:

StartDate: CurrentDate - 1 WEEK
EndDate: CurrentDate

This would indicate that every time the report is run, the default prompt should be one week ago to the current date. Other supported measures are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. This feature only supports a single addition or subtraction. This feature does not support multi-value parameters. Note: QuickDesigner Reports date values (in the right-hand panel) correspond to the variable values entered here, not the keywords themselves as in Report Designer.

You can also use functions to define the parameter value.

Data Type:

This allows you to specify the data type for the parameter value(s). If you have mapped the parameter to a column,the data type is set automatically.

Custom Date Format:

This allows you to set the format in which the date parameter should be entered. This option is only available when you have mapped the parameter to a date/time column or when you not mapped the parameter at all and the parameter data type is date, time, or timestamp. When you check this option you can specify a date format by either using the builder interface or by entering the date format in a combination of characters that represent time elements.

Date Format Dialog

If you click the Build button, the above dialog will appear. The date/time representations are listed on the right and optional spacers and symbols are shown as a collection of buttons on the bottom. Once you have finished creating the format, click OK to save.

The date and/or time format is a series of characters and delimiters. Letters are used to represent different elements of date/time data. For more on the characters and their formatting, please refer to the documentation for the printDate() report function in Section 4.1.6.2.8.3 - Date/Time Functions.

If the parameter is mapped to a column, once you have finished designing the format, click on the update button to refresh the parameter prompt with the new format.

Prompt Name:

This allows you to specify the prompt that is given to the user in the parameter dialog.

Once you have finished setting values for all the parameters, click Ok to close the dialog and save the settings.

3.2.2.2.2. Save Queries

You can save the created query, or save the changes to the query if you selected a data view query to begin with by clicking the Save Query option in the conditions window. This will bring up a dialog prompting you to specify a name for the query.

Save Query Dialog

Once you have specified the name, click OK. The query will be saved in the data registry as a new node under the data view.

3.2.2.2.3. Aggregation Alias

If you add aggregation and group bys to the query, you are also able to specify an alias for the aggregated field.

Set Alias

Clicking on the underlined option next to the column name will pop up a dialog allowing you to specify a name for the field. The name you specify here will replace the actual field name.

Alias Dialog

3.2.2.3. View SQL Query

You can view the constructed query in the SQL tab of the Data Source Dialog.

SQL View

3.2.2.4. Preview Query Results

You can preview the query at anytime by clicking the View DataSheet tab in the Data Source Dialog.

Query Results

You can navigate through the result set using the icons at the top of the window.

	Go to the first page of the data table.
	Go to the previous page of the data table.
	Set the number of records to display per page. This will open a new dialog prompting you to specify the record number.
	Go to the next page of the data table.
	Go to the last page of the data table.