Aggregation in BIRT - need to sum aggrevated values (total1+total2) coming from two different Datase

fatriz04 · 2016-02-18T11:21:34+00:00

<div>Hi Everyone,</div><br/> <div> </div><br/> <div>I need a very Urgent help in Birt 232 where I have to sum two aggregated values calculated on two different datasets.</div><br/> <div> </div><br/> <div>I have a attached the report viewer and rptdesign for your reference.</div><br/> <div>I have created a table with…

fatriz04

<div>Hi Everyone,</div>
<div> </div>
<div>I need a very Urgent help in Birt 232 where I have to sum two aggregated values calculated on two different datasets.</div>
<div> </div>
<div>I have a attached the report viewer and rptdesign for your reference.</div>
<div>I have created a table with maindataset binding. I have two other datasets displaying items and labour lines.</div>
<div>Inside this main table , in the detail row I have created three tables each one displaying three datasets data. First table displaying maindataset query , second displaying item lines , third displaying labor lines.</div>
<div>In the second I have put aggregation of total item cost (4125.2)and one more aggregation on total labor cost(450).</div>
<div>Now my question is How do i ADD this two aggregated values which comes from two different datasets??</div>
<div> </div>
<div>I have highlighted in yellow these two values in the attached report viewer which needs to be added and displayed at the bottom as 4575.25</div>
<div> </div>

Find more posts tagged with

Comments

Clement Wong

<p>This should work in BIRT 2.3.2, but I am not able to verify. I have a sample (attached) but it was written in BIRT 4.5.0.</p>
<p> </p>
<p>You can have a variable that stores the total of the two aggregations, and then use a Data Report Item to display that total. (You can use Text or Dynamic Text Report Item as well.)</p>
<p> </p>
<p>First, in the "sumtoitemcost", go to Script > onCreate event, add:</p>
<p> </p>
<p>myTotal = Number(this.getValue());</p>
<p> </p>
<p>Then, in the "TotBLcost", go to Script > onCreate event, add:</p>
<p> </p>
<p>myTotal = myTotal + Number(this.getValue());</p>
<p> </p>
<p> </p>
<p>Finally, add a Data Report Item. Data Type is "Decimal" and Expression is "myTotal". Format to right justified, and currency with 2 digits and 1,000 separator, and you're good to go.</p>
<p> </p>
<p> </p>
<p> </p>

fatriz04

<p>Hi Clement Wong,</p>
<p> </p>
<p>Thanks for your immediate response.</p>
<p> </p>
<p>I tried the same what have you told me to do. I have created myTotal in Oncreate method of <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">sumtoitemcost and </span><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">TotBLcost.</span></p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">I have put a data reportitem and expression as "</span><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">myTotal" and type as "Decimal"</span></p>
<p> </p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">But when i run the report , I am getting the below error :</span></p>
<p><strong> Data (id = 3831): </strong></p>
<div><strong>+ A BIRT exception occurred: Can not convert the value of myTotal to BigDecimal type.. See next exception for more information.</strong></div>
<div><strong>Can not convert the value of myTotal to BigDecimal type. </strong></div>
<div> </div>
<div>How to fix this error?..Kindly help me to fix this issue plz</div>
<div>  </div>
<div>This is what my report looks like below:</div>
<div> </div>
<div>
<div>MainDataSet #1 -<span style="color:#000000;"> <strong>WarrantyNo123</strong></span></div>
<div>header details </div>
<div> <span> </span>DataSet #2 - item lines details</div>
<div> col1 | col 2 | Total</div>
<div> <span> </span>   x | x | 10</div>
<div> <span> </span>   x | x | 10</div>
<div> total item line cost - 20</div>
<div> DataSet #3 - labor line details</div>
<div> col1 | col 2 | Total</div>
<div> <span> </span> x | x | 30</div>
<div> <span> </span> x | x | 40</div>
<div> total labor line cost - 70 <span> </span></div>
<div><strong> total claim Amt - 70+20 =90</strong></div>
<div>MainDataSet #2 - <strong>WarrantyNo234</strong></div>
<div>header details </div>
<div> DataSet #2 - item lines details</div>
<div> col1 | col 2 | Total</div>
<div> <span> </span>   x | x | 40</div>
<div> <span> </span>   x | x | 10</div>
<div> <span> </span> total item line cost - 50</div>
<div>DataSet #3 - labor line details</div>
<div> col1 | col 2 | Total</div>
<div> <span> </span> x | x | 40</div>
<div> <span> </span> x | x | 40</div>
<div> <span> </span> total labor line cost - 80<span> </span></div>
<div><strong> total claim Amt - 50+80 =130</strong></div>
<div>

</div>
<div><strong> Grand Total Claim Amt - 90+130 = 220</strong></div>
<div>

</div>
<div>keeps repeating for multiple warrantynos</div>
<div>and at last prints for grand total.</div>
<div> </div>
<div>Thanks!</div>
</div>

shamo

<p>Another way to do this if the script isn't working for you is this. it doesn't involve script. hope it gives you another perspective.</p>

Clement Wong

<p>Without being able to run your report and BIRT 2.3.2, I'm unable to debug your error. As a test, can you try changing the Data Type in the myTotal Report Item to "Float" instead of "Decimal"?</p>
<p> </p>
<p><strong>shamo </strong>presents an easier alternative without scripting and should work in BIRT 2.3.2. Please try that out too.</p>

fatriz04

<p>Hi Shamo,</p>
<p> </p>
<p>Thanks for your response!.. Kindly explain me how to do it in my scenario and </p>
<p>I m not able to open your atteached aggretest.rptdesign in my BIRT232 it says "The report file of version "3.2.23" is invlaid for it is greater than the latest supported one".</p>
<p> </p>
<p>Kindly explain me how to do it step by step. Because just now I started working on BIRT.Kindly help</p>
<p> </p>
<p>Hi Clemant,</p>
<p> </p>
<p>Thanks for your reply. But I canged the datatye to float , still I am getting the same Error.</p>
<p> </p>
<p> </p>
<p>Thanks.</p>

shamo

<p>This is basically what I did</p>