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?

   Regards.
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 ''
   
   union
   
   select 'A', 'dbr.html:<span style="color:green">2</span>', 20
   
   union
   
   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 ''
   
   union
   
   select 'A', 'dbr.html:<span class="current-month">2</span>', 20
   
   union
   
   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',
   aa.Week,
   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'
   FROM myTABLE
   WHERE date(aa.Week) BETWEEN date_sub(date(CURDATE()), INTERVAL 3 Week)
   AND DATE_ADD(curdate(),
   INTERVAL 1 YEAR)
   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';
   
   SELECT
   
     '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]'
   
   FROM myTABLE
   
   GROUP BY aa.Property, aa.Week;
   
   

   
   --
   myDBR Team
9. 

   shery, Member

   Got it, Thank you very much!

Reply

You must log in to post.