August 12, 2011 at 11:39 am
Hello,
I have sales per customer. I need to summary which displays the sales for all customers if there credit percent is equal to 0. Please help me.
Cust % Sales
1 1.00% $12
2 0.00% $13
3 2.00% $14
I need
Cust % Sales TotalSales
1 1.00% $12 $26
2 0.00% $13 $26
3 2.00% $14 $26
August 15, 2011 at 1:49 pm
Is this a detail line or a group line? I've only tested this partially, but think it should work... In your TotalSales column use the expression:
If it's a group, you may be able to get away with:
=SUM(IIF(fields!percent.Value <> 0, Fields!Sales.Value, 0))
If it is detail you will need to add the scope value which in my case is DataSet1 and equal to the name of the containng group or the dataset:
=SUM(IIF(Fields!percent.Value <> 1, Fields!Sales.Value, 0), "DataSet1")
Let us know if it works, Steve
August 15, 2011 at 2:19 pm
Hi Steve,
When i tried your expression, it gives shows me error in that field. I used the expression
SUM(IIF(Fields!Percent.Value > 0, Fields!Sales.Value, 0), "DataSet1")
I don't have grouping on this report, so i used this expression in details.
August 15, 2011 at 2:23 pm
The expression should be fine to use in the details, but you need to replace DataSet1 with the name of your dataset. If you do not know it, while in the Expression editor look to the bottom left in the Category box, it should be in parenthesis next to the word Fields
August 15, 2011 at 2:32 pm
My dataset name is dataset1. I usually don't change the dataset name, so it is DataSet1.
August 15, 2011 at 2:37 pm
And the other fields in the expression Percent and Sales (I just guessed those were the field names), are those valid names of your fields. Is anything underlined in the expression editor to indicate where the error is occuring?
August 15, 2011 at 2:40 pm
Yes, the field names are correct. Nothing has been underlined in the expression. Below is the exact expression:
=SUM(IIF(Fields!CreditAllowance.Value > 0, Fields!Sales.Value, 0), "DataSet1")
August 15, 2011 at 3:55 pm
Just to take a step back, what do you get when you just try:
=SUM(Fields!Sales.Value, "DataSet1")
August 16, 2011 at 12:51 pm
When i just use the =SUM(Fields!Sales.Value, "DataSet1") expression, it gives me total sales $39 i.e. (12 + 13 +14)
August 16, 2011 at 1:15 pm
That's good, that is what I would expect and for the most part what you want. Now what does this return?
=IIF(Fields!CreditAllowance.Value > 0, Fields!Sales.Value, 0)
August 16, 2011 at 1:57 pm
That displays values
Cust % Sales TotalSales
1 1.00% $12 $12
2 0.00% $13 $0
3 2.00% $14 $14
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply