Using BirtComp.anyOf

cypherdj

Hi there,

I am building a report which provides a count of new, closed and re-opened records over a given period.

I have a data set made of the following info:
- recordID,
- recordType,
- creationDate,
- historyID,
- historyDate,
- historyDesc

The first 3 elements are from a record table, the last 3 from a history table.

On this data set, I have added 2 computed columns:
newRecordInd: Boolean -> BirtComp.between(row["creationDate"], param["start"], param["end"])
closedRecordInd: Boolean -> BirtComp.between(row["historyDate"], param["start"], param["end"]) && BirtComp.anyOf(row["historyDesc"], "Closed", ...)

Now I have 2 questions:
1. rather than hardcoding the possible values for BirtComp.anyOf in the computed column expression, I'd like to make this somewhat more dynamic. Is this possible? How would one implement that (another data set? hidden parameter? other?)

2. while the 2 indicators rely solely on the data for the inspected row, the last indicator (re-opened records) would need to consider any of the previous history entries for the given record. Is there any way of implementing this (maybe using 2 data sets and a joint data set)? I can't quite figure out a way of performing this using SQL, but if it's not possible to do with Birt, there might be a way to modify the query to obtain the same result.

I'm currently using Birt 2.2.2, and probably would not consider upgrading the 2.3 until our next release.

Thanks for any assistance with this query,
Cedric

cypherdj

Ok, I got past my SQL query issue and found a way of getting my data in almost the shape I need.<br />
<br />
I've now added an indicator column to the data set, which is a String 'New', 'Closed', 'Reopened'.<br />
<br />
Some of these records may be duplicated due to the way the history table is set up.<br />
<br />
So I've created a data cube with recordType and yearMonth groups, and defined 3 measures:<br />
- newRecords : distinctcount with expr<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (rowDataSet["indicator"].equals("New"))
rowDataSet["recordID"]
</pre>
- closedRecords: distinctcount with equals("Closed") in prev expr,<br />
- reopenedRecords: distinctcount with equals("Reopened") in prev expr.<br />
<br />
I then produce a crosstab based on the given data cube.<br />
<br />
However, the figures in the crosstab don't seem to match the queried data!<br />
<br />
I've attached a sample of data, for which I would expect the crosstab to show:<br />
Reopened Closed New <br />
0 0 6 Accident Claim<br />
0 0 7 Liability Case<br />
0 0 6 3rd Party Injury Notification<br />
1 1 1 Insurance Claim<br />
<br />
but I'm getting:<br />
Reopened Closed New <br />
1 1 6 Accident Claim<br />
1 1 7 Liability Case<br />
1 1 6 3rd Party Injury Notification<br />
2 2 1 Insurance Claim<br />
<br />
It seems to add an extra reopened and an extra closed, but I can't figure out where it's coming from.<br />
<br />
Any ideas?<br />
Cedric