MultiValue Parameter

Hello,

I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

Thank you,

Itzhak

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

|||

Thank you, this will solve my issue.

I put the following expression and it works well !!

=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))

Thanks,

Itzhak

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text files mining

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

1.I should use a SSIS solution using "Import Column transform" to get the files and store in a column with varbinary(max) data type; if you want to store only text in a column you can use varchar(max) data type (you can store maximum 2 GB)

2.Using SSIS solution I told there is a way to matching file (use "for each loop container")

3.For my ideea you don't to do that; you can run periodically the package created with SSIS in a job , depends on your business logic

4.Study the tutorial from here if you want create a text mining project. The ideea if you want to know let say the frequence of the terms /phrases, extracting clustering terms/concepts from the docs.

|||

does SSIS have a readymade package to compare mulitple files? if yes how does one go about it? thanx man !!!

|||

what did ou mean "compare multiple files"?

using for each loop container ou can select *.txt or *.doc files or have a special name.

if mean comparing contents of files i think you can use script task when you can customize this comparison using .net (as you should did it using .NET)

|||

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

|||

vickwal wrote:

i mean that for example there are two text files (.txt or .doc) stored in SQL server, containing an essay on American Independance:

I want to check that the essays do not match more than 60%. How do i do this? help appreciated !!!

You can use MS Integration Service.

I think you have-to convert (presumably MS Word) .doc format into plain text .txt.

Then you can load .txt files into a table like ESSAYS(AUTHOR varchar(255), FILENAME varchar(255), [Content] TEXT) using For Each Loop Container control.

Then use Fuzzy Lookup comparing by Content field, using same ESSAYS table as base and as Lookup table.

You can play with Similarity Threshold there.

Fuzzy Lookup operator will produce an output for each row of base table where Similarity and Confidence columns will be. Just spool it into another table.

good luck,

Mark

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?

multiple text docs evaluation

hey everbody,

i'm absolutely new to any sort of data management

here it goes: suppose we store 100 .txt or .doc files in sql server and we want that none of the files data should match more than 60%: the question which arises are

1. how do we store files in ms-sql (binary format or normal text)?

2. how do we match the files?

3. what code we write in c# for this purpose?

4. has this nething to do with pattern recognition?

My request to all new n active experienced user's to participate because Plzzzzz help me?

What is the purpose of this? If you want to discriminate against files, that should be done before saving them. If you need a difference analyzer, I suggest you look how subversion, cvs or any other source control systems handles and saves the differences.|||

the purpose is:

say i store two text documents uploaded to SQL Server from a web portal. Now i want SQL Server to determine that how much percent the content of these two files matches? Say if I want that the files shouldn't match more than 60%, and if they do they should be discarded. thanks man, help appreciated !!!

|||That sounds like a trigger might be able to do that processing during a table load/update. I will move this post to the DB forum for advice.|||thanx man! I was wondering if SQL Server 05 had some SSIP analytic readymade service do that?