SSRS Expression - COUNT and IIF

Question

Hi,

I was building an SSRS Report. Meanwhile, I got stuck in finding the COUNT of rows having priority as "high" and business as "COM".

I tried by implementing the below mentioned expression but it returns count of all rows where Business = "COM" =IIF(Fields!Business.Value = "COM", count(Fields!Priority.Value="High"),0)

Can some one help me out?

Thanks

Answer 1

Count Function in SSRS falls under Report Builder Functions which basically returns a count of non-null values specified by the expression in the given scope.

Syntax for Count Function: Count(expression, scope, recursive) 

If statement in SSRS allows a developer to control the program flow and it's outputs. It is a decision function that can be reached through expression. In SSRS, the keyword for If statement is IIF.

Syntax for If statement:  IIf(Expression as Boolean, TruePart as Object, FalsePart as Object)

So you are trying to run logic on a non-aggregate field, and then attempting to do an aggregate. You can choose any of the below mentioned examples based on your preference. 

Note:   In the first example, I have counted your Key value. You need to replace it:

=COUNT(IIF(Fields!Business.Value = "COM" AND Fields!Priority.Value="High", Fields!YourIDField.Value, NOTHING))

=SUM(IIF(Fields!Business.Value = "COM" AND Fields!Priority.Value="High",1,0)) 

You can enroll for MSBI training that enables you to master MSBI tools like SSIS, SSRS, and SSAS using SQL Server.  As part of this training, you will learn data integration and creation of reports, dashboards, and cubes for faster reports generation.