February 21, 2012 at 5:35 am
I'm populating a textbox (which is going to be the title of a report) by using the following expression:
=First(Fields!Title.Value, "ReportTitleDataset")
The expression is calling a dataset which runs the following code against a database:
ReportTitleDataset
SELECT TOP 1 [Col1], CAST([Col2] AS nvarchar(2000)) "Title"
FROM [Schema].[Table]
WHERE [LCode] = @Parm1
AND ([MCode] = @Parm2
OR [MCode] IS NULL)
AND [Key] = 'ReportName'
ORDER BY [MCode] DESC
Everything works fine and the correct Report Title is returned except that the text returned spills onto a second line after the second word and doesn't seem to follow the Alignment property of the TextBox(Centralised). The text box is the width of the page and the length of the string returned by the query is 19 characters long (pseudo code: SELECT LEN([Col2]) FROM [Schema].[Table] - returns '19' (it's a short report title).
The datatype in the database table is char(2000).
When I use SELECT PATINDEX (Char(10), [Col2]) the result is '0' --<-- Looking for Line Feeds, none are found.
When I use SELECT PADINDEX (Char(13), [Col2]) the result is '0' --<-- Looking for Carraige Returns, none are found.
NB. When I hardcode the textbox with text instead of using an Expression, for example with "Report title goes here as a test" (ie. a really long string) there is no problem and the text does not spill onto a second line.
What is going wrong as this only seems to affect a few reports? Is it the conversion from char to nvarchar?
February 21, 2012 at 8:49 am
See if this helps:
REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CAST([Col2] AS nvarchar(2000)))),' ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),'') "Title"
First this trims all leading and trailing spaces, and then replaces all instances of more than one contiguous space with a single space.
February 22, 2012 at 3:32 am
I'll give that a go, thanks for your reply. What's strange is that when I run the query manually on the database (ie. not through the report) there is no evidence of trailing spaces or 'formatting' in the string returned. Why would there be, it's Char and only 19 characters long with a letter and/or space accounting for all nineteen characters.
It's certainly a strange one!
February 24, 2012 at 9:33 am
This jiggery-pokery on the data worked a treat and cured the problem. I couldn't see any strange formatting or spaces in the data but obviously there is some! Thanks again for your help and excellent assistance!
February 24, 2012 at 11:20 am
Yes, I have this in my code snippets file. I use it often.
Glad to help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply