Ceck how often a specific string occurs

Hello

I want to check a table row field with a specific string, how often it occurs

Here a sample of the table output

10:00 App1 NotOK
11:00 App1 NotOK
12:00 App1 OK
13:00 App1 OK
14:00 App1 OK
15:00 App1 OK
10:00 App2 OK
11:00 App2 NotOK
12:00 App2 NotOK
13:00 App2 OK
14:00 App2 NotOK
15:00 App2 OK

If you can see there is a grouping of the App column and the records sorted by time.
The chalange is, if you look at App2, there are 6 checks in ascending time sort. Each check has an interval of 1 hour. So App2 was 2 times not OK, One time more than 1 hour and one time less than one hour

Do you have a glue how I can realize this to get the result that App1 was 1 time not ok and App2 was two times not ok ?

Thanks for your help
Peter

Find more posts tagged with

Comments

thuston

You'd have to sort by App then time.
Then in script, store the previous row value.
Compare the new row with the previous and only count "Not OK" if it's the first value or the previous value was "OK"
You'll want to end up a HashMap value for each [App] that you can access in your controls.

Depending upon your desired use, you may need to have two tables so you can pre-read the data, then actually display it in the second table (the first would be set to not visible).

peterbauer

Thank you for your reply.

The thing which is not clear for me is, how can I store a field value in an iterating table and compare it with the next one.

I have tested it with integers to count if on state is NotOK but the outpust is everytime 1

in the group header i have "var count = 0;"
in the detailed row i have "if (row[state] == "NotOK"){ count = count + 1; } count;

the problem here is that I have always a count of one. it seems the variable will not be stored when iterating through the detailed rows

Do you have any ideas ?

johnw

It sounds like you want to count changes in state.<br />
<br />
My suggestion is to use a computed column on your data set, and a COUNT aggregation in your expression.<br />
<br />
The basic idea is that you use a report global variable to store your last check in (you can initialize this on report initialization, or leave it null and set it on first use). Then, in your computed column expression, you would retrieve that variable, which stores the last state, check if it matches the current state. If it does, great, no change and set the computed column to false. If it doesn't, check if the last state was an OK. If it is, you want to count that as an event, and you set your computed columns value to true. If it isn't, then you set it to false. <br />
<br />
Then in your table, you add an aggregation, and set the filter expression to the state change computed column. <br />
<br />
I have attached an example. Since I had to use a scripted data source, the code that goes in the computed column is actually in the Fetch event in the data set, and I have commented it to indicate what you will use there. I also initialized the global variable in the reports initialize event.<br />
<br />
Sorry, had to do this as XML, the attach option is not working.<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.6.1.v20100902 Build <2.6.1.v20100915-1750></property>
<property name="units">in</property>
<method name="initialize"><![CDATA[reportContext.setGlobalVariable("lastState", "OK");]]></method>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<property name="imageDPI">96</property>
<data-sources>
<script-data-source name="Data Source" id="7"/>
</data-sources>
<data-sets>
<script-data-set name="Data Set" id="8">
<list-property name="resultSetHints">
<structure>
<property name="position">1</property>
<property name="name">time</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">app</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">state</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">stateChanged</property>
<property name="dataType">boolean</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">time</property>
</structure>
<structure>
<property name="columnName">app</property>
</structure>
<structure>
<property name="columnName">state</property>
</structure>
<structure>
<property name="columnName">stateChanged</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">time</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">app</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">state</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">stateChanged</property>
<property name="dataType">boolean</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<method name="open"><![CDATA[time = 10;
app = "App1";
state = "NotOK";
cnt = 0;]]></method>
<method name="fetch"><![CDATA[if (cnt < 12)
{
row["time"] = time;
row["app"] = app;
row["state"] = state;

if (cnt == 5)
{
app = "App2";
time = 9;
}

if (cnt == 1)
{
state = "OK";
}

if (cnt == 6)
{
state = "NotOK";
}

if (cnt == 8)
{
state = "OK";
}

if (cnt == 9)
{
state = "NotOK";
}

if (cnt == 10)
{
state = "OK"
}

time++;
cnt++;

//This is a computed column expression below. Use this in a computed column expression
var lastState = reportContext.getGlobalVariable("lastState");

if (lastState == null)
{
row["stateChanged"] = false;
reportContext.setGlobalVariable("lastState", row["state"]);
}
else
{
if (lastState.equals(row["state"]))
{
row["stateChanged"] = false;
}
else
{
if (lastState == "OK")
row["stateChanged"] = true;
else
row["stateChanged"] = false;
}

reportContext.setGlobalVariable("lastState", row["state"]);
}
//End Computed Column Expression

return (true);
}

return (false);]]></method>
</script-data-set>
</data-sets>
<styles>
<style name="report" id="4">
<property name="fontFamily">sans-serif</property>
<property name="fontSize">10pt</property>
</style>
<style name="crosstab" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab-cell" id="6">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table id="9">
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">time</property>
<text-property name="displayName">time</text-property>
<expression name="expression" type="javascript">dataSetRow["time"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">app</property>
<text-property name="displayName">app</text-property>
<expression name="expression" type="javascript">dataSetRow["app"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">state</property>
<text-property name="displayName">state</text-property>
<expression name="expression" type="javascript">dataSetRow["state"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CountStateChanges</property>
<property name="dataType">integer</property>
<simple-property-list name="aggregateOn">
<value>groupOnApp</value>
</simple-property-list>
<property name="aggregateFunction">COUNT</property>
<list-property name="arguments">
<structure>
<property name="name">Expression</property>
</structure>
</list-property>
<expression name="filterExpr" type="javascript">row["stateChanged"]</expression>
</structure>
<structure>
<property name="name">stateChanged</property>
<text-property name="displayName">stateChanged</text-property>
<expression name="expression" type="javascript">dataSetRow["stateChanged"]</expression>
<property name="dataType">boolean</property>
</structure>
</list-property>
<column id="28"/>
<column id="30"/>
<column id="48"/>
<header>
<row id="10">
<cell id="11">
<label id="12">
<text-property name="text">time</text-property>
</label>
</cell>
<cell id="15">
<label id="16">
<text-property name="text">state</text-property>
</label>
</cell>
<cell id="43">
<label id="52">
<text-property name="text">stateChanged</text-property>
</label>
</cell>
</row>
</header>
<group id="31">
<property name="groupName">groupOnApp</property>
<property name="interval">none</property>
<property name="sortDirection">asc</property>
<expression name="keyExpr" type="javascript">row["app"]</expression>
<structure name="toc">
<expression name="expressionValue" type="javascript">row["app"]</expression>
</structure>
<property name="repeatHeader">true</property>
<property name="hideDetail">false</property>
<property name="pageBreakAfter">auto</property>
<property name="pageBreakBefore">auto</property>
<property name="pageBreakInside">auto</property>
<header>
<row id="32">
<cell id="33">
<data id="40">
<property name="resultSetColumn">app</property>
</data>
</cell>
<cell id="35"/>
<cell id="44"/>
</row>
</header>
<footer>
<row id="36">
<cell id="37">
<label id="41">
<text-property name="text">Total State Changes:</text-property>
</label>
</cell>
<cell id="39">
<data id="42">
<property name="resultSetColumn">CountStateChanges</property>
</data>
</cell>
<cell id="46"/>
</row>
</footer>
</group>
<detail>
<row id="17">
<cell id="18">
<data id="19">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">time</property>
</data>
</cell>
<cell id="22">
<data id="23">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">state</property>
</data>
</cell>
<cell id="45">
<data id="53">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">stateChanged</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="24">
<cell id="25"/>
<cell id="27"/>
<cell id="47"/>
</row>
</footer>
</table>
</body>
</report>

</pre>

johnw

Got the attachment to work that time