April 29, 2014 at 4:59 am
Hello
I have a table that gives me information, it is for example in column 3 appears OK the values and delete.
I would like the values in column 2 when the value in column 3 was Delete, puts me up values from column 2 to zero.
It is possible to do through the Reporting Services?
April 29, 2014 at 6:06 am
Table DDL, sample data and query please.
What do you expect to see in each of the columns with the sample data?
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 6:41 am
My table has the following results
col1 col2 col3
test1 10 OK
test1 10 DEL
test2 5 OK
this is the way I have this moment where col3 data are OK or DEL
I wanted the result to be:
col1 col2 col3
test1 10 OK
test1 0 DEL
test2 5 OK
ie, when the field was col3 DEL, put to zero the value of col2
April 29, 2014 at 6:46 am
Either change the sql query
SELECT col1, CASE WHEN col3 = 'DEL' THEN 0 ELSE col2 END AS [col2], col3
FROM
Or use an expression for column 2
=IIF(Fileds!col3.Value = "DEL",0,Fileds!col2.Value)
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 9:24 am
Thanks for the help was really what I needed.
Now let me just put one more question.
Did you do the SUM of col2 but I failed. the textbox returns only # errors
I'm using
= SUM (IIF (Fields!col3.Value = "DEL", 0, (Fields! col2.Value)))
April 29, 2014 at 9:33 am
Expression looks OK
Check all the values in col2, looks like you have non integer value or NULL.
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 9:37 am
I have a value 59.90
I can not use numbers with decimals?
April 29, 2014 at 9:50 am
Sorry my bad it is incompatible data types, try converting to double or decimal
=SUM(IIF(Fields!col3.Value = "DEL",CDbl(0),CDbl(Fields!col2.Value)))
or
=SUM(IIF(Fields!col3.Value = "DEL",CDec(0),CDec(Fields!col2.Value)))
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 10:05 am
Thank you very much. Your solution worked.
May 7, 2014 at 2:31 am
Now needed something else. placing the value of the negative col2 instead of 0.
I've tried doing the following ways and not resulted, where I am going wrong?
=SUM(IIF(Fields!col3.Value = "DEL",CDec(Fields!col2.Value)-(Fields!col2.Value),CDec(Fields!col2.Value)))
=SUM(IIF(Fields!col3.Value = "DEL",CDec(Fields!col2.Value)*(-1),CDec(Fields!col2.Value)))
May 7, 2014 at 7:29 am
Use
=SUM(IIF(Fields!col3.Value = "DEL",-CDec(Fields!col2.Value),CDec(Fields!col2.Value)))
or
=SUM(IIF(Fields!col3.Value = "DEL",CDec(Fields!col2.Value)*-1,CDec(Fields!col2.Value)))
I think putting -1 in brackets may be interpreted as a Boolean false
Far away is close at hand in the images of elsewhere.
Anon.
May 7, 2014 at 8:57 am
The value I have in col2 is 66, by placing the expression gets 2 when it should be -66?
Another example I have 60 and it appears as 4, when the result would be -60.
May 7, 2014 at 9:20 am
You are summing values so if there is a mix of DEL and non DEL rows (positive and negative values) then it is possible to have a positive value as a result
Far away is close at hand in the images of elsewhere.
Anon.
May 7, 2014 at 9:48 am
I'm so sorry but I could not understand ...: (
May 7, 2014 at 9:54 am
If your table contained
col1 col2 col3
test1 10 OK
test1 6 DEL
test2 5 OK
the logic would produce
col1 col2 col3
test1 10 OK
test1 -6 DEL
test2 5 OK
and when summed
col1 col2
test1 4
test2 5
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply