here is what i am wanting to do but is it possible to use multi fields with an IN statement?
SELECT *
FROM usmastf
WHERE usm_book, usm_acct IN
(SELECT usac_book, usac_acct
FROM uscommf
WHERE usac_code = 'O/W')
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 5 Replies View RelatedHello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType
from Product
order by ProductType
Any suggestion? thx
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
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
View 1 Replies View Related
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO
Anyone have any ideas?
Thanks,
Deb
I have a multi value field into which I have placed several different values. I wish to compare a value that is entered by the user with the multi value field, to find if the value entered is there. I am assuming ( and also hoping) that I can use DLookUp to do this. At the moment I get all of the multi values returned by DlookUp and they are separated by commas and the value that I want to find is in there. However this is giving me an error because of the separating commas. How can I isolate/extract the value that I want so that I can make a comparison ? Is it something like MyFieldName.Value?
View 12 Replies View RelatedI have a table (table1) that has a bunch of fields....[field1][field2][field3][field4][field5]None of these fields are unique, but if I combine them, then they areunique.I know there is a way to make multi-field primary keys, but when I tryI get an that field1 is not unique, which I already know,How can I make a multi-field primary key?thanks
View 2 Replies View RelatedHello,
Can anyone say me how i can make a Report with Parameter Boolean field,and as Default Value true and False. ( Both ).
With Multivalue and in the Query = Field in (@BoolPara) have i a Error in the Query.
Thanks
Trying to get a multi field grouping to print.
Client + Req Number + Status
Client and status are nvarchar fields
Req Number is an int.
Keep getting # Error for this field on the report. on the report.
It works in Access - should it be this difficult?
Thanks!
Terry
Hi, Im trying to run this query
UPDATE DataModif
SET t.Ind_des = Replace (t.Tit_Des,"'",'"')
FROM Tit_Modificables t
WHERE
t.Ind_num in
(SELECT CAST (t2.Ind_num AS VARCHAR(10))
FROM Tit_Modificables t2
WHERE t2.Emp_id ='1100004' AND t2.Ejercicio_fiscal = 2003 AND t2.Nom_tabla = CAST (10 AS NUMERIC))
but ir sends this message error
The multi-part identifier "t.Ind_num" could not be bound.
I don't know if there is a problem because I'm using an Update.. FROM
Any help?
Hi,
When I try execute one query in SQL 2k5, with alias in order by clausule, I retrieve the follow message:
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "alias.fields" could not be bound.
Where alias is a any alias and, fields is a field of the table with alias.
Already exists one fix to patch this?
Thanks
Greetings,
I have several reports for which the user has asked to have an optional muti-value parameter. They want to be able to select zero, one, many, or all values in the parameter list. The parm list is created through a query and the values are not static.
I would like to allow the user to leave the muti-value field empty if they want to allow all values to appear on the report. I've read some discussion about populating a multi-value default with the same query that produces the multi-value list values - presto, everything is selected. However, this is not a desirable solution for me because I "echo" the users parameter selections in the report heading. Selecting all values (and some parms have a lot of values) would cause the "parm feedback" section to grow large and unreadable.
In short, I don't want to tell the user they have to select everything when they really want to select nothing.
Is there any way to have a muti-value parm that won't insist the user select one or more values?
Thanks,
BCB
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
View 2 Replies View RelatedIm trying to execute following update SQL:
UPDATE Property SET ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = ?
But following error:
[Execute SQL Task] Error: Executing the query " UPDATE Property SET
ImageList = U.ImageList FROM Property
M INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID
AND M.FeedID = U.FeedID WHERE M.FeedID = ?" failed with
the following error: "The multi-part identifier "M.FeedID" could
not be bound.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
ByPassPrepare is set to TRUE and ParameterName = 0 to variable User::Feed_ID
HOWEVER - following query executes fine:
UPDATE Property SET
ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = 11
Beats me - any help with explaining this to me
please?
Hi Everyone
This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment.
Mitesh
I'm looking to see if there are any best known methods or better ways of handling multiple multi select parameters in reports coming from reporting services.
It's not a big deal to account for this in T-SQL when there are only a few multi-select boxes but each new one that I add creates a whole bunch more work for me. Each of these multiselect list boxes are optional filters for the report. The way that I"m handling it today is as follows:
I'm turning the multi-select lists into comma seperated variables and then using Itzik Ben-Gan's split UDF to turn them into tables that I can join to my query. I then take the base query w/out the joins and pass that into a temp table. Then I start a bunch of IF THEN program flow to either JOIN or not JOIN the results of the split UDF.
Here is my example scenarios:
Assumptions:
I have a report that has 5 input drop down lists in reporting services.
I have a user stored procedure that takes the multi-select comma seperated list and turns it into a table variable.
I cannot use Dynamic SQL because of information security policy.
The multi-select lists are optional parameters.
The Code below is accurate.... I didn't test it and just wrote it quickly to show the complexity... hopefully you get the idea.
In scenario 1: One of the drop down lists are multi-select enabled.
My SQL Sproc Would look like this:
CREATE PROCEDURE [dbo].[get_my_ssrs_data]
@Origin_Warehouse_IDs varchar(max) = '',
@Ship_To_Geo_IDs varchar(max) = '',
@Sold_To_Geo_IDs varchar(max) = '',
@Customer_Type_IDs varchar(max) = ''
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#MyCoreTemp', N'U') IS NOT NULL DROP TABLE #MyCoreTemp;
SELECT
Origin_Warehouse_ID
, Ship_To_Geo_ID
, Sold_To_Geo_ID
, Customer_Type_ID
INTO #MyCoreTemp
FROM
My_Fact_Table
IF @Origin_Warehouse_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp
END
ELSE
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
END
In scenario 2: Two of the drop down lists are multi-select enabled.
My SQL Sproc Would look like this:
CREATE PROCEDURE [dbo].[get_my_ssrs_data]
@Origin_Warehouse_IDs varchar(max) = '',
@Ship_To_Geo_IDs varchar(max) = '',
@Sold_To_Geo_IDs varchar(max) = '',
@Customer_Type_IDs varchar(max) = ''
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#MyCoreTemp', N'U') IS NOT NULL DROP TABLE #MyCoreTemp;
SELECT
Origin_Warehouse_ID
, Ship_To_Geo_ID
, Sold_To_Geo_ID
, Customer_Type_ID
INTO #MyCoreTemp
FROM
My_Fact_Table
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
END
ELSE
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
END
In scenario 3: Three of the drop down lists are multi-select enabled.
My SQL Sproc Would look like this:
CREATE PROCEDURE [dbo].[get_my_ssrs_data]
@Origin_Warehouse_IDs varchar(max) = '',
@Ship_To_Geo_IDs varchar(max) = '',
@Sold_To_Geo_IDs varchar(max) = '',
@Customer_Type_IDs varchar(max) = ''
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#MyCoreTemp', N'U') IS NOT NULL DROP TABLE #MyCoreTemp;
SELECT
Origin_Warehouse_ID
, Ship_To_Geo_ID
, Sold_To_Geo_ID
, Customer_Type_ID
INTO #MyCoreTemp
FROM
My_Fact_Table
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
END
ELSE
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs != ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs != ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
ELSE
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs != ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
ELSE
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
END
ELSE
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
As you can see by scenario 3 where there are only 3 multi-select lists it gets really hairy. If I go to a forth it will be even worse. Does anyone know of another better way to do this?
Thanks a bunch in advance!
Letni
Hello,
Today I was creating a report with a multi-value parameter.
Someone asked me if it was possible to select all by default.
So can someone tell me how to check "Select All" by default?
Found some topics which said to set "Default Values" to "From Query" and use the same query, but that didn't work for me.
Thanks,
Rens
I want retrieve data from properties field in prifile table in aspnetdb.mdf for multi users,what do i ?
View 1 Replies View RelatedI want in my query to select a different field in case another one is null. in mysql i'd do it like this:
select
a
,if(b is null, c, b)
,d
from
alphabet
how can this be done in sql server?
thanks
if there are 2 insert statement
insert ... select * from table1 union all select * from table2
or
insert ... select * from table1
insert ... select * from table2
pls advise which 1 is faster ...
When creating a windows forms report (rdl), multi-value parameters are rendered with a nice drop down list with check boxes...including a (Select All) item. When you run this via a report server and render the report for the web, the multi-value selection shows up nicely.
Can I create a similar web form dropdownlist so I can duplicate the functionality with an RDLC report?
Jim
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
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!
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
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
Dear experts,
I would like to ask for a help regarding MDX query.
I am developing reports in SSRS 2005 that use some multi-valued parameters.
I need to filter out my data according to the dimension's properties' value
For example, if I have only single value parameters, I would do:
SET [SelectedDestination] AS FILTER( [Country].[Destination].Members, [Country].CurrentMember.Properties("KEY") = Parameters!Country.Value)
However, I have no idea how to handle it if Parameters!Country is a multi-valued parameter.
Is there a better method to do this? Would anybody please kindly give a direction? it will be greatly appreciated!! any help will do..
Thank you very much
My report runs from a stored proc, which gets the user to input a project id and a status. So the where statement in my stored proc looks like this:
Where (p.project LIKE @project_ID + '%' AND p.status IN(@Active))
On my report, I have set the @Active parameter to multi-value, and entered the available values(active and inactive). When I run the report, and only select one of the values, the report runs just fine, but when I use the "select all" option, I get no results at all.
I can't see where I am going wrong. Anyone got any ideas?
Thanks in advance!
Hi,
Has the inability to change the width of the multi-select drop down for parameters been fixed yet? Even if I could at least make it default to be wider would be great.
Thanks.
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
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
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
Hello,
since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"
First Parameter:
available vlaues: From query
dataSetsGetCountry (defined as select from tblCountries)
ValueFied:ContryName
LabelField:CountryName
DefaultValues: from Query
dataSetsGetCountry
ValueFied:ContryName
SecondParameter:
available vlaues: From query
dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)
ValueFied:AreaName
LabelField:AreaName
DefaultValues: from Query
dataSetsGetAreas
ValueFied:AreaName
First time I open the report
the first and the second parameters are properly filled and for both parameters "select all" is checked.
I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool
Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!
Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?
Thankx for any helps!
Marina B