I have put following expression in a textbox.
="My name is " + First(Fields!Brok_Name.Value, "myDataset")
I just want to Bold the Field value and not the word "My name is ".
I also want to justify the text Alignment, how can I ?
I am using Sql Server Reporting Service 2005.
Any idea please ?
Best Regards,
LuqmanLuqman,
I don't think multiple formatting within a single text box is supported. You
might be able to embed an html character in the string like this ...>="My
name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>" ...never
tried it but maybe it will work?
I have always used 2 text boxes. One for the label "My name is " and another
for the field value. Then I right align the label and left align the field
value. As far as justifying the complete string "My name is " + Field ...not
sure if it is possible with different bold values?
luqman wrote:
>I have put following expression in a textbox.
>="My name is " + First(Fields!Brok_Name.Value, "myDataset")
>I just want to Bold the Field value and not the word "My name is ".
>I also want to justify the text Alignment, how can I ?
>I am using Sql Server Reporting Service 2005.
>Any idea please ?
>Best Regards,
>Luqman
--
Message posted via http://www.sqlmonster.com|||Hi,
The Html character <B> within the text is showing as text.
My expression is :
="My name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
Output is :
My name is <B> luqman </B>
Am I missing something ?
Best Regards,
Luqman
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7829016629ac6@.uwe...
> Luqman,
> I don't think multiple formatting within a single text box is supported.
You
> might be able to embed an html character in the string like this ...>="My
> name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
...never
> tried it but maybe it will work?
> I have always used 2 text boxes. One for the label "My name is " and
another
> for the field value. Then I right align the label and left align the
field
> value. As far as justifying the complete string "My name is " + Field
...not
> sure if it is possible with different bold values?
>
> luqman wrote:
> >I have put following expression in a textbox.
> >
> >="My name is " + First(Fields!Brok_Name.Value, "myDataset")
> >
> >I just want to Bold the Field value and not the word "My name is ".
> >
> >I also want to justify the text Alignment, how can I ?
> >
> >I am using Sql Server Reporting Service 2005.
> >
> >Any idea please ?
> >
> >Best Regards,
> >
> >Luqman
> --
> Message posted via http://www.sqlmonster.com
>|||Luqman,
It was just a shot in the dark...guess it did not work. I don't think it is
possible to have multiple formats within the same textbox.
luqman wrote:
>Hi,
>The Html character <B> within the text is showing as text.
>My expression is :
>="My name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
>Output is :
>My name is <B> luqman </B>
>Am I missing something ?
>Best Regards,
>Luqman
>> Luqman,
>[quoted text clipped - 26 lines]
>> --
>> Message posted via http://www.sqlmonster.com
--
Message posted via http://www.sqlmonster.com|||Hi,
In case, the textbox does not support multiple formats, can we display RTF
Text inside a textbox, saved in a database.
Best Regards,
Luqman
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7834e5fdfd74f@.uwe...
> Luqman,
> It was just a shot in the dark...guess it did not work. I don't think it
is
> possible to have multiple formats within the same textbox.
>
> luqman wrote:
> >Hi,
> >
> >The Html character <B> within the text is showing as text.
> >
> >My expression is :
> >="My name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
> >
> >Output is :
> >My name is <B> luqman </B>
> >
> >Am I missing something ?
> >
> >Best Regards,
> >
> >Luqman
> >
> >> Luqman,
> >>
> >[quoted text clipped - 26 lines]
> >> --
> >> Message posted via http://www.sqlmonster.com
> --
> Message posted via http://www.sqlmonster.com
>|||Luqman,
That's my extent of the knowledge on the subject...Sorry.
luqman wrote:
>Hi,
>In case, the textbox does not support multiple formats, can we display RTF
>Text inside a textbox, saved in a database.
>Best Regards,
>Luqman
>> Luqman,
>[quoted text clipped - 25 lines]
>> --
>> Message posted via http://www.sqlmonster.com
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200709/1|||On Sep 17, 10:55 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Luqman,
> That's my extent of the knowledge on the subject...Sorry.
> luqman wrote:
> >Hi,
> >In case, the textbox does not support multiple formats, can we display RTF
> >Text inside a textbox, saved in a database.
> >Best Regards,
> >Luqman
> >> Luqman,
> >[quoted text clipped - 25 lines]
> >> --
> >> Message posted viahttp://www.sqlmonster.com
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200709/1
You can't do it in 2005. I saw a video on reporting services 2008,
and it's a new feature microsoft is adding. Below is some links on
it:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2122612&SiteID=1
http://sqljunkies.com/WebLog/sqlbi/archive/2007/06/07/35631.aspx
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Friday, March 30, 2012
Part of textbox content in bold, is it possible ?
We're using reporting services 2000 sp2, i'd like to know if it is possible
to set in bold just part of the text in a textbox. We sometimes want ot
highlight a few words out of a sentence in our reports. We're mostly using
table controls to build the reports.
Thanks!That is not possible, would need to split into two or more textboxes.
Steve MunLeeuw
"/dev/null" <devnull@.discussions.microsoft.com> wrote in message
news:3F445BA7-11B8-40C4-B594-E1798A0351CA@.microsoft.com...
> We're using reporting services 2000 sp2, i'd like to know if it is
> possible
> to set in bold just part of the text in a textbox. We sometimes want ot
> highlight a few words out of a sentence in our reports. We're mostly
> using
> table controls to build the reports.
> Thanks!
to set in bold just part of the text in a textbox. We sometimes want ot
highlight a few words out of a sentence in our reports. We're mostly using
table controls to build the reports.
Thanks!That is not possible, would need to split into two or more textboxes.
Steve MunLeeuw
"/dev/null" <devnull@.discussions.microsoft.com> wrote in message
news:3F445BA7-11B8-40C4-B594-E1798A0351CA@.microsoft.com...
> We're using reporting services 2000 sp2, i'd like to know if it is
> possible
> to set in bold just part of the text in a textbox. We sometimes want ot
> highlight a few words out of a sentence in our reports. We're mostly
> using
> table controls to build the reports.
> Thanks!
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
parsing XML string in a text field
I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>
parsing XML string in a text field
I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>
Wednesday, March 28, 2012
parsing varchar fields
What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
--
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries using
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
--
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922.53434faa9cedec41603cbc091e9f06a8@.t eranews...
> Jerry Spivey opined thusly on Sep 24:
>> Scott,
>>
>> Instead of changing the existing product's tables why not create an
>> additional table(s) to store these values and then write your queries
>> using
>> joins.
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic code
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.
> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowing
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
--
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738.f429cee989a6daeb985244c70f0bb497@.t eranews...
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> I am not sure why you think the users would want to enter some cryptic
>> code
>> rather than exact values in two separate fields.
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>> That is more of a usability
>> with the UI, for instance if your parts were both numeric you may be
>> slowing
>> your users down forcing them to use non-keypad characters that require
>> shifting. You should also look at the business processes, for instance if
>> you processed everything for one vendor together then retaining the
>> vendor
>> information would remove the need to type it in again.
>>
>> If you need to concatenate the values for display purposes then you can
>> do
>> that in the code for your stored procedure or create a view.
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
--
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Scott Marquardt opined thusly on Sep 24:
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> If you are not writing a new interface then you are probably better using a
>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
>> a fixed length then you don't need a separator, but it may help the users
>> differentiate the different parts. I would have expect that you can
>> determine the fact that an invoice is a referral by a different means.
> Well, we could get into "I would have expected" exchanges, or we can skip
> the stuff I already know -- and posted in the original post. With all due
> respect, "duh."
> Good grief. Outta here.
John., I'm going to apologize for a short temper there. It was born of
impatience.
My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with it.
"Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart"
"Refund for can of beans, 100-3123&WidgetMart"
The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.
--
Scott|||>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<
Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.|||Scott,
Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).
Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.
Just thinking aloud,
Stu|||Hi Erland/Steve
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> If you are not writing a new interface then you are probably better
>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>> each part is a fixed length then you don't need a separator, but it may
>> help the users differentiate the different parts. I would have expect
>> that you can determine the fact that an invoice is a referral by a
>> different means.
>
> Now, how is that for a user interface? This is free text, and users will
> mangle it anyway, but they have better odds with a separator than fixed
> length.
That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.
> I have not intervened into this thread before, because I don't have
> much advice to offer Steve in this dire situation.
It seems to me, that if this an off the shelf package the benefits has been
lost some time ago, if this is a bespoke development then it has not been
specified thoroughly enough.
> Hm, possibly could have a trigger on the table that loaded a new table
> with the fields split in pieces. Still iffy with regards to the vendor,
> but at least no existing object is changed. (Then again, being on the
> vendor side of the fence, I know what I would think if I found that one
> of our customers had added a trigger to one of our tables.)
Writing a trigger may still invalidate the contract as the transactions will
be effected, the vendor may have the right to charge improportate fees to
fix a problem even if a problem is not related to this table. But if Steve
has already tweeked things that could already be the case! A trigger would
be useful for validation even if the replication route is not taken. Then
again Steve was worried about upgrades breaking things.
Dire may be an understatement!
Steve has my sympathy.
John
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Nothing seems to be going right in this thread!
Who is Steve?
Sorry Scott!
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:43364302$0$14057$da0feed9@.news.zen.co.uk...
> Hi Erland/Steve
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
>> John Bell (jbellnewsposts@.hotmail.com) writes:
>>> If you are not writing a new interface then you are probably better
>>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>>> each part is a fixed length then you don't need a separator, but it may
>>> help the users differentiate the different parts. I would have expect
>>> that you can determine the fact that an invoice is a referral by a
>>> different means.
>>
>>
>> Now, how is that for a user interface? This is free text, and users will
>> mangle it anyway, but they have better odds with a separator than fixed
>> length.
> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it
> for themselves.
>>
>> I have not intervened into this thread before, because I don't have
>> much advice to offer Steve in this dire situation.
>>
> It seems to me, that if this an off the shelf package the benefits has
> been lost some time ago, if this is a bespoke development then it has not
> been specified thoroughly enough.
>> Hm, possibly could have a trigger on the table that loaded a new table
>> with the fields split in pieces. Still iffy with regards to the vendor,
>> but at least no existing object is changed. (Then again, being on the
>> vendor side of the fence, I know what I would think if I found that one
>> of our customers had added a trigger to one of our tables.)
> Writing a trigger may still invalidate the contract as the transactions
> will be effected, the vendor may have the right to charge improportate
> fees to fix a problem even if a problem is not related to this table. But
> if Steve has already tweeked things that could already be the case! A
> trigger would be useful for validation even if the replication route is
> not taken. Then again Steve was worried about upgrades breaking things.
> Dire may be an understatement!
> Steve has my sympathy.
> John
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techin.../2000/books.asp
>>|||Hi Scott
Non taken!
You can't always give good advice, sometimes the best you can do is throw
ideas into the pot.
Using a scheduled job may be less of an preblem when considering
invalidating the contract! Although running it once a minute may cause
issues such as blocking.
I have assumed that the information you are entering is not available on
this system, therefore it is not possible to pick it up at report time from
other fields.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127610853.841b341db0737c3d0f8b5850b289ecae@.t eranews...
> Scott Marquardt opined thusly on Sep 24:
>> John Bell opined thusly on Sep 24:
>>> Hi
>>>
>>> If you are not writing a new interface then you are probably better
>>> using a
>>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part
>>> is
>>> a fixed length then you don't need a separator, but it may help the
>>> users
>>> differentiate the different parts. I would have expect that you can
>>> determine the fact that an invoice is a referral by a different means.
>>
>> Well, we could get into "I would have expected" exchanges, or we can skip
>> the stuff I already know -- and posted in the original post. With all due
>> respect, "duh."
>>
>> Good grief. Outta here.
> John., I'm going to apologize for a short temper there. It was born of
> impatience.
> My principal interest was in hearing how anyone else might PARSE the field
> to recover the data. Erland's trigger idea isn't bad for WHAT I do with
> it.
> "Invoice for 1/2 inch puce widgets and a can of beans,
> 100-3123@.WidgetMart"
> "Refund for can of beans, 100-3123&WidgetMart"
> The second one would need to be tagged as a referral to the first one. But
> if that's my basic technique, they're parse out the same way.
> --
> Scott|||--CELKO-- opined thusly on Sep 24:
>>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<
> Since free text requires human intelligence, we get people to encode
> it. If the text is short and styled the same way, you can find AI
> programs that are pretty good these days. Of course this has nothing
> to do with RDBMS and nobody in their right mind would try to do it in
> SQL.
Hey! How'd you find out about my condition?
;-)
--
Scott|||Stu opined thusly on Sep 24:
> Scott,
> Have you considered an ActiveX script in a DTS package to shred the
> data periodically (you can use the SQL Scheduler to run it once a
> minute). We do this to parse out syslog tables; it's kludgy, but its
> consistent. While not as immediate as a trigger to populate a table,
> it does keep you from modifying the vendor database (which, if your
> vendor has the ability to update their database, could be a big
> benefit).
> Obviously, your users would have to be trained on how to enter data
> consistently, and you'd probably want some sort of validation to pick
> up on coded entries that don't meant your
> data-followed-by-a-comma-followed-by-more-data rules. You may even
> look at Regular Expressions inside your DTS package to help with that.
> Just thinking aloud,
> Stu
Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!
I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.
--
Scott|||John Bell opined thusly on Sep 25:
> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it for
> themselves.
In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)
The vendor's likely to be cool with this. I'm documenting things pretty
well, and a few of my other hacks to the system may end up as features.
They have an aggressive development cycle and are responsive. Best of
worlds for trying stuff.
--
Steve ;-)|||Scott Marquardt at wit's-end:
> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
> unless you'd care to name some good controls. Haven't tried any, ever,
> really. It's been nice to have never needed to!
> I anticipate we'll see now more than 3000 such records a year, so this is
> probably something that could be done at runtime. Users won't be running
> anything that queries a result of this, so parsing it all out when running
> reports (not sure where to implement that, yet, but it won't be in the
> application itself) is an inelegant but tenable option.
More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/sc...er/default.mspx
It'll either drive you over the edge, or restore your sanity.
GeoSynch|||GeoSynch opined thusly on Sep 27:
> Scott Marquardt at wit's-end:
>> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
>> unless you'd care to name some good controls. Haven't tried any, ever,
>> really. It's been nice to have never needed to!
>> I anticipate we'll see now more than 3000 such records a year, so this is
>> probably something that could be done at runtime. Users won't be running
>> anything that queries a result of this, so parsing it all out when running
>> reports (not sure where to implement that, yet, but it won't be in the
>> application itself) is an inelegant but tenable option.
> More stuff to drive you even crazier - Log Parser from Microsoft:
> http://www.microsoft.com/technet/sc...er/default.mspx
> It'll either drive you over the edge, or restore your sanity.
No. You don't understand. We're so understaffed and overbooked that I could
have this operating right now somewhere, in a scheduled task or a script,
and I might not even know about it. I can't begin to count the number of
things we've been grateful for and implemented without documenting it.
That's the world of education, folks! ;-)
Hey! Download to the usual folder asks whether to overwrite. Guess I've got
it already! ;-)
--
Scott|||Scott Marquardt opined thusly on Sep 25:
> John Bell opined thusly on Sep 25:
>> That was my point, guess I should have said it that way! The users will
>> (probably) not like having to enter a coded string, even if you keep it as
>> simple! They may even deliberately mangle it if there is no value in it for
>> themselves.
> In this case, the only users needing to enter these have the only stake in
> these particular data points. This isn't something they'll grumble about;
> in this case, they're the ones demanding it. ;-)
OK, this is maybe (maybe) the kludgiest thing I've done in a while. If
anyone has any ideas for cleaning it up, I'm all ears. It works, though.
| select
| case
| when charindex(' ',ltrim(rtrim(comment))) < charindex('@.',ltrim(rtrim(comment))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| right(left(comment, charindex('@.',ltrim(rtrim(comment)))-1) ,charindex(' ', reverse(left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-2))))
| else
| left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-1)
| end
| as InvoiceNumber,
| case
| when charindex(' ',ltrim(rtrim(reverse(comment)))) < charindex('@.',ltrim(rtrim(reverse(comment)))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| left(right(comment, charindex('@.',ltrim(rtrim(reverse(comment))))-1),charindex(' ',ltrim(rtrim(reverse(comment)))))
| else
| reverse(left( ltrim(rtrim(reverse(comment))), charindex('@.',ltrim(rtrim(reverse(comment))))-1))
| end
| as VendorID, case when charindex('@.@.',comment) > 0 then 'True' else 'False' end as Reference, comment
| from bill_lineitems
| where comment like '%@.%'
That translates into this:
This allows for putting an invoice@.vendor (or invoice@.@.vendor) anywhere in
a description field, so long as it's set off on either side by spaces or
appears at the very beginning or end of the string. You can't, for example,
use it at the end of a grammatical sentence and append a period.
These work:
invoice@.vendor yada yada yada
yada yada yada invoice@.vendor
yada yada yada invoice@.vendor yada yada yada
yada yada yada invoice@.vendor yada yada yada
invoice@.vendor
invoice@.vendor [with spaces at the end as well]
These will not work:
There are problems with this invoice@.vendor!
Use the invoice that was not paid (invoice@.vendor)
Also, you can't use more than one such thing in a single record's
description.
--
Scott
parsing varchar fields
What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.
> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.
> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Parsing Text String Field - comma delimited
Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes? What I need is for each new value within the string to create a new record.
Example:
ID STRING
12345 1,2,3
67891 2,4
Becomes
12345 1
12345 2
12345 3
67891 2
67891 4
Thanks for any help!!
Check this page. It has great examples (if you are using 2005 there are two really great ways of handling this)
http://www.aspfaq.com/show.asp?id=2529
Parsing Text String Field
Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?
Example:
ID STRING
12345 1,2,3
67891 2,4
Becomes
12345 1
12345 2
12345 3
67891 2
67891 4
Thanks for any help!!
You should be handling this in the Dataset not in SSRS.
If you need help with SQL statements to to do this I'm sure posting to the Transact-SQL forum will provide a response on how to get a dataset to come back with the relevant rows. You may have to use functions, sp's, or temporary tables to get the data back in that format.
Craig
Parsing Text String Field
Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?
Example:
ID STRING
12345 1,2,3
67891 2,4
Becomes
12345 1
12345 2
12345 3
67891 2
67891 4
Thanks for any help!!
You should be handling this in the Dataset not in SSRS.
If you need help with SQL statements to to do this I'm sure posting to the Transact-SQL forum will provide a response on how to get a dataset to come back with the relevant rows. You may have to use functions, sp's, or temporary tables to get the data back in that format.
Craig
Parsing Text
Dear All,
I know how to parse this in ACCESS but not clear how to do this through
stored procedure. I Googled with different search criteria but didn't get an
y
useful hit. Any one have something handy?
I would like to parse:
"strFullName" listed as Habibullah, Mohammad I to three fields as:
strLastName: Habibullah
strFirstName: Mohammad
strMiddleName: I
Or another example, I would like "strFullName" listed as Huang, Chong Xi to
parse as:
strLastName: Huang
strFirstName: Chong
strMiddleName: Xi
Assuming that table name is User and field name is strFullName.
Thanks.
Habibullah.Habibullah,
You can use the T-SQL string functions to achieve your goal. See String
Functions in the SQL BOL. (esp. CHARINDEX and SUBSTRING). Ideally you'd
want to perform this string manipulation at the middle-tier or on the
client.
HTH
Jerry
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.|||Hi
One way to do it would be to use
SELECT
LEFT(strFullName,CHARINDEX(',',strFullNa
me)-1) AS strLastname,
LTRIM(SUBSTRING(strFullName,CHARINDEX(',
',strFullName)+1,LEN(strFullName)-CH
ARINDEX('
',REVERSE(strFullName))-CHARINDEX(',',strFullName))) AS strFirstname,
RIGHT(strFullName,CHARINDEX(' ',REVERSE(strFullName))-1) AS strMiddlename
FROM User
But this depends on how consistent the data is.
John
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.
I know how to parse this in ACCESS but not clear how to do this through
stored procedure. I Googled with different search criteria but didn't get an
y
useful hit. Any one have something handy?
I would like to parse:
"strFullName" listed as Habibullah, Mohammad I to three fields as:
strLastName: Habibullah
strFirstName: Mohammad
strMiddleName: I
Or another example, I would like "strFullName" listed as Huang, Chong Xi to
parse as:
strLastName: Huang
strFirstName: Chong
strMiddleName: Xi
Assuming that table name is User and field name is strFullName.
Thanks.
Habibullah.Habibullah,
You can use the T-SQL string functions to achieve your goal. See String
Functions in the SQL BOL. (esp. CHARINDEX and SUBSTRING). Ideally you'd
want to perform this string manipulation at the middle-tier or on the
client.
HTH
Jerry
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.|||Hi
One way to do it would be to use
SELECT
LEFT(strFullName,CHARINDEX(',',strFullNa
me)-1) AS strLastname,
LTRIM(SUBSTRING(strFullName,CHARINDEX(',
',strFullName)+1,LEN(strFullName)-CH
ARINDEX('
',REVERSE(strFullName))-CHARINDEX(',',strFullName))) AS strFirstname,
RIGHT(strFullName,CHARINDEX(' ',REVERSE(strFullName))-1) AS strMiddlename
FROM User
But this depends on how consistent the data is.
John
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.
Monday, March 26, 2012
parent/child text File Import
I have a file with a parent child records in the same file.
My game plan is to get it into a table recognize the parents and put them in the parent table and recognize the children and put them in the children table.
The problem I am having is the flat file import chokes on it because the column withs are different from line to line (parent / child).
Any thoughts or suggestions would be apprecaited.
Mardo
You'll need to import the data into the pipeline as a single wide column and then parse out the required values using a combination of the condistional split component and derived column component.
-Jamie
Tuesday, March 20, 2012
Parameters with no prompt
Can I have a parameter that wouldn't have a text for the prompt?
Thanksno
but you could use a colon or a period as a prompt - next best thing :)
"Mark Goldin" wrote:
> Can I have a parameter that wouldn't have a text for the prompt?
> Thanks
>
>
Thanksno
but you could use a colon or a period as a prompt - next best thing :)
"Mark Goldin" wrote:
> Can I have a parameter that wouldn't have a text for the prompt?
> Thanks
>
>
Monday, March 12, 2012
parameters via url not being seen
Iâ'm having a problem passing parameters to my report thru the url.
Iâ've read the online text, the manuals we have here, and the newsgroup
entries.
I created a "non-queried" parameter in my report: TestParam
I can test this parameter when running this report in Report Manager. I
enter a value, click View Report, and the data displays in a text box that
displays data based on the data entered into the parameter box.
When I call the report from a vb.net client application thru a url, the
report loads up in a Report Manager window, with NO errors, but no data
displays in either the parameter box or the text box. It is waiting for
parameter input, just as it normally would when bringing up the report in
report manager.
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
Iâ've tried this with and without the Command=Render. I've verified the
parameter name is correct and is the correct case.
Am I still missing something? Is there something more that I should do
with the report parameter before it will accept input from a url?Example from BOL
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
so does this work?
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"David" <David@.discussions.microsoft.com> wrote in message
news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> I'm having a problem passing parameters to my report thru the url.
> I've read the online text, the manuals we have here, and the newsgroup
> entries.
> I created a "non-queried" parameter in my report: TestParam
> I can test this parameter when running this report in Report Manager. I
> enter a value, click View Report, and the data displays in a text box that
> displays data based on the data entered into the parameter box.
> When I call the report from a vb.net client application thru a url, the
> report loads up in a Report Manager window, with NO errors, but no data
> displays in either the parameter box or the text box. It is waiting for
> parameter input, just as it normally would when bringing up the report in
> report manager.
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> I've tried this with and without the Command=Render. I've verified the
> parameter name is correct and is the correct case.
> Am I still missing something? Is there something more that I should do
> with the report parameter before it will accept input from a url?
>|||The second looks like it should work but what happens is exactly what he is
seeing, the parameters do not get filled in. You need to use reportserver
syntax as you show from BOL (the difference is RS portal (Report Manager)
versus ReportServer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23Z40Ny2RFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Example from BOL
>
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
> so does this work?
>
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> > I'm having a problem passing parameters to my report thru the url.
> >
> > I've read the online text, the manuals we have here, and the newsgroup
> > entries.
> >
> > I created a "non-queried" parameter in my report: TestParam
> >
> > I can test this parameter when running this report in Report Manager. I
> > enter a value, click View Report, and the data displays in a text box
that
> > displays data based on the data entered into the parameter box.
> >
> > When I call the report from a vb.net client application thru a url, the
> > report loads up in a Report Manager window, with NO errors, but no data
> > displays in either the parameter box or the text box. It is waiting for
> > parameter input, just as it normally would when bringing up the report
in
> > report manager.
> >
> >
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> >
> > I've tried this with and without the Command=Render. I've verified the
> > parameter name is correct and is the correct case.
> >
> > Am I still missing something? Is there something more that I should do
> > with the report parameter before it will accept input from a url?
> >
> >
>
Iâ've read the online text, the manuals we have here, and the newsgroup
entries.
I created a "non-queried" parameter in my report: TestParam
I can test this parameter when running this report in Report Manager. I
enter a value, click View Report, and the data displays in a text box that
displays data based on the data entered into the parameter box.
When I call the report from a vb.net client application thru a url, the
report loads up in a Report Manager window, with NO errors, but no data
displays in either the parameter box or the text box. It is waiting for
parameter input, just as it normally would when bringing up the report in
report manager.
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
Iâ've tried this with and without the Command=Render. I've verified the
parameter name is correct and is the correct case.
Am I still missing something? Is there something more that I should do
with the report parameter before it will accept input from a url?Example from BOL
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
so does this work?
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"David" <David@.discussions.microsoft.com> wrote in message
news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> I'm having a problem passing parameters to my report thru the url.
> I've read the online text, the manuals we have here, and the newsgroup
> entries.
> I created a "non-queried" parameter in my report: TestParam
> I can test this parameter when running this report in Report Manager. I
> enter a value, click View Report, and the data displays in a text box that
> displays data based on the data entered into the parameter box.
> When I call the report from a vb.net client application thru a url, the
> report loads up in a Report Manager window, with NO errors, but no data
> displays in either the parameter box or the text box. It is waiting for
> parameter input, just as it normally would when bringing up the report in
> report manager.
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> I've tried this with and without the Command=Render. I've verified the
> parameter name is correct and is the correct case.
> Am I still missing something? Is there something more that I should do
> with the report parameter before it will accept input from a url?
>|||The second looks like it should work but what happens is exactly what he is
seeing, the parameters do not get filled in. You need to use reportserver
syntax as you show from BOL (the difference is RS portal (Report Manager)
versus ReportServer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23Z40Ny2RFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Example from BOL
>
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
> so does this work?
>
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> > I'm having a problem passing parameters to my report thru the url.
> >
> > I've read the online text, the manuals we have here, and the newsgroup
> > entries.
> >
> > I created a "non-queried" parameter in my report: TestParam
> >
> > I can test this parameter when running this report in Report Manager. I
> > enter a value, click View Report, and the data displays in a text box
that
> > displays data based on the data entered into the parameter box.
> >
> > When I call the report from a vb.net client application thru a url, the
> > report loads up in a Report Manager window, with NO errors, but no data
> > displays in either the parameter box or the text box. It is waiting for
> > parameter input, just as it normally would when bringing up the report
in
> > report manager.
> >
> >
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> >
> > I've tried this with and without the Command=Render. I've verified the
> > parameter name is correct and is the correct case.
> >
> > Am I still missing something? Is there something more that I should do
> > with the report parameter before it will accept input from a url?
> >
> >
>
Parameters question
I am trying to create a set of parameters that will alow me to select data
either based on criteria from dropdown boxes or from a text box. In my
DataSet I have something like this (simpified)
if @.ItemDesc is null
select citemno, cdescript, 0.0 as price from icitem
where ....
else
select citemno, cdescript, 0.0 as price from icitem
where cdescript like @.ItemDesc
@.ItemDesc is a value from a text box. When I run the report it keeps
prompting me to enter a value into the first dropdown.
Thanks for help.On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am trying to create a set of parameters that will alow me to select data
> either based on criteria from dropdown boxes or from a text box. In my
> DataSet I have something like this (simpified)
> if @.ItemDesc is null
> select citemno, cdescript, 0.0 as price from icitem
> where ....
> else
> select citemno, cdescript, 0.0 as price from icitem
> where cdescript like @.ItemDesc
> @.ItemDesc is a value from a text box. When I run the report it keeps
> prompting me to enter a value into the first dropdown.
> Thanks for help.
You could have a default value in the drop-down box of an empty
string. That should avoid the prompt, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||And to do that I need to select default values non-queried?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am trying to create a set of parameters that will alow me to select
>> data
>> either based on criteria from dropdown boxes or from a text box. In my
>> DataSet I have something like this (simpified)
>> if @.ItemDesc is null
>> select citemno, cdescript, 0.0 as price from icitem
>> where ....
>> else
>> select citemno, cdescript, 0.0 as price from icitem
>> where cdescript like @.ItemDesc
>> @.ItemDesc is a value from a text box. When I run the report it keeps
>> prompting me to enter a value into the first dropdown.
>> Thanks for help.
>
> You could have a default value in the drop-down box of an empty
> string. That should avoid the prompt, etc. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> And to do that I need to select default values non-queried?
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am trying to create a set of parameters that will alow me to select
> >> data
> >> either based on criteria from dropdown boxes or from a text box. In my
> >> DataSet I have something like this (simpified)
> >> if @.ItemDesc is null
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where ....
> >> else
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where cdescript like @.ItemDesc
> >> @.ItemDesc is a value from a text box. When I run the report it keeps
> >> prompting me to enter a value into the first dropdown.
> >> Thanks for help.
> > You could have a default value in the drop-down box of an empty
> > string. That should avoid the prompt, etc. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
If you want to set the default value to an empty string in the stored
procedure/query that sources the parameter (and order it in the
dataset to show the empty string first) then you can set it as 'from
query.' If you want to just use null and check for the null in the
stored procedure/query then you can set it to non-queried. It depends
on where you want to set the default value. Hope this clarifies it
better.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am setting it up to a non-queried value and I am entering ="" into a
field.
Still asks me to enter parameter when I run it.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182954533.677728.17440@.u2g2000hsc.googlegroups.com...
> On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> And to do that I need to select default values non-queried?
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
>> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> >> I am trying to create a set of parameters that will alow me to select
>> >> data
>> >> either based on criteria from dropdown boxes or from a text box. In my
>> >> DataSet I have something like this (simpified)
>> >> if @.ItemDesc is null
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where ....
>> >> else
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where cdescript like @.ItemDesc
>> >> @.ItemDesc is a value from a text box. When I run the report it keeps
>> >> prompting me to enter a value into the first dropdown.
>> >> Thanks for help.
>> > You could have a default value in the drop-down box of an empty
>> > string. That should avoid the prompt, etc. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> If you want to set the default value to an empty string in the stored
> procedure/query that sources the parameter (and order it in the
> dataset to show the empty string first) then you can set it as 'from
> query.' If you want to just use null and check for the null in the
> stored procedure/query then you can set it to non-queried. It depends
> on where you want to set the default value. Hope this clarifies it
> better.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
either based on criteria from dropdown boxes or from a text box. In my
DataSet I have something like this (simpified)
if @.ItemDesc is null
select citemno, cdescript, 0.0 as price from icitem
where ....
else
select citemno, cdescript, 0.0 as price from icitem
where cdescript like @.ItemDesc
@.ItemDesc is a value from a text box. When I run the report it keeps
prompting me to enter a value into the first dropdown.
Thanks for help.On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am trying to create a set of parameters that will alow me to select data
> either based on criteria from dropdown boxes or from a text box. In my
> DataSet I have something like this (simpified)
> if @.ItemDesc is null
> select citemno, cdescript, 0.0 as price from icitem
> where ....
> else
> select citemno, cdescript, 0.0 as price from icitem
> where cdescript like @.ItemDesc
> @.ItemDesc is a value from a text box. When I run the report it keeps
> prompting me to enter a value into the first dropdown.
> Thanks for help.
You could have a default value in the drop-down box of an empty
string. That should avoid the prompt, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||And to do that I need to select default values non-queried?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am trying to create a set of parameters that will alow me to select
>> data
>> either based on criteria from dropdown boxes or from a text box. In my
>> DataSet I have something like this (simpified)
>> if @.ItemDesc is null
>> select citemno, cdescript, 0.0 as price from icitem
>> where ....
>> else
>> select citemno, cdescript, 0.0 as price from icitem
>> where cdescript like @.ItemDesc
>> @.ItemDesc is a value from a text box. When I run the report it keeps
>> prompting me to enter a value into the first dropdown.
>> Thanks for help.
>
> You could have a default value in the drop-down box of an empty
> string. That should avoid the prompt, etc. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> And to do that I need to select default values non-queried?
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am trying to create a set of parameters that will alow me to select
> >> data
> >> either based on criteria from dropdown boxes or from a text box. In my
> >> DataSet I have something like this (simpified)
> >> if @.ItemDesc is null
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where ....
> >> else
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where cdescript like @.ItemDesc
> >> @.ItemDesc is a value from a text box. When I run the report it keeps
> >> prompting me to enter a value into the first dropdown.
> >> Thanks for help.
> > You could have a default value in the drop-down box of an empty
> > string. That should avoid the prompt, etc. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
If you want to set the default value to an empty string in the stored
procedure/query that sources the parameter (and order it in the
dataset to show the empty string first) then you can set it as 'from
query.' If you want to just use null and check for the null in the
stored procedure/query then you can set it to non-queried. It depends
on where you want to set the default value. Hope this clarifies it
better.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am setting it up to a non-queried value and I am entering ="" into a
field.
Still asks me to enter parameter when I run it.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182954533.677728.17440@.u2g2000hsc.googlegroups.com...
> On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> And to do that I need to select default values non-queried?
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
>> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> >> I am trying to create a set of parameters that will alow me to select
>> >> data
>> >> either based on criteria from dropdown boxes or from a text box. In my
>> >> DataSet I have something like this (simpified)
>> >> if @.ItemDesc is null
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where ....
>> >> else
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where cdescript like @.ItemDesc
>> >> @.ItemDesc is a value from a text box. When I run the report it keeps
>> >> prompting me to enter a value into the first dropdown.
>> >> Thanks for help.
>> > You could have a default value in the drop-down box of an empty
>> > string. That should avoid the prompt, etc. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> If you want to set the default value to an empty string in the stored
> procedure/query that sources the parameter (and order it in the
> dataset to show the empty string first) then you can set it as 'from
> query.' If you want to just use null and check for the null in the
> stored procedure/query then you can set it to non-queried. It depends
> on where you want to set the default value. Hope this clarifies it
> better.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Friday, March 9, 2012
Parameters in an expression
I am trying to write an expression based on a parameter for a text box.
If I write it without the parameter it works fine, but when I add the
parameter, the expression evaluates as just text.
My dataset contains a field called "PeakSessions"
I have a parameter called "chrt", type string, containing the value
"PeakSessions".
=Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
data.
="Fields!" & Parameters!chrt.Value & ".Value"
This parameterized version evaluates to: Fields!PeakSessions.Value.
So it seems the parameter resolved correctly, but my expression is
being evaluated as text. What am I doing wrong?
SQL 2005 Reports Services, SP1, Standard EditionYou are just creating a string. Let's say you were trying to create a string
to display. This is how you would do it. RS will not evaluate the string
which is what you are trying to do. You should be able to do something like
this:
=Fields(Parameters!chrt.Value).Value
Just a guess.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152724801.223473.94690@.h48g2000cwc.googlegroups.com...
>I am trying to write an expression based on a parameter for a text box.
> If I write it without the parameter it works fine, but when I add the
> parameter, the expression evaluates as just text.
> My dataset contains a field called "PeakSessions"
> I have a parameter called "chrt", type string, containing the value
> "PeakSessions".
> =Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
> data.
> ="Fields!" & Parameters!chrt.Value & ".Value"
> This parameterized version evaluates to: Fields!PeakSessions.Value.
> So it seems the parameter resolved correctly, but my expression is
> being evaluated as text. What am I doing wrong?
> SQL 2005 Reports Services, SP1, Standard Edition
>|||You are right. I was just creating text, but I couldn't figure out the
right way. I had tried something similar to your suggestion
=Fields!(Parameters!chrt.Value).Value but the extra exclamation point
caused me problems. Your way worked. Thanks!
Bruce L-C [MVP] wrote:
> You are just creating a string. Let's say you were trying to create a string
> to display. This is how you would do it. RS will not evaluate the string
> which is what you are trying to do. You should be able to do something like
> this:
> =Fields(Parameters!chrt.Value).Value|||Good. Glad it worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152729191.900859.300580@.m79g2000cwm.googlegroups.com...
> You are right. I was just creating text, but I couldn't figure out the
> right way. I had tried something similar to your suggestion
> =Fields!(Parameters!chrt.Value).Value but the extra exclamation point
> caused me problems. Your way worked. Thanks!
>
> Bruce L-C [MVP] wrote:
>> You are just creating a string. Let's say you were trying to create a
>> string
>> to display. This is how you would do it. RS will not evaluate the string
>> which is what you are trying to do. You should be able to do something
>> like
>> this:
>> =Fields(Parameters!chrt.Value).Value
>
If I write it without the parameter it works fine, but when I add the
parameter, the expression evaluates as just text.
My dataset contains a field called "PeakSessions"
I have a parameter called "chrt", type string, containing the value
"PeakSessions".
=Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
data.
="Fields!" & Parameters!chrt.Value & ".Value"
This parameterized version evaluates to: Fields!PeakSessions.Value.
So it seems the parameter resolved correctly, but my expression is
being evaluated as text. What am I doing wrong?
SQL 2005 Reports Services, SP1, Standard EditionYou are just creating a string. Let's say you were trying to create a string
to display. This is how you would do it. RS will not evaluate the string
which is what you are trying to do. You should be able to do something like
this:
=Fields(Parameters!chrt.Value).Value
Just a guess.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152724801.223473.94690@.h48g2000cwc.googlegroups.com...
>I am trying to write an expression based on a parameter for a text box.
> If I write it without the parameter it works fine, but when I add the
> parameter, the expression evaluates as just text.
> My dataset contains a field called "PeakSessions"
> I have a parameter called "chrt", type string, containing the value
> "PeakSessions".
> =Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
> data.
> ="Fields!" & Parameters!chrt.Value & ".Value"
> This parameterized version evaluates to: Fields!PeakSessions.Value.
> So it seems the parameter resolved correctly, but my expression is
> being evaluated as text. What am I doing wrong?
> SQL 2005 Reports Services, SP1, Standard Edition
>|||You are right. I was just creating text, but I couldn't figure out the
right way. I had tried something similar to your suggestion
=Fields!(Parameters!chrt.Value).Value but the extra exclamation point
caused me problems. Your way worked. Thanks!
Bruce L-C [MVP] wrote:
> You are just creating a string. Let's say you were trying to create a string
> to display. This is how you would do it. RS will not evaluate the string
> which is what you are trying to do. You should be able to do something like
> this:
> =Fields(Parameters!chrt.Value).Value|||Good. Glad it worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152729191.900859.300580@.m79g2000cwm.googlegroups.com...
> You are right. I was just creating text, but I couldn't figure out the
> right way. I had tried something similar to your suggestion
> =Fields!(Parameters!chrt.Value).Value but the extra exclamation point
> caused me problems. Your way worked. Thanks!
>
> Bruce L-C [MVP] wrote:
>> You are just creating a string. Let's say you were trying to create a
>> string
>> to display. This is how you would do it. RS will not evaluate the string
>> which is what you are trying to do. You should be able to do something
>> like
>> this:
>> =Fields(Parameters!chrt.Value).Value
>
Subscribe to:
Posts (Atom)