Select Records then run report
-
I was wondering if it is possible to only run the SQL report on select records in a check box list or something.... currently i am trying to set up the web application before the report to load a dataset and then filter the dataset to what the user/client wants then save the dataset as a table then run the report from the table... but i do not think this is going ot work because of muilti-user site... Another thought is to allow to build the dataset as they like it like before...
EXAMPLE.. Instead of running the report on Records.. 1,2,3,4,5
they could run the report on only Records... 2, 3, 5 if they wanted. or just Record 1. Does not matter.
Then i want the report to run from the Dataset that the user built.... but i do not know if this is possible... I am a complete newbie at this so please fill free to point me in the right direction and or link(s) to examples that have done this....
Thanks,
Jason
-
It is possible if your datasets call SPs that accept user selections as parameters.
-
sounds like you want a multi value parameter on the record number field, only Reporting Services 2005 supports multi value parameters out of the box so you'll need to check which version you're on.
Kindest Regards,Martin
-
Hi Smith,
My understanding of the problem above mentioned is:
1. You wanted to retrieve Dataset by passing multi values (selected by user from a check box list)
2. Parameter will be coma delimited Ex:'1,2,3,4,5,6'
Generic Solution:
I am also faced a similar situation where I wanted to pass multi values to a Stored procedure.
Approach was:
1. Created a function (I got this code from the website – Sorry I couldn’t remember the url)
CREATE FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
1. Created a stored procedure with varchar as the input variable
Ex:
Create proc Example_Proc
(@Var varchar(1000))
Select * from the sample_table where Col_1in (
Select param from [fn_MVParam](@Var ,','))
3. Executed the procedure as
Exec Example_Proc '1,2,3,4'
I hope you are looking for the similar kind of solution. Please let me know if this solution is working for you
Regards,
Agson Chellakudam
-
i am using report server 2000 sp2 ... i think the above code will be helpfull i am just a little lost... currection in my VB code i return an array list as a string and it list the selected values like this: "1" "2"... so on... i am very lost of what i am doing here.. I am not sure how to return the list in a way that the user can see what they have chosen or how i can even work with the list... if anyone has any ideas on returning a list of selected values in a check box list then pushing that list to a stored procedure similar to what Agson C. posted above. the current code i am using to get my arraylist is..:
Private Function SaveCblPoles(ByVal list As CheckBoxList) As String()
Dim values As ArrayList = New ArrayList
Dim counter As Integer = 0
Do While (counter < list.Items.Count)
If list.Items(counter).Selected Then
values.Add(list.Items(counter).Value)
End If
counter = (counter + 1)
Loop
'Return values.ToArray
Return CType(values.ToArray(GetType(System.String)), String())
End Function
If anyone has any ideas on how to improve this code or at least make it so i can use it to insert values into a stored procedure than please help..
Thanks,
Jason
-
I ended up figuring out how to get the list as i needed it... using the code below... but now when i send that list as a parameter to my Stored Procedure it gives me
"Error converting data type nvarchar to real."
Maybe the solution above does it.. i will try and see but currently the parameter already looks like this before being sent to the stored procedure: 1,2,3,4,5 so when it sent it changes to '1,2,3,4,5' because it is being changed to nvarchar(255)... i need it to change to a real value because the column in the database is a real value... how do i do that?
Private
Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If txtShowValues.Visible = False Then
Dim strchklist As String = ""
Dim li As ListItem
For Each li In cblPoles.Items
If li.Selected And strchklist = "" Then
strchklist += li.Value
Else
If li.Selected Then
strchklist += " ," & li.Value
End If
End If
Next
If strchklist = "" Then
txtShowValues.Text = "No Items Selected"
Else
lblPoleSeqSelected.Visible =
True
txtShowValues.Visible =
True
txtShowValues.Text = strchklist.ToString
End If
cblPoles.Items.Clear()
Else
If txtShowValues.Text <> Nothing Then
lblPoleSeqSelected.Visible =
False
txtShowValues.Text = "In Progress, Still working on Reports"
End If
End If
End Sub
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply