?
Solved

SSRS Expression

Posted on 2012-08-23
8
Medium Priority
?
630 Views
Last Modified: 2012-09-11
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
Comment
Question by:vbnetcoder
  • 4
  • 2
  • 2
8 Comments
 
LVL 35

Expert Comment

by:James0628
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

by:vbnetcoder
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

by:vbnetcoder
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 27

Expert Comment

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

Author Comment

by:vbnetcoder
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

by:
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

by:planocz
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

by:vbnetcoder
ID: 38388905
ty
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question