• Status: Solved
• Priority: Medium
• Security: Public
• Views: 399

# Counting is SSRS

On my report i have two counts at a group level. 1 is where the sum of total cost per each order number is = 0 and the other is where it is >0.

Using the following data currently i am returning 1 for the = 0 count and 1 for the greater then zero count. That isn't correct, I should be returning only a 1 for the greater then zero count because:

10 + 0 + 30 + 0 = 40.

I don't know if it is and error in my expression or what but somehow I think i am returning the distinct records where it is > 0 and the distinct records where it is = 0.

order number = 4444 and TotalCost = 10
order number = 4444 and TotalCost = 0
order number = 4444 and TotalCost = 30
order number = 4444 and TotalCost = 0

How do i acomplish this task so the SUM of total cost per each order number is evaluated for counting???
0
vbnetcoder
• 5
• 4
1 Solution

Commented:
I think you're trying to say that your result numbers are way too big.

Try to test your report with a dataset that only has records where the total cost is zero, and make sure that it pulls a count but no sum. Then reverse the process - give it all records with total costs > zero.

hth

Mike
0

Author Commented:
I am saying in the example data that i need add one to the > o count because the SUM of all the totals is > 0.  Currently, it is doing that but for some reason I am adding one to the = 0 count as well.
0

Commented:
Then experiment with data that shouldn't give anything for the "=0" count and see where your code is producing values into the "=0" count.  What's the code that creates each of the counts ?
0

Author Commented:
The code looks like this

> 0

=CountDistinct(iif(Fields!NumberField.Value > 0
And Ucase(Fields!Is_Valid.Value) = "NO" ,
Fields!Order_Number.Value, Nothing))

> 0

=CountDistinct(iif(Fields!NumberField.Value = 0
And Ucase(Fields!Is_Valid.Value) = "NO" ,
Fields!Order_Number.Value, Nothing))

I believe the > 0 is working but when it tries to do the = 0 it returns 1 even though it should return 0 because the SUM is not 0.
0

Commented:
What are you trying to do? Count how many are > 0 and how many = 0, or get the sum of the non-zero ones?
I have no idea of the form of your report, of course, but have you thought of adding a column (that you'll eventually hide) and making the value of that column be

iif  (  Fields!NumberField.Value > 0
And       Ucase(Fields!Is_Valid.Value) = "NO" ,
1, 0   )

and then doing a straight sum of that field? That will give you a count of how many > 0 rows you have ...

hth

Mike

PS I know it'll look messy, but with reporting there's sometimes no other way - we just cover it up before putting it on display!
0

Author Commented:
I  have to columns on the report:

The first one is  for > 0
The second one is for = 0

My problem is that I need evaluate the SUM of Fields!NumberField.Value > 0 per order number in my groups.  Using my example data you code would give me

2 for the  = 0
2 for the >0

That is NOT what I need.

I need to sum up per each order number .... in the case of my sample data:

10 + 0 + 30 + 0 = 40

Therefore order 4444 should have one in the > 0 count and zero for the = 0.

I have a group for the order number and my code does this for me at that level (as far as I can tell) but I am not able to get it to work at the outer group level.
0

Author Commented:
My question in the other thread relating to counting textboxes is one idea that i was thinking about as to how to solve this issue. I do get the correct result at the group level (as far as i can tell) so if only i could do Sum(ReportItem!textbox1.value) I would be good...
0

Commented:
Well, if you need the sum of what is in that field, then change the   1   that I used into the value itself:

iif  (  Fields!NumberField.Value > 0
And       Ucase(Fields!Is_Valid.Value) = "NO" ,
Fields!NumberField.Value, 0   )

You shouldn't need more.
0

Author Commented:
I don't need just the sum .... i need to evaluate if the sum of Fields!NumberField.Value = 0 or the sum of the field >0 and then returning a count for both of these categories. Per each unique order number it is either >0 or 0 but it can't be both.

I have no problem suming a value or counting where the value is 0 or > 0. My problem is first determining at the group level what the sum of Fields!NumberField.Value is and then returning a count of both of these categories at the group before as counts.

It the example data i gave above  at the group level I would have the following

"count where the total is zero  = 0"
"count where the total is > zero  = 1"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.