Query / Reporting

Overview

The Query / Reporting Tool is designed to allow the user to extract data from the DDI-Connect system.

Designing a Query

To begin designing a query:

Go to the DDI-Connect, Extract Module, click the Query / Reporting item. Then click new to start a new Query:

Enter in the Query Name. Then select if you want other Users to be able to run this query or not. Select a category, if desired.

Select a data source to add to the query.

Select the Query Type. Most standard queries will use the "All Rows" option.

Select a selection or multiple selections to filter your data. The "Prompt at runtime" button allows you to chose that filter option when running the query.

Select the field columns you want to display in your query.

Select the default column sort for your query.

QT7.png

Add another data source or click finish.

Running a Query

If you click the drop-down arrow for the subject, you will see a list of pre-defined subjects:

There is also an icon to the right of the subject field:

Clicking this icon will launch the Subject Explorer screen:

The Subject Explorer allows you to search for a specific subject, or select from a list of subjects. To select a subject, you can click the subject and click OK.

If you need assistance on how to use the Subject Explorer screen, you can click the Help button in the lower left corner. This will launch the DataPA Help Manual.

Once you select a subject, you can choose to create either a standard or a summary query:

• A standard query returns all rows of information
• A summary query does not return all rows. Instead, it summarizes/groups them by a designated field. For example, you could create a summary query for investments, grouping by investment state. Any investment fields returned would have to be configured to sum, count, average for each state., etc., or you could opt to receive the maximum or minimum for each state. This type of query should typically only be used for numeric fields

Click Next:

Adding Criteria to the Query

On this screen, you can add criteria to the query. To do so, click the Add button:

First, select the field that you wish to use for the criteria. Click the drop down arrow to see a list of fields available for the query subject you selected:

Next, select the function you wish to use. Click the drop down arrow to select from a list of functions available:

Finally, select the value or expression to use. Additional fields will appear, depending on the value/expression selection you make:

Once you have entered your criteria:

Click OK. You will be returned to the Query Wizard:

If desired, you can add additional criteria functions here. If you are done, click Next to proceed:

This screen allows you to select the fields that you wish to include in the query. To select a field, you can double-click it to move it from Available to Selected, or you can click it once and then click the >> button. Once you have selected your fields, you can then opt to move them up or down, to re-order how they appear:

Once you have selected your fields and positioned them in the order you wish to use, click Next:

This screen allows you to indicate how you wish to sort the data. To select a field or fields for the sort, you can either double-click them to move them to the Selected column, or you can click once and then click the >> button:

Below the Selected column, there is a checkbox called Ascending? If selected, the query results will sort in ascending order. If not selected, they will sort in descending order.

When you have finished selecting the field(s), click Next:

Adding Additional/Child Subjects

NOTE: Adding multiple query subjects is an advanced process, requiring familiarity on how to relate tables via fields and/or criteria. DDI strongly recommends that you do not attempt to build multiple subject queries without assistance from DDI Client Support.

This screen allows you to add additional subjects to the query, if desired. You can add primary subjects, or you can add a child subject to an existing subject.

At the bottom of the screen, there are two selection boxes: Inner Join and Split Query Results

• Split Query Results allows you to return each subject as a separate recordset. If it is not selected, Digital Dashboard will combine the query results into a single recordset
• Inner Join is used when linking queries. When selected, the system creates a new result table by combining column values of the two tables

Split Query Results example: If you are building a query that links gift headers and gift items. If you do not select Split Query Results, you will end up with one table of data containing a row for every gift line item, and the gift header data will be replicated for each line item. If you select Split Query Results, however, you will receive two tables of data - one for the headers and one for the line items. When you create your graph, you will have to select which table you wish to use for the data.

Inner Join example: You have configured Query A and Query B, and wish to link them based on Account PIN. The system will compare the two tables and return a results row for any row where the Account PIN in Query A matches the Account PIN in Query B

If you click Add here, the Query Wizard - Select Subject screen will appear:

Follow the steps to add an additional subject to the query, as noted above.

When you have finished adding your additional subject(s), click Next:

Click Finish to complete the Wizard. The Query will contact the database and refresh:

NOTE: Please note that this process could take a couple of minutes to complete, depending on your selections. Be patient while the query refreshes

Once the query completes the refresh, your screen will look like this:

Configuring the Appearance of Your Dashboard

At the bottom of the screen, click the Toolkit tab:

The Toolkit screen will appear:

This screen allows you to determine how your Dashboard screen will look. To select an option, click it in the left column and drag it over to the right side of the screen. The Dashboard Object Definition screen will appear:

There are multiple tabs on this screen.

Data Tab

The Data tab looks like this:

This screen allows you to define the way that the Dashboard filters and groups information. The available columns are displayed on the left. To begin, click the column in the left and then click the top > arrow. In this example screenshot, we are telling the system that we want it to take action when the interest rate changes:

The options here are:

Selection	Description
On Change of	Creates a record for each distinct combination of the group values selected
For Each Record	Creates a record for each record in the source data set, regardless of the group values selected. The For Each Record option will only be offered if you are defining data for a data grid. If you select this option, you will not be able to specify any summary columns as the records are not summarized
For All records	Creates a single record regardless of the group values selected

Grouping Query Results

Next, you'll tell the system how to group the Dashboard. Click the Group Options button:

This screen allows you to select the group, as well as determine the Group Sorting. There are two tabs on this screen.

Group Selection Tab

On the Group Selection tab, you will have options based on the column you chose to group by. The options will enable, depending on the data within the column.

Group Sorting Tab

When you click the Group Sorting tab, you will see this information:

This tab allows you to tell the system how to sort the information within the column you are grouping by. You can select the column and the sort order to use.

When you have finished making your selections, click OK to return to the Dashboard Object Definition screen.

In the lower portion of the Data tab, you can tell the system which summary column(s) to use. Certain object types will only allow you to add a single summary column. To add a summary column, select the column from the list on the left and then click the lower > button:

Appearance Tab

When you click the Appearance tab, you will see a screen similar to this one:

If you wish to change your chart type, you can do so here by clicking the drop down arrow to the right of the Type field. On this tab, you can also modify the title, add a second title, and opt to add point labels.

Numeric Format Tab

When you click the Numeric Format tab, you'll see this screen:

On this screen, you can format how you wish to display numeric values. You can designate the number of decimals, force trailing zeros, add a currency or number prefix, and add a number suffix, if desired.

X Axis Tab

When you click the X Axis tab, you will see this screen:

On this screen, you can change the title of the axis, select the label orientation, and limit the length of labels on the X axis, if desired.

Y Axis Tab

You will see this screen when you click the Y axis tab:

This tab allows you to modify the title of the Y axis, indicate the number of decimals (if applicable), fix the minimum value as 0, and enter fixed values for minimum and maximum.

Drill Down Tab

The Drill Down tab looks like this:

This tab allows you to configure the options for users to drill down into the chart data, if desired. You can enable the drill down functionality, as well as configure what level of information you wish to display when users drill down into the chart.

Color Tab

When you click the Color tab, you will see this screen:

NOTE: The Color tab will only allow you to configure custom colors if your graph has already been generated. If you try to configure colors prior to generating your graph, you will receive an error.

Advanced Tab

When you click the Advanced tab, you will see this screen:

This screen should only be modified with the assistance of a DDI Client Support representative.

Once you have finished configuring your query, your screen will refresh with your selections:

On the toolbar, you have additional options.

Tabs

The Tabs button allows you to add additional tabs to your query. When you click the Tabs button, you will see this menu:

New Tab

When you click New tab, the system adds a new dashboard tab to your screen:

This allows you to present the query results in a different manner. For example, you may wish to display a pie for this view. To add the display, click the option (such as Pie 3D Chart) and drag it to the right side of your screen. The Toolkit screen will appear, allowing you to select your grouping and appearance. Once you have made your selections, the screen will refresh with the information:

Rename Tab

If you wish to rename your tabs, you can do so using the Rename Tab function. To rename a tab:

• Click on the tab you wish to modify
• Click Tabs
• Click Rename Tab

The Rename Dashboard Tab screen will appear:

In the Tab title field, enter the name you wish to assign to the tab, then click OK. The tab name is now updated.

Delete Tab

To remove a tab, make sure you click on the tab you wish to remove. Then, click Tabs and select Delete Tab. You will see this warning:

If you are sure you wish to delete the tab, click Yes. Otherwise, click No to cancel.

You can now save your Dashboard and exit the Dashboard Designer.