July 30, 2014 at 2:40 pm
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.
July 30, 2014 at 3:18 pm
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.
July 30, 2014 at 3:40 pm
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.
July 30, 2014 at 3:53 pm
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, ' ')
August 2, 2014 at 2:48 am
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
August 3, 2014 at 6:39 pm
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.
August 3, 2014 at 10:22 pm
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