Round Function in Derived column transformation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Round Function in Derived column transformation

Author

Topic

loveyaseen
Starting Member

1 Post

Posted - 2007-10-31 : 09:43:55

Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help

Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194

The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.

tm
Posting Yak Master

160 Posts

Posted - 2007-10-31 : 10:22:49

There are various ways to do this.

If you use round in dataset (use T-SQL) you can use round(SomeValue, -2)

If you are using expression in the report you can use either:

=round((SomeFieldValue / 100), 0) * 100
Example: =round((16221 / 100, 0) * 100

OR

=someFieldvalue - (someFieldvalue Mod 100)
Example: =16221 - (16221 Mod 100)

tm
Posting Yak Master

160 Posts

Posted - 2007-10-31 : 10:32:44

Oops .. Sorry I am giving wrong info. I was looking at SSRS. Sorry!

tm
Posting Yak Master

160 Posts

Posted - 2007-10-31 : 10:44:37

loveyaseen,

It is basically the same in SSIS.
In the derived columns use:

round((SomeFieldValue / 100), 0) * 100
Example: round((16221 / 100, 0) * 100

OR

someFieldvalue - (someFieldvalue % 100)
Example: 16221 - (16221 % 100)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources