Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS - Formatting a multi-value field?? Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:02 AM
Points: 5, Visits: 24
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.


Post #1597980
Posted Wednesday, July 30, 2014 3:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 3,636, Visits: 8,152
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598004
Posted Wednesday, July 30, 2014 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:02 AM
Points: 5, Visits: 24
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.
Post #1598015
Posted Wednesday, July 30, 2014 3:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 3,636, Visits: 8,152
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598019
Posted Saturday, August 2, 2014 2:48 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:04 AM
Points: 33, Visits: 58
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.
Post #1598860
Posted Sunday, August 3, 2014 6:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 3,636, Visits: 8,152
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1599092
Posted Sunday, August 3, 2014 10:22 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:04 AM
Points: 33, Visits: 58
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.
Post #1599114
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse