I know I can't do this but I dont know will work. any help is much appreciated. the code below in red will always return more than one value, and yes the ?? is actually data (dont ask i didnt do it).
I am basically coding this because of the ?? someone before me thought was a good idea.
inner join MHDDTest.dbo.Admin_tbl_Vendor f on
(case when len(b.vadr) > 0 then
(case when (b.vadr = '??') then
(select vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)
else
b.vend + '-' + b.vadr
end)
else
b.vend
end
= f.vendorNumber)
SELECT CASE WHEN WM_Template.WBS2 = '500' THEN '1750.00' + WM_Template.WBS2 ELSE '2000.20' + WM_Template.WBS2 END AS WBS2, WM_Template.WBS3 AS WBS3, CASE WHEN WM_Template.WBS2 = '260' THEN 'Pre Con Mtg / Inspections/ Punch List' WHEN WM_Template.WBS2 = '250' THEN 'All Environmental Permits & Approvals' WHEN WM_Template.WBS2 = '150' THEN 'Project Admin / Clerical / Status Reports ' ELSE WM_Template.LongName END AS LongName, (ISNULL(LB.AmtBud, 0) + ISNULL(EB.AmtBud, 0)) AS AmtBud, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND LedgerAR.Period <= '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BTD, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND LedgerAR.Period = '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BillExt FROM PR LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '') AND WM_Template.WBS2 <> '210' AND pr.wbs1 = '001-298' GROUP BY WM_Template.WBS2, WM_Template.WBS3, WM_Template.LongName, lb.amtbud, eb.amtbud
second query
SELECT WM_Template.WBS2, CASE WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'HM & SC' WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN 'HM' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'SC' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') THEN 'SC' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') AND (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM & SC' END) ELSE 'N/A' END AS FeeType FROM PR LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '') AND pr.wbs1 = '001-333' GROUP BY WM_Template.WBS2
I have one data table (tblPresultPeriods) and one lookup table (lkpStatus).
The query is:
select PPPeriodNumber as PeriodNumber, Max(PPstatus) as Status from tblPresultPeriods where ppmemberid = 133 and pptransseq > 0 group by ppperiodnumber
It returns (correctly):
PeriodNumber Status ------------ ------ A1 1 Q1 4 Q2 1
I want to do a join so that I get the lkpStatus.StatusDescription value to display.
The query is:
select PPPeriodNumber, Max(PPstatus)as status, StatusDescription from tblPresultPeriods, lkpStatus where tblPresultPeriods.PPstatus = lkpStatus.Statuscode and ppmemberid = 133 and pptransseq > 0 group by PPPeriodNumber, PPstatus, StatusDescription order by PPPeriodNumber, PPstatus, StatusDescription
but I get (not what I want):
PPPeriodNumber status StatusDescription -------------- ------ -------------------------------------------------- A1 0 Initialize A1 1 Enter Presults Q1 0 Initialize Q1 1 Enter Presults Q1 4 Re-Review Presutls Q2 0 Initialize Q2 1 Enter Presults
because the of the additional group by & order by columns.
I assume this requires a sub-query, but I am baffeled.
Hi, Iwant to make a left join between two tables connected with a specific field but I also want to have some criteria on the right table. I want to display all the rows from the first(left) table and only the rows from the secod (right) table that match the join and the criteria. However If I have criteria on the second table the join behaves like an inner join displaying only the rows that exist in both tables. In MS Access instead of the right table I use a query in which I enter the criteria and then I connect it with a left join in a new query in order to get the right results. How can I do itin MSSQLServer without using Views? Must I use a subquery with Exists and what is the right syntax in order to achieve this?
After working with Oracle (can I say that out loud here? ) databases for a couple of years, our company recently switched to SQL2005. So far I really like it, and the switch has been relatively easy for me. Except for one thing...
I'm trying to find out how to join a subquery on multiple columns. In Oracle it would like this:
select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)
But this doesn't work in SQL-T. I've temporarily solved it by doing this:
select * from #temp1 where id_1+id_2 not in (select id_1+id_2 from #temp2)
I don't like concat solutions for joins though (it always feels 'unsafe' to me). Plus, I can't believe there's no way to solve this.
I've searched the FAQ and BOL but I just can't seem to find the solution. Please help. I feel stupid.
Hi, I have a long sql query and I have to left join two tables. The problem is that I need to do a subquery in the left join for the second table. Let me give an example.
My second table haves this data: ID GAME CREATED 1 SOCCER 1-8-1980 1 BASKKET 1-9-2000 2 TENIS 1-10-2000 2 GOLF 1-12-2000 2 CARS 1-9-2010 3 BIKE 1-2-1930
My first table left joins the second one by the ID column. So, I want first to determine the max record of each ID of the second table. Ones I have the max record for each ID in table one i want to left join it with table A. How do I have to build the subquery in the inner join? I believe is something like this:
SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 (SUBQUERY) TABLE2_ALIAS ON TABLE1.ID = TABLE2_ALIAS.ID
SELECT A.EmpId,A.IncidentDate FROM EmployeePoints1 as A WHERE IncidentDate= (SELECT MAX(IncidentDate) FROM EmployeePoints1 WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A') LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId
I have got question about the performance of subquery and left outer join
My scenario is that I have two tables Customer Mater and address book.
Customer Mater and address book tables hold the record for both carrier and customer. and i want retrieve the record for customer and related carrier as well
i have two options
1. either use left outer join (i.e. Join Customer master to Address Book 2 times, 1 for customer and 1 for carrier) by:
Address book (as customer) Left outer Join Customer Master (on related Key)
AddressBook (as carrier) Left Outer Join Customer master (on related Key)
2.or use 1 join for customer and use subquery to retrieve carrier name (from Customer table) as I only need that one
Address Book AB (as Customer) Join Customer Master CM Subquery: Select carrier_name From customer_master cm2 where cm2.carrier = ab.carrier
can any one tell one which is the best as far as performance is concern. My opinion is that the subquery is better becuase left outer join will have the overhread of extra columns... not pretty sure about it..
The problem:I'm updating a report to be "multi-language" capable. Previously,any items that had text associated with them were unconditionallypulling in the English text. The database has always been capable ofstoring multiple languages for an item, however.Desired output:Given the test data below, I'd like to get the following resultsselect * from mytestfunc(1)Item_Id, Condition, QuestionText1876, NOfKids <= 10, This many children is unlikely.select * from mytestfunc(2)CheckID, Condition, QuestionText1876, NOfKids <= 10, NULLThe current SQL for my UDF:CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,tblQuestionText.QuestionTextFROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =tblQuestionText.Item_ID)WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR(tblQuestionText.LanguageReference IS NULL)))Test data:CREATE TABLE [dbo].[tblCheck] ([Item_ID] [int] NOT NULL ,[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblCheck] ADDCONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED([Item_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblLanguage] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Priority] [int] NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Spoken] [bit] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADDCONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] ADDCONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR[CreationDate],UNIQUE NONCLUSTERED([Priority]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblQuestionText] ([Item_ID] [int] NOT NULL ,[LanguageReference] [int] NOT NULL ,[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SameAs] [int] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblQuestionText] ADDCONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED([Item_ID],[LanguageReference]) WITH FILLFACTOR = 90 ON [PRIMARY]GOINSERT INTO tblCheck (Item_ID, CheckDescr)VALUES(1876, 'NOfKids <= 10')INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(1,1,'English', 1)INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(2,2,'Espanol', 1)INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)VALUES (1876, 1, 'This many children is unlikely.')Any tips or pointers will be appreciated. Thanks.
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
Hi. I am fairly new to reporting. I have a report that currently displays queried data in both a table and a matrix. I'd like for the data that is displayed to be based off of my report parameters. I have three, all of which are multivalue. Currently, I am able to display the report, based on the only the first value of TWO of the three parameters. I tried using the dataset parameters, but was not successful with linking them to my report parameters. Please help! In addition, is there a way to set multivalue parameters to only display distinct values? Thanks, in advance, for any help.
I need to set a label in my report when all the values in a certain multivalue parameter are selected. In other words, i would need to check whether this parameter has the value "Select All" selected or not. How do i accomplish this?
We have a report that has a banner page. The user can select categories from a multi-select parameter and then this is passed into the report as a csv file. The actual report works doing this, however, we need the banner page to use this parameter's value (the multiple selected labels) to display a string of the selected values. For the textbox expression, we are using =Parameters!Cat.Value, but it always wants to reference Paramaters!Cat.Value(0). We were thinking of using a function in code to pass in the parameter and then concantenate each value into on string and return that string, but isn't there an easier way to do this?
I have passed a list of parameters to a multivalue parameter in a SQL Report.
I can click the drop down and see the list of parameters that were passed.
In my select statement for the report, here is the condition I am using in the where clause.
AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@RUs))
Nothing is returned in the result set if more than one value is passed to the multivalue parameter.
Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.
For example, in the multivalue dropdown list, there are the following values:
211
212
If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.
I have multivalued parameter in my report. I have text box to display heading of the report. the report heading is dependent on the parameter selected.
with single value parameter the text box displays the correct parameter but it cannot with multi valued parameter. Is there any way to display the last value of the parameter in the heading of the report in case of multivalued parameter?
Ex. multivalued parameter is "YEAR"
my report shows the data for 3 years say 2003,2004,2005. report header should show 2005(last of the parameter list) report.
I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".
Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.
i am trying to build a report that uses multivalue parameters - the report is a simple listing report that lists out a list of cases as a table - the data is obtained from a view which just returns all details for all cases
there is no user input for the selection details - am going to be calling the report and passing over the parameter values directly to it.
there are several parameters for the report - they all default to "All"
for each of these parameters, i need to be able to either use the default value of "ALL" or to enter a list (comma separated ?)
q1) could i just enter the list as a string comma separated list (rather than as multivalued parameters) and then use this list to filter the data using the FILTERS tab on the dataset
q2) if q1 is not possible, if i use multivalue parameters, how do i get my list of parameters into the report as the application calling the report just has a list of values
Is there an easy way to handle a multivalue parameter where the values could have comma's? I have County and State in a single column and want to select one from the multivalue parameter but when the string is passed it is not being found because I am parsing the parameter on comma. Here is what the drop down list looks like.
In SQL Server 2005 reporing services, I am using the syntax in the documentation for a multivalue parameter.
AND c.Track IN (@Track)
It works fine when I just select one value, but if I select 2 or more, I get an error about the commas being wrong. Is the syntax correct? Is there a way I can see the SQL generated by the report so I can see what is wrong?
ReportParameter[] parameters = new ReportParameter[2]; parameters[0] = new ReportParameter("IncidentID", incidents); parameters[1] = new ReportParameter("Assets", assets);
throws the error: cannot convert from 'int[]' to 'string'
ReportParameter[] parameters = new ReportParameter[2]; parameters[0] = new ReportParameter("IncidentID", incidents); parameters[1] = new ReportParameter("Assets", assets);
results in "The 'IncidentID' parameter is missing a value"
There is a select all option for each multivalue parameter. If this option is selected, I would like to display in the report that the parameter selected is "ALL" instead of using the Join(parameter!parametername.value,",") to display all the values. Any idea how I check to see if the select all at the beginning is checked?
I have a report (Say Report 1)which is asking for a multivalue parameter Period. The user selects three period from the dropdown list available for the Parameter. Lets say that the user select 0407,0507,0607. Now is this report 1 there is a particular field which is linked to another sub report. So if the top level Report 1 supplies the revenue for 3 regions say for the periods selected then clicking on any of the regions would open a second detailed report, say Report 2 for the three periods as selected.
Till this everything is fine.
Now the problem is that in this detailed Report 2 there would be a particular text box as "Click here to naviagate back to parent report". So that when the user clicks on this cell he is taken back to the parent report, Report 1 which was originally generated for the 3 periods.
Can anyone please advise as to how the Multivalue Parameter which was passed from Report 1 to Report 2 can again be looped back to Report 1 from Report 2.
I'm trying to output all the values selected through a multivalue parameter, but I am having difficulties. In the text box, the parameter contains only four member functions (value, label, count, ismultivalue). However, to output a value or label for the parameter you need to specify an index in the form of:
Parameters!Names.Value(0)
Ideally, I would like to be able to obtain all these values with a function call, but it doesn't look like there is one. Any ideas of how to get all the values?
Greetings all trying to get a multivalue parameter to accept either typed in data ex: 111111111,111111112 or if I want to return all id numbers type in %. Problem is when I test it by typing in 111111111,111111112 it throws an error saying " Incorrect syntax near ','.
I can enter 111111111 or % and get results, the error comes when I try to type in two or more id numbers. the parameter in the dataset looks like
where a.id_number LIKE (@id_number+ '%')
any suggesstions? Im sure there are threads out there but Im pressed to meet a deadline and wanted to see if there were any quick solutions
