vbnetcoder
asked on
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???
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???
ASKER
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.
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 ?
ASKER
The code looks like this
> 0
=CountDistinct(iif(Fields!
And Ucase(Fields!Is_Valid.Valu
Fields!Order_Number.Value,
> 0
=CountDistinct(iif(Fields!
And Ucase(Fields!Is_Valid.Valu
Fields!Order_Number.Value,
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
=CountDistinct(iif(Fields!
And Ucase(Fields!Is_Valid.Valu
Fields!Order_Number.Value,
> 0
=CountDistinct(iif(Fields!
And Ucase(Fields!Is_Valid.Valu
Fields!Order_Number.Value,
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.
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.Valu
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!
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.Valu
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!
ASKER
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.
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.
ASKER
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.va
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
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"
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