Solved

SSRS Expression

Posted on 2012-08-23
579 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

LVL 34

Expert Comment

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

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

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

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

Author Comment

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 34

Accepted Solution

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

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

ty
0

Featured Post

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…