1. 

   Jay, Member

   I have a report like this:

   User | Amount
   -------------
   user1 | $100.00
   | $120.00
   | $200.00
   ---------------
   user1 $420.00

   I would like to add the users budget and remaining budget to the totals like this:

   User | Amount |
   ---------------
   user1 | $100.00
   | $120.00
   | $200.00
   ---------------
   user1 $420.00 Budget $1000.00 Remaining $580.00

   I can get it to sort of work by adding the budget and the remaining amounts as columns but then I have a bunch of row data that is duplicated (for budget) or meaningless (for remaining).

   Is there an option for this kind of thing? I thought dbr.hidecolumn.data might do it, but that seems to be for a horizontal layout.

   Thanks

   Jay
2. 

   myDBR Team, Key Master

   If you do not want to show the budget and remaining columns, you can hide those columns and set just the value for a summary row cell:

   select 'dbr.hdr', 'User';
   
   select 'dbr.sum', 'Amount', 'Budget', 'Rm';
   
   select 'dbr.summary.options', 'limit_summary_level', 0;
   
   select 'dbr.summary.text', 'B', 'Budget $[Budget] Remaining [Rm]';
   
   select 'dbr.hidecolumns', 'Budget';
   
   select 'dbr.calc', 'Rm', '[Budget]-[Amount]';
   
   select 'user1' as 'User', 100 as 'Amount', '' as '[B]', 1000 as 'Budget', null as 'Rm'
   
   union
   
   select 'user1', 120, null, 0, null
   
   union
   
   select 'user1', 220, null, 0, null;
   
   

   --
   myDBR Team
3. 

   Jay, Member

   I'm sorry, I oversimplified my question. Your answer is perfect and is helpful but I am still looking for a solution. I need a slight variation on the solution you supplied.

   The report should look like this:

   User | Amount
   -------------
   |user1 | $100.00
   | | $120.00
   | | $200.00
   ---------------
   user1 $420.00 Budget $1000.00 Remaining $580.00
   ---------------
   |user2 | $80.00
   | | $20.00
   | | $200.00
   ---------------
   user2 $300.00 Budget $500.00 Remaining $200.00
   ---------------

   Is there a way to make the dbr.summary.text display in the subtotal instead of the final total?

   Thanks again for your help.

   Jay
4. 

   myDBR Team, Key Master

   Jay,
   you can also use dbr.calc which accepts PHP expressions combined with ColumnReferences. If you do not want the summary row, you can hide it.

   Calc Expression

   --
   myDBR Team
5. 

   Jay, Member

   You guys are great, thanks Key Master.

   Jay

Reply

You must log in to post.