CODEWITHSUNDEEP
sql-serverreporting-servicesreportexpressionrdl
tinonetic
tinonetic

Reputation: 8034

Report Expression to SUM [Column A] values for records with null/empty values in [Column B]

I am working on an existing SSRS report and have the following situation:

  1. It is working well and displays grouped data from a stored procedure
  2. A request has been made to SUM values of certain column(say Quantity) ONLY when values are empty/null in a certain field (Defeted - Bit field with a NULL Default)
  3. E.g

Products Table

+----+--------------+----------+----------+
| ID | PRODUCTNAME  | QUANTITY | DEFECTED |
+----+--------------+----------+----------+
|    |              |          |          |
| 1  | Ice Cream    | 8        | 1        |
|    |              |          |          |
| 2  | Soap         | 3        |          |
|    |              |          |          |
| 3  | Kit Kat      | 10       |          |
|    |              |          |          |
| 4  | Baked Beans  | 2        | 0        |
|    |              |          |          |
| 5  | Toilet Paper | 2        |          |
+----+--------------+----------+----------+

I would like to have an expression that calculates the Total quantity of PRODUCTS whose DEFECTED status is NULL/Empty (not 1 or 0)

i.e It should be total for the following:

Soap + Kit Kat + Toilet Paper = 3 + 10 + 2 = 15

My attempt is:

Sum(IFF(First(Fields!Defected.Value, "Products_Report_Data_Set_Name") = "",First(Fields!Quantity.Value, "Products_Report_Data_Set_Name"),0))

Where Products_Report_Data_Set_Name returns the list of Products

Its not working

Can I solve it through an expression? Or will I have to add another data set?

Upvotes: 0

Views: 2132

Answers (1)

Aditya
Aditya

Reputation: 2311

Have you tried this ?

= IIF(Fields!Defected.Value = "", SUM(Fields!Quantity.Value), Nothing)

Hope this should work as expected.

Upvotes: 1

Related Questions