Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (17.6k points)
edited by
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?



1 Answer

0 votes
by (47.2k points)
selected by
Best answer

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. 

Related questions

0 votes
1 answer
asked Feb 4, 2020 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
asked Feb 3, 2020 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
asked Feb 6, 2020 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
asked Jan 31, 2020 in BI by Vaibhav Ameta (17.6k points)

Browse Categories