SQL Calculated Field CEILING or ROUNDUP

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.
Sue TaylorProject ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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;
Mark WillsTopic AdvisorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.