Crosstab Column Formatting

(9 posts) (2 voices)
  1. shery, Member

    Dear concern,

    Can I know how to apply conditional formatting on the Crosstab Column? Say, for any specific value X, Column background/text should be Green?


  2. myDBR Team, Key Master

    You mean the crosstab data column?

    You can use dbr.cellstyle/dbr.cellclass for it. See the documentation.

    myDBR Team

  3. shery, Member

    It says:

    Command 'dbr.cellclass' cannot be assigned to the crosstable column!

    I am referring to the Crosstab Column like:

    SELECT 'dbr.crosstab', 'Week';

    I want to display the Week Column title that is produced dynamically in Green Background if it is for Current Week. I have handled the data part, but as 52 weeks are being displayed and I want the header/value coming in the Week to be in Green text/Background for the current week.

  4. myDBR Team, Key Master

    What is the part you wish to format? The crosstab column is the one that is repeated in the header.

    If you look at a sample in the demo, the Quarter column is the one that is the crosstab column. Columns after that are the crosstab data columns (Items & Weight in the sample).

    myDBR Team

  5. shery, Member

    Here is my Crosstab:

    SELECT 'dbr.crosstab', 'Week';

    Week is having dynamic values say, 52 weeks that are being produced. I want to display the current Week column header in Green text/Background.

  6. myDBR Team, Key Master

    If you want to color the header text in green, you can use dbr.html: for it:

    select 'dbr.crosstab', 'm';
    select 'A', '1' as 'm', 10 as ''
    select 'A', 'dbr.html:<span style="color:green">2</span>', 20
    select 'A', '3', 30;

    If you want to change the background color, you can use JavaScript for it:

    select 'dbr.crosstab', 'm';
    select 'A', '1' as 'm', 10 as ''
    select 'A', 'dbr.html:<span class="current-month">2</span>', 20
    select 'A', '3', 30; select 'dbr.javascript', '$(".current-month").parent().css({"background":"green","color":"white"})';

    myDBR Team

  7. shery, Member

    Week is dynamic, 3 Past weeks, Current Week, 52 Future Weeks. I don't want to use union and decline performance. The values in the column as Green for the Current Week(This is achieved).

    Here is the code:

    SELECT 'dbr.crosstab', 'Week';

    SELECT 'dbr.count','Sr. No.' ;
    SELECT 'dbr.count.prefix','Sr. No.', 'Total Records: ' ;

    SELECT 'dbr.css', '.greenclass {color:green;}';
    SELECT 'dbr.hidecolumn', 'class';
    SELECT 'dbr.cellclass', '[value]', 'class';

    SELECT aa.Property 'Property',
    Count(aa.Week) AS '[value]',
    IF(aa.Week= date_format(date_add(date(curdate()), interval -WEEKDAY(date(curdate())) day),
    '%d-%m-%Y') , 'greenclass', '')
    AS 'class'
    WHERE date(aa.Week) BETWEEN date_sub(date(CURDATE()), INTERVAL 3 Week)
    AND DATE_ADD(curdate(),
    Group by aa.Property, aa.Week;

    I want that if Week is Current Week than display as a Green Cell.

  8. myDBR Team, Key Master

    The union was just a sample code. You can do it with one query:

    SELECT 'dbr.crosstab', 'Week';
    'dbr.rownum' AS 'Sr. No.',
    aa.Property 'Property',
    if (aa.Week = week(now()), concat('dbr.html:<span style="color:green">',aa.Week,'</span>'), aa.Week) as 'Week',
    COUNT(aa.Week) AS '[value]'
    GROUP BY aa.Property, aa.Week;

    myDBR Team

  9. shery, Member

    Got it, Thank you very much!


You must log in to post.