none
Values to be shown in Thousands millions and billions in Report Builder 2.0 RRS feed

  • Question

  • Hi,
        I want to show the values of the amount in thousands,millions and billions.Though the functionality is given in the currency property of the textbox properties,I would like to place it in the parametric field.So that when a user choose to see the values in Thousands he can see all the amount fields in Thousands when millions is chosen then millions so on and so forth. How can this be achieved in Report Builder 2.0 ? Thanks in Advance.
    Thanks and Regards, Franco Lopes. Ask a Question and you are fool for a second but if don't ask a question you are fool forever.
    Thursday, January 7, 2010 9:19 AM

Answers

All replies

  • Franco,

    That should be implemented at the database level to ensure report accuracy.

    Setup a stored procedure with display number format parameter for thousands, millions, etc.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, January 7, 2010 10:39 AM
  • Hi SQLUSA,
                      Thank you for your reply.What I want is that I will provide the users with an option with thousands ,millions and billions in a drop down in  parameter by which they will be able to  choose which ever  way they want to view data they can view it.By just selecting  the drop down  option should be able to view data in that particular format.How can I do this? Will it be possible in RB2 without writing any Stored Procedure?  Thanks in advance

    Thanks and Regards, Franco Lopes. Ask a Question and you are fool for a second but if don't ask a question you are fool forever.
    Thursday, January 7, 2010 11:23 AM
  • In BIDS, you can develop a conditional expression based on the input parameter to set the format.

    The following format is for millions display: '$'0,,.00;('$'0,,.00)

    This format is for thousands display: '$'0,.00;('$'0,.00)

    Billions: '$'0,,,.00;('$'0,,,.00)

    You can set it at different levels, including data region (tablix) level.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, January 7, 2010 12:35 PM
  • Can you try something similar to this on the report DATASET?

    Though i have not mentioned the zeros correctly, i hope you ll figure it out.

    Select colum1,
    case
    when @parameter = 'Million' then Amount/10000
    when @parameter = 'Billion' then amount/100000
    when @parameter = 'Trillion' then Amount/1000000
    else amount
    end as AMOUNT

     from AccountTABLE

    or if you encounter some datatype mismatches, try something like and make modifications on to your report parameter.

    Select colum1,
    case
    when @parameter = 1 then Amount/10000
    when @parameter = 2 then amount/100000
    when @parameter = 3 then Amount/1000000
    else amount
    end as AMOUNT

     from AccountTABLE


    Let me know if it helped.
    Thursday, January 7, 2010 12:50 PM