Skip to Content
Author's profile photo Karol Kalisz

Design Studio SDK: UI5 Table with Conditional Formats

Following the blog Design Studio SDK: New Features in UI5 Table, today the Conditional Format function.

if you want to:

* display the data from any data source in a table

* highlight some cell values based on conditions, like this below:

cond-form.PNG

then this blog is for you.

The baseline.

This is an extension of the UI5 Table component, as here –Design Studio SDK: UI5 Table Component.

The idea.

The community flatten functions provided by Mike (and a bit extended by me) are capable to flatten the result set into the table row/cols specification. This allows easy access on cell level to the data – and to the members. It means, the evaluation is made on CONTENT basis, not on logical dimension / member / measure basis. But this is enough for some start.

The rules need to be given as an JSON Array:

[

    {“condition”: “value”, “sign”: “>”, “value”:”4″, “simpleFormat”: “green2”},

    {“condition”: “value”, “sign”: “<“, “value”:”3″, “simpleFormat”: “red2”},

    {“condition”: “contains”, “members”: [“Brewer”, “Zavala”], “exclude”: false, “simpleFormat”: “yellow1”}

]

the rules can be checked in 3 ways:

* Match First (then ignore others)

* Match Last (last always win)

* Collect (check all and place the assigned “simpleFormats” into an array

The “simpleFormat” is a string which is passed into special attribute on the table

data-condformat=”<the dimple format string>

and it allows you to select this content via “normal” css selector. Some selectors are already build in, but you can freely add your own.

Example for CSS class

div[class~=scn-pack-ActivateSimpleConditions] td>div>label[data-condformat=”green1“] {

    background-color: #9fff9f!important;

}

you just need to replace “green1” with your own string passed as simpleFormat

Conditional Options

Then, we have following evaluation options:

contains

checks if the content contains given strings.

positive check

{“condition”: “contains”, “members”: [“Berlin”, “Munich”], “simpleFormat”: “red1”}

negative check (exclude)

{“condition”: “contains”, “members”: [“Berlin”, “Munich”], “exclude”: true, “simpleFormat”: “red1”}

pattern

check the content for existence of some characters. you can use ‘?’ to accept any character. Character ‘*’ stops the check and returns the current state.

positive check

{“condition”: “pattern”, “members”: [“Be??in*”, “Mun*”], “simpleFormat”: “red1”}

this should match all Be<here 2 characters which does not matter>in<and here any characters>

OR

Mun<and any character

equals

Like contains, but checks on exact content. also possible with exclude flag.

positive check

{“condition”: “equals”, “members”: [“Berlin”, “Munich”], “simpleFormat”: “red1”}

empty

Checks for empty cells, can also be used with exclude flag, then non empty cells will be marked.

positive check

{“condition”: “empty”, “simpleFormat”: “red1”}

value

works only for numbers, compares the content with given number.

positive check

{“condition”: “value”, “sign”: “>”, “value”:”4″, “simpleFormat”: “green2”},

APS Update

The rules can be maintained in additional proeprties, so now no JSON is requried.

The maintenance does not need to be made by JSON any more, an UI is available for it:

cond-form1.PNG

cond-form2.PNG

All is available in the preview / stable as of now. If someone is willing to test / double check – you are welcome.

Restricting the Conditions to Columns

With this property you can additionally restrict applying of the conditions to some columns only. you need to place the key of the dimension/measure which is in this column.

cond-form3.PNG

In this case, the key of “Unit Sales” is “unit_sales” and then this condition will be applied only to “unit sales” column.

I hope this helps.

Btw, in case you have 2 dimensions in columns, this works as well – you need to make the “Column Header Id” as combination of those:

my drill down here:

cond-form4.PNG

The required ID: “<gender id> | <measure id>”

cond-form5.PNG

the release package is updated with this function now.

Help / API Specification

Go directly to the help site and find the component: Component List – SCN Design Studio Community

Download & Use

This component is available on the community package

More information on the SCN community package in blog  SCN Design Studio SDK Development Community

Example as BIAPP:

org-scn-design-studio-community/applications · GitHub

Any thoughts?

feel free to add as usual…

Enhancements Ideas?

if you have good ideas (to those who would like to contribute but cannot code…) – place an “issue” with tag “enhancement” under Issues · org-scn-design-studio-community/sdkpackage · GitHub

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Harish Nayak
      Harish Nayak

      Hi Karol,

      Thanks for sharing the information. This is very helpful.

      Is it possible to highlight conditional format for specific columns?

      Example:

      1) Red and Green for "Store Sales" Column - If value< 2 the Red else Green

      2) Yellow and Blue for "Unit Sales" Column - If Value<5 then Yellow else Blue.

      Please let me know.

      Thanks,

      Harish Nayak

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      not yet, I need to think about.

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      Hi Harish,

      ok, I have found a simple way - based on the column id, either the dimension ID or measure ID. please check it up. the blog above is updated. package is rebuild.

      Karol

      Author's profile photo Harish Nayak
      Harish Nayak

      Thanks Karol. This works fine!! 🙂

      Regards,

      Harish Nayak

      Author's profile photo Mike Howles
      Mike Howles

      Karol,

      Very nice - I may take this and incorporate into the 'Advanced Data Tables' somehow as Conditional Formatting was something I needed to get back around to.  I like your approach!

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      Hi Mike,

      thanks, I think it is simple and flexible at the same time. Perhaps later on we can improve the UI for the maintenance and make some function for moving it around components (export / import). I will try to make the same for the "Nice Chart" component then we can validate how inter-component it is.

      Karol

      Author's profile photo Jacques-Antoine Ollier
      Jacques-Antoine Ollier

      Hello Karol, I am facing some troubles with the Additionl Properties -> COnditional formating. I wanted to delete them and start from scratch but it seems I am not able to do so. When I click on delete, nothing is happening. Did you already see something like that? Thank you very much for your attention. Best regards. Jacques-Antoine Ollier

      Author's profile photo Former Member
      Former Member

      Hi Karol,

      This component is very useful in our case to meet conditional formatting requirement. we are using UNX universe as a datasource. Since regular crosstab has limitation on conditional formatting we trying to evaluate this component as an alternative. I was able to achieve conditional formatting, however when I tried to apply to only one column by giving "column header id" it is not being applied. Is it a limitation on universe datasource ? or am i doing something wrong ?

      The example mentioned above ("Restricting the Conditions to Columns") was a bit unclear to me as the table header named as "Unit Sales" (init caps and no underscores, while other column store_cost is lowercase and has underscore) and column header id was given as "unit_sales" (lowercase and underscore). I tried the same but was not successful. My column name coming from universe is 'Customer Number' and gave column header id as "customer_number" which is not working. also tired '4' it is column no.5 in the sequence.

      Best regards

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      Hi, please check if the new setting "Show Keys instead of Texts" will help you to find the correct key. It is possible that the key is a guid which is not visible on first view.

      ui44.PNG

      in case this will not help you to find the correct key, please make an issue on github.

      https://github.com/org-scn-design-studio-community/sdkpackage/issues

      Karol

      Author's profile photo Jacques-Antoine Ollier
      Jacques-Antoine Ollier

      Thank you Karol!

      Author's profile photo Fabien AUBERT
      Fabien AUBERT

      Hi Karol,

      A customer is interested to highlight value whether they are positive or negative. Basically they want to set different font color to differentiate them. Data are coming from Hana.

      I saw your UI5Table component that's seem to match their requirement.

      I downloaded your test application SCN_UI5TABLE and I imported it in DS1.6 SP3. I have a couple of questions

      • It seems that the css works only for background and not for the font. For instance if I had the line "font-style: italic!important;" or "color: #00ffff!important;" in a css it's not applied in the UI5table ? Am I correct here ?
      • If I suppress some lines in a css (for instance I remove "background-color: #ff0000!important;") I stil have the backgournd color apply ? Even if I reload the application ?

      Thanks

      Fabien

      Author's profile photo Former Member
      Former Member

      Hi Karol,

      I'm working on design studio 1.6 on unx universe. Is there any way where we can highlight the complete row if the condition is met.

      Thanks

       

      Author's profile photo Knoa Support
      Knoa Support

      FYI, I was able to modify the text color of a cell using the following:

      label[data-condformat="green"] {
          color: green!important;
      }
      Author's profile photo Sridhar Chidurala
      Sridhar Chidurala

      Hi Karol,

      I have used UI5 table in one of the reports however, I have a few points which I need clarity. Below are the same – –

       

      • I am using 3 dimensions one in column wise and other 2 are row-wise. So, I am not getting the merging options of 2 dimensions.
      • When there is huge data, the columns are collapsing so is there any way we can expand the columns automatically?

      Can you please let me know if i am missing something in UI5 table?

      A quick response would be highly appreciated.