I am trying to select a group of records based on a parameter value
passed to the db from a web page. The value comes in as @Status and
has a list of statusID's: (1,2,5,9) I've tried to use
"Where table.status IN (Select * from @Status AS ValueList)"
And also tried
"Where table.status IN (@Status)"
And neither worked. Any suggestions?
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies View RelatedDoes anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
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
Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
Converting Sybase to SQL Server !
On the Sql Server's database I did :
-- test list function with cursor
alter proc proc_list(@key varchar(255), @retlistvalues varchar(8000) output)
as
declare @incrcurs2 cursor
declare @value varchar(255)
declare @listvalues varchar(8000)
set @incrcurs2 = cursor scroll dynamic
for
select t.title_Id from publishers p, titles t where p.pub_id = t.pub_idand p.pub_id = @key
order by t.pub_id
open @incrcurs2
fetch next from @incrcurs2 into @value
select @listvalues = convert(varchar(255),@value)
WHILE @@FETCH_STATUS = 0
begin
select @listvalues = @listvalues + ',' + convert(varchar(255),@value)
fetch next from @incrcurs2 into @value
end
CLOSE @incrcurs2
DEALLOCATE @incrcurs2
select @retlistvalues = @listvalues
go
declare @listvalues varchar(8000)
execute proc_list '1389',@listvalues output
print @listvalues
With Sybase, easily :
Select t.pub_id, list(t.title_Id) from publishers p, titles t where p.pub_id = t.pub_id
And I 've got all the titles in a comma-separated string by publisher !!!!
So, with SQL Server, I suppose that if I want the result by publisher, I must imbricate two cursors !!!
Perhaps I dream
Or can somebody see a better solution ????
Thanks
Axel Thimonier
I've recently read in Joe Celko's "SQL For Smarties" book that there is a LIST() function in Sybase's SQL Anywhere that will aggregate string data into a single comma separated column.
Unfortunately, this command is not supported in T-SQL. Does anyone know how to achieve a similar result with conventional SQL, where I want to aggregate string data into a single column.
Here's my task. Say I have two tables, tParents and tChildren, a parent has many children. I want a sql statement that returns one row for each parent and the multiple children associated to each in a single comma separated column, i.e
Joe Blow 'Child1, Child2, ...'
Parent2 'Child4, Child5, ...'
The SQL with this LIST() function would look like:
Select Parent, List(tChildren.Child) as Children
From tParents, tChildren
Where tParents.PID = tChildren.CID
Group By Parent
Thanks in advance for any help.
Cheers,
Steve Roch
Research Systems, Inc.
stever@rsinc.com
In sybase, there is function List, which joins few records in one cell. Is there any function, who does it in MS SQL?
View 14 Replies View RelatedI want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies View RelatedI have a subquery that retunrns multiple lines. To solve it, I have used the List function. The separation of List function is comma.
TblBill is in the main query with many other tables.
(Select List(tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr
This subquery returns: B12, B13, B14, B43 and so on...
Now I want to use SUM from another field (quant) from table tblBatch in the subquery and then separate those with a line break instead of comma. Batch number may occur several times and I want to summarize these
I want the subquery return like this:
---Batchnr---
3 B12
4 B13
1 B14
1 B43
and so on..
How do I solve it the best way with SQL Server?
Hello,
I have do a sql function for return a list of element from a query send in variable.
When I test the function on self I have no problem.
But when I use the function in a sql query I have problem.
example :
Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL,
dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
AS V_PROCESS_ITEMS
FROM APPLI_SUPPLIER INNER JOIN
APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
This is the error :
Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24
Only functions and extended stored procedures can be executed from within a function.
When I do an exec of the function I have this problem :
Code Snippet
exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'
This is the function
Code Snippet
CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM]
(@QUERY AS VARCHAR(3900)=null)
RETURNS varchar(8000)
AS
BEGIN
-- Insert statements for procedure here
declare @v_List_ITEM as varchar(8000)
set @v_List_ITEM=''
if @QUERY is not null
Begin
declare @cur_Lect_ITEM CURSOR;
declare @FUNCTION AS NVARCHAR(4000);
Declare @ITEM as VARCHAR(255);
SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor'
EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output
fetch next from @cur_Lect_ITEM into @ITEM
while @@fetch_status=0
begin
set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM
fetch next from @cur_Lect_ITEM into @ITEM
end
deallocate @cur_Lect_ITEM
SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '')
set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3)
End
RETURN @v_List_ITEM
END
Can you help me please?
Thank you
I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
View 4 Replies View RelatedI have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like....
SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))
The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?
Any help much appreciated!
I have a SQL query like this:
Select * from myTable where myTable_ID in (2,6,7,9)
I want to build the list of values that are in parenthesis, in my VB code and pass it in through a parameter, so it's like this:
Select * from myTable where myTable_ID in (@myValues)
Is this possible? I tried it where myValues = '2,6,7,9' but am getting a conversion error. I'm starting to believe it's not possible to do what I'm trying to do. Is there another way?
I have a report with a dataset/parameter to select the salesperson.
SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID
FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN
[DatabaseName$Sales Shipment Header] ON
[DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code]
UNION ALL
SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID
ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name
In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".
Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
Hi all
I have a parameter that is populated by a dataset.
How can I set its default value to the first row in the dataset?
Thanks
I am building a report with several search parameters; three of which are dropdown boxes each populated by a data set. I wanted something more user-friendly than the default "(Null)" in the dropdown, so for each data set, it returns a result set UNIONed with " N/A" to show up first as the default.
For some reason, when the report loads, the first dropdown populates just fine and does indeed have the default value " N/A" but the other two dropdowns are grayed-out. Until you change the first dropdown (to anything, apparently it doesn't matter what you change it to) the other two stay grayed-out. When the first is changed, the other two are populated and default to the desired " N/A" default value.
Why would it do this, when it didn't do it before; i.e. when the default for all of them was simply null. The dropdowns are not dependent on each other; they don't depend on other parameters for their values. Any help would be appreciated.
Hello,
I have the following issue:
I have the following statement into a function:
select a,b form T where c IN @parameter
t is the table
c is datatype= integer
@parameter is a input parameter in the function, the @parameter contains more values and passed as a string.
Running the statement above I got an error due to conversion type.
How can I pass a list of parameters in the @parameter variable to make the statement works?
Thank in advance.
I have a year-month hierarchy that I am using as a parameter in a report. I'm using the From query choice for available values and using the ParameterValue.
My parameter list displasy as:
2006
1
10
11
12
2
3
...
2007
1
10
11
12
2
3
4
Is there a way to get this to display months as integers rather than strings?
Thanks.
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
Is there a way to retrieve the parameter list for a given stored procedure?
I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.
Thanks,
Allen K.
I have a parameter consisting of a Year-Month hierarchy. The drop down parameter list appears as:
2007
1
2
3
4
5
6
7
8
9
10
11
12
2008
1
I have two questions:
Can I get the month names to appear instead of number?
When the parameter is selected the Parameters!DateShippedYearMonth.Label displays the month ONLY. How can I see the Year AND Month that was selected?
Thanks.
I am trying to report against a cube and I want the user to be able to choose the date for the report.
Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.
I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?
Hi All,
I've got a problem with increasingly long parameter lists for SProcs... Especially when one calls another SProc, and so on. Is there any way around this? Like can you dynamically construct a string and pass that? I'm just looking to see if more experienced players have found ways around this, or have just dealt with it by using well formed code.
Thanks
Chris
I have a units conversion table. I'd like to populate the parameter drop down with the name of the unit and have the value selected be the numrical value found in a different attribute.
Here is my current MDX:
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Units].[Units].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim Units].[Units].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim Units].[Units].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim Units].[Units].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])
Units Parameter Caption Parameter Value Parameter Level
(null) All [Dim Units].[Units].[All] 0
Gross Ton Gross Ton [Dim Units].[Units].&[Gross Ton] 1
Hundred Weight Hundred Weight [Dim Units].[Units].&[Hundred Weight] 1
I can't seem to get different attribute to show up for ParameterValue.
Also, is there a way to NOT include the "all" member? For what I am trying to do it would have no meaning.
Thanks.
How to Populate multiple values for Parameters in reporting
services.i am talking about the Default values for a parameter
As i am a fresher in this field , i would like to know much about it
Hi,
I believe somebody know the reason. Please help me on it!
I plan to pass a multivalued parameter from my web application to the server report, and let user select the value(s) from the value set. But when I run the report, there's no checkbox leftside of the values, that means user has no choice on this parameter.
Is there any way to do it?
Thanks,
Jone
Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris
View 1 Replies View RelatedI am new for SQL SP, I have three table,
Table 1 - Merchant
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
Table 2 - Region
RegionID RegionName
1 Region1
2 Region2
3 Region3
Table 3 - Offer
OfferID RegionID MerchantID
1 1 3
2 1 2
3 1 1
4 2 2
5 2 4
I have two input parameter for this SP, @MerchantName = NULL and @RegionName = NULL
Expected Result Table
If input parameter @MerchantName = NULL and @RegionName = NULL
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
If we Search by RegionName="Region1" Ex: @MerchantName = NULL and @RegionName = 'Region1'
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
If we Search by RegionName="Region2" Ex: @MerchantName = NULL and @RegionName = 'Region2'
MerchantID MerchantName Zip
2 Merchant2 1002
4 Merchant4 1004
If we search by MerchantName='Merchant1' Ex: @MerchantName = 'Merchant1' and @RegionName = ''
MerchantID MerchantName Zip
1 Merchant1 1001
How to get the expected result for the above scenario.
How do I get the difference between DatabaseProperty() function and DatabasePropertyEx() Functions and how to get list of parameter that uses DatabaseProeprty() and DatabasePropertyEx() Functions.As in sql 2012 both gives red wavy line when I try to use in query analyser.
View 3 Replies View Related