SQL Server Reports Aggregate Functions

Question

G'day,

I'm a total n00b when it comes to SQL Server reports and my Vb.Net knowledge is ageing now.

I have a detailed report of rows in a database, with one of the columns that is nullable. What I want to do is show a simple pie chart over the data in the result which shows how many rows have a value and how many do not.

I can't work out how to write the Count() aggregate in the expression for the data series so that it filters.

eg.

I have 10000 rows of which 2000 have a null. I want a pie chart that shows two results, one small pie chunk with 2000 and a larger pie chunk with 8000. When I try and do =Count(IsDbNull(Fields!TransactionID.Value)) and =Count(Not IsDbNull(Fields!TransactionID.Value)) it appears to send the same result twice, ie. the result set is still the same size it just consists of trues and falses.

Cheers for your help.

Answer 1

I'd use something like this

• -SUM(CInt(IsDbNull(Fields!TransactionID.Value)))
• COUNT(Fields!TransactionID.Value) + SUM(CInt(NOTIsDbNull(Fields!TransactionID.Value)))

COUNT literally counts values, same as SQL Sever. So:

• IsDBNull gives true/false -> -1/0 -> SUM that gives you number of NULLs (minus of course)
• Then take full count, subtract the SUM gives non-NULL count

Answer 2

Since you are using COUNT you are just counting values; IsDbNULL is returning TRUE or FALSE both of which are being counted.

Try doing this for the Non-NULLS =SUM(IIF(ISNOTHING(Fields! TransactionID.Value),0,1)) and for the NULLs use =SUM(IIF(ISNOTHING(Fields! TransactionID.Value),1,0))

Actually, for the non-NULLs you can just use COUNT(Fields!TransactionID.Value)