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; } } }

Average as contents table cell?

Macamba

Hi, 
 
Sorry, just started using Birt and have troubles mapping what I want to what I need to do in Birt. 
 
I have a table containing downtimes of systems I manage. 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. 
 
This is the Data Set I created: 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT public.incidents.system_name, public.incidents.mtrs_minutes,
public.incidents.end_month, public.incidents.incident_cause,
public.incidents.priority
FROM public.incidents
WHERE ( public.incidents.priority = 'Prio1'
OR public.incidents.priority = 'Prio2' )
</pre>
 
In the design of the report a created a table containing 1 row and 6 columns containing the following column headers: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Prio1Month Prio1Year Prio2Month Prio2Year
</pre>
 
In the 'System' cell I entered 'public.incidents.system_name'. Now I want to enter the average downtime of a system in the Prio1Year cell. 
 
Using the formula 
<pre class='_prettyXprint _lang-auto _linenums:0'>dataSetRow["mtrs_minutes"]</pre>
I expected to get the down time (the mtrs_minutes) per system. What I got is (probably) the total downtime of all systems. 
 
How do I limit the end result to the downtime of a system to Prio1 downtimes in this month? 
 
TIA

Find more posts tagged with

Comments

thuston

Did you preview the DataSet to check your assumption?
I doubt the dataSetRow[] is returning more than one value (unless it is used in a Aggregation). If it is in an Aggregation, then you probably would see all the data. You must define a Group on your table (for system), then you can adjust the Aggregation to only Average over the group.

Macamba

Hi, 
 
I tried to group, like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Prio1Month Prio1Year Prio2Month Prio2Year
[system_name] Right click ...
</pre>
 
In the right click menu I choose 'Insert group' and added an aggregation on mtrs_minutes. In the end my table looked like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Prio1Month Prio1Year Prio2Month Prio2Year
[system_name]
[system_name] Sigma[Prio1MonthSystem]
</pre>
 
Where Sigma stands for the sum sign, and Prio1MonthSystem is a float average on dataSetRow["mtrs_minutes"]. 
 
the result in the preview window looks like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>Organisiatie Prio 1 - Maand Prio 1 - Jaar Prio 2 - Maand Prio 2 - Jaar
System 1
System 1 5008,774
System 1 5008,774
...
System 1 5008,774
System 2
System 2 5008,774
...
System 2 5008,774
etc.
</pre>
 
Clearly I'm approaching it the wrong way. But what is the correct way? 
 
TIA

thuston

Double click the Aggregate control and on the bottom of the builder will be an 'Aggregate On:' section.
Make sure this is set to your new Group and not Table (Table does all data).

You probably also want to move your controls to the Group Header or Footer Table Row, so you only see it once per Group.
If you don't have any per row Detail, Edit the Group and select 'Hide Detail' to prevent the blank rows from appearing in your output.

Macamba

Found it. Thanks