Sql – How to exclude null values from Count() function in reporting services 2005

reporting-servicessqlvisual-studio-2005

I am creating a report that has a pareto-like graph and a table of Order Types and how many units of each order type there are. The subset returned from the stored procedure that I am using includes a field called WorkItemId, and if that value is null that means that item isn't to be counted. How should I count Order Types in the report without including the values that have the null WorkItemId? Right now I am using the expression: 

Count(Fields!OrderType.Value)

to count the each unit for a specific order type.

Thanks!

EDIT: WorkItemId is what cannot be null to be counted, not Order Type

Null values in WorkItemId are needed in other reports, so I can't just simply filter them in SQL.

Best Answer

You can use something like

Sum(IIF(IsNothing(Fields!WorkItemId.Value),0,1))
Related Topic