sqlsql-serverdatabasereporting-servicesdecimal

How to set decimal precision dynamically using a variable


I am trying to set decimal precision dynamically in a stored procedure like as below

 DECLARE @precison int
 select @precison = [ConfigValue] from [TestData] WHERE ConfigName = 'Precision'
 select CAST( (10.56/10.25)as decimal(18, @precision))

If precision is '2' SP should return '1.03' as result.But cast function is not accepting @precision parameter as variable .I want to return data as decimal itself from stored procedure.How can i achieve this in SQL Server


Solution

  • The problem here is you have entirely the wrong idea. Formatting of data should be handled in the Presentation layer (in this case SSRS) not the RDBMS. Though you could dynamic declare something with a dynamic precision or scale, you would have to use dynamic SQL and that could severely overly complicate the matter and would likely break your SSRS report unless you understand how you implement dynamic statements successfully into SSRS.

    Instead, you should be using the presentation layer to determine the formatting. This isn't particularly easy in SSRS, but it's also not difficult. Lets assume you have a parameter, Precision, in your SSRS report, which is defined as an Integer. I'm also going to use a basic data set from the below SQL:

    SELECT d
    FROM (VALUES(10),(10.1),(10.12),(10.123),(10.1234))V(d);
    

    This means you might have a report that looks a little like this:

    enter image description here

    Right click the blank space, and select Report Properties and go to the Code pane. Then enter the below code:

    Public Function DynamicPrecision(ByVal Precision AS Integer) AS String
    
        Dim I AS Integer
        If Precision = 0
            DynamicPrecision = "#,#"
        Else
            DynamicPrecision = "#,#."
            For I = 1 to Precision
                DynamicPrecision = DynamicPrecision & "0"
            Next I
        End If
    End Function
    

    Now click your cell (or hold control and click the cells) you want the dynamic precision on and then press F4 to open the properties pane. Fine Format in the Properties Pane and then then drop down and select "«expression»". Then enter the following expression:

    =Code.DynamicPrecision(Parameters!Precision.Value)
    

    Your cell will then dynamic format to that precision.

    So, for 0,2, and 4, the data would look like this:

    enter image description here

    enter image description here

    enter image description here