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

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
Asked:
vbnetcoder
  • 5
  • 4
1 Solution
 
DcpKingCommented:
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
 
vbnetcoderAuthor 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
 
DcpKingCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
vbnetcoderAuthor 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
 
DcpKingCommented:
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
 
vbnetcoderAuthor 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
 
vbnetcoderAuthor 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
 
DcpKingCommented:
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
 
vbnetcoderAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now