Result Extensions

Demo Query

Switch to design mode the Result Extensions demo query.

The query has the sole purpose to demonstrate three of the extensions presented here below: Format, Summary and Highlight. These are non-SQL features, usually found in advanced spreadsheet applications, applied on current results AFTER the query has been executed. While they must parse every single cell of the result page, they may also slow down the overall query processing.

Format

In the Format builder, you may select a specific display format for numeric, string or datetime contextual expressions, from a combo box. There are static style formats and calculated formats (with * prefix name). Format cannot be applied today to the exported result. It applies however on selected rows you Copy to clipboard.

If your cell data cannot be properly represented for the chosen format, a red icon with a tooltip will tell you why. You may have a format for integer or positive numbers, and (some of) your values are float or negative.

A field “remembers” the format you applied to it last time, in another query, and tries to automatically set the same format in any new query. Saving the format occurs only when the field was directly selected, i.e. there are no intermediate lookup, formulas or grouping.

Here is the list of formats today (it may change in time):

• numeric
  • currency (locale $) – ex $123.46
  • decimal (+/-int) – ex 1234
  • exponential – ex 1.052033E+003
  • fixed-point (float) – ex 1234.57
  • general (float) – ex 123.456
  • number (group separators) – ex 1,234.57
  • percent (* 100) – ex 100.00 %
  • hexa capitals (integers only) – ex FF
  • hexa (integers only) – ex ff
  • always positive (calculated, integers only) – ex +123.56
  • always negative (calculated, integers only) – ex -123.56
  • opposite (calculated, integers only) – ex +/-123.56
• datetime
  • short date – ex 6/15/2009
  • long date – ex Monday, June 15, 2009
  • full, short time – ex Monday, June 15, 2009 1:45 PM
  • full, long time – ex Monday, June 15, 2009 1:45:30 PM
  • general, short time – ex 6/15/2009 1:45 PM
  • general, long time – ex 6/15/2009 1:45:30 PM
  • month/day – ex June 15
  • month/day capitalized – ex JUNE 15
  • RFC 1123 – ex Mon, 15 Jun 2009 20:45:30 GMT
  • sortable – ex 2009-06-15T13:45:30
  • short time – ex 1:45 PM
  • long time – ex 1:45:30 PM
  • universal sortable – ex 2009-06-15 20:45:30Z
  • universal full – ex Monday, June 15, 2009 8:45:30 PM
  • year month – ex June, 2009
  • year month capitalized – ex JUNE, 2009
• string
  • all upper cases (calculated) – ex ABC
  • all lower cases (calculated) – ex abc
  • reverse text (calculated) – ex cba
  • to title case (calculated) – ex Me Too
  • trim (calculated) – ex abc
  • left/start trim (calculated) – ex abc
  • right/end trim (calculated) – ex abc

For each column, the demo query has the following format:

• id as percent – 1 becomes 100.00% etc
• name as all upper cases – as a calculated format, this means we implemented a function to traverse each cell and individually convert all characters to upper cases. This could get slow for large data sets!
• unit_price as currency
• quantity as exponential
• depends_on as hexa – characters 0-9, A-F

Summary

Bottom design row(s) with aggregate sums, averages, and counts on a column result. This applies only to the single page returned. The aggregate functions are similar and try to mimic those exposed elsewhere. However, they are calculated differently, on the client side. While most other aggregates are calculated specifically by your database server. Unlike all other builders, both Summary and Highlight rows remain also visible (if populated) in Results mode, but they will be read-only. When results are present, the cells show calculated values. Otherwise they show aggregate’s name. You can fill more than one row with different aggregates.

For each column, the demo query calculates:

• count of id fields
• maximum alphabetic value of name
• sum of unit_price values
• maximum value of quantity values
• average of depends_on values

Highlight

Bottom design row with simple search conditions to find and highlight in yellow background all result cells with matching values. You can also refer to another column (as a query expression) in some conditions.

Available operations are:

• numeric, datetime or string comparisons, including Is [Not] Null.
• Is [Not] In List Values.
• strings that [not] contains, starts or ends with.

For each column, the demo query highlights the cells with:

• id numbers within (5, 1, 3, 200) – remark a Format was also applied on the result, so 300.00% is actually 3!
• names containing ‘es’ – case insensitive search in most cases (not always though!)
• unit_price greater than 2.9
• quantity values <= depends_on values – remark the use of a query expression!
• Null depends_on cells – remark how = Null is transparently handled inside properly.

Hide Nulls

Switch toolbar button to show cells with no data as either “(Null)” or empty. This is useful when you don’t care if a cell is Null or just an empty string, and you want a cleaner view:

Hide Duplicates

Switch toolbar button to color in light gray duplicate column values on consecutive rows. This has a similar effect with the groups used in reports. To get a meaningful effect, data should be usually properly sorted, left to right.

When you show let’s say all Tracks of all Albums of all Artists, it make sense to highlight only the changes in data, as below:

Merge Duplicates

Same Hide consecutive duplicate values button as for hiding duplicates. But turn Merge on Hide Duplicates option on. You get a different effect now: no alternate grid row color, merged cells and text deleted. If you export the values, duplicates are indeed removed. Here is a crosstab/pivot query result using such effect:

Export

Export toolbar button and menu commands are available for queries and work only when you have a queried result on screen. Export will save formatted data from your results grid into a custom .CSV or .TXT file, in (Excel-compatible) Comma-Separated-Values or TAB-Text-Delimited format, with a UTF8 encoding. Export will not issue another query execution, it will just save what you queried last time and still have on screen.

NULL values are saved as either (Null) – or what other Null Display option you set – or empty string on Hide Nulls.  On Hide Duplicates, grayed out text is exported as well. However, duplicates are removed on Merge Duplicates, reflecting what you see on screen. Binary data and images (even rendered on-screen imaged) appear as (Binary) – or what other Binary Display option you set. You can still copy cell images in clipboard and later do what you want with them, but there is no other export option for binary data at this time.

Data is exported with your styled or calculated Format applied. Format errors are reflected by a (Bad format) text. Unsupported image format usually appears as (Bad image format). Other format errors will appear and will be exported as (error text here).