Visual Studio .NET - RDLC IIF CONDITION - Asked By michael brown on 31-May-13 03:03 PM

I have developed a report in vb.net/rdlc. what i want is to have a count of studentID for all male students(sex coded as 1) in grade 1 to be in a textbox, and a count of all female students in grade 1(sex coded as 0)to be in another textbox.

The query i have for the report is generating the data as below;

Sex               grade      studentID

1                  1        2009105012410059

1                  1        2009105012410060

0                  1        2009105012410063

0                  1        2009105012410065


 
here is what i have in rdlc

=iif(Fields!Sex.Value = 1 and Fields!grade.Value = 1,count(Fields!studentId.Value),0)

only the false part is being executed i dont know why. Would please assist me to establish where i might go wrong
Bill B replied to michael brown on 14-Jun-13 04:01 AM
not sure where you are putting the IIF statement such that you expect Count to aggregate anything like that
 
you wouldn't put the student ID in count(), its always count(*)
which will count the rows for the 1 group
or how many there are depending on the other non-aggregate fields included in the select

Here's what professor Rozenshtein called a delta function (advanced SQL with ez example)

Instead, why not change your sql to be

select  sum(  1- sgn( abs( Sex - 1) )  ) as Male,  sum(  sgn( abs( Sex - 1) )  ) as Female , Grade
from YourTable
where grade = 1
group by Grade
order by grade

when Sex=1, then  
1- sgn( abs( 1 - 1) )
1- sgn( abs( 0) )
1- sgn( 0)
1-0
=1
so, SUM will add 1 for the custom Male field
and when Sex=0
1- sgn( abs( 0 - 1) )
1- sgn( abs( -1) )
1- sgn( 1)
1-1
=0, so sum for the Male field doesn't add anything


when Sex=0, then  
sgn( abs( 0 - 1) ) 
sgn( abs( -1 ) )
sgn( 1)
=1
so, SUM will add 1 for the custom Female field

when Sex=1, then  
sgn( abs( 1 - 1) ) 
sgn( abs( 0 ) )
sgn( 0)
=0
so SUM will add 0 when Sex=1 for the female aggregate custom field



That technique is very powerful, but the gist of a delta function is to return 0 or 1
and its often multiplied by some other data that is also similarly placed inside an aggregate function
like SUM or AVG

Does that help?