Multi Value Parameter and Filtering

Hi All
I am trying to filter a table on my report based on a multi-value parameter,
I have tried several methods to no avail. When I use the following I do not
get an error but I do not get data showing on the report.
Expression is set to =Fields!Account.Value
Operator is set to In
Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
Can anyone help this newbie style question
RegardsI think you should be able to leave out the Join function.
Set the value as: =Parameters!Select_Individual_Account.Value
I do that in one of my reports and it works fine. Just make sure there is
not a (0) at the end of the paramerter.
"Are friends electric?" wrote:
> Hi All
> I am trying to filter a table on my report based on a multi-value parameter,
> I have tried several methods to no avail. When I use the following I do not
> get an error but I do not get data showing on the report.
> Expression is set to =Fields!Account.Value
> Operator is set to In
> Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> Can anyone help this newbie style question
> Regards
>
>|||Thanks for helping Matt
I now get the following error
The filter expression for the table cannot be performed,
cannot compare data of type system.string and system.object
any ideas
Thanks
Steve
"Matt M" wrote:
> I think you should be able to leave out the Join function.
> Set the value as: =Parameters!Select_Individual_Account.Value
> I do that in one of my reports and it works fine. Just make sure there is
> not a (0) at the end of the paramerter.
> "Are friends electric?" wrote:
> > Hi All
> >
> > I am trying to filter a table on my report based on a multi-value parameter,
> >
> > I have tried several methods to no avail. When I use the following I do not
> > get an error but I do not get data showing on the report.
> >
> > Expression is set to =Fields!Account.Value
> > Operator is set to In
> > Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> >
> > Can anyone help this newbie style question
> >
> > Regards
> >
> >
> >
> >

Multi value "Select All"

Hi all
I have a multi-value drop down list as my report's parameter.
Once the report is executed with the selected parameter, I am
displaying the selected parameter also as a part of the report's
results.
Is there a way I can display the word "All" in the report results when
the user selects "Select All" of the report parameter?
At present it shows a long list of concatenated values of the drop down
list.
Thanks heapsI might be misunderstanding you, but you can display the parameter
label that is selected: Parameter.label instead of parameter.value|||I am sorry if my question was not clear.
I tried Parameter.Label, but gives an error if the parameter is a Multi
Value type.
So, one has to use Join(Parameter.Label,",").
This displays a list of all the paramters that were selected.
What I want is, if the user selects "Select All" instead of showing the
list of parameters, i should be able to display the word "All".
Does this make sense?
Thanks

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
>

Multi Parameter Select query

Hi All,

I have a procdeure as written below.

I have created datasets in te report and in Report parameters clicked the Multi-value Parameter option.

When I run the report, I get all the customer names, when I select one customer report returns correct data. When I select two customers in the list box, the result set is empty. Can anyone guide me on what the error could be?

Thanks

Josh

Procedure:

create procedure MyMultiReport @.customername nvarchar(30), @.businessplantype nvarchar(30), @.businessplanyear nvarchar(10) as

Select PlanDatameta.sort,sysperiod.id,Planmeta.id,Planmonthlydata.Productmainpkey,Country, BusinessDivisiondescription, PlanSegmentPkey, Plantext.referencepkey, Plantext.usage, sheet, name, Plantext.text, Brand, Size, text1, PlanDatameta.sort+' '+Plantext1.text as LineDescription,line, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12, Total from Planmonthlydata join Plantext on Plantext.referencepkey=Planmonthlydata.Plansegmentpkey join PlanDatameta on PlanDatameta.pkey=Planmonthlydata.PlanDatametapkey join Productdescription on Productdescription.Productmainpkey=Planmonthlydata.Productmainpkey join Productmain on Productdescription.Productmainpkey=Productmain.pkey join Plansegment on Plansegment.pkey=Planmonthlydata.Plansegmentpkey join bpamain on bpamain.pkey=Plansegment.bpamainpkey join sysperiod on sysperiod.pkey=Plansegment.sysperiodpkey join Planmeta on Planmeta.pkey=Plansegment.Planmetapkey join Plantext Plantext1 on PlanDatameta.pkey=Plantext1.referencepkey where Planmonthlydata.status<>'d' and (PlanDatameta.sheet='PlanProductSummary') and Plantext.text<>'' and (PlanDatameta.line='MyPlanBaselineVolumeBasic' or PlanDatameta.line='BaselineVolumes' or PlanDatameta.line='IncrementalVolumes'or PlanDatameta.line='TotalVolumes') and name in (@.customername) order by PlanDatameta.sort,Plantext.text,text1

return

Hi,

If thecustomer name is the multi valued parameter you can't use that customer name @.CustomerName in the Where clause.

suppose Customer Name parameter contain these values:aaa, bbb , ccc then if you select the aaa, bbb from the parameter it will pass to the Stored procedure in the following format:

@.CustomeName='aaa,bbb'

When you selecting the one value from customer name parameter it is passing to the stored procedure like this: 'aaa' this when you used in In cluase will give you the result.

This @.CustomeName you can't directly use IN Cluase of where.

Select .. from Where name in('aaa,bbb') this will not give any result just you check by running the above select.

Instead you can do one thing

a)First create a table valued function like the following which will take the @.CustomerName as Input parameter and will return the table containg the

aaa

bbb splits the input string by comma and place in the Table.

ALTER function [dbo].[GetCSV]

(@.array varchar(max))

Returns @.t Table (Col1 varchar(max))

as

Begin

DECLARE @.separator_position INT

,@.array_value VARCHAR(1000)

,@.separator CHAR(1)

Set @.separator=','

--For my loop to work I need an extra separator at the end. I always look to the

-- left of the separator character for each array value

SET @.array = @.array + @.separator

-- patindex matches the a pattern against a string

WHILE PATINDEX('%' + @.separator + '%', @.array) <> 0

BEGIN

SELECT @.separator_position = PATINDEX('%' + @.separator + '%',@.array)

SELECT @.array_value = LEFT(@.array, @.separator_position - 1)

INSERT INTO @.t SELECT @.array_value

-- This replaces what we just processed with and empty string

SELECT @.array = STUFF(@.array, 1, @.separator_position, '')

END

Return

End

And use that resulted table in the where clause of your select statement

Select ..

from ..

Where name in (Select * from dbo.GetCSV(@.CustomerName))

It will give you the result.

Hope this helps.

Thanks

|||

You can also use dynamic SQL and filter the records and put them in a temporary table (#temp) first and use this query clause in main query:

code for dynamic SQL:

DELCARE @.strSQL VARCHAR(MAX)

CREATE TABLE #temp (name VARCHAR(50))

SET @.strSQL = 'SELECT name INTO #temp FROM [Table] WHERE name IN (' + @.customername + ')'

EXEC(@.strSQL)

code for main query:

AND name in (SELECT name FROM #temp1) AND ....

Also, dont forget to change the datatype of the input parameter @.customername to NVARCHAR(MAX).

Shyam

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?