1. 

   thomasj, Member

   Hi,
   I have a request to have a table layout as follows

   ---------------------------------------------
   !Monday ! Tuesday !Wednesday!Thursday!Friday!
   ---------------------------------------------
   ! Hans ! Hans !Jonas !Jonas !Jonas !
   ---------------------------------------------
   ! Sue ! Jonas !Sue !Sue !Sue !
   ---------------------------------------------
   ! Mary ! Mary !Mary !Mary !Mary !
   ---------------------------------------------
   etc..

   Here the idea is to show for each day in the week which kids are going to come to a nursing institution.
   Now for the kids there is a table where each kid is registered with its sex.
   Furthermore a table specifies on which days a kid will be in the nursing institution.

   From this I can get the table above generated (well actually 5 tables besides each other, but that is not so important here)

   Anyway: it is highly wanted to have the columns for each kid showing the sex by having boys colored in blue and girls colored in red.

   This information is stored in the Kids table, so I can determine this; but it cannot automatically be determined from the cell content, as is used in the examples on column styles in the myDBR Docu.

   So my question: is there any possibility to have this information from the specific database entry decide which color should be used via the column style? I currently don't see one

   regards Thomas
2. 

   myDBR Team, Key Master

   Thomas,
   you should be able to create the table above with a single query using cross tabulation report.

   if you wish to apply styles to individual cells, you can do that using the 'dbr.purehtml:' command. To keep reports easy to read, you can encapsulate the cell formatting inside a function.

   How the actual report query might look like:

   SELECT 'dbr.crosstab', 2;
   
   SELECT '', dayname as 'Day', f_Boy_Girl(childname,sex) as ''
   
   FROM mydata
   
   

   And the formatting function:

   CREATE FUNCTION f_Boy_Girl( inName varchar(30), inSex int )
   
   RETURNS varchar(80)
   
   DETERMINISTIC
   
   READS SQL DATA
   
   BEGIN
   
     DECLARE vColor varchar(10);
   
     IF ( inSex = 0 ) THEN
   
       SET vColor = 'red';
   
     ELSE
   
       SET vColor = 'green';
   
     END IF;
   
     RETURN CONCAT('dbr.purehtml:<span style="color:',vColor,'">',inName,'</span>');
   
   END
   
   

   --
   myDBR Team
3. 

   thomasj, Member

   Thanks a ton for the hint to using a function feeding into dbr.purehtml. I now also managed to contain this in one table (by using union), So now I have the table layouted as wished by my users

   regards Thomas

Reply

You must log in to post.