SSRS - Formatting a multi-value field??

  • Hey guys,

    got an issue I'm trying to workout. I have report in SSRS that in one of the fields, it returning multiple values separated by a comma. Which is what I need, however. When trying to format the values into currency, i get an #error. the field is dynamic so one row can have one value but a second row can have two or three values.

    When I format the text box to be currency, if the row has only one value, it converts fine, but when it was more than one, I get an error. I figure I have to use an expression, but I am not sure how 😕

    Field

    Number1

    Number1, Number2

    Number1,Number2, Number3

    Current Results

    Field

    Number1 = $Number1

    Number1, Number2 = #Error

    Number1,Number2, Number3 = #Error

    Need it to look like this

    Field

    Number1 = $Number1

    Number1, Number2 = $Number1, $Number2

    Number1,Number2, Number3 = $Number1, $Number2, Number3

    Thanks in advance for the help.

  • If you're receiving the values as a comma-separated list, then you should format them in the back-end. In other words, you need to format the values before the concatenation.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I am getting the values from a separated comma list(see syntax below) If I can just multiply the value times .01 that would be sufficient.

    STUFF(( SELECT ', ' + svalue FROM dbo.F_DoubleSplitter(column_A, '&', '=') AS fm

    WHERE fm.mid=2

    FOR

    XML PATH('')

    ), 1, 1, ' ')

    Thanks for the help.

  • Something like this?

    SELECT STUFF(( SELECT ', ' + '$' + STUFF( svalue, LEN( svalue) - 1, 0, '.') FROM (VALUES('1299'),('397'),('1500'),('11001'))x(svalue)

    FOR

    XML PATH('')

    ), 1, 2, ' ')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi

    I recommend create a nice function on your database.

    CREATE FUNCTION [dbo].[getParmsFromString]

    (@String VARCHAR(MAX))

    RETURNS @Parms TABLE

    (

    Token VARCHAR(MAX)

    )

    AS

    BEGIN

    IF CHARINDEX(',', @String) != 0

    BEGIN

    ;WITH cte0(Token, List) AS

    (

    SELECT SUBSTRING(@String, 1, CHARINDEX(',',@String,1) - 1)

    ,SUBSTRING(@String,CHARINDEX(',',@String,1) + 1, LEN(@String)) + ','

    UNION ALL

    SELECT SUBSTRING(List,1,ISNULL(CHARINDEX(',',List,1) - 1,1))

    ,SUBSTRING(List,CHARINDEX(',',List,1) + 1, LEN(List))

    FROM cte0

    WHERE LEN(cte0.List) > 0

    )

    INSERT INTO @Parms (Token)

    SELECT Token

    FROM cte0

    OPTION (MAXRECURSION 0)

    RETURN;

    END

    ELSE

    INSERT INTO @Parms

    SELECT @String

    RETURN;

    END

    GO

    To use this function all you need to do is call it and parse the Paramater value.

    The return of this function will give you a table with each Currency in the Paramater value as a single row.

    For example

    String1 = 'Dollar'

    String2 = '1,2,3,4,5'

    select * from dbo.getParmsFromString(String1) will result in

    Dollar

    select * from dbo.getParmsFromString(String2 ) will result in

    1

    2

    3

    4

    5

    Hope this will help you solve your problem.

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
    http://www.sql-sa.co.za

  • Hi Daniel,

    The problem in this case is not about splitting but about joining the values.

    Another problem is that you're using a multi-statement table valued function that uses a recursive CTE. That can be a real performance problem. For a blazing fast function, I'd recommend you to read the following article which compares several options to split strings.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi Luis

    Thanks for the link. Will have a look at it.

    Regards Daniel

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
    http://www.sql-sa.co.za

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply