X

An Oracle blog about BI Publisher

  • RTF
    January 24, 2008

Conditional Totals

I did a couple of posts a while back on totaling here, here and here - I recently got a question asking how to do some conditional totaling, more specifically in a list of invoice amounts how I can total according to an invoice type - something along those lines anyway. To make myself clearer assume you have the following data:

<G_INVOICE_NUM>
 <SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
 <GL_DATE>10-NOV-04</GL_DATE>
 <NVL_I_DISCOUNT_AMOUNT_TAKEN_0>0</NVL_I_DISCOUNT_AMOUNT_TAKEN_0>
 <INV_TYPE>Standard</INV_TYPE>
 <INVOICE_NUM>981110</INVOICE_NUM>
 <INVOICE_DATE>10-NOV-04</INVOICE_DATE>
 <INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
 <ENT_AMT>122</ENT_AMT>
 <ACCTD_AMT>122</ACCTD_AMT>
 <VAT_CODE>VAT22%</VAT_CODE>
</G_INVOICE_NUM>
<G_INVOICE_NUM>
 <SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
 <GL_DATE>28-MAY-04</GL_DATE>
 <NVL_I_DISCOUNT_AMOUNT_TAKEN_0>.41</NVL_I_DISCOUNT_AMOUNT_TAKEN_0>
 <INV_TYPE>Internal</INV_TYPE>
 <INVOICE_NUM>100000</INVOICE_NUM>
 <INVOICE_DATE>28-MAY-04</INVOICE_DATE>
 <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
 <ENT_AMT>122</ENT_AMT>
 <ACCTD_AMT>20.33</ACCTD_AMT>
 <VAT_CODE>VAT22%</VAT_CODE>
</G_INVOICE_NUM>



Take a look at the INV_TYPE, we have 'Standard' and 'Internal' invoice types. How can we get totals by invoice type?
One method would be to group by the INV_TYPE, that way we could very easily get the totals, but lets assume we can not do that, our users do not want that type of grouping. The just want an invoice listing with a total fro all invoices and totals for each type. We can achieve this using an  XPATH expression.



If we loop over the invoices ie for-each:G_INVOICE_NUM to list the invoices we can then generate a total for the invoice entered (ENT_AMT) and accounted amounts (ACCTD_AMT) quite simply with:



<?sum(ENT_AMT)?> and <?sum(ACCTD_AMT)?>



To get at the totals for the invoice types and assuming that we know the different types we can use:



<?sum(ENT_AMT[../INV_TYPE='Internal'])?> and <?sum(ACCTD_AMT[../INV_TYPE='Internal'])?> and



<?sum(ENT_AMT[../INV_TYPE='Standard'])?> and <?sum(ACCTD_AMT[../INV_TYPE='Standard'])?>



the XPATH statement is essentially an if statement, including only those amounts that satisfy the boolean expression. Notice the ../, we need that because INV_TYPE is at the same level as the amount elements, so we effectively go up a level to the parent and then down to the INV_TYPE.



Now lets assume we do not know the number of invoice types in the data but we want to show them and their invoice totals. For this we are going to have to use some grouping. We can group the invoices by INV_TYPE and then do a sum on the invoice amount values.



ConditionalSum1:



Its pretty striaght forward (once you're familiar with the regrouping) we have a for-each-group on the G_INVOICE_NUM parent and group by INV_TYPE thus:



<?for-each-group:G_INVOICE_NUM;./INV_TYPE?>



Then we just create the table to hold the results, for the totals we do not need the XPATH any more :



<?sum(current-group()/ENT_AMT)?> and <?sum(current-group()/ACCTD_AMT)?>



We need the current-group() command because our amounts are inside the newly created INV_TYPE group. This approach does mean that we have to effectively loop over the data again but I think its cheaper than to create updateable variables to store types and totals as you do the main loop.



Hopefully we have now covered the totaling question with these articles, you can get the sample template and data here.

Join the discussion

Comments ( 3 )
  • alan telford Wednesday, February 11, 2009
    Hi Tim,
    appreciate the article. I'm trying to write a bucket type report. E.g we have a list of invoices for customers. We want to have a series of columns showing the number of invoices unpaid over 30 Days, 90 Days 120 Days etc. by supplier
    I have tried doing this in BI Publisher but can't find the right way. Any tips?
    thanks
    Alan
  • Leonard Friday, February 26, 2010
    Hy,
    I use this code an it's ok with one problem.
    I have 2 records in xml, one have value for my tag 1 and other with value 2.
    the code for sum is
    If the record with DT's value equal 2, it is first, it's ok, if the record is second sum is 0.
    Any tips?
    Thanks
    Leo.
  • Leonard Friday, February 26, 2010
    in preview post the code for sum is < ? sum(C7[../DT='2']) ? >
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.