1. 

   Steve44, Member

   I have a report which is displaying correctly, but I want to show some columns as percent calculations with 2 decimal places (partial code is shown below). For, example if the calculation is .356241 I would like 35.62% to display in the column. The percents appear correctly, but they are left-aligned because they are converting to strings. I want them to be right-aligned. I am trying to do this with dbr.colstyle and the built-in style "align_r", but it's not working. How can I do this?

   CREATE PROCEDURE sp_DBR_payasr_Reserves_Summary() BEGIN

   DECLARE vSum DECIMAL(10,2);

   SELECT 'dbr.title', 'PaymentAssure'; SELECT 'dbr.text', Concat('Reserves SummaryDate: ', DATE_FORMAT(CURRENT_DATE(),'%Y-%m-%d')), 'ParamHeaderClass'; SELECT 'dbr.colstyle', 'R%', mydbr_style('align_r'); SELECT 'dbr.report', 'sp_DBR_payasr_Reserves_Detail', '[Date]', 'inline', 'vYearInt=[funding_year]', 'vMonthInt=[funding_month]', 'show_link=1>0'; SELECT 'dbr.hidecolumn', 'funding_year', 'funding_month';

   CREATE TEMPORARY TABLE IF NOT EXISTS temp_reserves_summary ( month_date DATE NOT NULL, funding_year INT NOT NULL, funding_month INT NOT NULL, amount_gross_profit DECIMAL(10,2) NOT NULL, amount_funding DECIMAL(10,2) NOT NULL, amount_reserves DECIMAL(10,2) NOT NULL, amount_reserves_percent DECIMAL(10,4) NOT NULL );

   UPDATE temp_reserves_summary SET amount_reserves_percent = (amount_reserves / amount_gross_profit);

   SELECT funding_year, funding_month, amount_gross_profit AS 'Gross Profit', amount_funding AS 'Funding', amount_reserves AS 'Reserves', CONCAT(FORMAT(amount_reserves_percent * 100, 2), '%') AS '%[R%]' FROM temp_reserves_summary GROUP BY funding_year, funding_month ORDER BY funding_year, funding_month;

   END $$
2. 

   myDBR Team, Key Master

   Steve,
   Instead of converting the number column to a string and concatenating the '%' sign at the end, you should just format the number column with the desired format. This allows myDBR to treat the number format as number (correct alignment, column sorting, user number formatting preferecne etc). Now that you convert it to string, you will not only lose that all, but your report code also becomes harder to read and maintain.

   So, instead of:

   select CONCAT(FORMAT(amount_reserves_percent * 100, 2), '%')

   You should do:

   select 'dbr.colstyle', '%.2f %'

   select amount_reserves_percent * 100

   Btw, your dbr.report's show_link usage has no function as it is always valuated as true, but we assume you already know this.

   --
   myDBR Team
3. 

   Steve44, Member

   That worked. I thought it would be an answer like that (don't convert to a string and format the number), but I wasn't sure how to do that format. As for the show_link function, I thought it was required and I wanted it to always be true. But after reading your comment I tested the link without show_link and it always appears, so I will leave the show_link out.

   Thanks again.

Reply

You must log in to post.