Create a formatted table that can be filtered and sorted

Plugin Fancy Table

Use this wiki plugin to display data using the odd/even table style. Settings allow the table to be sortable by multiple rows, cause rows to span multiple columns and vice versa, set individual column widths and align text horizontally and vertically. Beginning with Tiki12 add sorting, filtering and pagination using the tablesorter parameters (separate tab below).

Parameters

Introduced in Tiki 1.
Preferences required: wikiplugin_fancytable

Parameters Accepted Values Description Default Since
(body of plugin) Rows separated by >> in the header; for the table body, one row per line. Cells separated by | (since Tiki4) or ~|~ in both cases.
tstotalformat Format for table totals (click here for patterns). Example: #,###.
colwidths Column widths followed by "px" for pixels or "%" for percentages. Each column separated by |. 4.1
headclass CSS class to apply to the heading row. 1
head Header rows of the table. Use >> to separate multiple rows. 1
colaligns text Table body column horizontal alignments, separated by |. Choices: left, right, center, justify 4.1
colvaligns text Table body column vertical alignments, separated by |. Choices: top, middle, bottom, baseline 4.1
headaligns text Horizontal alignments for header cells, separated by |. Choices: left, right, center, justify 4.1
headvaligns text Vertical alignments for header cells, separated by |". Choices: top, middle, bottom, baseline 4.1

Parameters Accepted Values Description Default Since
sortable any string except for HTML and PHP tags Serves as the overall switch for turning jQuery Tablesorter on (also for filtering) as well as overall sort settings. Enter y to allow sorting and n to disallow (n is the default). Enter type:save to allow sorts to be saved between page refreshes. Enter type:reset;text:***** to allow sorting and show an unsort button with custom text. Enter type:savereset;text:buttontext to allow the same for saved sorts. n 12.0
tsfilters any string except for HTML and PHP tags Enter y for a blank text filter on all columns, or n for no filters. Or set custom column filters separated by | for each column for the following filter choices and parameters:
Text - type:text;placeholder:xxxx
(For PluginTrackerlist this will be an exact search, for other plugins partial values will work.)
From Tiki 18, you can add initial:t option to allow prefix search for text filter. E.g. type:text;initial:t
Dropdown - type:dropdown;placeholder:****;empty:****;option:****;option:****;option:****
Options generated automatically if not set and the server parameter is not y.
Use value=Display label to have the option value be different than the displayed label in the dropdown.
Use empty:Display label to include an option with the specified label that will filter only empty rows. Only used if other options are not specified manually.
Date range - type:date;format:yy-mm-dd;from:2013-06-30;to:2020-12-31
(from and to values set defaults for these fields when user clicks on the input field)Beware that items with empty date values will not be shown when default date range filters are applied.
Numeric range - type:range;from:0;to:50
No filter - type:nofilter
For example: tsfilters="type:dropdown;placeholder:Type to filter..." would result in a dropdown filter on the first column with all unique values in that column in the dropdown list.
12.0
tscolselect any string except for HTML and PHP tags Add a button for hiding and re-showing columns. Also sets priority for dropping columns when browser is too narrow. Set each column to a number between 1 and 6 (1 is highest priority and last to be dropped) or to critical to never hide or drop. An example with 4 columns:tscolselect="critical|4|5|6" 14.0
tsfilteroptions any string except for HTML and PHP tags The following options are available: reset (adds button to take off filters), and hide (Filters are revealed upon mouseover. Hide doesn't work when date and range filters are used.). To use both, set tsfilteroptions="type:reset;text:button text;style:hide" 12.0
tspaginate any string except for HTML and PHP tags Enter y to set default values based on the site setting for maximum records in listings (on the pagination table of the Look & Feel admin panel). Set to n (and server cannot be set to y) for no pagination. Set custom values as in the following example: max:40;expand:60;expand:100;expand:140 12.0
sortList any string except for HTML and PHP tags Bracketed numbers for column number (first column = 0) and sort direction (0 = ascending, 1 = descending, n = no sort, y = allow sorting but no pre-sort), for example: [0,y],[1,0],[2,n]. If the first pre-sorted or no filter column is not the first column, then you should use the y parameter (as in [0,y]) to assign all previous columns. 12.0
tstotaloptions any string except for HTML and PHP tags Pipe-separated options for totals for each column which are set in the tstotals parameter:
format - overrides the default number format set in tstotalformat
ignore - column will be excluded from total calculations set in the tstotals parameter. Remember to include any columns that will be added for row totals set in the tstotals parameter.
Example:ignore|ignore|#,###.
15.0
tstotals any string except for HTML and PHP tags Generate table, column or row totals and set labels, using either y or the following syntax for each total: type:value;formula:value;filter:value;label:value.
Setting to (y) will add one column total row set as follows: type:col;formula:sum;filter:visible;label:Totals.
Separate multiple total row or column settings with a pipe (|). Set type only to generate sums of visible values. In all cases, cells in columns set to be ignored in the tstotaloptions parameter will not be included in calculations.
Instructions for each total option follows:
type - Choices are col, for a row of columns totals, row, for a column of row totals, and all to include amounts from all cells in the table body in a row total.
formula - set what the calculation is. Choices are: sum, count, max, min, mean, median, mode, range, varp, vars, stdevp, stdevs. Click here for a description of these options.
filter - Determines the rows that will be included in the calculations (so no impact if type:row). Also, when server="y", only visible cells are included regardless of this setting. Choices are visible (rows visible on the page), unfiltered (all rows not filtered out, even if not visible because of pagination), all (all rows, even if filtered or hidden), and hidden (rows filtered out and rows hidden due to pagination).
label - set the label for the total, which will appear in the header for row totals and in the first column for column totals.
15.0
tsortcolumns any string except for HTML and PHP tags Set type and group settings for each column, using | to separate columns. To show group headings upon page load, the Pre-sorted Columns parameter (0sortList) will need to be set for a column with a group setting. Group will not work in plugins where the Server Side Processing parameter (server) is set to y.
Set type to one of the following: text, digit, currency, percent, usLongDate, shortDate, isoDate, dateFormat-ddmmyyyy, ipAddress, url, time
12.0

Prerequisites and Tips

• In order to use tablesorter,
• Javascript must be enabled (enabled by default at Control Panels > Features > Programmer (tab)
• jQuery Sortable Tables must be activated (at Control Panels > Features > Interface (tab)
• Sorting a table with cells that span multiple columns or rows can give unexpected results
• Any | that you don't want interpreted as cell dividers may need to be placed inside of the ~np~ and ~/np~ tags.

Examples

Sorting, Filtering and Paginating

There are a number of options that allow the user to filter, sort and paginate a table. Below is an example that demonstrates many of these options.

This code:

{FANCYTABLE(head=" Fruit | Number | Vegetables | Date | Amount" sortable="type:reset" sortList="[0,0],[1,0]" tsortcolumns="type:text;group:letter|type:digit;group:number|type:word;group:word|type:shortDate;group:date-year|type:digit;group:number-10" tsfilters="type:text;placeholder:Type to filter...|type:range;from:0;to:200|type:dropdown|type:date|type:range;from:5;to:60;style:popup" tsfilteroptions="type:reset" tspaginate="max:5")}
apples|10 | onions | 2/1/2010|40
lemons|200 | cucumbers | 3/3/2011|50
oranges|100 | carrots | 4/3/2012|60
lemons |10| tomatoes|1/2/2011|30
berries |50 | peas|6/6/2011|55
apples|10 | onions | 2/1/2010|66
lemons|200 | cucumbers | 3/3/2011|66
oranges|100 | carrots | 4/3/2012|30
lemons |10| tomatoes|1/2/2011|22
berries |50 | peas|6/6/2011|77
apples|10 | onions | 2/1/2010|77
lemons|200 | cucumbers | 3/3/2011|34
oranges|100 | carrots | 4/3/2012|56
lemons |10| tomatoes|1/2/2011|67
berries |50 | peas|6/6/2011|78
apples|10 | onions | 2/1/2010|89
lemons|200 | cucumbers | 3/3/2011|12
oranges|100 | carrots | 4/3/2012|43
lemons |10| tomatoes|1/2/2011|32
berries |50 | peas|6/6/2011|76
apples|10 | onions | 2/1/2010|76
lemons|200 | cucumbers | 3/3/2011|87
oranges|100 | carrots | 4/3/2012|11
lemons |10| tomatoes|1/2/2011|22
berries |50 | peas|6/6/2011|6
{FANCYTABLE}

Would produce:

Tip

Shift-click on column headings to sort by multiple columns.

Automatic Totals

Automatic columns, row and table totals can be added. In addition to sums, the totals can be the count, max, min, mean, median, mode, range, varp, vars, stdevp, and stdevs (click here for a description of these options). The number format can also be set to apply to all totals or specific formats for each. The example below has all three types of totals (column, row and table) as well as variations showing totals based on whether values are hidden or not, including due to filtering.

This code:

{FANCYTABLE(head=" Fruit | Number | Vegetables |  Amount" sortable="type:reset" sortList="[0,0],[1,0],[2,y],[3,y],[4,n]" colaligns="left|right|left|right" tsortcolumns="type:text|type:digit|type:word|type:digit" tsfilters="type:text;placeholder:Type to filter...|type:range;from:0;to:200|type:dropdown|type:range;from:5;to:60;style:popup" tsfilteroptions="type:reset" tspaginate="max:5" tstotals="type:col|type:col;filter:hidden|type:col;filter:all|type:all|type:all;filter:hidden|type:all;filter:all|type:row" tstotaloptions="ignore||ignore|" tstotalformat="#,###.")}
apples|10 | onions | 40
lemons|200 | cucumbers | 50
... (leaving out some rows for illustration)
lemons |10| tomatoes|22
berries |50 | peas|6
{FANCYTABLE}

Would produce:

Multiple live filtering

In a simpler example, all you need to do is set sortable to y, in addition to being able to sort each column, you also get a line with fields to filter your data by matching a search string in one or more columns.

Example from http://i18n.tiki.org/Status :
This code:

{FANCYTABLE(head="Language code (ISO)|English name|Native Name|Completion|Percentage|Number of strings", sortable="y")}
ar | Arabic | العربية | {gauge value="2,29" size="100" showvalue="false"} | 2,29% | Total: 14923 %%% Translated: 341 %%% Untranslated: 14582
bg | Bulgarian | български език | {gauge value="0,01" size="100" showvalue="false"} | 0,01% | Total: 14922 %%% Translated: 2 %%% Untranslated: 14920
ca | Catalan | Català | {gauge value="39,22" size="100" showvalue="false"} | 39,22% | Total: 14996 %%% Translated: 5882 %%% Untranslated: 9114
...
{FANCYTABLE}

Would produce (upper part):

Click to expand

Then you can sort by one or more columns, and filter you results by searching for some string in one or more columns. In the example below, sorted by one column ("Percentage"), and filtered by content in another column ("Native name" containing "de"):

Click to expand

Cells Spanning More Than One Row

This code:

{FANCYTABLE( head=" Type | Value ")}
\\Vegetable | onions
carrots
\\\Fruits | lemons
tomatoes
strawberries
{FANCYTABLE}

Would produce:

Type Value
Vegetable onions
carrots
Fruits lemons
tomatoes
strawberries

Keywords

Keywords serve as "hubs" for navigation within the Tiki documentation. They correspond to development keywords (bug reports and feature requests):

Accessibility (WAI and 508)
Accounting
Articles and Submissions
Banners
Batch
BigBlueButton audio/video/chat/screensharing
Blog
Bookmark
Browser Compatibility
Calendar
Category
Chat
Clean URLs
Communication Center
Compression (gzip)
Content Templates
Contribution
Credit
Date and Time
Debugger Console
Documentation link from Tiki to doc.tiki.org (Help System)
Docs
Draw
Dynamic Content
Dynamic Variable
External Authentication
FAQ
File Gallery
Forum
Friendship Network (Community)
Groups
Hotword
HTML Page
i18n (Multilingual, l10n, Babelfish)
Image Gallery
Import-Export
Install
Integrator
Interoperability
Inter-User Messages
InterTiki
Kaltura video management
Karma
Live Support
Logs (system & action)
Look and Feel
Lost edit protection
Mail-in
Map with Mapserver
Meta Tags
Mobile Tiki and Voice Tiki
Mods
Module
MultiTiki
MyTiki
Payment
Permissions
Platform independence (Linux-Apache, Windows/IIS, Mac, BSD)
Polls
Profiles
Profile Manager
Report
Toolbar
Quiz
Rating
Feeds
Score
Search engine optimization
Search
Search and Replace
Security
Share
Shopping cart
Shoutbox
Slideshow
Smiley
Social Networks
Spam protection (Anti-bot CATPCHA)
Spellcheck
Stats
Surveys
Tags
Tell a Friend, alert + Social Bookmarking
TikiTests
Theme CSS & Smarty
Trackers
Transitions
TRIM
User Administration including registration and banning
User Files
Watch
WebDAV
Webmail
Web Services
Wiki History, page rename, etc
Wiki Plugin extends basic syntax
Wiki Syntax
Wiki structure (book and table of content)
Workspace
WSOD
WYSIWYCA
WYSIWYG
XMLRPC