Trying to get grand total from subtable aggregates

I'm wondering what's the best way to get a grand total of multiple subtable aggregates. I found something close on another post where the subtable data is getting aggregated correctly - see attached report. 
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/subtotal_aggr.jpg' alt='Posted Image' class='bbc_img' /> 
 
So if the report is modified to have multiple sales orders, I'm trying to figure out a way to get a grand total of each of the sub-table aggregate totals. Any ideas?

Find more posts tagged with

Comments

jverly01

Looks like the report didn't attach. Here it is.

johnw

Take a look at the attached example. In it, I am using scripting to add up the values of an aggregation in a child table, and displaying that in a parent table. You can set a global variable in the onCreate method, and due to the order that the numbers are generated, this will add it up for the sub-tables. Just be sure to reset that variable. Use the Outline view, under the Scripts section, to see what scripts are being used.

jverly01

I've included some background on the report I'm trying to use in Maximo. The report has a main data set and two sub data sets. 
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/rptdatasetlayout.jpg' alt='Posted Image' class='bbc_img' /> 
 
So the LABORCODE field pulls up multiple values for the WORKHOURS and REGULARHRS fields, which come from different sub data sets. So in the grid for the sub data sets, each week's worth of data is aggregated to each LABORCODE (basically the employee ID) - see below. 
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/subdata_aggrsum.jpg' alt='Posted Image' class='bbc_img' /> 
 
The sub data aggregate uses a script on the onCreate method 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>importPackage(Packages.java.lang);
var lastValue = this.getValue();
reportContext.setPersistentGlobalVariable("tot_workhours", new Float(lastValue));</pre>
 
So now I want to get a grand total of each sub data table aggregate totals.

johnw

I provided an example of how to do that in the attachment I provided.

jverly01

Thanks for the example report. My issue is I want to to now have a grand total all grouped "currentAggregationValue" totals - see attached. 
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/examplerptgrandtotal.jpg' alt='Posted Image' class='bbc_img' /> 
 
Any suggestions?

jverly01

I think I got it figured out. Will update with results over the weekend. Thanks again for the help.

kclark

Glad you got it working, let us know if you run into anything else!

jverly01

FIXED - This is how I got the grand total I was looking for to work out. This is an extension of the report that John provided earlier in the message stream (thanks again for the start). There were basically 4 steps to get the aggregate grand total to work. 
 
1. Insert a new aggregate in the footer of the report. The aggregate does not actually get applied to a field. In the expression builder, just enter a value like 1.00. A javascript will be used to overwrite the aggregate value later on. 
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/insert_grandtotal_aggr.jpg' alt='Posted Image' class='bbc_img' /> 
 
2. Add a new variable for the overall grand total. So on the outline, click on the report name and add a new variable via the script tab on the Initialize method. 
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/add_variable.jpg' alt='Posted Image' class='bbc_img' /> 
 
3. On the subdata aggregate field, in this case the the SumTotalPrice field highlighted in yellow as shown in step 1, add a new javascript function to summarize the field's value on the script tab on the onCreate method. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>grandtotal += parseFloat(this.getValue());</pre>
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/add_js_summary.jpg' alt='Posted Image' class='bbc_img' /> 
 
4. Then on the aggregate in the footer, add javascript to show the overall value on the onRender method. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.setDisplayValue(grandtotal);</pre>
 
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/grand_total_value_js.jpg' alt='Posted Image' class='bbc_img' /> 
 
Attached is an updated version of the report John originally provided for full details.