nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Only display unique (average) rows?

Macamba

Hi, 
 
Still trying to get a grip on Birt. So obvious problem follows. 
 
Short version: 
The report needs to display the average downtime of Priorities 1 and 2 in the last month, and last 12 months per system. I get per record of a system in the database the average downtime for priorities 1 and 2 in the last month, and the last 12 months. How do I get my output so it displays per system the downtime per month, and for the last 12 months for priority 1 and 2? 
 
Background: 
The database I use to report on contains downtimes of systems that are managed. A downtime is called an incident. During a month a system can go down during a number of minutes. A down time can have a certain priority. For each system I want to display the average downtime for Priorities 1 and 2 for the previous month, and previous 12 months. 
 
In the report I build, I first dragged system_name to my table. Next I added a row below the row containing system_name, and inserted a group, which grouped on organisation_name. In the others cells I added aggregations on 'Prio 1 month', 'Prio 1 year', 'Prio 2 month' and 'Prio 2 year'. 
 
The result I got is: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Prio1Month Prio1Year Prio2Month Prio2Year
System 01 56,50 3.270,43 7.902,75
System 01 56,50 3.270,43 7.902,75
..
System 01 56,50 3.270,43 7.902,75
System 02 79,25 28,00
..
System 02 79,25 28,00
System 03 38.819,00 2.972,73 25.923,00 11.537,67
..
System 03 38.819,00 2.972,73 25.923,00 11.537,67
</pre>
 
The result I wanted to have was: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Prio1Month Prio1Year Prio2Month Prio2Year
System 01 56,50 3.270,43 7.902,75
System 02 79,25 28,00
System 03 38.819,00 2.972,73 25.923,00 11.537,67
..
</pre>
 
How do I get from the result I got to the result I expected? 
 
Macamba

Find more posts tagged with

Comments

tmseidel

Hi, 
 
1. Add for each Prio a computed column. 
2. Define in your dataset Collections to store the already calculated values. 
3. in your calculated column, add a script which stores the column of the corresponding "real column" to the collection. Something like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (collectionPrio1Month.contains(row["Prio1Month"])) {
null;
} else {
collectionPrio1Month.add(row["Prio1Month"]);
row["Prio1Month"];
}
</pre>
 
The result would be something like that: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Prio1Month Prio1Year Prio2Month Prio2Year Prio1MonthCalculated
System 01 56,50 3.270,43 7.902,75 56,50
System 01 56,50 3.270,43 7.902,75
</pre>
 
Now you can apply a filter to the computed column for non-null values.

Macamba

Hi,

Thanks for your reply, and the time it took to answer. In the end I found it out myself. My method is quite cumbersome, yours has a nice slick feeling to it. So I might try to use it myself.

Macamba

Macamba

<blockquote class='ipsBlockquote' data-author="'tmseidel'" data-cid="67886" data-time="1283159733" data-date="30 August 2010 - 02:15 AM">
1. Add for each Prio a computed column. 
2. Define in your dataset Collections to store the already calculated values. 
3. in your calculated column, add a script which stores the column of the corresponding "real column" to the collection. <SNIP> 
 
Now you can apply a filter to the computed column for non-null values. </blockquote>
 
<ul class='bbcol decimal'><li>Added in my Data Set. They are called P1M (Priority 1 Month), P1Y (Priority 1 Year), P2M and P2Y.</li><li>Collections? Do I add fields in my query. I had to comment them out as I could not preview my results. </li><li>I take it you mean the calculated column in the 'Report Editor'? Do I select the table, the row, or the cell element? When does the script needs to execute? Initialize, beforeFactory, afterFactory, before~/afterRender, onPageStart/~End? Or am I completely looking in the wrong direction?</li></ul>
 
My solution was in the Report Editor. First I defined a Report Variable called last_month, with the formula 'BirtDateTime.month(BirtDateTime.today())-1'. Next I created a table with 5 columns and 1 row. In the first cell of the row I dragged 'system_name' from the Data Explorer. Next I right-clicked on the 2nd cell of the row, and choose 'Insert group'. In the cell I placed an aggregation on the downtime and priority, grouping on system_name. One way or the other I succeeded in getting one row for each system. 
 
the solution 
 
That was what I was aiming at, sort of. In a previous version of the report I got that working. 
 
The solution 'tmseidel' suggest has the added bonus that you separate the business logic from the presentation of the data. In my solution I jumble them together. 
 
So, does anyone know what I need to do to get the suggested approach working?