1. 

   haigda, Member

   I am trying to use two column totals from a crosstab report to calculate a third figure.

   Basically I have two columns of values ([type] is either 'income' or 'expenditure') and want to take the total of income from expenditure in the summary line, or beneath it so that it reads-
   'Profit or Loss' = [income.sum] - [expenditure.sum]

   Here is the crosstab code as it stands at the moment -
   select 'dbr.subtitle', ConCat('Summary (P&L) View for ', inTourCode, ' Departing: ', inDeparting );
   select 'dbr.crosstab', 'type';
   select 'dbr.sum', 'value';
   select 'dbr.crosstab.title', 'Profit or Loss';
   select 'dbr.summary.text','item','Totals';

   select item,
   type,
   value
   from mydbr.summary_tmp;

   Any help greatly appreciated!
2. 

   myDBR Team, Key Master

   You need to separate the income and expenditure into own columns so you can do the calculations. (While the crosstab does seem to do the separation visually, it still thinks they are one an the same column.), Instead, you can use something like this:

   select 'dbr.calc', 'profit_loss', '[Income]-[Expenditure]';
   
   select 'dbr.sum', 'Income', 'Expenditure', 'profit_loss';
   
   select
   
     item as 'Item',
   
     sum(if (type='income', value, 0)) as 'Income',
   
     sum(if (type='expenditure', value, 0)) as 'Expenditure',
   
     null as 'Profit or Loss[profit_loss]'
   
   from mydbr.summary_tmp
   
   group by item;

   Easiest solution would be that when you create the temporary table, put income and expenditure data into different columns so you do not need the type-column at all.

   --
   myDBR Team
3. 

   haigda, Member

   Thanks once again for your prompt response - I have re-arranged the data to be in two different columns in the temporary table.

   However, I now have a column for the 'Profit & Loss' on each row - is there a quick way of telling it to just appear on the Summary row?

   Cheers,
   David
4. 

   myDBR Team, Key Master

   How do you want the summary row to look like? If you want to show the "Profit or Loss xxx" under Item column using dbr.summary.text, you can hide the profit_loss column thar is being used for calculation.

   select 'dbr.summary.text', 'Item', 'Profit - Loss [profit_loss]';
   
   select 'dbr.hidecolumn', 'profit_loss';

   --
   myDBR Team
5. 

   haigda, Member

   That will do for the moment - I can use some formatting to make it stand out.

   Many thanks again,

   Cheers,
   David

Reply

You must log in to post.