1 2 Previous Next 23 Replies Latest reply: Oct 16, 2007 2:30 PM by 524753 RSS

    Sub -totals

    549017
      Hi,

      I need to perform sub-totals based on the changing value of a certain field.

      Where could i find documentation to do this?

      do we have to look at the template, create a variable in the template?

      thanks.
        • 1. Re: Sub -totals
          Tim Dexter-Oracle
          Hi
          Are you using the Template Builder for Word? If so,
          1. pick the insertion point for the subtotal field - this is important and will affect the level of summarization
          2. Use the Insert > Field dialog - select the field you want to summarize then the summary function
          3. Click insert to get the field into the document.
          Done
          tim
          • 2. Re: Sub -totals
            549017
            I am using a <?sum(COST)?> calculation - but would like this to sub-total based on the changing driver_name. Right now, this gives an overall sum of all the rows.

            I have tried - <?for-each:DRIVER_ID?><?sum(COST)?><?end for-each?> - but with no result. it doesn't sub-total.

            Thanks.

            Message was edited by:
            user546014
            • 3. Re: Sub -totals
              PradeepSharma-Oracle
              The steps mentioned by Tim will add the <?sum(current-group()/COST)?> with in your loop, but your logic does not have any group.

              You can create a group and then sub total can be achieved within the group

              Something like this -

              <?for-each-group:DRIVER_ID;groupbyfieldname?>
              <?for-each:current-group()?><?sum(current-group()/COST)?><?end for-each?>
              <?end for-each-group?>

              Regds
              Pradeep
              • 4. Re: Sub -totals
                549017
                I have inserted this new field but for some reason i don't see the sub-totals. I have inserted the field based on COST. This is the code:
                ---
                <?for-each-group:DRIVER_KEY;groupbyfieldname?>
                <?for-each:current-group()?>
                <?sum(current-group()/COST)?>
                <?end for-each?>
                <?end for-each-group?>
                ---
                and the name of the field is "sum COST".

                Do you see anything odd with this code?

                I tried this as well:
                --
                <?for-each-group:DRIVER_KEY;groupbyfieldname?>
                <?sum(current-group()/COST)?>
                <?end for-each-group?>
                --
                with no results.

                Message was edited by:
                user546014

                Message was edited by:
                user546014
                • 5. Re: Sub -totals
                  524753
                  <?for-each-group:DRIVER_KEY;groupbyfieldname?>

                  Dude, did you use, as it displayed here ?

                  If so, its not going to work for sure. :)

                  replace string "groupbyfieldname" with the element name in your xml, for which you want to group by.
                  • 6. Re: Sub -totals
                    549017
                    ok - I did change it to use the field name:-

                    <?for-each-group:COST;DRIVER_KEY?>

                    where DRIVER_KEY is the key we are group sorting on.

                    It still doesn't work - what could i try next?

                    Many thanks!
                    • 7. Re: Sub -totals
                      PradeepSharma-Oracle
                      Please post a sample XML data.
                      • 8. Re: Sub -totals
                        549017
                        How do I get a hold of that?

                        Pardon my ignorance, i started using this tool last thursday.

                        Thanks.
                        • 9. Re: Sub -totals
                          524753
                          Is this report based on Concurrent program ?
                          or Java file ?

                          check for the sql query it contains in the file, run that in Db get the output in xml format.

                          Which tool are you using ?

                          Desktop tool ? enterprise edition ?
                          • 10. Re: Sub -totals
                            549017
                            we are using the BI Publisher add-on that comes with MS Word 2007, the template builder. that is where i am making these changes, in the MS Word 2007.

                            Message was edited by:
                            user546014
                            • 11. Re: Sub -totals
                              PradeepSharma-Oracle
                              I think first you will have to tell us where are you trying the code, which environment and how are you testing it ? To create a template it is good to install the template builder (ie also referred as Desktop Client). Here you can preview the output of your report in PDF, HTML, RTF,Excel and PPT formats. But to use template builder you need to have sample XML data, ie, either a XML data that can be loaded from any .xml file, or you can connect to BI Publisher Enterprise Server (if this is your working environment) by logging into the server and extracting a sample xml data.

                              So let us know if you know what is the data that you were using with the for-each loop.

                              Regds
                              Pradeep
                              • 12. Re: Sub -totals
                                549017
                                Then we are using the Desktop Client and BI Publisher Enterprise Server.

                                We create these templates using BI Publisher.

                                Then connect to the server in MS Word, open the template we want to work and make changes here. There is an "upload template option" in the Desktop client, which uploads this modified template right away and view changes here.

                                ==
                                <?xml version = '1.0' encoding = 'utf-8'?>
                                <xliff version="1.0">
                                <file source-language="EN-US" target-language="EN-US" datatype="XDO" original="orphan.xlf" product-version="orphan.xlf" product-name="">
                                <header>
                                <skl>
                                <internal-file><DELETED</internal-file>
                                </skl>
                                <prop-group name="ora_reconstruction">
                                <prop prop-type="TemplateCode">n/a</prop>
                                <prop prop-type="extractorVersion">10.1.3.3.0_1.29</prop>
                                </prop-group>
                                </header>
                                <body>
                                <trans-unit id="1fe7aa" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>DATA</source>
                                <target>DATA</target>
                                <note>Text located: header/table</note>
                                </trans-unit>
                                <trans-unit id="6733c82b" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>Date: [&amp;1]</source>
                                <target>Date: [&amp;1]</target>
                                <prop-group name="ora_reconstruction">
                                <prop prop-type="e1">DELETEd</prop>
                                </prop-group>
                                <note>Text located: header/table, token &amp;1:anonymous placeholder(s)</note>
                                </trans-unit>
                                <trans-unit id="35b3f0c7" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>Page [&amp;1] Of [&amp;2]</source>
                                <target>Page [&amp;1] Of [&amp;2]</target>
                                <prop-group name="ora_reconstruction">
                                <prop prop-type="e1"></prop>
                                <prop prop-type="e2"></prop>
                                </prop-group>
                                <note>Text located: header/table, token &amp;1 ,&amp;2:anonymous placeholder(s)</note>
                                </trans-unit>
                                <trans-unit id="10738" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>C_4</source>
                                <target>C_4</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="10736" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>C_2</source>
                                <target>C_2</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="10735" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>C_1</source>
                                <target>C_1</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="10737" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>C_3</source>
                                <target>C_3</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="4da9951" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>VALUE</source>
                                <target>VALUE</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="1fa7cd" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>COST</source>
                                <target>COST</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="9fa1cf0d" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>INVOICE</source>
                                <target>INVOICE</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                <trans-unit id="96b19948" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
                                <source>VENDOR</source>
                                <target>VENDOR</target>
                                <note>Text located: body/table</note>
                                </trans-unit>
                                </body>
                                </file>
                                </xliff>

                                Message was edited by:
                                user546014
                                • 13. Re: Sub -totals
                                  PradeepSharma-Oracle
                                  If you have access to the Enterprise Server, you can export the xml data from there.
                                  The xml you have posted is not the actual xml data , this is an XLIFF file which is used for translation of report to any other language.

                                  So just check if you can logon to the Enterprise Server and export the XmL data.
                                  • 14. Re: Sub -totals
                                    524753
                                    Ok, here we go..

                                    Try this sample work..
                                    This is template, copy paste this in word and save as rtf .

                                    <?for-each-group:ROW; DRIVER_KEY?>
                                    <?DRIVER_KEY?> <?sum(current-group()/COST)?>
                                    <?end for-each-group?>

                                    this is XML , copy to notepad, and save to test.xml
                                    <ROWSET>
                                    <ROW>
                                    <DRIVER_KEY>441</DRIVER_KEY>
                                    <INDUSTRY>Motor vehicle &amp; parts dealers</INDUSTRY>
                                    <YEAR>2005</YEAR>
                                    <MONTH>March</MONTH>
                                    <COST>75770</COST>
                                    </ROW>
                                    <ROW>
                                    <DRIVER_KEY>441</DRIVER_KEY>
                                    <INDUSTRY>Furniture &amp; home furnishings stores</INDUSTRY>
                                    <YEAR>2005</YEAR>
                                    <MONTH>March</MONTH>
                                    <COST>8804</COST>
                                    </ROW>
                                    <ROW>
                                    <DRIVER_KEY>443</DRIVER_KEY>
                                    <INDUSTRY>Electronics &amp; appliance stores</INDUSTRY>
                                    <YEAR>2005</YEAR>
                                    <MONTH>March</MONTH>
                                    <COST>8292</COST>
                                    </ROW>
                                    </ROWSET>

                                    now, simple, open the rtf and in the add-ons, load the xml,
                                    click preview :)
                                    , you can figure how it works.
                                    1 2 Previous Next