nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Parent Child Summations and roll up of dates

Dhanya

Hi,

I have capability form in RTC acting as a parent record with Action getting attached as the children.
From such a relation, I was able to display the parent and child details in the below capability.rptdesign.

Now I need to get the below information at parent level.

1) summation of actual efforts
2) Get the lowest Start date and the highest end date

Eg: parent-wi-id child-wi-id child-actual-efforts child-start-date child-end-date
75 76 20 11/01/2011 14/01/2011
80 02 12/01/2011 16/01/2011
82 05 10/01/2011 15/01/2011

The information that I want to be displayed at the parent level
Total-Actual-efforts start-date end-date
238 10/01/2011 16/01/2011

Similarly at each parent I require such displays.

What I am looking for is javascripting which make use of the beforeOpen, onFetch methods.
I got a scripting done for the summation of actual hours. But I am not able to reset the variable once the summation for a parent is done.The capability.rptdesign contains the scripting for that. But the variable doing the summation is not getting reset even after I assigned a value of 0 onFetch method of the parent. So the variable is having the running sum for the entire childred irrespective of the parent.

Please let me know if you need any further information to decode this request
Thanks
Dhanya

Find more posts tagged with

Comments

mwilliams

You'll probably need to do your calculations in the onCreate of the inner tables and use the onRender event of the element in the outer table to set its value. You may need to use PGVs to do this.

Dhanya

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="76008" data-time="1303142863" data-date="18 April 2011 - 09:07 AM">
You'll probably need to do your calculations in the onCreate of the inner tables and use the onRender event of the element in the outer table to set its value. You may need to use PGVs to do this. </blockquote>
Hi, 
 
Thank you. 
I got the variable reset in the onRender event. 
 
I didn't get what is the meaning of PGV. 
 
Another question that I have is as of now I am displaying my date as a text item. 
But that has a very few formatting option. I want the format of mm/dd/yyyy. 
What the text can give me is mm/dd/yy (there are other formats but this is the closest). 
So is there an option to get the mm/dd/yyyy format? 
 
Dhanya

mwilliams

PGV = persistentGlobalVariable, sorry.

If you're displaying your date in a text element, you're probably using the <value-of> tag. If this is the case, you can use <value-of format=...> to set the format of the returned value. Hope this helps.

Dhanya

Thank you Micheal.

I got most of simple summations and an idea on comparing dates.
But I have new issue. I am doing aggregation on child table to get some dates using conditions so that from the timestamp_val fields i will get the custom date fields. I got all the custom dates. And did aggregation to get allt he required fields in the Group header. If I need to do summation on the timespent (which is now aggregated on first funtion) how should i refer that aggregated field.

totalHour += row["Aggregation"] --> this is not working.
Similarly I was using the below logic to find the minimum date and maximum date.

beforeOpen method of parent

counter = 1;
var maxDate = Date();
var minDate = Date();

OnFetch method of Child

if (counter == 1) {
maxDate = row["TIMESTAMP_VAL"];
} else
if (row["TIMESTAMP_VAL"] > maxDate ) {
maxDate = row["TIMESTAMP_VAL"];
}
if (counter == 1) {
minDate = row["TIMESTAMP_VAL"];
} else
if (row["TIMESTAMP_VAL"] < maxDate ) {
minDate = row["TIMESTAMP_VAL"];
}
counter ++;

Initializing these variables 'onRender' method.
This logic used to fetch the maximum date from the child at the parent level.
Similarly the minimum date also.

But When I want to compare the dates in the Group Header, how should I refer those date fields?

Thanks in advance,

Dhanya

mwilliams

I'm not sure I'm understanding correctly. Can you post your report design? What would be best is if you could also attach a file of data so that I can run the report and see exactly what you're doing. Or, just recreate this issue with the sample database.

Dhanya

Michael,

I find it difficult to post the required attachments.
So I will explain the scenario.Was able to attach the report design.
The basic intention of the report is to get the summation of total timespent and the minimum start date and maximum start dates from the child Actions to their parent level.

The child Action has a sample data as below.
These are the rows for the Action item 591. Each child action has 5 rows due to the custom date fields.

WI_ID SUMMARY TIME_SPENT TIMESTAMP_KEY TIMESTAMP_VAL DUE_DATE
591 Review of FS and CDF 16 com.ibm.ams.gbs.actual.end.date Mar 18, 2011 12:00 PM Mar 15, 2011 12:00 PM
591 Review of FS and CDF 16 com.ibm.ams.gbs.actual.start.date Mar 3, 2011 12:00 PM Mar 15, 2011 12:00 PM
591 Review of FS and CDF 16 com.ibm.ams.gbs.planned.start.date Mar 1, 2011 12:00 PM Mar 15, 2011 12:00 PM
591 Review of FS and CDF 16 com.ibm.ams.gbs.replanned.end.date Mar 17, 2011 12:00 PM Mar 15, 2011 12:00 PM
591 Review of FS and CDF 16 com.ibm.ams.gbs.replanned.start.date Mar 2, 2011 12:00 PM Mar 15, 2011 12:00 PM

Since I want the Planned start date, Actual start date and Actual end date at the Work item level,
I have grouped the child Actions on Work item id and aggregated the timestamp values to get the dates.
The below are my aggregations

Aggregation - Review of FS and CDF
Aggregation_1 - 16
Aggregation_2 - Mar 1, 2011 12:00 PM
Aggregation_3 - Mar 15, 2011 12:00 PM
Aggregation_4 - Mar 3, 2011 12:00 PM
Aggregation_5 - Mar 18, 2011 12:00 PM

Now since I want to aggregate the timespent for each child action of the parent, I should take the timespent value from the Group header. Here if I am adding row["TIME_SPENT"] to my variable, then it would give me a value of 16*5 for timespent for this child as there are 5 rows and each row has the timespent as 16.
So the correct method should be to add the row["Aggregation_1"]. But this way I am not able to add. Even I should get the dates from the Aggregated ones rite?
How do I refer these aggregated values in my script for onFetch, beforeOpen methods?

Thanks
Dhanya

Dhanya

Hi All,

I was able to resolve the below issue with some scripting

Instead of using aggregation I put some counters, and flags.
And used some simple scripting to get the summation of hours from the children.
If anyone having this issue, I can pass the scripts.

Thanks
Dhanya

mwilliams

Sorry for the delayed response. Glad you got your issue solved. Let us know whenever you have questions!

stevec

<blockquote class="ipsBlockquote" data-author="Dhanya" data-cid="76287" data-time="1303898612">
<div>
Hi All, I was able to resolve the below issue with some scripting

Instead of using aggregation I put some counters, and flags. And used some simple scripting to get the summation of hours from the children. If anyone having this issue, I can pass the scripts. Thanks Dhanya
</div>
</blockquote>
I would like the script please
 
Thanks