Is it possible to format part of an expression in a textbox? I have this
expression which displays the parameter value(s) that are selected by the
user:
="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
I wish to put "Selected Region(s): " in bold. I can use a second textbox in
bold "Selected Region(s): " and leave just the expression in the other one,
but I'm trying to do everything in a single textbox.
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comYou cannot do this in RS 2005 (rich text in a single textbox)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:ON$sP4yQIHA.748@.TK2MSFTNGP04.phx.gbl...
> Is it possible to format part of an expression in a textbox? I have this
> expression which displays the parameter value(s) that are selected by the
> user:
> ="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
> I wish to put "Selected Region(s): " in bold. I can use a second textbox
> in bold "Selected Region(s): " and leave just the expression in the other
> one, but I'm trying to do everything in a single textbox.
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
Showing posts with label format. Show all posts
Showing posts with label format. Show all posts
Friday, March 30, 2012
Part of string
Need help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanxtry this
select substring('last,first',1,charindex('first,last'))
i hav'nt checked it.. I think u may have to alter it a little|||Assuming there is always a comma
declare @.name char(30)
set @.name = 'GATES, BILL'
select left(@.name,charindex(',',@.name)-1)
returns
GATES|||Isn't this your lucky day? I just adapted and updated this function yesterday off of some old Access Basic code I had.
Call it like this: "Select dbo.FormatName([YourNameString], 'L')" and it should give you what you want. It handles names in multiple formats. If you find a name string it won't handle, let me know so I can update it.
create function FormatName(@.NameString varchar(100), @.NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--FormatName decodes a NameString into its component parts and returns it in a requested format.
--@.NameString is the raw value to be parsed.
--@.NameFormat is a string that defines the output format. Each letter in the string represents
--a component of the name in the order that it is to be returned.
-- [H] = Full honorific
-- [h] = Abbreviated honorific
-- [F] = First name
-- [f] = First initial
-- [M] = Middle name
-- [m] = Middle initial
-- [L] = Last name
-- [l] = Last initial
-- [S] = Full suffix
-- [s] = Abbreviated suffix
-- [.] = Period
-- [,] = Comma
-- [ ] = Space
--Test variables
-- declare @.NameString varchar(50)
-- declare @.NameFormat varchar(20)
-- set @.NameFormat = 'L, h. F m. s.'
-- set @.NameString = 'Father Gregory Robert Von Finzer Jr'
Declare @.Honorific varchar(20)
Declare @.FirstName varchar(20)
Declare @.MiddleName varchar(30)
Declare @.LastName varchar(30)
Declare @.Suffix varchar(20)
Declare @.TempString varchar(100)
Declare @.IgnorePeriod char(1)
--Prepare the string
--Make sure each period is followed by a space character.
set @.NameString = rtrim(ltrim(replace(@.NameString, '.', '. ')))
--Eliminate double-spaces.
while charindex(' ', @.NameString) > 0 set @.NameString = replace(@.NameString, ' ', ' ')
--Eliminate periods
while charindex('.', @.NameString) > 0 set @.NameString = replace(@.NameString, '.', '')
--If the lastname is listed first, strip it off.
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString)))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString, len(@.TempString)+2)))
if right(@.TempString, 1) = ',' set @.LastName = left(@.TempString, len(@.TempString)-1)
if len(@.LastName) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get rid of any remaining commas
while charindex(',', @.NameString) > 0 set @.NameString = replace(@.NameString, ',', '')
--Get Honorific and strip it out of the string
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
if @.TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'MAJ', 'Major', 'PVT', 'Private', 'FATHER', 'SISTER') set @.Honorific = @.TempString
if len(@.Honorific) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get Suffix and strip it out of the string
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @.Suffix = @.TempString
if len(@.Suffix) > 0 set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
if @.LastName is null
begin
--Get LastName and strip it out of the string
set @.LastName = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.LastName)))
--Check to see if the last name has two parts
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @.LastName = @.TempString + ' ' + @.LastName
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
end
end
--Get FirstName and strip it out of the string
set @.FirstName = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.FirstName)))
--Anything remaining is MiddleName
set @.MiddleName = @.NameString
--Create the output string
set @.TempString = ''
while len(@.NameFormat) > 0
begin
if @.IgnorePeriod = 'F' or left(@.NameFormat, 1) <> '.'
begin
set @.IgnorePeriod = 'F'
set @.TempString = @.TempString +
case ascii(left(@.NameFormat, 1))
when '72' then case @.Honorific
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
when 'Hon' then 'Honorable'
when 'Maj' then 'Major'
when 'Pvt' then 'Private'
else isnull(@.Honorific, '')
end
when '70' then isnull(@.FirstName, '')
when '77' then isnull(@.MiddleName, '')
when '76' then isnull(@.LastName, '')
when '83' then case @.Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@.Suffix, '')
end
when '104' then case @.Honorific
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
when 'Honorable' then 'Hon'
when 'Major' then 'Maj'
when 'Private' then 'Pvt'
else isnull(@.Honorific, '')
end
when '102' then isnull(left(@.FirstName, 1), '')
when '109' then isnull(left(@.MiddleName, 1), '')
when '108' then isnull(left(@.LastName, 1), '')
when '115' then case @.Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@.Suffix, '')
end
when '46' then case right(@.TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@.TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@.TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
if ((ascii(left(@.NameFormat, 1)) = 72 and @.Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@.NameFormat, 1)) = 115 and @.Suffix in ('II', 'III')))
set @.IgnorePeriod = 'T'
end
set @.NameFormat = right(@.NameFormat, len(@.NameFormat) - 1)
end
-- select ltrim(rtrim(@.TempString))
Return @.TempString
end
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanxtry this
select substring('last,first',1,charindex('first,last'))
i hav'nt checked it.. I think u may have to alter it a little|||Assuming there is always a comma
declare @.name char(30)
set @.name = 'GATES, BILL'
select left(@.name,charindex(',',@.name)-1)
returns
GATES|||Isn't this your lucky day? I just adapted and updated this function yesterday off of some old Access Basic code I had.
Call it like this: "Select dbo.FormatName([YourNameString], 'L')" and it should give you what you want. It handles names in multiple formats. If you find a name string it won't handle, let me know so I can update it.
create function FormatName(@.NameString varchar(100), @.NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--FormatName decodes a NameString into its component parts and returns it in a requested format.
--@.NameString is the raw value to be parsed.
--@.NameFormat is a string that defines the output format. Each letter in the string represents
--a component of the name in the order that it is to be returned.
-- [H] = Full honorific
-- [h] = Abbreviated honorific
-- [F] = First name
-- [f] = First initial
-- [M] = Middle name
-- [m] = Middle initial
-- [L] = Last name
-- [l] = Last initial
-- [S] = Full suffix
-- [s] = Abbreviated suffix
-- [.] = Period
-- [,] = Comma
-- [ ] = Space
--Test variables
-- declare @.NameString varchar(50)
-- declare @.NameFormat varchar(20)
-- set @.NameFormat = 'L, h. F m. s.'
-- set @.NameString = 'Father Gregory Robert Von Finzer Jr'
Declare @.Honorific varchar(20)
Declare @.FirstName varchar(20)
Declare @.MiddleName varchar(30)
Declare @.LastName varchar(30)
Declare @.Suffix varchar(20)
Declare @.TempString varchar(100)
Declare @.IgnorePeriod char(1)
--Prepare the string
--Make sure each period is followed by a space character.
set @.NameString = rtrim(ltrim(replace(@.NameString, '.', '. ')))
--Eliminate double-spaces.
while charindex(' ', @.NameString) > 0 set @.NameString = replace(@.NameString, ' ', ' ')
--Eliminate periods
while charindex('.', @.NameString) > 0 set @.NameString = replace(@.NameString, '.', '')
--If the lastname is listed first, strip it off.
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString)))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString, len(@.TempString)+2)))
if right(@.TempString, 1) = ',' set @.LastName = left(@.TempString, len(@.TempString)-1)
if len(@.LastName) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get rid of any remaining commas
while charindex(',', @.NameString) > 0 set @.NameString = replace(@.NameString, ',', '')
--Get Honorific and strip it out of the string
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
if @.TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'MAJ', 'Major', 'PVT', 'Private', 'FATHER', 'SISTER') set @.Honorific = @.TempString
if len(@.Honorific) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get Suffix and strip it out of the string
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @.Suffix = @.TempString
if len(@.Suffix) > 0 set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
if @.LastName is null
begin
--Get LastName and strip it out of the string
set @.LastName = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.LastName)))
--Check to see if the last name has two parts
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @.LastName = @.TempString + ' ' + @.LastName
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
end
end
--Get FirstName and strip it out of the string
set @.FirstName = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.FirstName)))
--Anything remaining is MiddleName
set @.MiddleName = @.NameString
--Create the output string
set @.TempString = ''
while len(@.NameFormat) > 0
begin
if @.IgnorePeriod = 'F' or left(@.NameFormat, 1) <> '.'
begin
set @.IgnorePeriod = 'F'
set @.TempString = @.TempString +
case ascii(left(@.NameFormat, 1))
when '72' then case @.Honorific
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
when 'Hon' then 'Honorable'
when 'Maj' then 'Major'
when 'Pvt' then 'Private'
else isnull(@.Honorific, '')
end
when '70' then isnull(@.FirstName, '')
when '77' then isnull(@.MiddleName, '')
when '76' then isnull(@.LastName, '')
when '83' then case @.Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@.Suffix, '')
end
when '104' then case @.Honorific
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
when 'Honorable' then 'Hon'
when 'Major' then 'Maj'
when 'Private' then 'Pvt'
else isnull(@.Honorific, '')
end
when '102' then isnull(left(@.FirstName, 1), '')
when '109' then isnull(left(@.MiddleName, 1), '')
when '108' then isnull(left(@.LastName, 1), '')
when '115' then case @.Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@.Suffix, '')
end
when '46' then case right(@.TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@.TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@.TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
if ((ascii(left(@.NameFormat, 1)) = 72 and @.Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@.NameFormat, 1)) = 115 and @.Suffix in ('II', 'III')))
set @.IgnorePeriod = 'T'
end
set @.NameFormat = right(@.NameFormat, len(@.NameFormat) - 1)
end
-- select ltrim(rtrim(@.TempString))
Return @.TempString
end
Wednesday, March 28, 2012
Parsing Variable Length Delimited Records
I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg
SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.
Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.
-Jamie
Parsing Variable Length Delimited Records
I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg
SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.
Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.
-Jamie
Friday, March 23, 2012
Parent child reports problems
Hi,
I have a report which I have created 3 or 4 child reports from.
If I make a change to the format of an object within the parent report
(e.g. the colour of a text box) then the change appears on the child
reports as I would expect.
The problem is that if I make a change to the properties of the report
(e.g. the margins or the page size) then the change is NOT picked up
in the child reports.
Does anybody have any solution to this annoying problem.
Regards,
JamesOn Nov 6, 4:33 am, joli...@.googlemail.com wrote:
> Hi,
> I have a report which I have created 3 or 4 child reports from.
> If I make a change to the format of an object within the parent report
> (e.g. the colour of a text box) then the change appears on the child
> reports as I would expect.
> The problem is that if I make a change to the properties of the report
> (e.g. the margins or the page size) then the change is NOT picked up
> in the child reports.
> Does anybody have any solution to this annoying problem.
> Regards,
> James
This might not be exactly what you're looking for, but you could use a
comparison tool to compare the RDL files side-by-side and merge the
changes pretty quickly. There's a trial version of a tool I like to
use here: http://www.araxis.com/merge/
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
I have a report which I have created 3 or 4 child reports from.
If I make a change to the format of an object within the parent report
(e.g. the colour of a text box) then the change appears on the child
reports as I would expect.
The problem is that if I make a change to the properties of the report
(e.g. the margins or the page size) then the change is NOT picked up
in the child reports.
Does anybody have any solution to this annoying problem.
Regards,
JamesOn Nov 6, 4:33 am, joli...@.googlemail.com wrote:
> Hi,
> I have a report which I have created 3 or 4 child reports from.
> If I make a change to the format of an object within the parent report
> (e.g. the colour of a text box) then the change appears on the child
> reports as I would expect.
> The problem is that if I make a change to the properties of the report
> (e.g. the margins or the page size) then the change is NOT picked up
> in the child reports.
> Does anybody have any solution to this annoying problem.
> Regards,
> James
This might not be exactly what you're looking for, but you could use a
comparison tool to compare the RDL files side-by-side and merge the
changes pretty quickly. There's a trial version of a tool I like to
use here: http://www.araxis.com/merge/
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Wednesday, March 7, 2012
Parameters formatting
Hello Everyone,
Is it possible to format the textboxes that are used for parameters.
For example a string field is double the length we require. We show 9
characters but it show 27 characters.
This is a great product, the user friendliness of the parameters is
causing issues with the directors and senior management.
Please help me as I want to keep using this.
Thanks
MichaelI posted a sample yesterday on www.MSBICentral.com
The sample is Parameters.FormattedDates.RDL... It shows how to format a date
for the parameters... I think this will show you how to do what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Michael van der Veeke" <michael@.property4view.com> wrote in message
news:uNVrXYyEFHA.624@.TK2MSFTNGP15.phx.gbl...
> Hello Everyone,
> Is it possible to format the textboxes that are used for parameters. For
> example a string field is double the length we require. We show 9
> characters but it show 27 characters.
> This is a great product, the user friendliness of the parameters is
> causing issues with the directors and senior management.
> Please help me as I want to keep using this.
> Thanks
> Michael
Is it possible to format the textboxes that are used for parameters.
For example a string field is double the length we require. We show 9
characters but it show 27 characters.
This is a great product, the user friendliness of the parameters is
causing issues with the directors and senior management.
Please help me as I want to keep using this.
Thanks
MichaelI posted a sample yesterday on www.MSBICentral.com
The sample is Parameters.FormattedDates.RDL... It shows how to format a date
for the parameters... I think this will show you how to do what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Michael van der Veeke" <michael@.property4view.com> wrote in message
news:uNVrXYyEFHA.624@.TK2MSFTNGP15.phx.gbl...
> Hello Everyone,
> Is it possible to format the textboxes that are used for parameters. For
> example a string field is double the length we require. We show 9
> characters but it show 27 characters.
> This is a great product, the user friendliness of the parameters is
> causing issues with the directors and senior management.
> Please help me as I want to keep using this.
> Thanks
> Michael
Subscribe to:
Posts (Atom)