1. 

   TJH, Member

   Hi,

   I seem to have come across an issue in crosstab formatting. Using any amount of IFNULL or CAST doesn't seem to fix the problem.

   The issue is when doing sum's and/or hsum's in a crosstab, MyDBR starts to round the totals and do only an integer format rather than the standard decimal format.

   I can apply a manual format to the column but then I'm unable to get a thousands separator. Is there a special style to trigger MyDBR's default 2 decimal style?

   Here's a screenshot of the issue

   
   
   DROP PROCEDURE IF EXISTS sp_DBR_Crosstab_Formatting_Issue
   
   $$
   
   CREATE PROCEDURE sp_DBR_Crosstab_Formatting_Issue()
   
   BEGIN
   
   SELECT 'dbr.subtitle', 'Cross Tab 1';
   
   SELECT 'dbr.no_data', 'No data matches.', 'comment';
   
   SELECT 'dbr.hdr',      1;
   
   SELECT 'dbr.crosstab', 2;
   
   SELECT 'dbr.sum',      3, 4;
   
   SELECT 'dbr.hsum',     3, 4;
   
   SELECT grp,
   
          period,
   
          SUM(metric1) AS m1,
   
          SUM(metric2) AS m2
   
     FROM (
   
   	SELECT 'Group A'  AS grp,
   
   	       'Period 1' AS period,
   
   	       1925.13    AS metric1,
   
   	       NULL       AS metric2
   
   	UNION ALL
   
   	SELECT 'Group B'  AS grp,
   
   	       'Period 1' AS period,
   
   	       NULL       AS metric1,
   
   	       1227.15    AS metric2
   
           ) a
   
    GROUP BY grp, period; 
   
   SELECT 'dbr.subtitle', 'Cross Tab 2';
   
   SELECT 'dbr.no_data', 'No data matches.', 'comment';
   
   SELECT 'dbr.hdr',      1;
   
   SELECT 'dbr.crosstab', 2;
   
   SELECT 'dbr.sum',      3, 4;
   
   SELECT 'dbr.hsum',     3, 4;
   
   SELECT grp,
   
          period,
   
          SUM(metric1) AS m1,
   
          SUM(metric2) AS m2
   
     FROM (
   
   	SELECT 'Group A'  AS grp,
   
   	       'Period 1' AS period,
   
   	       1925.13    AS metric1,
   
   	       NULL       AS metric2
   
   	UNION ALL
   
   	SELECT 'Group B'  AS grp,
   
   	       'Period 1' AS period,
   
   	       NULL       AS metric1,
   
   	       1227.15    AS metric2
   
   	UNION ALL
   
           SELECT 'Group C'  AS grp,
   
                  'Period 1' AS period,
   
                  0.00       AS metric1,
   
                  0.00       AS metric2
   
           ) a
   
    GROUP BY grp, period; 
   
   SELECT 'dbr.subtitle', 'Cross Tab 2 w/ format';
   
   SELECT 'dbr.no_data', 'No data matches.', 'comment';
   
   SELECT 'dbr.hdr',      1;
   
   SELECT 'dbr.crosstab', 2;
   
   SELECT 'dbr.sum',      3, 4;
   
   SELECT 'dbr.hsum',     3, 4;
   
   SELECT 'dbr.colstyle', 3, '%.2f';
   
   SELECT 'dbr.colstyle', 4, '%.2f';
   
   SELECT grp,
   
          period,
   
          SUM(metric1) AS m1,
   
          SUM(metric2) AS m2
   
     FROM (
   
   	SELECT 'Group A'  AS grp,
   
   	       'Period 1' AS period,
   
   	       1925.13    AS metric1,
   
   	       NULL       AS metric2
   
   	UNION ALL
   
   	SELECT 'Group B'  AS grp,
   
   	       'Period 1' AS period,
   
   	       NULL       AS metric1,
   
   	       1227.15    AS metric2
   
   	UNION ALL
   
           SELECT 'Group C'  AS grp,
   
                  'Period 1' AS period,
   
                  0.00       AS metric1,
   
                  0.00       AS metric2
   
           ) a
   
    GROUP BY grp, period; 
   
   END
   
   $$
   
   
2. 

   myDBR Team, Key Master

   Yes,
   that looks like a bug. We'll take a look at it.

   --
   myDBR Team
3. 

   myDBR Team, Key Master

   Hi,
   The problem is now fixed. Run the updater to get the latest build.

   Thank you for reporting this issue.

   --
   myDBR Team
4. 

   myDBR Team, Key Master

   A newer build is available to fix this error.

   --
   myDBR Team
5. 

   TJH, Member

   Much appreciated!

   Trevor

Reply

You must log in to post.