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

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?