Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
last
Starting Member
25 Posts |
 Posted - 2009-04-23 : 04:47:24
|
I have hit a brick wall where I have a Field that returns boolean "T" and "F" values but i need to do a calculation in an expression that takes the complete number of Rows returned and divides that by the number of Rows where the value is "F"My expression is:(CountRows()/Count(Fields!SLA_BREACH.Value="F"))*100") the problem with this it takes Count(Fields!SLA_BREACH.Value="F") counts all fields whether they have a "F" or "T" value |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-23 : 05:37:51
|
Something similar to this?100.0 * CountRows() / SUM(IIF(Fields!SLA_BREACH.Value = "F", 1, 0))
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
last
Starting Member
25 Posts |
Posted - 2009-04-23 : 06:20:30
|
| Hi Peso I had tried something similar to that before but not with Sum but count and still get no joy. The expression ignores the condition that it only has to count rows that have an "F" value.Say if an Owner returns 3 rows with 2 of them being "F"and 1 being "T" the expression Count(IIF(Fields!SLA_BREACH.Value = "F", 1, 0)) returns 3 where else it should return 2 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-23 : 06:21:39
|
Why are you using COUNT? Of course will reeturn 3! This is becase Zero is a value too.I posted SUM. Use that.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
last
Starting Member
25 Posts |
Posted - 2009-05-12 : 09:12:23
|
| I solved the solution by using T-SQL using CASE. So when a values returns F then convert to 0 and T to 1 and the totals in the report are accurate. |
|
|
|
|
|
|
count on Field expression with specific value