MS SQL Server :: Multi-Select String Parameter Values Are Converted To N'Value1', N'Value2' In Clause When Report Is Run

Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:

Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!

View Replies !

How Do I Enter In A Default Values For A Report Parameter That Accepts Multi Values

I have my stored procedure set to
Territory_code IN (@Territory)

, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?

The field is a Varchar 20

View Replies !

Select Values From Multi-value Parameter
Is it possible to pass values from UI to a multi-value parameter in a report and from this report, select values from this multi-value parameter to finally display data?

Thanks!

View Replies !

Is There A Way To Select What Fields To Display On A Report From A Multi-value Parameter?
I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt.

Is this possible with reporting services 2005?

Thanks.

View Replies !

Passing An Image Converted To A String As A Parameter (RS 2005)
Can an image in a database be converted to a string in an aspx page, passed to the report as a string parameter, and then converted back to an image in the page header section of the report? The image value would have the expression:

=System.Convert.FromBase64String(Parameters!ImageString.Value)

Thanks,

John

View Replies !

Displaying Values For Multi-value Parameter
At the top of my report, if I want to display the selected value for a given
parameter, I can put this expression in a text box:

="The xyz param's value is: " & Parameters!paramXYZ.Label(0).ToString

How do I go about displaying all the selected values for a multi-value
parameter (one that has mulitple checkboxes in the drop-down list)? Is it
possible to do a for loop and iterate through the values of the param within
an expression? Something like

for i = 0 to Parameters!paramXYZ.Count - 1
mystring = mystring & ", " & Parameters!paramXYZ.Label(i).ToString

And then display mystring in the textbox? Or can I use a list control on the
report?

Thank you,

View Replies !

In String Comparison For Multi-Value Parameter
I have a multi-value parameter where a user can select from a list of locations, all populated from a table query except one that I had to hard-code. I am trying to evaluate the values selected and determine which query to run based on the selection. Basically it would look like this:

if "X" in @location then

select * from a
else

select * from b

Obviously this isn't pretty but due to "workarounds" this is necessary.

Any ideas/feedback would be greatly appreciated!

View Replies !

Multi-value Parameter Default Values Not Working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select  distinct
  CityName

from  dimHotel dH (NOLOCK)
Inner join  factHotel fH (NOLOCK)
on  dh.HotelKey = fH.HotelKey

Where  dH.CityName <> ''
and  dH.CityName is not null
and  fH.ClientKey in (@ClientID)

Order by  CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas??

Thanks!!

Thanks

View Replies !

Report Parameter - Suffix A Text To Report Parameter Prompt String

Is it possible to include a text/string as suffix(append to the end of textbox) to an report parameter prompt string ? Something like this

Enter Date: [ Textbox ] format:mm/dd/yyyy

Enter Age:[Textbox ] e.g 50

I need to include text like the one that I have highlighted in blue. Could someone help me out?

View Replies !

How To Display Multiple Values On Report Page From A Multi-value Report Param
How do I display multiple parameter values on report page from a multi-value report parameter. For example, I have a report parameter where users can select multiple attendance codes and I want them displayed at the top of the report after it's run.

Currently, only the first value is showing on the report.

Thanks.

View Replies !

Parameter Multi-Select All Upon Boolean Select True
I know this is simular to a previous post but I have a slightly different slant and need help.

I have a Boolean Parameter that when selected should make a multi-select parameter set to 'All'

How to do this in Dataset?

Is this cascading parameters concept?

View Replies !

Multi-value Parameter In Master Report Passing To Single Param Sub-report In A List.
Here's tricky one.

I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.

Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?

I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.

Essentially, I want to create a for each loop.

Any ideas?

View Replies !

Returning All Selected Values In Multi-valued Parameter
How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where

(Expression)

=Fields!LOCATION_ID.Value

(Operator)

=

(Value)

=Parameters!Loc.Value(0)

This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.

View Replies !

String Type Multi-Valued Parameter Issue
Hi There.

I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about?

Thanks, Mike

View Replies !

Displaying &&"All&&" On A Report When &&"Select All&&" Is Chosen From A Multi-value Parameter
Hi All

New user here. I've got a report with a couple of multi-value parameters and I'm displaying the selected values on the report using = Join(Parameters!ReportingGroup.Value, ", "). This works properly.

The users now want the report to display "All" if "Select All" is chosen, rather than showing the whole list.

Can anybody help me with this? I'm thinking of using an IIF statement in my expression, but have no idea how to define the condition?

Thanks in advance

Grant

View Replies !

Reporting Services 2005 Multi-value Parameter Possible Values Search
Hi there,

I have a problem with multi-value parameter in ReportingServices2005, hope to find a solution here.

if I use ordinary parameter in a report(multi-value checkbox is not selected), after deployment I can click on drop down list and type possible value of that parameter using keyboard(it will be selected from what i typed).

if I use multi-value parameter somewhy it doesn't respond to anything i type from my keyboard.

Why is that?
What should i do to make that multi-value parameter select possible result from what i type?

View Replies !

Multi Value In Report Parameter Using MSVS Report Designer.
Hi All,

I have a question, How to user the Multi value in Visual Studio Report Designer.

My dataset contains the following

Select empno,empname,basicpay,deptcd from employee where deptcd in (substring(@gdept,1,4))

Here variable gdept is the report parameter. I am passing the value.
it works fine with single value.

( the source of gdept is a another dataset to list the deparment code+name,

Example

DEP1 - COMPUTER

DEP2 - ADMIN

DEP3 - FINANCE

)

when I preview, i have a combo box to select the department.

If I choose one deparment, it works fine.

If I choose more than one department or select all I have error message

"substring function requires 3 aruguments."

what is wrong is the query

Please advise.

Cheers,

Saleem

View Replies !

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

View Replies !

Multi Select Parameter Problem
I have a multi-select parameter for a report, but If I select more than one value the report does not return any data. However, if only one value is selected data for that value is returned. I am using the IN option for the SP parameter.

SELECT Code,Description FROM Product

WHERE Code > 0 AND Description IN (@Description)
I am using SP2 on SQL2005, also I do not have the option of Select-All which I expected to be present.
Any help would be most appreiated.

Thanks

JohnJames

View Replies !

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,text1return

View Replies !

Multi Select Type-in Parameter
Hi All

Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.

My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..

I'm using SSRS with SSAS cube all in BI all 2005

Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.

Thanks in advance

Gerhard Davids

View Replies !

Multi Value Report Parameter
HI there.

I have been working on this for quite some time and I can not figure out what I am doing wrong.
I have a report filter called ItemFilter and I have declared as a report parameter where the multi value checkbox is checked. The values from this filter are coming from the item table.
SO the users drilldown on the filter when they run the report an select the Items that they would like to display info for. I had the following code before in the where

where
([CRONUS International Ltd_$Item].No_ = @itemfilter). This only worked if they selected on item from the list.

I want them to be able to select multiple Items or all. The code above did not work because there were commas separating one item from the other and I was getting an error.

I changed to

whrere

([CRONUS International Ltd_$Item].No_ IN (@itemfilter)).

Now this only returns the first item that I enter on my Itemfilter parameter.

Does anyone have any clue in regards to this ?? I am sure I am using the wrong syntax, but I do not know what to change it too.

Thanks

View Replies !

Expression Problem Using A Multi-Select Parameter
I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.

My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.

The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.

How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?

I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.

View Replies !

Multi-Select Parameter Issues - DataType Also?
I've tried several things to get my multi-select parameter to take more than one value but it seems to only take the 1st one I input. I've been working on this for days and been trying alternate methods but nothing seems to work. I think my first question is if I'm planning on using a parameter as a multi-select parameter what is the data type I should call that parameter. In my case here, the evt_key is unique and will always be 36 characters. I cant imagine in my lifetime that I'm going to have any more than 20 items in the multiselect. Am I looking over something or any other suggestions? I also attached my sp at the bottom.

Thanks

____________________________________________________________________

CREATE procedure dbo.rpt_client_ngcoa_event_registration_attendees_by_state
@evt_key varchar(4000)
as

set nocount on
set transaction isolation level read uncommitted

create table #tmp_dates
(
tmp_evt_key varchar(4000),
tmp_title nvarchar(160),
tmp_start_date datetime
)

create table #temp
(
tmp_key varchar(4000), -- event
evt_title nvarchar(160),
reg_key varchar(38),
reg_session_code nvarchar(400),
type nvarchar(20),
reg_name nvarchar(300),
reg_sort nvarchar(300),
reg_org nvarchar(300),
reg_date datetime,
amount_paid money,
adr_city_state_code NVARCHAR(110) NULL,
adr_state NVARCHAR(40) NULL,
adr_country NVARCHAR(60) NULL,
adr_city NVARCHAR(40) NULL,
adr_post_code NVARCHAR(20) NULL,
src_code NVARCHAR(50) NULL,
days_out int
)

/*
-- FOR MUTLI SELECT FUNCTIONALITY --
create table #tempet
(
tmpet_key varchar(4000)
)

if @evt_key = '' or @evt_key is null

begin
insert #tempet
select distinct evt_key
from ev_event
where evt_key in ('00394886-dfc7-4466-b674-1f2b3ede79ea', '10BE664D-DA4F-44F4-802C-ABD4FC015FB4','56504014-9787-4207-8FAD-EC6C6A384C1D')
and evt_delete_flag = 0
end

else
begin
insert #tempet
exec _selectstringfromstring @idstring = @evt_key,@intorstring='S'
end

if @evt_key = '' goto finalselect
set @evt_key = dbo.av_preprptguidparam(@evt_key,'returnnull')

*/

IF @evt_key='' SELECT @evt_key=NULL

insert into #tmp_dates
select distinct
reg_evt_key,
evt_title,
evt_start_date
from ev_registrant
join ev_event on reg_evt_key = evt_key
--join #tempet on evt_key=tmpet_key
where reg_delete_flag = 0
and evt_delete_flag = 0 --and reg_evt_key = '10BE664D-DA4F-44F4-802C-ABD4FC015FB4'
and (@evt_key is null or (@evt_key is not null and reg_evt_key = @evt_key))
---AND (@evt_key IS NULL OR (@evt_key IS NOT NULL AND reg_evt_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))

insert into #temp
select tmp_evt_key,
tmp_title,
reg_key,
-- net_prc_code,
case net_prc_code when null then tmp_title
when '' then tmp_title
when 'NULL' then tmp_title
when ' ' then tmp_title
else net_prc_code end,
'Reg Code',
cst_ind_full_name_dn,
cst_sort_name_dn,
cst_org_name_dn,
dbo.av_end_of_day(reg_add_date),
net_payamount,
adr_city_state_code,
adr_state,
adr_country,
adr_city,
adr_post_code,
src_code,
datediff(dd, reg_add_date, tmp_start_date)
from #tmp_dates
join ev_registrant on reg_evt_key = tmp_evt_key
join vw_ac_invoice_detail on reg_ivd_key = net_ivd_key
join co_customer on cst_key = reg_cst_key
LEFT JOIN co_customer_x_address x ON x.cxa_key = reg_cxa_key AND cxa_delete_flag = 0
LEFT JOIN co_address a ON a.adr_key = x.cxa_adr_key AND adr_delete_flag = 0
LEFT JOIN co_source_code o ON src_key = reg_src_key AND src_delete_flag = 0
--join #tempet on reg_evt_key=tmpet_key
where reg_delete_flag = 0
and reg_cancel_date is null
and cst_delete_flag = 0
AND (@evt_key IS NULL OR (@evt_key IS NOT NULL AND tmp_evt_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))

finalselect:
select *
from #temp
--where (@evt_key IS NULL OR (@evt_key IS NOT NULL AND tmp_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))
order by days_out desc, type, reg_name
GO

View Replies !

Report Parameter And Multi Value Prompt
Hi all

Basically I had 2 paramater which ComputerName and MemberName.

The membername was set as Multi-Value as sets as follow:
Label: Value:
Exclude Domain Admins Domain Admins
Exclude Local Install Local Install

And the query is:

SELECT id,
ComputerName,
GroupName,
MemberName,
ScanDate
FROM GROUPMEMBERS WITH (NOLOCK)
WHERE ComputerCN LIKE '%' + @ComputerName + '%'
AND MemberName NOT IN (@MemberName)
ORDER BY ComputerName ASC

The prompt is like this:
Computer Name [ ] Excluded by [ ]

The issue is in the second prompt I have to select
"something" which is I don't want to. I want the user to have this parameter as an optional.

Any ideas?

Thanks

View Replies !

Dropdown - Multi-select Won't Show Values If Only One Item Is Available
I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.

When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.

Since we can't attach anything here, below is the link to a screenshot of my issue:
http://docs.google.com/View?docid=ddd6j2xn_52c5qd5

If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.

What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.

Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.

Notes:
+ No special chars are in either dropdown
+ I am using IE 7 (Also had someone test this on IE 6 - same problem)
+ Using Visual Studio 2005 to build report - where this works fine

Work around:
+ If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2

Please share your thoughts
Thanks,
h.

View Replies !

Report Parameter As A Where Clause
Hello

Is it possible to construct a dataset where the parameter of the report is the where clause?

I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.

View Replies !

Type-ahead In Multi-select Parameter Lists?
When running reports in preview mode in Visual Studio I can use type-ahead to get to selections in a dropdown multiple select parameter list by typing the first letter; typing w for instance will go to the first word in the list starting with 'w' and will automatically check it. Very useful if the lists are long enough to require scrolling. When the report is deployed to the Report Server Web page the type-ahead feature disappears. Is this because the dropdown is presented as HTML? Is there any way to get the type-ahead feature short of using a Report Viewer within a .NET application. I prefer the simplicity of the Web deployment rather than creating a VB or C# application just to show the report. I noticed that, for single-select parameters, the type-ahead still works; I'm guessing that is because they don't have the checkboxes in front of the words. Thanks for any help.

View Replies !

Null Values For Datetime Values Converted To '0001-01-01'
Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.

If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If

Any help welcome.

View Replies !

SSRS 2005 Report Parameter Multi Value
I know this is the code to add a single valued report parameter to a report via asp.net

Dim paramList As New Generic.List(Of ReportParameter)

paramList.Add(New ReportParameter("MyParameter", "MyValue", False))

Me.ReportViewer1.ServerReport.SetParameters(paramList)

But how do I go about adding a multi valued parameter?

View Replies !

Store Multi-Select Values In An Image Data Type?
I was working on figuring out where a certain application wasstoring the multiple selection choices I was doing through the app.I finally figured out that they were being store in an IMAGEdata type colum with the variable length of 26 bytes.This is the first time I ran into such way of storing multipleselections in a single Image data type.Is this a better alternative than to store into a One-to-Manytables? If so then I'll have to consider using the Image datatype approach next time I have to do something like storing1 to thousands of selections.Thank you

View Replies !

Pass Multi Selected Values To Another Report(ssrs2005)
hi,
i am using ssrs 2005 and need to pass the multi selected parameter values to another report. how can i achieve this task? I use jump to report option.

View Replies !

Select Stored Procedure With One Parameter And A Where Clause
Here is my procedure:
ALTER PROCEDURE dbo.SelectMeds
@RX int
AS
SELECT RX FROM tblMeds WHERE RX= @RX

RETURN
but it return no rows, how do I fix this?

View Replies !

Control Number Of Options Selected In A Multi Select Parameter
Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.
Many Thanks
Delli

View Replies !

How To Change Report Name Based On Multi Valued Parameter
Hi All,

I'm having trouoble with changing my report name when I swtich to different parameter values.

I use the following expression.

IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total"))

This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work.

Is there any solution for this?

Thanks

View Replies !

How To Display The Selected Multi-value Report Parameter/s In A Text Box?

Hi All,

I have a report parameter which i multivalue. I want to display the selected value in a text box. I have written the following code :

=Switch(Parameters!ServiceAttribute.Count = 1,Parameters!ServiceAttribute.Value(0),

Parameters!ServiceAttribute.Count = 2,(Parameters!ServiceAttribute.Value(0) & ", " & Parameters!ServiceAttribute.Value(1)))

Which suggests that if only one of the two multivalue parameter id is selected display the same (Parameters!ServiceAttribute.Value(0)).

This works fine when i select both the attributes but throws an exception "Index was outside the bounds of the array" when i select only one of the parameters. Can anyone help me with that?

Also i want this report parameter to allow null i.e. if a user does not select anything he should still be able to view the report.
In case of regular dropdowns i have added a <Null> value to the existing values and set the default to null. But in case of multi-value, it does not give an option of adding <Null>

View Replies !

Displaying Multi Parameter Info : Report Builder
Hi All,

Is there is any way to display the User selected parameter information if the parameter is multiselect ?

I was able to display if the parameter is single valued but if it is multivalued (list), i was not able to .

Any help is greatly appreciated.

Regards,

View Replies !

Multi-value, Non-queried, Report Parameter Expression Problem
I need to use a non-queried report parameter to filter a dataset for a report.

The dataset column I'm filtering is numeric. The dataset is not a sproc, it's a table in SQL Server that I am querying.

The Non-queried parameter values (Multi-value) are 1, 2, 3, 4, 5, >=6.

Selecting the >=6 throws the error: "Error converting data type nvarchar to numeric."

Which sucks.

Because...when I go straight into my dataset I can filter my numeric column with the exact same values (=1 or =3 or >=6) and everything works fine. The error is only raised when I use the @Parameter in the dataset.

I've tried eveything, researched everywhere online and I can't find any guidance anywhere.

View Replies !

Accessing Values Parameter Values From Another Report
Hi, How can I display a value of a report parameter from one report into a textbox on another report??

View Replies !

Remove &&"Select All&&" Options From Multi Select Parameter Dropdown
Hi All

I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.

Is there any way I can remove that option from the list?

Thanks

View Replies !

Keep A Multi-valued Parameter Cleared From Selections Until The User Select His Choice
I have a report that includes two multi-valued parameters.
In the Default Values section, I choose 'from query' and select dataset and value field.
In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field.
When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected).
How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.

View Replies !

(Report Viewer) Problem Setting Multi-valued Parameter
I have a small problem with the reportviewer control that i just cannot seem to get around. I have report with a text box and a dropdownlist. The text box is used to specify a user name and the dropdownlist contains the list list of companies that the user can access. The dropdownlist is dependent on the text box. The report works fine on the reporting portal and in visual studio. The problem I am having is setting the values of the dropdownlist from a web page. The dropdownlist is a multivalued parameter. If I add one value to the parameter value collection that will be used to populate the the dropdownlist then the report works fine. the problem occurs when I Try to add more than one value to the report parameter collection. for example:

                for (int arrLengh = 0; arrLengh < arr1.Length; arrLengh++)
                {
                    reportParam.Values.Add(arr1
[arrLengh].ToString());
                }

Please note that the values are actually added to the collection. The problem is that none of the items in the dropdownlist is selected when more than one value is added to the report parameter value collection.

Is there a special way to set multiple values for a multivalue parameter in reportviewer for remote mode. Your help is greatly appreciated.

View Replies !

Error With Multi-Valued Report Parameter Using Stored Procedure
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:

CREATE PROCEDURE spprodsales
@productid int

AS

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

RETURN

And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

So, can anyone please help me out possibly using the same stored procedure as above.

Thanks,
Kripa

View Replies !

Sub Report Parameter Selections Via Values From Master Report Data
I am designing a report which will be used under the subreport control of a master report, now i am using a stored procedure for the subreport which has two parameters.

In the subreports dataset parameters tab, how can i mention the master reports columns, since i do not see the master reports columns here. since it is a different report.

If i use a hardcoded select query then i can just say

select address, city, state, zipcode from customer where (customerid = @customerID)

but i am using a stored proc. how to define the parameters under datasets parameter tab.

Thank you very much for the information.

View Replies !

Converting String Parameter Values To Date

Hi,

I have a calendar parameter in one of my reports. The values of the calendar are of the form 'yyyy-mm-dd hh:mms'. And they are string values.

Now when I generate the report I have a textbox that takes the parameter label (e.g., parameter!calendar.label). However, I would like to format (or trim) the label as just 'yyyy-mm-dd'. Does anyone know how I can do this? I tried to change the parameter value format to datetime but this just throws an invalid datatype error...

cheers,
Al

View Replies !

Mapping A String Field To Boolean Output In SELECT Clause
Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

View Replies !