Solved

# SSRS Expression

Posted on 2012-08-23
Medium Priority
630 Views
In the following expressions i return 1 value for the first expression and 1 value for the second.

=CountDistinct(iif(Fields!TotalCost > 0
And Ucase(Fields!IsValid.Value) = "NO"
,Fields!OrderNumber.Value, Nothing))

=CountDistinct(iif(Fields!TotalCost = 0
And Ucase(Fields!IsValid.Value) = "NO"
,Fields!OrderNumber.Value, Nothing))

In reality the second expression = 0 should NOT return anything because what I WANt to do is get the sum of Fields!TotalCost
per each unique order number.

So, if in my dataset i have

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

the sum of all the order numbers is 40. So, for the first expression i should return 1 and the second expression i should return 0.

How do i do it?
0
Question by:vbnetcoder
• 4
• 2
• 2

LVL 35

Expert Comment

ID: 38328251
I don't follow.  You're doing a count of OrderNumber where TotalCost > 0 and a count where TotalCost = 0, but then you say that you want "the sum of Fields!TotalCost".  How do those counts relate to that sum?

You say that the TotalCost = 0 count should be 0.  Why should it be 0?  When would it _not_ be 0?  What are you actually trying to count?

James
0

Author Comment

ID: 38328727
In my report i have two counts at a group level that should return the count of all the order numbers where the sum per each unique order number = 0 and another where the sum is > 0

So, the follow data should return a count of 1 to the > 0 group total which it is doing BUT it is also returning 1 to the = than 0 total which it should not be doing.

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

In the following example 1 should be counted to the = 0 group because 10 + -10 = 0

order number = 333 and TotalCost = 10
order number = 333 and TotalCost = -10
0

Author Comment

ID: 38328776
But, in the 333 example currently i would get 1 for > 0 and 1 for = 0.

In this example:

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

I would get  1 for > 0 and 1 for = 0 as well but i should ONLY get 1 for >0 because 10 + 0 + 30+ 0 = 40 which is >0.

Again, this is the code i am using at the group level to the the counts

--greater then 0

=CountDistinct(iif(Fields!TotalCost > 0
And Ucase(Fields!IsValid.Value) = "NO"
,Fields!OrderNumber.Value, Nothing))

- equal to 0
=CountDistinct(iif(Fields!TotalCost > 0
And Ucase(Fields!IsValid.Value) = "NO"
,Fields!OrderNumber.Value, Nothing))

What i probable need to do is get the SUM of TotalCost Per each ordernumber BEFORE evaluating else...
0

LVL 27

Expert Comment

ID: 38329224
Are you wanting just a row count of how many rows are 0 and how many are > 0?
0

Author Comment

ID: 38329265
not a row count .... i can do that. it has to be a count where the SUM of each ordernumber is >  0 and another where the sum of the order number = 0.

The problem is that in my dataset i have several different order numbers that are the same. It needs to evaluate the sum of the total cost per each distinct order number
0

LVL 35

Accepted Solution

James0628 earned 2000 total points
ID: 38331969
I agree with what you said earlier - It sounds like you need to test the sum of TotalCost for each order to see if that's 0.  Unfortunately, I have no idea how, or if, you can do that in SSRS.  I've never used SSRS.  No one else had responded to your question, so I thought I'd try to at least get things rolling by asking some questions.

If you have control over the query, then one option might be to have the query create the order total for you.  Then you could just check that total in the report, instead of trying to generate the total in the report and then do a count/sum based on that.

James
0

LVL 27

Expert Comment

ID: 38332989
you need to change your sql query to group by order number.
yhen sum each order number group then you will be ablr to see each order number is 0 or > 0.
I think you are not understanding what you really need out of the report. Either money totals or how much total orders per order number.
0

Author Closing Comment

ID: 38388905
ty
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installatiâ€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signatâ€¦
###### Suggested Courses
Course of the Month16 days, 22 hours left to enroll