Design Studio Extension – Advanced Data Tables – Phase 1
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)
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.
- Phase 1 – Settling on a table library, offering basic properties control, and getting feedback from community on this release.
- 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.
- Phase 3 – Control over table appearance from the property sheet wherever possible without becoming a CSS guru.
- 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 🙂
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
These properties are seen in any component using my inherited data class and are rather straightforward. These are just common datasource-specific settings.
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:
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 🙂
- 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.
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!
I have been reviewing the Advanced Data Tables. I have used the data tables jquery plugin in the past on other projects. I am having an issue with running a databound query. It returns headers but no data. It is a Bex query which uses a hierarchy variable for selection. I have tried other table examples in the SDK and they are also not returning data. The UI5 table returns data without issue. It appears it is using a different approach to fetching flat data. The UI5 table uses "Central Provisioning" as discussed below.
Design Studio SDK: Result Set Information Component
Any suggestions on why the results are not returned to the table?
That's odd, however we may need Karol Kalisz's insight for a quicker reply regarding the 'Central Provisioning' on this one as he would know much more about what makes his approach different than mine. I'm sure we could introduce some easy tweak to my flatten data function to make it work. I've tested it with BEx data sources and know that this should work fine, so I'm curious as to why it might be specific to usage of a Hierarchy Variable?
the way how the data is loaded from the data source is same in both cases. There is indeed a slight different provisioning in the client which I have used to manipulate the resultsets before those gets display in the UI5 table.
Can you explain more about the data source you use? Is it also some SDK datasource which is fetching the data in some ways? Perhaps there is some timing issue which make the one way working and the other not.
thank you very mucho for this awsome component.
While phase 2 is not released, I would like to format the table using the current formatting option, but I don't know how, could you, please give me a basic example or head me to another page with more information?
Thank you Mike for this document,
it would be very good,if you could help me in the below issues.
we are in latest version of Design Studio 1.6 SP3 and BIP 4.2
I have used SDK component "Advanced Data Table 2.0" in my application.But i cannot format the numbers even if i selected TYPE as "Numeric" from "Additional Properties". and also right alignment and "comma separation" like (eg:00,000,000.00)
Another thing is i cannot display numbers as in the data source, where i have applied "Scaling" for millions. And it is showing full numbers instead.
Excellent component, which I've found very useful.
Trying to sort on a column which holds dates, but the sort seems to ignore year and month. Is there anyway around this?
Very useful component to display the tabular data in design studio with smart search.
Please suggest on how to export all the data(till last page) into pdf ?
am trying to export but i could only export the data on the first page.
PDF export support I've not considered, and it would probably have to be supported by the jQuery Data Tables API which I doubt is a feature. Sorry!
Thanks Mike, hoping the export feature is supported by the component in future similar to crosstab.
Much awaiting for the formatting to be available!