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 Complete Forum Thread with Replies
Related Forum Messages:
Multi-Select String Parameter Values Are Converted To N'Value1', N'Value2' In Clause When Report Is Run
I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like: Where columnName in (N'Value1', N'Value2', N'Value3'...) This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!? Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar. Thanks, TC
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 !
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 !
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 !
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 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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
Filtering Data Based On Select Parameter Values
Hi, I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters: @subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these: FRRIJ FRRIC FRMM ROBECO FRJV MAIL FRUKV FRICE Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How? @subaccount VARCHAR(8) = NULL , @numDaysCutoff INT = 1 , @numDaysPcts INT = 1 SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount) , SecurityID = ISNULL(h.security_id, lo.security_id) , SecurityName = s.name , QtyHeldAndPending = ISNULL(h.quantity, 0) + (CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1) WHEN lo.type = 2 THEN lo.resulting_quantity ELSE 0 END ) , L.AverageDailyVolume , XDaysVol = L.AverageDailyVolume * @numDaysPcts , CutoffVol = L.AverageDailyVolume * @numDaysCutoff , DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0) , HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100 , TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd , CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd , TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100 , DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * s.price_usd), 0) , NDaysCutoff = @numDaysCutoff , NDaysPcts = @numDaysPcts FROM subaccount_positions_table h --vGlobalHoldings h JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id) LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99 WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount) OR lo.subaccount = ISNULL(@subaccount, h.subaccount) ) AND status = 1 AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol OR -- Targeted Vol exceeds cutoff ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) > ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol ) ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id) Thanks in advance!!! sqlnovice123
View Replies !
Different Results With Sproc As Opposed To Simple SELECT (parameter Values)
Could someone please tell me why the following SELECT statement... SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)) ...gives me 9 rows back, but embedding the exact same statement in the following sproc... set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)) ...and passing the exact same parameter values to with the following execute statement... USE [Geomusdb] GO DECLARE @return_value int EXEC @return_value = [petrander].[DynamicQuery] @museum = 1, @collection = 1, @binomen = N'le', @locality = N'Fakse' SELECT 'Return Value' = @return_value GO gives me 0 rows!? What is different!? Any help is greatly appreciated...
View Replies !
&&"Select All&&" As Default For A Multi-value Parameter
Hi! I have the following problem: In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list). I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this? [The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued] Any help will be greatly appreciated! Thanks Stefano
View Replies !
SSRS Toggle Parameter Between Multi-Value And Single Value Based On Another Parameter
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful. Thanks
View Replies !
Help With Multi Join Or Multi Tier Select.
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
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 !
How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters
Hi, I have parameters in my report. The user can choose the year, month and date (3 parameters). Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter. My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension . Does somebody know how to make it possible to set default values for this parameters? Other question : Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007". But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All". Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future. Thanks ! JF
View Replies !
Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
View Replies !
Refreshing Parameter Values When A Higher Level Parameter Changes
Hi All. I have a parameter (hidden) that gets its value using an expression base on another parameter. When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes. When I change the parent parameter again, the value of the child parameter does not seem to change. How can I make this parameter change automatically when the parent is changed ? Any help will be appreciated. Thanks, Neil
View Replies !
Multi Value Parameter
hi, i read from this forum that to pass array to report is using multi value parameter . my problem now is 1. can i pass multi dimension array. 2. how do i show the multi value parameter in a table . for example i have a multi value parameter that may contain 10 or 20 array . how do i dynamic it show in table. rgds, charles
View Replies !
Multi Value Parameter
Hi I'm trying to create a multi value parameter of type integer. My sproc looks something like this.. CREATE PROC spRptNewMatterMSRS ( @BUnitID bigint, @FromDate datetime, @ToDate datetime ) AS ... ... ... WHERE (BUnit.BusinessUnitID IN (@BUnitID) OR @BUnitID IS NULL) Then i try run the report passing in 3 values for @Bunit -> 200,201,202 I receive the error -> can't convert bigint to string Is it possible to use the multi value params for integer arrays on a sproc? Thanks Dave
View Replies !
Multi Value Parameter
Hi, In my report i have a multi valued parameter, when i view my report in the Web application with Report Viewer, The multi valued parameter is displaying in light color.Can we able to change this. Thanks in advance
View Replies !
Multi Value Parameter
Hi, In my report,I have multivalue parameter for this parameter the Available values are from the Dataset.Suppose the dataset contains Names like this: 'AL - Alabama Poison Center (Tuscaloosa)', 'AL - Regional Poison Control Center (Birmingham)', 'AR - Arkansas Poison & Rock)', 'Arizona Department of Health' But my problem is the Multivalue parameter TextBox is of fixed size.Now i want the size of the text box to the size of the name which is of length long in the Name. Whether it is possible or not. How to achieve this.
View Replies !
Multi-value Parameter
Hi: I am building a report and have a few parameters. One of this parameters is set up as Multi-Value. When I only select one value everything is running fine. But when I select multi values I get an error saying I must declare my variable. Any idea why this is happening? Ben
View Replies !
Multi-Value Parameter
I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code: Code Snippet SE [RC_STAT] GO /****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] (@Region int = Null) AS BEGIN SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type, Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description, ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty, Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget, Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period, Forecast.SalesPerson_Purchaser_Code FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN (SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget ) AS Forecast INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code WHERE (Region.Region_Key IN( @Region)) AND (Forecast.Budget_Year = 2007) END what am i doing wrong?
View Replies !
Adding Values To A Parameter That Can Take Multiple Values
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
View Replies !
Sum Up Multi Values Same Column
Hi All, I have the following Ex: Table A Col A <- Col that I need to check against Col B Col C <- Sum this col So if Col A has the following values: Col A Col C 2 10.00 4 15.00 2 25.00 4 15.00 3 10.00 3 5.00 7 4.00 9 20.00 I need to bring back the sums of 2, 4 and 3 in one resultset. How would I do this? Thanks, JJ
View Replies !
Help With Sproc And Multi Parameter
I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters. Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction ( @ClientID int, @Priority int, @TStart datetime, @TEnd datetime, @Status nvarchar, @ConTypeID int, @Caller nvarchar, @Keyword nvarchar ) AS SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDate FROM tblTasks WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND (ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL) RETURN
View Replies !
Empty Multi-value Parameter
Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL: WHERE ( [Field1] = @Field1_Filter OR @Field1_Filter IS NULL ) How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter. I also don't know how you would check for the "not entered". Someone suggested the following SQL: WHERE ( [Field1] IN (@Field1_Filter) OR @Field1_Filter IS NULL ) This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ". How is this supposed to be done? Thanks for any insight
View Replies !
Random Multi-Value Parameter Bug
Hi, I have a multi-value parameter who's domain values are being populated and defaulted from a simple query. There are exactly 80 domain values available in this parameter and most of the time it works. About every 1 in 4 times the report is loaded, the report pulls all of the domain values but none of them are selected by default. Now, we have about 30 more reports which are almost identical, and contain the same parameter, that don't have this problem. Has anybody ever experienced this? Is there a viable solution to this problem? Thanks! Tennyson
View Replies !
Multi Value Integer Parameter
I am trying to create a report with a multi value integer parameter. I have tried ="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Parameters!StatusCode.Value + ")" I get an error stating An error occurred during local report processing. An error has occurred during report processing. Cannot set the command text for data set 'ProjectRequestStatus'. Error during processing of the CommandText expression of dataset 'ProjectRequestStatus'. Has anybody worked with integers? Thanks, Fred
View Replies !
Allow Null Value In Multi-value Parameter
Hello, I have a multi-value parameter field and each item comes from a table in the DB. This multi-value parameter allows to select all or some of the items. What if I don't want to select ANY item?? I read in the forum the several tray to do the same but the answers are not so clear.. If I say that at the end is a bug in SSRS... AM I right? Thank you Marina B.
View Replies !
Multi-value Parameter Calculation
OK Guys.... If you answer this one, you will save my life...and you WILL be the MAN or WOMAN!!!! Problem: I have a set of 24 matrix's that need to calculate the difference between the last two years and display in a field to the right of the last rendered column. Since I have been struggling with this, let's just assume there is no better way than how I currently have it set up. (one table that does the calculations for me and I set one field on the report to display the most recent two columns difference in my report) what I can not figure out is: when I choose one of my parameters the report displays the information I want...but when I choose more than one...well there is the problem.... In order to obtain the most help for myself I will ask this in the most general way possible so as not to get bogged down into my specific solution... Desired Result: How to pass all my parameter values from my multi-value parameter during runtime to a SQL Stored proc from my dataset within Reporting Services at runtime, Match the parameter to the field, get the result and store it in a variable, then do it again and add the second to the first within the variable, and so on and so on , until all of the parameters are used. Then sum the values and display in a field. HELP, HELP, HELP Please....
View Replies !
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?
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 !
Multi-Value Parameter Width
Good afternoon, I've designed a Reporting Services report that has a multi-value parameter. The report works just great. The only issue I'm running into is that the users are complaining that when they want to select the values, within the multi-value parameter box, they have to scroll it to the sides way too much. This also makes them select values by mistake. Is there away to inscrease the width of the multi-value parameter box? Best regards.
View Replies !
|
TRACKER
