March 2011

Tech Info

Powerful Client Table Features for Data Analysis

Client tables are easy to create. You can use them to pivot data from column to row, and to sort and filter the data set. With a little extra effort, you can perform in-depth data analysis and unleash the true power of client tables. In this issue, we’ll show you how to create new calculations; add ranking to rows and columns; and apply data markers to highlight and add focus to the result set.

Note: You can re-create the tables in this article using the view, CTVIEW, in the SEQUELEX library.

Create New Calculations

Creating new calculations for your client table is very easy. Once you’ve been through the Client Table Wizard and laid out the basic elements of the table, select Format\Calculated Fields from the Design menu.

In the Calculated Fields Editor, complete the following four steps:

1. Click Add to create the new calculated field.
2. In the Field Settings section, enter a caption (this will become the column heading in the table) and a VarName, to be used internally.
3. Enter the Formula for the new calculation. Here, we double-clicked on the field, sumAMOUNTSLS, added the multiply sign (*), and entered a value of 1.2 to calculate projected sales.
4. Click Close to see the new column in the table. The new calculation is added instantly to the results.



Result with new calculation

Add Row and Column Ranking

Ranking is applied to the fields currently defined in the column or row section of the table, and is used to sort the data based on the row or column total values. In this table,we see totals by product and country—notice that the totals are in no particular order.

To set or change the ranking by row, select Format\Layout on the Design menu and complete the following five steps:

1. On the Fields tab, select the row field (Product) to display its properties in the right panel.
2. Set the Orientation (Row).
3. Set the Rank Style (Ascending).
4. Choose a field to Rank On (Amount Sales).

 

5. Click Apply and OK to see the changes to the results.

You can use the same process with a column field to create ranking on the column totals.

Ranking by row

Ranking by column

Use Data Markers to Highlight Important Data

Data markers are a powerful way to highlight data, and add both informational and visual impact to your table results. Select Format\Data Markers from the Design menu.

In the Data Markers Editor complete these five steps:

1. Highlight the field you want to base the marker on (Amount Sales).
2. Enter a low and high value for the Range.
3. Click the Foreground or Background buttons to open a color palette and select a color.
4. Click Save to add the defined range to the list.
5. Click Close to save your changes and return to the table.

The markers you define are saved with the table and update automatically every time you run it.

These techniques are easy to add to your own client tables. Give them a try and see how your data comes alive.

By Steven Smith, Technical Consultant