SQL Calculated Field CEILING or ROUNDUP

Sue Taylor
Sue Taylor used Ask the Experts™
SSRS 2012 - Visual Studio 2012 - SQL 2012
I have a field called "Trend" and a field called "Multiplier" that I was to multiple together.  Then I need to take the sum of those two fields and roundup to a multiple of a field called "Inv_Max"

For example:  Trend is 1572 and multiplier is 3.  (1572 * 3 = 4716)  The Inv_Max field is 2400 so I want to roundup the 4716 to 4800, which is a multiple of Inv_max.

I want to be able to add a calculated field and can't figure out the formula.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

I assumed integers for the data types.  If they aren't you can probably remove the CAST.

Logic is:  multiply them then divide.  Round up the remainder and multiple that by the max again:
select ceiling(cast(trend as float)*cast(multiplier as float)/inv_max)*inv_max from your_table;
Topic Advisor, Page Editor
Distinguished Expert 2018
Been a while.... but should be something like
=CEILING( (fields!trend.value * fields!multiplier.value * 1.0) / fields!inv_max.value) * fields!inv_max.value(

Open in new window

Is this something that could be pre-calculated in your dataset (on the server) ?

Just to add clarity to slightwv's post above....

server side you can : cast(trend as int)
SSRS side you use : =Cint(fields!trend.value)

The above does use integer arithmetic, so, might need to cast  CInt(fields!inv_max.value)

And, ROUNDUP() I was under the opinion you can use that in DAX,  For SSRS CEILING() is what you want.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial