Trying to get grand total from subtable aggregates
Options
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.<br />
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/subtotal_aggr.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
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?
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/subtotal_aggr.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
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?
0
Comments
-
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.Warning No formatter is installed for the format ipb0
-
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. <br />
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/rptdatasetlayout.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
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. <br />
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/subdata_aggrsum.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
The sub data aggregate uses a script on the onCreate method <br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>importPackage(Packages.java.lang);
var lastValue = this.getValue();
reportContext.setPersistentGlobalVariable("tot_workhours", new Float(lastValue));</pre>
<br />
So now I want to get a grand total of each sub data table aggregate totals.0 -
Thanks for the example report. My issue is I want to to now have a grand total all grouped "currentAggregationValue" totals - see attached. <br />
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/examplerptgrandtotal.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
Any suggestions?0 -
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.<br />
<br />
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.<br />
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/insert_grandtotal_aggr.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
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.<br />
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/add_variable.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
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.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>grandtotal += parseFloat(this.getValue());</pre>
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/add_js_summary.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
4. Then on the aggregate in the footer, add javascript to show the overall value on the onRender method.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>this.setDisplayValue(grandtotal);</pre>
<br />
<img src='http://theothersideof.mygeekdaddy.net/wp-content/uploads/2012/11/grand_total_value_js.jpg' alt='Posted Image' class='bbc_img' /><br />
<br />
Attached is an updated version of the report John originally provided for full details.0
Categories
- All Categories
- 109 Developer Announcements
- 49 Articles
- 100 General Questions
- 122 IM Services
- 40 OpenText Hackathon
- 31 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 897 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 123 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management