Multi Value Parameters

Hi all, I am a beginner of SQL Server 2005.

How do you display multi-values parameters with coma in the SQL Server 2005 reporting?.

I believe we have to create a fucntion to do this, but how do you create a function in SQL Server 2005? we only can create / alter table.

I have an example from a book but it doesnt help me at all. it just give me an example of the code but when I tried, it did not work coz its a function like in VBA.

I want in the report look like this in the Layout:

SubCityID: =Code.ParameterList(Parameter!City.Value)
SubStore: =Code.ParameterList(Parameter!Store.Label)

Then in the report will looks like this.

SubCityID: 1, 5, 35
SubStore: Alabama, Hybird, Zap

The code for ParameterList that I ger looks like this:

Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""

For each sParamItem in Parameter
If sParamItem is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next

Return sParamVal.SubString(0, sParamVal.Length - 2)

End Function

If there is a better way please help me.

Cheers

Quote:

Originally Posted by monadel

Hi all, I am a beginner of SQL Server 2005.

How do you display multi-values parameters with coma in the SQL Server 2005 reporting?.

I believe we have to create a fucntion to do this, but how do you create a function in SQL Server 2005? we only can create / alter table.

I have an example from a book but it doesnt help me at all. it just give me an example of the code but when I tried, it did not work coz its a function like in VBA.

I want in the report look like this in the Layout:

SubCityID: =Code.ParameterList(Parameter!City.Value)
SubStore: =Code.ParameterList(Parameter!Store.Label)

Then in the report will looks like this.

SubCityID: 1, 5, 35
SubStore: Alabama, Hybird, Zap

The code for ParameterList that I ger looks like this:

Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""

For each sParamItem in Parameter
If sParamItem is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next

Return sParamVal.SubString(0, sParamVal.Length - 2)

End Function

If there is a better way please help me.

Cheers

Retrieve the whole SubCityId from the database using the following query and use it in your back end code...

declare @.sParamItem varchar(1000)

SET @.sParamItem = ''

SELECT @.sParamItem = @.sParamItem + convert(varchar(3),SubCityId) + ', '
FROM Table_Name WHERE My_Condition

select @.sParamItem

Multi value parameter in db2 query

hi,

i'm trying to perform a query against a db2 database like this:

SELECT ... FROM ... WHERE (field IN (?))

Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:

An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension

But when i type the query like this

SELECT ... FROM ... WHERE (field IN ('value1','value2'))

it executes flawlessly.

I am using the IBM ole db driver for db2 if that matters

Can anyone help me?

IBM's Ole DB driver only supports parameterized queries with single valued parameters. See e.g. this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81223&SiteID=1

SSRS 2005 contains specific functionality to perform query rewrite for multi-valued query parameters in the cases of SQL Server, Oracle 9 or later, and SSAS / XML/A-based data sources.

For all other cases you would need a custom data extension implementation to provide the query rewrite functionality.

-- Robert

Multi value parameter

In our project we are using reporting service web service calls to get report
paramaeters and report data.
We are passing an array of parameter values to webservice method
GetReportData() as an input parameter to render the output bytes.This
parameter values array consists of each controls's selected value.
Now in case of multivalue parameters, how to pass these multivalues to
webservice method GetReportData()?
Suppose for the control if we select the multiple values, how to send them
to array along with the other control's values to parametervalues collection.
please give suggestions on these.
Thanx
sriramI got the answer for this multivalue parameter
We have to send the multivalue parmaeters with same parameter name but with
different selected values in to parameter values array as below:
Here Foo is a multivalue parameter
<ParameterValues>
<ParameterValue>
<Name>Foo</Name>
<Value>1</Value>
</ParameterValue>
<ParameterValue>
<Name>Foo</Name>
<Value>2</Value>
</ParameterValue>
<ParameterValue>
<Name>Bar</Name>
<Value>NorthWest</Value>
</ParameterValue>
</ParameterValues>
"SRIRAM" wrote:
> In our project we are using reporting service web service calls to get report
> paramaeters and report data.
> We are passing an array of parameter values to webservice method
> GetReportData() as an input parameter to render the output bytes.This
> parameter values array consists of each controls's selected value.
> Now in case of multivalue parameters, how to pass these multivalues to
> webservice method GetReportData()?
> Suppose for the control if we select the multiple values, how to send them
> to array along with the other control's values to parametervalues collection.
> please give suggestions on these.
> Thanx
> sriramsql

Multi Select Parameters?

In SQL Reporting Services, Is there anyway to create a parameter in
which a user can be prompted to choose multiple values from a list?
Ultimately, the multi-selected values would be passed to the Dataset
query as an "IN" qualification.
Example:
SELECT first_name, last_name
FROM employees
LEFT OUTER JOIN department ON
employees.dept_id = department.dept_id
WHERE dept_name IN ('Accounting', 'Sales', Support')
Crystal Reports has this functionality but I can not figure out how to
do this in MSRS.
If you know how to do this and would like to help a guy out, could you
include step by step answer or a link to a step by step tutorial?
Thanks!Here are a couple of options:
Option #1: Use a query expression as follows: ="select top 10 name, type
from sysobjects where type in (" & Parameters!Report_Parameter_0.Value &
")".
Option #2: Check
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=0a3800c4-4180-419c-a117-bfa21b2de099.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:ltnrg01f5qf251e89c5vsrmcdn7fu01k4i@.4ax.com...
> In SQL Reporting Services, Is there anyway to create a parameter in
> which a user can be prompted to choose multiple values from a list?
> Ultimately, the multi-selected values would be passed to the Dataset
> query as an "IN" qualification.
> Example:
> SELECT first_name, last_name
> FROM employees
> LEFT OUTER JOIN department ON
> employees.dept_id = department.dept_id
> WHERE dept_name IN ('Accounting', 'Sales', Support')
> Crystal Reports has this functionality but I can not figure out how to
> do this in MSRS.
> If you know how to do this and would like to help a guy out, could you
> include step by step answer or a link to a step by step tutorial?
> Thanks!sql

multi parameters problem in CTP June

In CTP June Reporting Service and try the multi-value,
use Adventure Work DB and the SQL as follow..
SELECT Name, CountryRegionCode
FROM Person.CountryRegion
WHERE (CountryRegionCode = @.code)
When I checked the multi-value then browse,
I keyin the multi value AF, AL and the textbox show as AF, AL
click " View Report " then response the error message:
An error has occurred during report processing.
Query execution failed for data set 'Adventure'.
Incorrect syntax near ','.
What's happen?
How could I use multi-value in reporting services?
The multi-value dose work on AS Cube Report, but dosen't work on relational
DB?
Thanks for any advice!
AngiYou have to use the IN keyword in the query:
SELECT Name, CountryRegionCode
FROM Person.CountryRegion
WHERE CountryRegionCode (IN @.code)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angi" <enchiw@.msn.com> wrote in message
news:%23ndxNNduFHA.3660@.tk2msftngp13.phx.gbl...
> In CTP June Reporting Service and try the multi-value,
> use Adventure Work DB and the SQL as follow..
> SELECT Name, CountryRegionCode
> FROM Person.CountryRegion
> WHERE (CountryRegionCode = @.code)
> When I checked the multi-value then browse,
> I keyin the multi value AF, AL and the textbox show as AF, AL
> click " View Report " then response the error message:
> An error has occurred during report processing.
> Query execution failed for data set 'Adventure'.
> Incorrect syntax near ','.
> What's happen?
> How could I use multi-value in reporting services?
> The multi-value dose work on AS Cube Report, but dosen't work on
> relational DB?
> Thanks for any advice!
> Angi
>

Mult Select in SQL Reporting Parameters

Is there anyway to have a parameter that can be a multi select. Example
something that will allow multiple states to be selected?Sql Server 2000 Reporting Services does not directly support this
functionality. It is on our wish list for inclusion in a future release.
However there is an solution to this post on the GotDotNet.com web site:
http://www.gotdotnet.com/Community/Resources/Default.aspx?AFXPath=/Resource%5b@.ResourceId='2E882C0A-8D2B-4EAD-81BE-8E66C0941A18'%5d
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:u4DuLTjqEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Is there anyway to have a parameter that can be a multi select. Example
> something that will allow multiple states to be selected?
>|||Here's a similar thread:
http://groups.google.com/groups?hl=en&lr=&threadm=eW7vUsFsEHA.2952%40TK2MSFTNGP10.phx.gbl&rnum=9&prev=/groups%3Fq%3Djerrynixon%2540gmail.com%26num%3D100%26hl%3Den%26lr%3D%26group%3Dmicrosoft.public.sqlserver.reportingsvcs%26scoring%3Dd
Here's another similar thread:
http://groups.google.com/groups?hl=en&lr=&threadm=F2B6BAF3-1667-4E1F-8A1A-54D0FC08DEA6%40microsoft.com&rnum=7&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dmulti%2Bselect%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.reportingsvcs
One suggestion: if the parameter only needs TWO you could just have
two parameters that prompt the user for the same information. For
example, if you needed to select two months, you could have two month
parameters.
If you do not know the number to be selected - a custom interface is
the best solution - a free text with "1,2,3,4,5" entered by the user
is fine, though prone to error from human entry issues.
BR//Jerrysql

Mulit-Value Reporting Services April CTP2005

I am using the Multi-Value Parameter feature in the April; SQL CTP 2005 in
reporting services. The functionality differes from Visual Studio to the
deplyed version on IIS. Ther Visual Studio allows rich edit control features
but IIS deployed version doesn't. I am able to copy and paste parameters in
the mult-value parameter field and all of the parameters are accurately
selected prior to and after selection. But this behavior is quite different
in IIS deployed report. It only allow manual input of a single adhoc
parameter item. Also cascading parametrers work differently in Visual
Studio(works good in VS) than does in IIS deployed version. In IIS deployed
copy cascading is not dynamic but pararmeter that is a dependent process is
greyed out until selection is made (one time only) and View Report button is
pressed. (Windows 2000) Server. Any Help?Regarding entering values for deployed report:
How do you enter values? Do you press Enter to go to next line?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"HenryJ" <hjartu@.eneighborhoods.com (donotspam)> wrote in message
news:74617DFF-6C89-4C0B-8333-7698013D18A8@.microsoft.com...
>I am using the Multi-Value Parameter feature in the April; SQL CTP 2005 in
> reporting services. The functionality differes from Visual Studio to the
> deplyed version on IIS. Ther Visual Studio allows rich edit control
> features
> but IIS deployed version doesn't. I am able to copy and paste parameters
> in
> the mult-value parameter field and all of the parameters are accurately
> selected prior to and after selection. But this behavior is quite
> different
> in IIS deployed report. It only allow manual input of a single adhoc
> parameter item. Also cascading parametrers work differently in Visual
> Studio(works good in VS) than does in IIS deployed version. In IIS
> deployed
> copy cascading is not dynamic but pararmeter that is a dependent process
> is
> greyed out until selection is made (one time only) and View Report button
> is
> pressed. (Windows 2000) Server. Any Help?

MSSQL.3

On one server the report services folder exists here;
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer
On another server it exists here;
C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
Services\ReportServer
Why MSSQL.3 on one and MSSQL.4 on the other?
Thanx in advance ,
GregOn Aug 29, 11:32 am, "SurferJoe" <Surfer...@.newsgroup.nospam> wrote:
> On one server the report services folder exists here;
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer
> On another server it exists here;
> C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
> Services\ReportServer
> Why MSSQL.3 on one and MSSQL.4 on the other?
> Thanx in advance ,
> Greg
This is common when you have a different number of SQL Server Services
installed (i.e., if you have the SQL Server Database Engine,
Integration Services and Reporting Services installed on one server,
SSRS might be in MSSQL.3; whereas, if you have the SQL Server Database
Engine, Integration Services, Analysis Services and Reporting Services
installed on the other server, SSRS might be in MSSQL.4). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

MSSQL Server 2005 Not Reporting Errors to Windows Event Log

Hello All,

I just performed a fresh install of MSSQL 2005 Server Developer Edition on my XPSP2 machine along with MSSQL 2005 SP1 and I noticed that errors are not being reported in the Windows Event Viewer. Is there a way to enable/disable logging here?

I need to view these logs because when the tasks > 'copy database' task fails it submits its error in the Windows Event Log however since I reinstalled MSSQL 2005 these log entries don't appear here.

thanks in advance.

Hi,

if you are using a job for this you can switch the Windows event logging on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Its not a job. I just manually select the task from Management Studio. In the last installation performing this task created log entries in the windows event log but for some reason they aren't appearing now.

|||

Hi:

The main log for SQL Server 2005 installation has changed, and placed in the following location:

%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

For more information, please refer to the following MSDN reference.

http://msdn2.microsoft.com/en-us/library/ms143702.aspx

Hope this helps. :-)

Thanks.

|||

Interesting... I wonder why events don't show up in the Windows Event Log anymore? Especially since MSSQL 2005 specifically says that it will make log entries in the Windows Event Log in the Copy Database task wizard.

|||

Hi,

If the Severity Level is 19 or Higher then only it will be logged in the Windows Event Log.

HTH

Hemantgiri S. Goswami