1. 

   vannc, Member

   Dear Team!

   I am struggling with calculating percentages in the 'Total' column. I have done with 'dbr.hsum' to calculate the sum, but I am having trouble with percentages. :(
   this is my code:
   select 'dbr.summary.text', 'Total';
   select 'dbr.hsum', 'ty';
   --select 'dbr.havg', '%Actual';
   select 'dbr.hsum', 'Plan';
   --select 'dbr.havg', '%Plan';
   --select 'dbr.havg', '%';
   select 'dbr.hsum', 'MoM';
   --select 'dbr.havg', '%MoM';
   select 'dbr.hsum', 'ly';
   --select 'dbr.havg', '%YoY';
   --select 'dbr.summary.calc', '%', 'ty/Plan*100';
   --select 'dbr.summary.calc', '%MoM', '(ty/MoM-1) * 100';
   --select 'dbr.summary.calc', '%YoY', '(ty/ly-1) * 100';

   Thanks Team!
2. 

   myDBR Team, Key Master

   The question contains no no query so it would be a quess what you are trying to do.

   Couple of notes:

   • You can use dbr.calc instead of dbr.summary.calc. The dbr.summary.calc is kept just for compatibility for old versions.
   • See the manual for dbr.calc usage, i.e. use brackets around column references
   
   

   --
   myDBR Team
3. 

   vannc, Member

   Dear Team!

   Thanks for your note. I will use dbr.calc. But percentages are not working for me.
   This is the full source code:
   https://drive.google.com/file/d/1J4r9HwcD2YNAAMNxkg1zMc-lyP1YcDp2/view?usp=sharing
   And this is picture of total column
   https://drive.google.com/file/d/1e2-1wsjmWySTP3YUK2bdsXIt070SBBxr/view?usp=sharing

   Thanks Team!
4. 

   myDBR Team, Key Master

   When doing calculations, the formula is applied to all columns where the value is NULL. In your code you have the only calculation on the %YoY-column where you calculate the value already by yourself.

   --
   myDBR Team
5. 

   vannc, Member

   Dear Team,

   So you mean that is the reason why the percentage column is hidden in the 'Total' group?
6. 

   myDBR Team, Key Master

   The crosstab horizontal summary columns show up on those columns you have defined the horizontal actions: In your case the columns defined by the dbr.hsum-command.

   In order to add the %YoY-column to be added and in order to keep the NULL value for calculation, use dbr.hnull-command for the column.

   --
   myDBR Team
7. 

   vannc, Member

   Dear Team!

   Wow, finally, the percentage columns show up. Thanks team! :D

   Best regards:
   Vannc
8. 

   vannc, Member

   Dear Team!

       CASE
   
           WHEN (sum(b.recent) / sum(b.[20240331HDQTV1]) * 100) >= 100 THEN 'color:green;'
   
           ELSE 'color:red;'
   
       END AS 'a_style',

   But how can I apply this CSS for '%' column in the 'Total' group?
   

   select 'dbr.calc', '%', '[ty]/[Plan]*100';
9. 

   myDBR Team, Key Master

   See the documentation. The dbr.calc command has the summary_format-option uses the same format as dbr.colstyle.

   --
   myDBR Team
10. 

    vannc, Member

    https://mydbr.com/demo/mydbr/report.php?r=3&p1=sp_DBR_footercellstyle&m=4&h=edd64b387dd72d3e744913cccdd4baa7cf058031&i=1

    Thanks team, I followed your instruction another topic; this is about

    cellclass 

    for

    dbr.calc

Reply

You must log in to post.