RunningSum in Charts

Hi,

I'm trying to create a progress line with "number of passed test cases (y axis) up until a certain week (x axis)".

A short summary of interesting fields in my DB:
Filter: Only show test cases that are "PASSED"
Num (Int) - constant 1
Week (Int) - 12-22 depending on when the test was executed

This is what I want to accomplish:
A progress line with week (preferably a fixed value range 8, 9, 10, 11, 12, 13...) as X-axis and "progress" (sum of Num up to the current week (x-value)) as Y-axis. I also want a second static curve to represent a "target" to see if the progress is one track or not (right now this is solved by a computed field with fixed values, maybe there 's a better solution?).

I've run into two problems:
1) I can't get the runningSum to work properly. I've tried to create a computed field in the DataSet with "runningSum up until the current week" but failed. Never got the filter / counting right with runningSum so all fields were counted leaving me with a constant "90" (total number of passed test cases) no matter what week. I've also tried some settings in the chart but without any result.

* So how do I create a runningSum (because I guess this is what I'm looking for) that sums the number of passed testcases (Num) for every value from the start to "now".

2) One thing that has been problematic from the start for me is to get a "value range" as X-axis instead of destinct values. In my example I want to show a value range from 12-22 and for every value show the current number of passed testcases up until that week. Right now I'm stuck with showing only weeks when something actually happened. For example, w13 happened nothing to the progress this hides that week from my chart. Week 16 and forward is also a head of use so ofcause no progress has been done these weeks yet, still want to show them though.

* So how do I show a value range instead of destinct values for a certain DB-field as X-axis and still accomplish what 's been described above.

Sorry if all this is confusing and basic but I can't get this to work.

I'm currently stuck with 2.2.1 since I'm using Clear Quest which may be a problem (read about som bug in runningSum).

Thanks a lot for any help I can get!

BR,
Erik

Find more posts tagged with

Comments

bhanley

In the case of Number 1, you can set up a running sum when you bind the data to your chart. When you drag the value field onto the axis, look for the aggregation button (it has a Sigma on it) right next to the text box displaying the filed name you dragged over. In that drop-down you can select "Running Sum" as the aggregation. I would send you a sample, but I do not have BIRT 2.2.1 installed to give you a sample on the right version.

I think I am going to need a bit more detail to give you some insight on number 2.

brickuz

Hmm, I was looking for runningsum in that menu earlier but it just wasn't there (may be due to 2.2.1?). Tried a manual version with "Total.runningSum(row["Num"])" but it did not work .(

Any other way around this when runningSum isn't available in the chart's aggregation menu (I've got the Data Binding button down to the right if that helps though)

Thanks a lot for taking your time bhanley!

bhanley

It could well be the 2.2.1 thing. In my 2.2.3 install it is right there. 
 
I had another thought. Why not build a table using your data to power the chart? It would have 3 columns (at least). A column for the X-Axis, one or more columns for Y-Axis (series) and lastly a column for the aggregation. In the aggregation column, add a Dynamic Text field that will aggregate your running sum using "Total.RunningSum(row["...Your Field..."])". Next, add a header row to the table, merge all the cells in the header row together. Then add a line chart inside the header row of the table. When it comes time to bind the chart to data, select "Inherit from Container". This will let your chart use the data in the table, including your aggregate field. 
 
I wish you had 2.3.2, I just built a working sample to prove this strategy out!

Give this a try and let me know how things look. 
 
Good Luck!

brickuz

Well, the good news is that problem one is solved, the "bad news" is that it was solved all along. The problem was not Birt it was the people providing me with data, they used the system incorrect so my timestamps (weeks) failed and therefor it looked like it summed up everything and not just the relevant data but the answer was that everything was correct but I only had PASSED testcases in the first week (if you got all that I'm impressed .)

Anyways... I'll come back with a better explaination of my second problem but the short version is:

Let's say I have this variable as X-axis:

Week : Sum
3 : 1
3 : 1
3 : 1
4 : 1
6 : 1
6 : 1

Normally I would add Week to X-axis and show "destinct value" with interval 0 and it will render a chart with the X-values 3, 4 and 6 (without 5). Just realized though that maybe I shouldn't use value 0 and instead use 1 .) I have a general tendency to solve advanced problems but overlook the obvious solutions to the more simple ones.

Still want a longer range though just to keep the layout identicle from week to week so I want to print out w16 and forward even though no data are available for those weeks (yet) is this somehow possible?