Spaces/Official ESi/Scriptaculous & Adv. Tags
Expressions explained
Andrew Donnan
posted this on November 25, 2008 10:50
sum(capacity)
example #2:
capacity-occupancy
The <expression> tag allows you to perform calculations on the data in a board.
- This supposes that the fields are number (int) fields (After all, you cannot add '5' and 'lamp' together, it's impossible).
The expression is translated into a SQL Server expression.
- If outside the eocrepeatallrecords tag, performs calculation on all data within board. Useful for summing rows and other aggregate functions. (example #1 above = This would be useful if you wanted to find the total capacity across ALL shelters)
- If inside the eocrepeatallrecords tag, performs calculation on only one record at time. (example #2 above = This would be useful for finding the availability for EACH shelter)
Filtered expressions:
Useful for when you want to exclude certain criteria from your calculations, or if you want to find the totals based on something other than a number field.
sum(case when Shelter_Status = 'OPEN' then 1 else 0 end)
The logic behind the above expression... if the shelter's status is "Open", then it will be assigned the number 1, otherwise it will be assigned the number 0. Since this is a sum(), it will loop through all records in the SQL table and assign each record either a 0 or a 1, and then total up all of them.
To round a field value to two decimal places use the following code in your board’s display view:
<expression name="expression_name">isNull(cast (round ( sum (cast(my_fieldname_goes_here as float) ),2) as varchar(100)),0)</expression>
Common Expressions:
Basic Count expression for a total (bear in mind that this does not respect filters at all, so it will return the count of ALL records in the table, regardless if they are or not displaying on the display view).
<expression name="totalcount" omitalias="">count(*)</expression>
Comments
I found the expression tag nicely handled an equation while processing two functions. For example, I calculated the total availability of my shelters with the following:
<expression name="total_availability">sum(capacity)-sum(occupancy)</expression>
Great point Johnathan!
To add to this, one more thing to note is that an expression like this cannot contain an expression name within it (since an expression is not a SQL column).
Example:
You cannot create a third expression and simply say:
You actually have to bring the contents of the other expressions into this third one like you have in your comment.
How do you find a percent using expressions? I am using the equation below but get an error because the pfd is used twice.
<expression name="PerAvailable">(sum(pfd) - sum(leave) - sum(tdy) - sum(med_qtrs))/sum(pfd)*100</expression>
Thanks.
Brian,
You should be able to use pfd multiple times within an expression. You may receive an error because the denominator in your expression is equal to 0 (the error from the error log would validate this). If this is the case, we will need to work around this a bit by factoring this situation into our equastion:
<expression name="PerAvailable">case when sum(pfd) = 0 then 0 else ((sum(pfd) - sum(leave) - sum(tdy) - sum(med_qtrs))/sum(pfd)*100)end</expression>
This basically says if the value of sum(pfd) is zero, then display the number 0, otherwise calculate the percentage.
Let me know if this works for you!
Thanks Gerrit. I don't have access to the error log so I couldn't check that.
I used the equation you provided but am now getting a 0 instead of a percent. I do have numbers in the tables that so the pfd is not equal to 0.
Thanks.
Brian