Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

sql

Multi parameter aggregate CLR-function?

In the provided CLR-function sample there is only one parameter from the SQL statement to the function. I can't find any info about how to pass more then one parameter. Is this possible somehow?

like:

Code Snippet

Select ShopCode,PickAValue(OrderDate,Article) as BestArticle

from T_Sales

Group by ShopCode

hans

I was looking at something similar recently and came across this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=789719&SiteID=1

It looks like you can only have one parameter in a UDA though the XML work around mentioned may be suitable (I've not tried it, mind).

HTH.

|||

Yes I have found a similar solution, by concatenating the values with a TAB as seperator, this looks like the XML variant, eg putting the values of all needed paramteres in a single string value.

hans

|||

There is a post on the .NET Framework inside SQL Server Katmai forum that says there is a strong possibility multi-input aggregates will be supported in Katmai:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1703860&SiteID=1

Multi Language Report Function

I have written a function which translates text based upon the language the
user chooses to see the report in (it can also be passed in as a data driven
parm for static runs) Anyway ... my question is this ... since I am new to RS
and new to Vb .Net I was wondering if someone out there can look at what I am
trying to do and suggest a better way ... since I dont want to have to code a
function like this for 25+ fields to be translated and in 4+ languages. I
think when you see the function I wrote you will get the idea and perhaps be
able to suggest an efficient way to translate all fields at once or perhaps
"generic" function (which I tried to code too with nested CASE statements but
abandoned). Anyway ... here is the newbie's (my) solution - dont laugh :-)
This is what I code in the text box for the expression ===>
=Code.TranslateTitle(Parameters!pm_language_code.value)
HERE IS THE FUNCTION
Function TranslateTitle ( ByVal Language_Code As String ) As String
Select Language_Code
Case "EN"
Return "Detailed Report : "
Case "FR"
Return "Rapport Détaillé : "
Case "DE"
Return "Ausführlicher Report : "
Case "NL"
Return "Gedetailleerd Rapport : "
Case Else
Return "Detailed Report : "
End Select
End Function
This works just fine ... but I have 25+ fields with translations !!!
Thanks in advanceHi,
You can use resource files just as in ASP.NET applications.
You can reference these resource dll files within a function written as a
custom code.
Eralper
http://www.kodyaz.com
"MJ Taft" wrote:
> I have written a function which translates text based upon the language the
> user chooses to see the report in (it can also be passed in as a data driven
> parm for static runs) Anyway ... my question is this ... since I am new to RS
> and new to Vb .Net I was wondering if someone out there can look at what I am
> trying to do and suggest a better way ... since I dont want to have to code a
> function like this for 25+ fields to be translated and in 4+ languages. I
> think when you see the function I wrote you will get the idea and perhaps be
> able to suggest an efficient way to translate all fields at once or perhaps
> "generic" function (which I tried to code too with nested CASE statements but
> abandoned). Anyway ... here is the newbie's (my) solution - dont laugh :-)
> This is what I code in the text box for the expression ===>
> =Code.TranslateTitle(Parameters!pm_language_code.value)
> HERE IS THE FUNCTION
> Function TranslateTitle ( ByVal Language_Code As String ) As String
> Select Language_Code
> Case "EN"
> Return "Detailed Report : "
> Case "FR"
> Return "Rapport Détaillé : "
> Case "DE"
> Return "Ausführlicher Report : "
> Case "NL"
> Return "Gedetailleerd Rapport : "
> Case Else
> Return "Detailed Report : "
> End Select
> End Function
> This works just fine ... but I have 25+ fields with translations !!!
> Thanks in advance|||Thanks for your response however I dont know asp .net - in fact ... just got
a book to learn it. So I dont know how to do what you are suggesting here.
Could you be more specific? Point to a simple example maybe?
"eralper" wrote:
> Hi,
> You can use resource files just as in ASP.NET applications.
> You can reference these resource dll files within a function written as a
> custom code.
> Eralper
> http://www.kodyaz.com
> "MJ Taft" wrote:
> > I have written a function which translates text based upon the language the
> > user chooses to see the report in (it can also be passed in as a data driven
> > parm for static runs) Anyway ... my question is this ... since I am new to RS
> > and new to Vb .Net I was wondering if someone out there can look at what I am
> > trying to do and suggest a better way ... since I dont want to have to code a
> > function like this for 25+ fields to be translated and in 4+ languages. I
> > think when you see the function I wrote you will get the idea and perhaps be
> > able to suggest an efficient way to translate all fields at once or perhaps
> > "generic" function (which I tried to code too with nested CASE statements but
> > abandoned). Anyway ... here is the newbie's (my) solution - dont laugh :-)
> >
> > This is what I code in the text box for the expression ===>
> >
> > =Code.TranslateTitle(Parameters!pm_language_code.value)
> >
> > HERE IS THE FUNCTION
> >
> > Function TranslateTitle ( ByVal Language_Code As String ) As String
> > Select Language_Code
> > Case "EN"
> > Return "Detailed Report : "
> > Case "FR"
> > Return "Rapport Détaillé : "
> > Case "DE"
> > Return "Ausführlicher Report : "
> > Case "NL"
> > Return "Gedetailleerd Rapport : "
> > Case Else
> > Return "Detailed Report : "
> > End Select
> > End Function
> >
> > This works just fine ... but I have 25+ fields with translations !!!
> >
> > Thanks in advance

MSXML3: Reference to undeclared namespace prefix: 'sql'.

I have a vb com+ object that has a function that is supposed to return some XML from the database. It gets the XML via an ADO stream object. However, when I run the VB from its test harness in the project group it gives the following error;
MSXML3: Reference to undeclared namespace prefix: 'sql'.
My project references has;
MS ActiveX Data Objects 2.6 Library
MS XML v4.0
If I debug print the XML out it before writing it to the stream it gives ;
<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='ID'>
1
</sql:param></sql:header><sql:query>
exec sp100PaAddress_Select @.ID
</sql:query></ROOT>
"Richard P" <anonymous@.discussions.microsoft.com> wrote in message
news:3D95465D-CAD3-41CF-894E-B4077DF9B75C@.microsoft.com...
> I have a vb com+ object that has a function that is supposed to return
some XML from the database. It gets the XML via an ADO stream object.
However, when I run the VB from its test harness in the project group it
gives the following error;
> MSXML3: Reference to undeclared namespace prefix: 'sql'.
Try posting the code that is causing the error...
Bryant

MSSQL to Text File? how? thanks...

As we know, MySQL have function to output its data into Text File using "Select * into outfile 'C:/mytext.txt".

Does MSSQL has "Select into Outfile" function? If yes, what is the function??

Thanks in advance :)

Anderson

Do you have access to the SQL Manager GUI? You can output to a file from that.

You can also useSQLCMD -i Input.sql -o C:\mytext.txt -e from the command prompt.

|||P.S. - I'm not positive, but I don't think you can do it directly from the SQL statement like you can in MySQL.|||

You can use supplied BCP application or SQL Server Management Studio Express.

Example for BCP:

c:\Program Files\Microsoft SQL Server\90\Tools\Binn>bcp RZBWSPR.dbo.MenuItem out Result.txt -T -S DVESIC\SQLEXPRESS -c

|||You can call bcp utility with xp_cmdshell from T-SQL. Take a look at this post:
http://forums.asp.net/thread/1388531.aspx

MSSQL of MS Access "LAST"

Hi Folks,

I am translating an Access query to SQL but having a hard time!

The LAST function works fine in Access but barfs in MSSQL. Is there an equivalent? Here is a snip of the code that works great in Access:

SELECT DISTINCT Last(Customers.custLastName) AS LAST1, Last(Customers.custFirstName) AS FIRST1, Last(Customers.custAddr1) AS SHIP1, etc etc.

The output just takes the last of a bunch of almost identical entries, ie only one customer per line even though they make many orders.

DISTINCT still seems to grab all of the entries in the db. Do I need to expand on this?

Thanks!try using MIN() instead

rudy|||Hi Rudy,

Thanks for helping with this, but I have a new problem now. If I use the MIN() function, I get one result per name (good) but some fields do not match up (bad). Here's what I mean:

The only way to get a working result is to use this query

SELECT DISTINCT MIN(sfCustomers.custLastName) AS LAST1, MIN(sfCustomers.custFirstName) AS FIRST1, MIN(sfCustomers.custAddr1) AS SHIP1, MIN(sfCustomers.custAddr2) AS SHIP2, MIN(sfCustomers.custCity) AS CITY1, MIN(sfCustomers.custState) AS PROV, MIN(sfCustomers.custZip) AS ZIP1, MIN(sfCustomers.custPhone) AS PHONE, MIN(sfCustomers.custCountry) AS CONTACT1, MIN(sfCustomers.custEmail) AS NOTES, MIN(sfCustomers.custLastName) AS [LAST], MIN(sfCustomers.custFirstName) AS [FIRST], MIN(sfCustomers.custAddr1) AS ADD1, MIN(sfCustomers.custAddr2) AS ADD2, MIN(sfCustomers.custCity) AS CITY, MIN(sfCustomers.custZip) AS ZIP, MIN(sfCustomers.custCountry) AS CONTACT, MIN(sfOrders.orderDate) AS [DATE], MIN(sfOrderDetails.odrdtOrderId) AS IDENT, MIN(sfOrderDetails.odrdtProductID) AS ITEM, MIN(sfOrderDetails.odrdtQuantity) AS Q, MIN(sfProducts.prodName) AS [DESC], MIN(sfProducts.prodPrice) AS PRICE
FROM sfCustomers, sfOrderDetails, sfProducts, sfOrders
WHERE (((sfOrders.orderCustId)=sfCustomers.custID) And ((sfOrderDetails.odrdtOrderId)=sfOrders.OrderID) And ((sfOrderDetails.odrdtProductID)=sfProducts.prodID ))
GROUP BY sfOrders.orderCustId;

However, the fields aren't from the same record! If I use just a MIN() on the first column, I get the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'sfCustomers.custFirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm confused! How do I get just the one record, but with the full info from the one record?

Thanks by the way, I'm really in a bind here!|||start with the GROUP BY

you are grouping by orders, i.e. you will get one row per order

assuming each order belongs to only one customer, you can bring in customer info along with order info and still get one row per order

but what about products ordered? presumably there can be multiple products on an order, so which (single) product did you want to pick? remember, you are asking for only one row per order

rudy