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;

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

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?

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?