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 |
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-08-12 : 00:36:25
|
| I have a result set like follows:Value10942020090100and I need to calculate the number of rows where Value is < 90. So the answer should be 3.I tried this but if I put it in a cell of a table without a group it returns many rows where each row is either 1 (if Value < 90) or NULL. I have also tried adding groups but then it seems to return NULL.=IFF(Fields!Value.Value < 90, CountDistinct(Fields!EventID.Value), "NULL")I also tried Running Value and again returned many rows where count was upto 3 but results after and before that with NULL. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-08-12 : 03:33:41
|
| ?<90 Answer should be 2...10 & 24. 90 is [b]not[/] less than 90I don't use AS/RS, but IFF is normally an ACCESS construct. CASE is the more normal SQL alternative. Should you be using that construct? |
 |
|
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-08-12 : 03:51:42
|
| IFF is an SQL Server 2008 command in RS. Case is not available in RS only Transact SQL. Yes.. answer should be 2:) My example above works but it counts each row individually rather than the required aggregate. Normally this can be fixed by using a group. |
|
|
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-08-13 : 03:26:35
|
| This is what I ended up with to calculate percentage based upon a condition;=Round(Sum(IIF(Fields!SelectToAck.Value < Parameters!MinSelectToAck.Value,1,0)) / Count(Fields!SelectToAck.Value) * 100) |
|
|
|
|
|
|
Count using equivalent to a where clause in RS