1. 

   ajdjackson, Member

   Hi

   I've created a crosstab report that mimmicks a short Profit and Loss Account e.g. I've created a Category Field and populated that with Sales, Costs, Gross Margin and % Gross Margin. The Crosstab enables me to have a number of previous months.

   The crosstab works great but I have a problem in setting the number of decimal places. For each Category I've used:

   select 'dbr.colstyle', 3, '%0.0f;-;[color:red](%0.0f)';

   However for the % Gross Margin category in the crosstab I wish to have it displayed with 2 decimal places.

   I've tried creating an extra column in the select statement and hiding it as follows:

   select 'dbr.hidecolumns', 'class'; select 'dbr.css', '.perclass {[color:red]%.2f;} .greenclass {color:green;}';

   select substring(Category,3) as '[Cat]', case MonNum when 0 then "Total" else date_format((STR_TO_DATE(MonNum, '%m')),'%b') end as Month , value as '', if (substring(Category,3)='% Gross Margin','perclass','greenclass') as 'class'

   But this has no effect.

   What's the best way to do what I'm trying to achieve?

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   The dbr.css takes valid css as parameter. The definition .perclass {[color:red]%.2f;} is not valid css.

   To format the number in cell, use either dbr.colstyle (if all the rows share same formatting) or dbr.cellstyle (if different row in same column have different formatting).

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Many thanks for getting back.

   I'm still stuck.

   What I wish to do is in the crosstab when the Category = '6.% Gross Margin' display this to 2 decimal places and when it's not display with no decimal places.

   This is where I've got to but all the columns display with 2 decimal places and the negative numbers do not display as red and in parenthesis.

   Jake

   select 'dbr.crosstab',2; /*select 'dbr.colstyle', 3, '%0.0f;-;[color:red](%0.0f)';*/ select 'dbr.report','sp_DBR_QTR_Analysis_SalesNum',1,'inline','inCat=Cat'; select 'dbr.export.options','orientation','landscape'; select 'dbr.export.options', 'paper_size', 'A4'; select 'dbr.export.options', 'autosize', 0; select 'dbr.hidecolumns', 'class'; select 'dbr.cellstyle','Val','class';

   select substring(Category,3) as '[Cat]', case MonNum when 0 then "Total" else date_format((STR_TO_DATE(MonNum, '%m')),'%b') end as Month , value as '[Val]', if (left(Category,1)='6','%0.2f;-;[color:red](%0.2f)','%0.0f;-;[color:red](%0.0f)') as 'class'

   from analysis_2_tmp order by Category,MonNum desc;
4. 

   myDBR Team, Key Master

   Sorry to be inaccurate. For cell formatting you will have two commands: dbr.cellformat and dbr.cellstyle. dbr.cellformat gets formatting parameter as printf and dbr.cellstyle gets a CSS style definition.

   Now that you are applying different formatting on same column based on another column (category) and the value itsef (positive, zero, negative) the cleanest way to do the formatting is to create a function for both dbr.cellformat and dbr.cellstyle which will take category and the value as parameter and return the wanted format and CSS style.

   select 'dbr.cellstyle', 'Val', 'cellstyle';
   
   select 'dbr.cellformat', 'Val', 'cellformat';
   
   select
   
     substring(Category,3) as '[Cat]',
   
     case MonNum when 0 then "Total" else date_format((STR_TO_DATE(MonNum, '%m')),'%b') end as Month,
   
     value as '[Val]',
   
     fn_category_format( Category, value ) as 'cellstyle',
   
     fn_category_style( Category, value ) as 'cellformat'
   
   

   
   --
   myDBR Team
5. 

   ajdjackson, Member

   Hi

   Still stuck :(

   I need help in what is returned from the function.

   I decided to try and get the number formatting working first and have created a function below:

   CREATE FUNCTION mydbr.fn_category_format( inCategory varchar(30), inValue float ) RETURNS varchar(100) CHARSET utf8
   READS SQL DATA
   DETERMINISTIC
   BEGIN
   DECLARE vDecimal varchar(10);

   IF ( left(inCategory,1) = '6' ) THEN
   SET vDecimal = '%0.2F';
   ELSE
   SET vDecimal = '%0.0F';
   END IF;

   RETURN CONCAT('dbr.purehtml:<span style="vDecimal">',inValue,'</span>');
   END;

   I have amended the crosstab report as follows:

   select 'dbr.crosstab',2;
   select 'dbr.colstyle', 3, ';-;[color:red]()';
   select 'dbr.report','sp_DBR_QTR_Analysis_SalesNum',1,'inline','inCat=Cat';
   select 'dbr.export.options','orientation','landscape';
   select 'dbr.export.options', 'paper_size', 'A4';
   select 'dbr.export.options', 'autosize', 0;
   select 'dbr.hidecolumns', 'cellformat';
   select 'dbr.cellformat','Val','cellformat';

   select substring(Category,3) as '[Cat]',
   case MonNum
   when 0 then "Total"
   else
   date_format((STR_TO_DATE(MonNum, '%m')),'%b')
   end as Month ,
   value as '[Val]',
   fn_category_format( Category, value ) as 'cellformat'
   from analysis_2_tmp
   order by Category,MonNum desc;

   So I'm not sure what to return from the function to set the humber of decimal places.

   What would I return form the function to achieve '%0.0f;-;[color:red](%0.0f)';?

   Many thanks for your help

   Jake
6. 

   myDBR Team, Key Master

   Jake,
   the dbr.cellformat takes two parameters. First parameter is the ColumnReference to the column which will be formatted. The second parameter is the column where the printf-formatting can be found.

   Your fn_category_format function should return the printf-string used to format the column. In your case it would be either '%0.2f' or '%0.0f', nothing else.

   LIkewise, when you create the fn_category_style-function, it should return just the CSS to be applied to the cell. In your case it would be either 'color:red' or empty string '' for no color.

   --
   myDBR Team
7. 

   ajdjackson, Member

   Hi

   Thanks - I'm almost there :)

   I've changed the function to:

   CREATE FUNCTION mydbr.fn_category_format( inCategory varchar(30), inValue float ) RETURNS varchar(100) CHARSET utf8 READS SQL DATA DETERMINISTIC BEGIN DECLARE vDecimal varchar(10); DECLARE vBracketl varchar(10); DECLARE vBracketr varchar(10); set vDecimal =""; set vBracketl = "("; set vBracketr = ")";

   IF ( left(inCategory,1) = '6' ) THEN SET vDecimal = '%0.2F'; ELSE SET vDecimal = '%0.0F'; END IF;

   IF ( inValue < 0 ) THEN SET vDecimal = concat(vBracketl,vDecimal,vBracketr); ELSE SET vDecimal = vDecimal; END IF;

   RETURN vDecimal;

   END;

   and the report code is now:

   select 'dbr.crosstab',2; select 'dbr.colstyle', 3, '%0.0f;-;[color:red](%0.0f)'; select 'dbr.report','sp_DBR_QTR_Analysis_SalesNum',1,'inline','inCat=Cat'; select 'dbr.export.options','orientation','landscape'; select 'dbr.export.options', 'paper_size', 'A4'; select 'dbr.export.options', 'autosize', 0; select 'dbr.hidecolumns', 'cellformat'; select 'dbr.cellformat','Val','cellformat';

   select substring(Category,3) as '[Cat]', case MonNum when 0 then "Total" else date_format((STR_TO_DATE(MonNum, '%m')),'%b') end as Month , value as '[Val]', fn_category_format( Category, value ) as 'cellformat'

   from analysis_2_tmp order by Category,MonNum desc;

   The select 'dbr.colstyle', 3, '%0.0f;-;[color:red](%0.0f)'; line in the report still works with negative numbers and the rows are now displaying with the correct number of decimal places. I don't see the need to for creating the fn_category_style function or am I missing something?

   The only issue I have is that negative numbers are displayed in red with brackets but still have the minus sign. Is there a way to remove the minus sign?

   Many thanks for your patience

   Jake
8. 

   myDBR Team, Key Master

   Jake,
   Negative numbers show the minus sign because they are negative numbers. To show the number without the negative sign, make sure you display always a positive number (convert the number to absolute number using MySQL abs-function). When you do this, you should create the fn_category_style-function so you can separate negative numbers from positive numbers by using the color. You do not need to use the dbr.colstyle-command.

   So the code would look like the example code given couple of posts back:

   select 'dbr.cellstyle', 'Val', 'cellstyle';
   select 'dbr.cellformat', 'Val', 'cellformat';

   select
   
     substring(Category,3) as '[Cat]',
   
     case MonNum when 0 then "Total" else date_format((STR_TO_DATE(MonNum, '%m')),'%b') end as 'Month',
   
     abs(value) as '[Val]',
   
     fn_category_format( Category, value ) as 'cellstyle',
   
     fn_category_style( Category, value ) as 'cellformat'

   The fn_category_style would return 'color:red' when you want a red number and an empty string '' when no color is required.

   --
   myDBR Team

Reply

You must log in to post.