Running sum / running difference

Hi All,
I am trying to figure out how to get a data item to do a running difference.
Users input a continuous meter reading value in the database and i need to display the actual meter consumption.
e.i. for each month the would typically enter 4 or 5 readings
(10,15,19,26,30 for week 1, 2 ...) what i am required to show in the report is the actual weekly consumption (10, 5,4,7,4).

I saw the option for running sum when playing around with a data item and was wondering how i could get it to give me a running difference

thanks in advance

Find more posts tagged with

Comments

mwilliams

Hi magic_bern,

You should be able to create a computed column using a variable called "temp" or something to store the last week's reading and track this yourself. If you have any questions, post exactly how your data looks in here and I'll make a quick example.

magic_bern

Below is my open method and it return either for 4 or 5 values that were entered as reading for that month 
date | Reading | actual consumption 
01/09/09 |81 | 
07/09/09 |82 | 
14/09/09 |82 | 
25/09/09 |84 | 
30/09/09 |83 | 
 
and i basically need the third actual consumption colum 
 
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); 
maximoDataSet.open(); 
 
var sqlText = new String(); 
 
var site = params["SiteName"]; 
 
// If the site variable starts with an "=" sign 
if(site.substring(0,1) == "=") { 
// Change site to remove the "=" sign 
site = site.substring(1); 
} 
else { } 
 
sqlText = "select readingdate, reading " 
+ "from meterreading " 
+ "where metername = 'mainswater' and readingdate >= " 
+ MXReportSqlFormat.getStartDayTimestampFunction(params["StartDate"]) 
+ " and readingdate <= " 
+ MXReportSqlFormat.getEndDayTimestampFunction(params["EndDate"]) 
+ " and siteid = '" + site + "'" 
+ " order by readingdate asc" 
; 
 
maximoDataSet.setQuery(sqlText);

mwilliams

magic_bern,

All you'll need to do is create a variable and set it to 0 in your beforeOpen script of your dataSet. Then, in your dataSet, you compare your reading to that value to display as the consumption. Then, you set the temp variable you're using to the new reading value so that it holds over to the next loop through to compare the next reading value to.

Hope this helps.

magic_bern

Hi Michael,
this seem to make some sense and follow a logic but it kind of just went over my head...But i guess it's because i dont have much programming experience. the good news is that i have an intensive 2 weeks C++ course from 2 weeks so i'll get back to you after that to hopefully say yeah i got it to work..

Thanks for help

mwilliams

magic_bern,

Ok, just let me know if you need any help or if you have any other questions.

magic_bern

Hi MWilliams,
i have been trying to complete my computed column that return the consumption but i am unable to assign the new row to my temp variable. i keep getting either 0, the same reading or the difference i have is between the reading and the temp value instead of being the difference between consecutive reading.

how do i set the temp variable to the new reading?
thanks in advance.

magic_bern

Hi MWilliams, 
After a series of trial and error, I finally manage to get the running difference I have been after. 
i created 2 variable in the beforeOpen method, temp and consumption and assign them both 0; 
and here is the code i added in the data element: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
{
//first reading consumption is 0
if (temp==0){
temp = dataSetRow["reading"]- dataSetRow["reading"];
consumption=temp;
temp = dataSetRow["reading"];
}
else{
temp = dataSetRow["reading"]- temp;
consumption=temp;
temp = dataSetRow["reading"];
}
}
consumption;
</pre>
long winded i guess but it gave me what i was looking for. 
Thank again for the help

mwilliams

Sorry for the delay in response. Glad you got it working! Let us know whenever you have questions!

magic_bern

Hi MWilliams,
now i have another issue while using that calculated field to create a line graph or a scatter graph.
for some reason, the first reading is always strange (a random number which i dont understand)
i have attached an example to explain what i mean) according to the data i am trying to plot, the first reading should be zero, but it is not for some reason.

Thanks

mwilliams

What's your BIRT version?

magic_bern

version="3.2.17"
thanks

mwilliams

Put your expression for the y-series value in the chart and run it in the web viewer, it works then.

magic_bern

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="72197" data-time="1294856762" data-date="12 January 2011 - 11:26 AM">
Put your expression for the y-series value in the chart and run it in the web viewer, it works then. </blockquote>
 
I am not sure if i understand what you mean? The graph does work for me the problem i have is with the first value (-45) it is supposed to be 0 and i dont know why it is not or where the -45 comes from. 
Thanks

mwilliams

Rather than using your computed data binding as your y-axis field, use the same expression as you used for your y-axis field in your chart.