1

I cannot make the my expression NOT count a NULL value in my SSRS matrix.

In my SSRS matrix, I have 2 columns one for AppraisalCompany and a count under the SubmittedDate column. In my report this what is happening:

enter image description here

Per Derrick's suggestion here is the change I made in the ColumnGroup properties for the SubmittedDate:

enter image description here

Here is my expression change in the ColumnGroup properties:

enter image description here

Unfortunately I got this error:

enter image description here

Melinda
  • 1,501
  • 5
  • 25
  • 58
  • can't you use a sum? something like this ... =SUM(IIF(IsNothing(Fields!SubmittedDate.Value),0,1) – Chris Jan 22 '19 at 17:31
  • Your IIF expression should be INSIDE the COUNT - `=COUNT(IIF(IsNothing(Fields!SubmittedDate.Value),NOTHING,Fields!AppraisalCompany.Value))` or like @Chris said. Otherwise the IIF just checks the first record rather than evaluate each record individually. – Hannover Fist Jan 22 '19 at 17:47
  • Thanks Hannover and Chris I will try that. Chris, the DEC-18 is from a CASE statement on the SubmittedDate column. – Melinda Jan 22 '19 at 17:49
  • You placed the IsNothing logic in the value property, it needs to be placed in the expression property. Here's a picture, he used `= false`, instead of `<> true` but either will work. https://stackoverflow.com/a/30325554/3194005 – Derrick Moeller Jan 23 '19 at 15:55

2 Answers2

1

I'm suspicious of your Dataset, I'm not entirely sure how you're getting a null value to return 1 in the COUNT. I have been unable to reproduce your results.

Dataset Query

SELECT 'Drive In' AS AppraisalCompany, NULL AS SubmittedDate
UNION
SELECT 'Photo App - English', 'Dec-18'

Next I created a Row Group on AppraisalCompany and a Column Group on SubmittedDate.

I filtered the column group to remove the null grouping, using the expression =IsNothing(Fields!SubmittedDate.Value), operator <>, and Value true.

In the textbox in the matrix I used [Count(SubmittedDate)].

OUTUT

Appraisal Company   |  Dec-18
-------------------------------
Drive In            |         0
Photo App - English |         1
Derrick Moeller
  • 4,808
  • 2
  • 22
  • 48
  • Hi Derrick, I tried another approach. In my dataset from my query, I set the NULL value to 0 in the resultset from the query instead of trying to handle the NULL value. So in my expression I tried this: =IIF(Fields!SubmittedDate.Value = "0","0",COUNT(Fields!AppraisalCompany)) but it is still counting the 0 as 1 and NOT as 0. Any suggestions? Thanks again. – Melinda Jan 22 '19 at 18:42
  • @Melinda, you do not want to set your null values to zero in your query. If you do that Count will not work correctly. Also that should have created an extra column? I believe you're leaving out something obvious, I haven't been able to reproduce your issue. – Derrick Moeller Jan 22 '19 at 19:31
  • @Derrrick, I made the changes in the Column Groups section for the (SubmittedDate) but had a question. You said in the expression you removed the NULL which I did and put in =IsNothing(Fields!SubmittedDate.Value), operator <>, and Value true but when I tried that VS is squawking with this error: An error roccurred during local reporting process. Failed to evaluate FilterValue of the Grouping 'SubmittedDate'. What I put in the expression is: =IsNothing(Fields!SubmittedDate.Value <> true) .Sorry just trying to get some clarification. Thanks. – Melinda Jan 23 '19 at 13:29
0

By a Decimal (Number) datatype Nothing and 0 are the same. You can test this.

Put a tablix into your report with year from 2017 to 2019. Then put the year in a column of the tablix as a number format, then write the following expression in the detail textbox:

=CDec(IIF(CDec(Fields!Year.Value) = 2017, 0, Nothing))

After executing your report you will notice that every value in the year column is 0.

The same goes for the check. Both of these expressions will always return Yes. I basically check for 0 and the second one for for Nothing:

=IIF(CDec(IIF(CDec(Fields!Jahr.Value) = 2017, 0, Nothing)) = 0, "Yes", "No")
=IIF(CDec(IIF(CDec(Fields!Jahr.Value) = 2017, 0, Nothing)) = Nothing, "Yes", "No")

But remember your textbox/column has the be a number format.

So if you want to return Nothing and you display it in a number format textbox, it will show you a 0.

With this in mind it will make sense that a Count() returns the value 1 for 0 AND Nothing. So basically this will do the trick:

'Cont
=Sum(IIF(Fields!YourValue.Value = Nothing, 0, 1))
Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • Count doesn't count null values. And you're getting a zero because you converted to decimal. If you simply format a null field as a number you still get null in SSRS. – Derrick Moeller Jan 23 '19 at 16:17
  • Yep count doesn´t count null values but in a number format its a zero and zero are counted. That was what I was trying to explain. Based of his posts and the comments I thought this was the problem. – Strawberryshrub Jan 24 '19 at 05:54