OTN Discussion Forums : Sub -totals ...

user546014

Posts: 74
Registered: 11/29/06

Sub -totals
Posted: Oct 12, 2007 10:37 AM

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.

Tim Dexter

Posts: 2,261
Registered: 01/10/01

Re: Sub -totals
Posted: Oct 12, 2007 4:14 PM

in response to: user546014

Reply

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

user546014

Posts: 74
Registered: 11/29/06

Re: Sub -totals
Posted: Oct 15, 2007 5:32 AM

in response to: user546014

Reply

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

PradeepS

Posts: 225
Registered: 03/27/06

Re: Sub -totals
Posted: Oct 15, 2007 5:54 AM

in response to: user546014

Reply

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

user546014

Posts: 74
Registered: 11/29/06

Re: Sub -totals
Posted: Oct 15, 2007 7:18 AM

in response to: user546014

Reply

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

Vetsrini

Posts: 2,610
Registered: 07/24/06

Re: Sub -totals
Posted: Oct 15, 2007 9:12 AM

in response to: user546014

Reply

<?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.

user546014

Posts: 74
Registered: 11/29/06

Re: Sub -totals
Posted: Oct 15, 2007 10:42 AM

in response to: Vetsrini

Reply

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!

PradeepS

Posts: 225
Registered: 03/27/06

Re: Sub -totals
Posted: Oct 15, 2007 10:53 AM

in response to: user546014

Reply

Please post a sample XML data.

user546014

Posts: 74
Registered: 11/29/06

Re: Sub -totals
Posted: Oct 15, 2007 10:56 AM

in response to: PradeepS

Reply

How do I get a hold of that?

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

Thanks.

Vetsrini

Posts: 2,610
Registered: 07/24/06

Re: Sub -totals
Posted: Oct 15, 2007 11:01 AM

in response to: user546014

Reply

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 ?

user546014

Posts: 74
Registered: 11/29/06

Re: Sub -totals
Posted: Oct 15, 2007 11:02 AM

in response to: Vetsrini

Reply

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

PradeepS

Posts: 225
Registered: 03/27/06

Re: Sub -totals
Posted: Oct 15, 2007 11:06 AM

in response to: user546014

Reply

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

user546014

Posts: 74
Registered: 11/29/06

Re: Sub -totals
Posted: Oct 15, 2007 11:11 AM

in response to: PradeepS

Reply

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: &1</source>
<target>Date: &1</target>
<prop-group name="ora_reconstruction">
<prop prop-type="e1">DELETEd</prop>
</prop-group>
<note>Text located: header/table, token &1:anonymous placeholder(s)</note>
</trans-unit>
<trans-unit id="35b3f0c7" maxbytes="4000" maxwidth="23" size-unit="char" translate="yes">
<source>Page &1 Of &2</source>
<target>Page &1 Of &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 &1 ,&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

PradeepS

Posts: 225
Registered: 03/27/06

Re: Sub -totals
Posted: Oct 15, 2007 11:20 AM

in response to: user546014

Reply

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.

Vetsrini

Posts: 2,610
Registered: 07/24/06

Re: Sub -totals
Posted: Oct 15, 2007 11:25 AM

in response to: user546014

Reply

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 & parts dealers</INDUSTRY>
<YEAR>2005</YEAR>
<MONTH>March</MONTH>
<COST>75770</COST>
</ROW>
<ROW>
<DRIVER_KEY>441</DRIVER_KEY>
<INDUSTRY>Furniture & home furnishings stores</INDUSTRY>
<YEAR>2005</YEAR>
<MONTH>March</MONTH>
<COST>8804</COST>
</ROW>
<ROW>
<DRIVER_KEY>443</DRIVER_KEY>
<INDUSTRY>Electronics & 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.