This discussion is archived
6 Replies Latest reply: Nov 1, 2012 4:43 AM by 970647 RSS

Calculated value at the top of the table?

970647 Explorer
Currently Being Moderated
Hi,
I am writing a report from scratch to use in E-Business Suite. I have created my data definition and RTF template, however am struggling with a business requirement on the template.

We have a tabular report which has some data. Now the business want a calculated value to be displayed on the report, but they want it at the TOP of the report rather than at the end. So I need to render the report (because then the variables from within the report are populated and the calculation is done) but then display the result of this before the report. Is this possible? Any pointers are appreciated.

Thanks,
  • 1. Re: Calculated value at the top of the table?
    AlexAnd Guru
    Currently Being Moderated
    possible

    1 way) create element with sum function in rtf
    2 way) create element with sum function in data template
    3 way) create element with value calculated in package

    so post simple data (xml) and simple report output


    in the next time use appropriate forum BI Publisher
  • 2. Re: Calculated value at the top of the table?
    970647 Explorer
    Currently Being Moderated
    Apologies - I wasn't sure where to post it as this was EBS and XMLP rather than core BIP.

    I'd prefer to do the calculation in the template rather than the data definition or package, because it is just a calculation based on the table values.

    Sample XML:
    <?xml version="1.0" encoding="UTF-8"?>
    <G_DATA>
      <G_VENDOR_LIST>
        <VENDOR_CODE>A1234</VENDOR_CODE>
        <VENDOR_NAME>John Smith</VENDOR_NAME>
        <G_PARTS_LIST>
          <PART_NUMBER>ABC123</PART_NUMBER>
          <DESCRIPTION>DUMMY PART</DESCRIPTION>
          <INDEX_SCORE>1</INDEX_SCORE>
          <CONTAINER_TYPE>C4</CONTAINER_TYPE>
          <CONTAINER_DESCRIPTION>Metal</CONTAINER_DESCRIPTION>
        </G_PARTS_LIST>
        <G_PARTS_LIST>
          <PART_NUMBER>DEF321</PART_NUMBER>
          <DESCRIPTION>DUMMY PART 2</DESCRIPTION>
          <INDEX_SCORE>0</INDEX_SCORE>
          <CONTAINER_TYPE>AB1</CONTAINER_TYPE>
          <CONTAINER_DESCRIPTION>OtherPackaging</CONTAINER_DESCRIPTION>
        </G_PARTS_LIST>
      </G_VENDOR_LIST>
    </G_DATA>
    My report looks something like this:
    Part No   Description  Index   PK Code   PK Description
    -------------------------------------------------------
    ABC123    DUMMY PART     1        C4     Metal
    DEF321    DUMMY PART 2   0        AB1    OtherPackaging
    -------------------------------------------------------
    Total Parts: 2 | Total Index: 1
    Where "Total Parts" and "Total Index" are variables which I add up when the element is printed in the report and then print at the end.

    What the requirement is though is to have an "Index Rating" which is based on the total index / total parts. And this is required to be printed above the report. So I am fine printing it at the bottom like this:
    <? xdoxslt:get_variable($_XDOCTX,'RET_COUNT') div xdoxslt:get_variable($_XDOCTX,'PART_COUNT') ?>
    but if I put that at the top of the report then it obviously uses the initialization values of zero.



    Thanks
  • 3. Re: Calculated value at the top of the table?
    AlexAnd Guru
    Currently Being Moderated
    >
    Apologies - I wasn't sure where to post it as this was EBS and XMLP rather than core BIP.
    >
    Forum: BI Publisher
    ;)

    >
    I'd prefer to do the calculation in the template rather than the data definition or package, because it is just a calculation based on the table values.
    >
    i prefer all based on business requirements

    did you try to use your variables before table?
    <?xdoxslt:set_variable($_XDOCTX,'RET_COUNT', count(//DESCRIPTION))?>
    <?xdoxslt:set_variable($_XDOCTX,'PART_COUNT', sum(//INDEX_SCORE)) ?>
    
    "Index Rating": <? xdoxslt:get_variable($_XDOCTX,'RET_COUNT') div xdoxslt:get_variable($_XDOCTX,'PART_COUNT') ?>
    "Index Rating reverse": <? xdoxslt:get_variable($_XDOCTX, 'PART_COUNT') div xdoxslt:get_variable($_XDOCTX, 'RET_COUNT') ?>
    
    
    table
    
    
    Total Parts: <?xdoxslt:get_variable($_XDOCTX,'RET_COUNT')?> | Total Index: <?xdoxslt:get_variable($_XDOCTX,'PART_COUNT') ?>
  • 4. Re: Calculated value at the top of the table?
    970647 Explorer
    Currently Being Moderated
    Hi,
    Thank-you - that is almost working now! The only problem I am having is that there are multiple tables printed (ie, one for each vendor) and the rating is for the supplier, not for all suppliers.

    So at the moment, the counts being returned are for the whole report, not a count for the current group (vendor).

    I have tried:
    <?xdoxslt:set_variable($_XDOCTX,'PART_COUNT', count(current-group()//PART_NUMBER)) ?>
    but get a runtime error:
    Font Dir: C:\Program Files\Oracle\XML Publisher\Template Builder\fonts
    Run XDO Start
    setLocale: en
    java.lang.reflect.InvocationTargetException
         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
         at java.lang.reflect.Method.invoke(Unknown Source)
         at oracle.apps.xdo.common.xml.XSLT10gR1.invokeProcessXSL(XSLT10gR1.java:580)
         at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:378)
         at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:197)
         at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:156)
         at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:916)
         at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:178)
         at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1507)
         at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:913)
         at RTF2PDF.runRTFto(RTF2PDF.java:535)
         at RTF2PDF.runXDO(RTF2PDF.java:423)
         at RTF2PDF.main(RTF2PDF.java:217)
    Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
         at oracle.xdo.parser.v2.XSLTContext.peekExprValue4Grouping(XSLTContext.java:861)
         at oracle.xdo.parser.v2.XPathFunctionCall.evaluate(XPathFunctionCall.java:526)
         at oracle.xdo.parser.v2.PathExpr.evaluate(XSLNodeSetExpr.java:851)
         at oracle.xdo.parser.v2.XPathFunctionCall.evaluate(XPathFunctionCall.java:554)
         at oracle.xdo.parser.v2.XPathExtFunction.evaluate(XPathExtFunction.java:221)
         at oracle.xdo.parser.v2.XSLValueOf.processAction(XSLValueOf.java:101)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLResultElement.processAction(XSLResultElement.java:180)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLResultElement.processAction(XSLResultElement.java:180)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLResultElement.processAction(XSLResultElement.java:180)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLResultElement.processAction(XSLResultElement.java:180)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLForEach.processAction(XSLForEach.java:142)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLResultElement.processAction(XSLResultElement.java:180)
         at oracle.xdo.parser.v2.XSLNode.processChildren(XSLNode.java:417)
         at oracle.xdo.parser.v2.XSLTemplate.processAction(XSLTemplate.java:191)
         at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:508)
         at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:485)
         at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:264)
         at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:150)
         at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:187)
         ... 15 more
    Thanks
  • 5. Re: Calculated value at the top of the table?
    AlexAnd Guru
    Currently Being Moderated
    looks like
    >
    <?xdoxslt:set_variable($_XDOCTX,'PART_COUNT', count(current-group()//PART_NUMBER)) ?>
    >
    is in for-each
    so try smth
    count(PART_NUMBER)
  • 6. Re: Calculated value at the top of the table?
    970647 Explorer
    Currently Being Moderated
    Hi,
    Thank-you very much, that has worked a treat. Yes, it is in a for-each. I'm still not 100% sure as to what "//" was doing (I have seen similar syntax in the XML Publisher Users Guide) but it's worked either way. :)

    Cheers
    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points