Design Studio Extension - Advanced Data Tables - Phase 1

[mike_howles4]

Background

The out-of-box Design Studio Crosstab is great for pivot table use cases.  Beyond that, there's plenty of room for other tabular forms of data.  This is an introduction to the first of a multi-phase effort to create a flexible table that is called 'Advanced Data Table'.  This initial Phase 1 work is available to try now and is downloadable using the links in this page: SCN Design Studio SDK Development Community

First off, I'll be taking a 2-D approach to flatten data always into the concept of rows and columns.  No tuples or dimension concepts.  I find it easier to think in 2 dimensional terms when thinking in tabular terms.

I did not want to reinvent the wheel, so I looked at a few Open Source options and settled on the Data Tables plug-in for jQuery.  (Open Source MIT Licensed)

Goals

I've seen a number of threads where designers are wrestling with CSS, Column Widths, Visibility, Cell Formatting, etc and while I'm not here to solve all problems in this first phase, I've broken them into the following areas to address (and solicit friends in the SCN SDK Team to help if they'd like!)  Below are the items I plan on addressing.

1. Phase 1 - Settling on a table library, offering basic properties control, and getting feedback from community on this release.
2. Phase 2 - Introduce BI Actional Language support, and introduce Numeric Formatting, Conditional (numeric and string) Formatting based on either static targets, of targets defined in a column.  Condition Formatting could include scorecard-type icons or simple text/background formatting.
3. Phase 3 - Control over table appearance from the property sheet wherever possible without becoming a CSS guru.
4. Phase 4 - Local column calculations - Ability to perform common mathematical functions on columns.

Like any good scope, it's prone to change based on feedback and ideas :smile:

Phase 1 Review

What is available now in Phase 1?

Let's look at some of the properties...

A quick note: Since I am using my new Advanced Properties Sheet (APS) generation technique, and component inheritance, we can benefit from a consistent look and feel in our new community components going forward.  I can also cut down on the time otherwise spent with common properties and data flattening, etc.  You've seen these in my maps components such as SAP Design Studio SDK - Choropleth Maps Refined, Part 3 and now karol.kalisz has adopted this technique going forward in some of his more recent blogs as well such as

Data

These properties are seen in any component using my inherited data class and are rather straightforward.  These are just common datasource-specific settings.

Columns

This is just the beginning of what will become more robust in next phases, but for starters you can see that per-column, you can change the following properties.  This supports as many Columns as you have in the Initial View.  There is no limit.

• Column (Read Only) - Shows the 'Key' of the column.
  
  
  
• Title - If you do not like the Text coming from your Data Source, you may change it here.
  
  
• Sortable - Control whether a user may sort a column.  Users CAN sort multiple columns by holding Shift at runtime.
  
  
• Searchable - Whether the text search functionality applies to this column.

Note: In further phases I will add many more properties (such as whether columns are visible by default, etc) where this property table would become too cramped to show many more, so this is why you see a table selection button to the left of the 'Column' column which will bring up a detail list of even more properties to come.  I am just getting started!

Columns - Visibility

Even though your initial view may have 10 columns, the user may want to quickly toggle between seeing everything at once.  Sure, we can do this at the data source level, but it's a server round trip for something that could be instant.  I am using the data tables ColVis plugin in this case to offer the menu and ability for the user to at runtime decide what they want to see, if the developer decides to enable it.  To the left are some options available.  Below is what the menu would look like at runtime:

Behavior

Here we can see many behavior-type options.  Many are related to pagination, so they may end up showing up under their own Behavior->Pagination menu path but for now, they are here.

• Enable Sorting - Whether user may sort any column.  You may further refine individual columns as seen in the Columns section.
  
  
• Responsive - A really awesome responsive layout component that you can choose to enable.  This lets narrow tables change the approach for showing too many columns.  A screenshot on this comes later in this post.

Pagination options include:

• Rows per Page - If enabled, the number of rows per page are used.
• Starting Row - If for some reason you do not want to start at row 0, you can adjust.
• Enable Pagination - Whether to use pagination or not.  if not used, all other pagination options have no effect.
• Enable Pagination Resize - This is a cool pageResize plugin for Data Tables.  As the table size itself changes (such is the case where you have static margins but auto width/height), this will adjust the best per-page rows-per-page.  This is a really cool one, and will override whatever you would have for Rows per Page as a result.
• Pagination Type - This just controls whether you see 'Next', 'Prev' pagination options or also the numbers, etc.

Behavior - Search

This is a really cool part of Data Tables where you can filter down to only certain rows based off of search criteria.

• Enable Searching - Whether the user even gets the searching capability
  
  
• Initial Search Term - As a designer you could pre-filter by a certain criteria, and by using BIAL, you could set this property to make it dynamic.
  
  
• Case Insensitive - For your thick-skinned searches :smile:
  
  
• Smart Filtering - This one is cool, for each term separated by a space, the smart filtering will interpret that as an AND clause.  So for example if you wanted to search for Material 'M01' for year '2004', you could simply type 'm01 2004'.  Likewise, if you want a space to be a space, you'd disable this option so you could search for customer 'John Doe'.
  
  
• Use Regular Expressions - If Smart Filtering and Case Insensitive are not enough, with this enabled, you can specify a regular expression to get very specific with pattern matching.  While your users may not be Regex gurus, using this in combination with initial search term and BIAL may prove powerful.

A note on searching, you can control which columns are searchable in the Column config.

About

And finally, thanks to our inheritance pattern and APS generation, we have a place to explain a simple overview of the component at design time.

What Does it Look Like?

Below are some screenshots of the Advanced Data Table and descriptions of what's going on.

Show above is a data table with Material and Cost Center sorting disabled, and a default smart search term filtering down to only data with m01 and 2002.  The info bar on the bottom shows the user that it is showing 11 filtered results of 189 rows.

Let's check out the Show/Hide Options at runtime:

I've unchecked a few columns and also you can see the pagination adjusted due to my margins making the table shorter.

And finally, what if we have a very tight space to work in?  Let's try responsive out.

With Responsive enabled, if the columns would not have enough room, a green button appears, which is a cue for the user to click for more detail.  The responsive logic does its best guess at this and while not perfect, is pretty solid.  Let's click on of these green buttons:

This Phase 1 work is now open to play with and feedback, critique, questions, etc are welcomed!