Search code examples
vbareporting-servicesssrs-2008-r2ssrs-expression

How to remove zeroes after the decimal point as an expression in SSRS

I have the following expression in one of my textbox in my SSRS Report:

=IIF(IsNothing(Lookup(Trim(Fields!venta_Cod_vendedor.Value) & "-" & ReportItems!Textbox233.Value, Fields!AgregarVentas.Value, Fields!venta_prom_sem.Value, "EfectividadDeFrecuencias_Ventas")) = True

,"0"

,IIF(Lookup(Trim(Fields!venta_Cod_vendedor.Value) & "-" & ReportItems!Textbox233.Value, Fields!Agregar.Value, Fields!total_cant_pos.Value, "EfectividadDeFrecuencias_Total") <> "0"

,FormatNumber(Lookup(Trim(Fields!venta_Cod_vendedor.Value) & "-" & ReportItems!Textbox233.Value, Fields!AgregarVentas.Value, Fields!venta_frecuencia.Value, "EfectividadDeFrecuencias_Ventas")
/ Lookup(Trim(Fields!venta_Cod_vendedor.Value) & "-" & ReportItems!Textbox233.Value, Fields!Agregar.Value, Fields!total_cant_pos.Value, "EfectividadDeFrecuencias_Total"),2)

,"0"))

That division will give me an int64 number, 15 digits (If such math operation gives that amount of decimal digits).

So the results are:

enter image description here

Now here is the tricky part: My code behind that grabs the Dataset does a round and converts to decimal and then shows to a Crystal Report.

dr.venta_prom_sem = (Convert.ToDouble(dr.total_cant_pos) != 0 ? (Math.Round((Convert.ToDouble(dr.venta_frecuencia) / Convert.ToDouble(dr.total_cant_pos)), 2)).ToString() : "0");

So this will give me:

enter image description here

as you can see if I use a format Number the 1.3 will convert to 1,30 and that will be wrong, same as 1 (1,00). Now 1,339...etc will give me 1,34 and that is fine.

But check the 1.065, with FormatNumber that will give me 1.07 instead of 1.06.

So the thing is, how can I format my numbers to be the last non zero digit after the decimal point AND select the lower value if the (in this case) 3rd value is 5, instead of 1.07 be 1.06. I think If I use Ceiling or Floor it gives me the integer part.

Solution

  • Try this:

    =ROUND(1.339,2,MidpointRounding.ToEven)

    This gives: 1.34

    And 

    =ROUND(1.065,2,MidpointRounding.ToEven)

    Gives: 1.06

    Let me know if this was helpful.