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

Computed value based on contents fields?

Macamba

Hi, 
 
Question: 
Can computed columns of a dataset be computed based on the contents of a field? 
 
Background: 
My dataset contains donwtimes of systems expressed in minutes (mtrs_minutes). I would like to calculate the downtime for a month and for a year for a system (AveMonth and AveYear). 
 
Example: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
System Month mtrs_minutes AveMonth AveYear
System 01 8 12 12 1234.56
System 01 9 2 17.5 1234.56
System 01 9 33 17.5 1234.56
System 01 10 1065 329,78 1234.56
System 01 10 66 329,78 1234.56
System 01 10 1295 329,78 1234.56
System 01 10 8 329,78 1234.56
...
System 02 8 375 598 4567.8
System 02 8 82 598 4567.8
System 02 9 7 24.5 4567.8
System 02 9 42 24.5 4567.8
System 02 10 28770 28770 4567.8
....
System 0n 9 770 770 33.44
System 0n 10 3643 2146 33.44
System 0n 10 649 2146 33.44
</pre>
 
The AveMonth is the calculated column. The result I get at this moment is based on the average downtime for each month for all systems, using the following formula: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Expression: dataSetRow["mtrs_minutes"]

Filter: dataSetRow["Month"]==BirtDateTime.month(BirtDateTime.today())
</pre>
But this approach gets me the AveMonth and AveYear for al records. 
 
How do I compute my column for each system (or is my approach wrong)? 
 
TIA, 
Macamba

Find more posts tagged with

Comments

thuston

You have to group. You can't do that with DataSet computed columns. You either have to do it in the SQL or in the layout Table.

Macamba

In SQL, do you mean: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT system,
month,
avg (mtrs_minutes)

FROM incidents

GROUP BY system,
month

ORDER BY system,
month
</pre>
 
I get the following error in Birth: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ERROR: syntax error at or near "avg"
</pre>
 
And avg is recognised as a SQL reserved word (it got bold and purple).

Macamba

<blockquote class='ipsBlockquote' data-author="'thuston'" data-cid="68167" data-time="1283868508" data-date="07 September 2010 - 07:08 AM">
You (either) have to do it in ... the layout Table. </blockquote>
 
What I already accomplished is adding this result in the 'Report editor'. I added the table with 3 columns and one row. Next I: 
<ul class='bbcol decimal'><li>In the top row I added 4 labels: "System", "Month" and "Year". Month and Year being the average of mtrs_minutes over the last month and the last year.</li><li> In the field under System, I dragged [system] from the data explorer.</li><li> In the field next to [system] I right clicked, and asked to insert a group on system. A new row was added. </li><li> In the new row I right clicked in the field next to [system]', and again asked to insert a group on system. A third row was added. In the field to [system]'' I entered an aggregation on mtrs_minutes with the formula 
dataSetRow["month"]== (BirtDateTime.month(BirtDateTime.today())-1).</li><li> In the third field I entered an aggregation on mtrs_minutes with no formula (and so getting an aggregation over all months)</li><li> Lastly, a removed the 1st, 3rd, 4th and 5th row.</li></ul>
 
This resulted in the required table, with for each system the average mtrs_minutes for the last month and all year. 
 
Is that the only possible approach?