Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Table two Column Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 4:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:16 AM
Points: 39, Visits: 149
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?
Post #1565847
Posted Tuesday, April 29, 2014 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 7,050, Visits: 6,799
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.

Post #1565870
Posted Tuesday, April 29, 2014 6:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:16 AM
Points: 39, Visits: 149
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
Post #1565889
Posted Tuesday, April 29, 2014 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 7,050, Visits: 6,799
Either change the sql query
SELECT col1, CASE WHEN col3 = 'DEL' THEN 0 ELSE col2 END AS [col2], col3 
FROM [table]

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.

Post #1565891
Posted Tuesday, April 29, 2014 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:16 AM
Points: 39, Visits: 149
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)))
Post #1566003
Posted Tuesday, April 29, 2014 9:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 7,050, Visits: 6,799
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.

Post #1566012
Posted Tuesday, April 29, 2014 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:16 AM
Points: 39, Visits: 149
I have a value 59.90

I can not use numbers with decimals?
Post #1566017
Posted Tuesday, April 29, 2014 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 7,050, Visits: 6,799
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.

Post #1566023
Posted Tuesday, April 29, 2014 10:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:16 AM
Points: 39, Visits: 149
Thank you very much. Your solution worked.
Post #1566032
Posted Wednesday, May 7, 2014 2:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:16 AM
Points: 39, Visits: 149
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)))
Post #1568328
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse