Compare scores from a data set and only report the latest score that is not null

Lazarus33

Hey all!

I'm a fairly inexperienced BIRT Report Designer and need some help doing something I think may be simple, yet I can't get anything I write to work. I have a series of scores being brought into a flat XML Data Set that my .rptdesign file is then looking at. However, what I really need to do is have BIRT cycle through these scores and only give me the latest score that is not null.

For example, I have the following scores being brought into a flat XML Data Set file as strings:

<Score-1> 12 </Score-1>
<Score-2> 5 </Score-2>
<Score-3> 3 </Score-3>
<Score-4> </Score-4>
<Score-5> </Score-5>

In this case, I would want it to skip Scores 4 & 5 because they are null, and return me the highest score that has an actual value - which would be Score-3 in this case. I have a table set up and would like to only bring in <Score-3> so that the table would show in that particular cell - "3" which is the value of Score-3. Does that make sense?

The logic I'd like to incorporate is this, basically:

IF <Score-5> is not null, then <Score-5>. ELSE if <Score-4> is not null, then <Score-4>. ELSE, etc....

I'v tried this as a computed column which I can then take the result of and drop into that cell in the table. I just don't know how to properly form up that logic into code for the computed column. Am I just trying something weird or can anyone provide any help or insight on this? I'd be glad to post data set and report files if that would help. Thanks so much in advance for any help you can provide!

-Lazarus33

mwilliams

Lazarus33,

So, with the XML above, your dataSet would return:

value
12
5
3
null
null

and you want to grab the value 3 from this?

If so, in your initialize script, you could initialize a variable, then in your computed column, you could set the current value to your variable and pass the current value through to the computed column. Then, if the current value is null, you could pass through the stored variable value instead and not change it's value to the current, Null, value. Then, in your report you could use the aggregation "LAST" to grab the last actual value. You could also do something similar to this in your onFetch method of your dataSet and avoid the extra column. You'd just store the value in a variable for use in your report when needed. Let me know if you have questions or if this doesn't help.