I am pretty new to reporting services and hope some of you experts can help.
I created a report from AS cube that has a column "Description" with 30 distinct values. The report has a multi-value parameter "Description" with the following details:
Data Type : String
Value Field: Description
Label Field : Description
The filter that i have on the table is =(Fields!Description.Value) = Cstr(Parameters!Description.Value(0))
When i preview the report i only see the top 1 item from the values that i select from the parameter list.
Hi everyone, This is the sp i have written which works fine in the sql server management studio when i run it but when i call it from the vb.net web application the output parameters are generated as nothing but the sp return the data from the select statement, so here is the sp followed by vb.net code to access the sp. This is the Store ProcedureCREATE PROCEDURE [dbo].StaffAuthenticate @Staff_ID nvarchar(50), @Password nvarchar(15), @IsAuthenticated int OUTPUT, @P_message_code int OUTPUT AS BEGIN SET NOCOUNT ON; Select * From Staff; SET @IsAuthenticated = 0; SET @P_message_code = 100; RETURN END GOThis is the VB.NET code. Dim ConStr As String = GenericDataAccess.GetConnection(IConnectionStrNames.OAAStaffAuthStr) Dim Conn As SqlConnection = New SqlConnection(ConStr) Dim Command As SqlCommand = Nothing Conn.Open() Command = New SqlCommand() Command.Connection = Conn Command.CommandText = "StaffAuthenticate" Command.CommandType = CommandType.StoredProcedure
Dim myDataReader As SqlDataReader = Command.ExecuteReader() Dim Res As New StringBuilder
While (myDataReader.Read()) For i As Integer = 0 To myDataReader.FieldCount - 1 Res.Append(myDataReader(i).ToString()) Res.Append(" ; ") Next Res.AppendLine() End While
MsgBox(Command.Parameters("@IsAuthenticated").Value) MsgBox(Command.Parameters("@P_message_code").Value) MsgBox(Res.ToString, MsgBoxStyle.Information, "DAL : StaffSqlAuthenticate") Thanks for all the help, Really could use one. Kabir
I have created a dataset table adapter with existing stored procedure.Stored Procedure has 3 parameters.I am trying to create a page where use can search on different field and generate report on gried view control. They can either search by one parameter , two or may be multiple. But When I try to run this it gives me error if I dont put all three parameters, how can I make this work so that even if I put one, two or multiple parameter it works error free. CREATE PROCEDURE [dbo].[ConvertParentSupplier_SP] @supplierID varchar(10),@active varchar(10)ASselect distinct Supplier.SupplierID, SupplierName, ParentSupplierID, isnull(Active,'')as Activefrom supplier where supplier.ParentSupplierID =@supplierIDand Supplier.SupplierID=Supplier.ParentSupplierID and supplier.active=@activeorder by supplier.supplieridGO
Hi,I am currently creating an ASP page that returns a recordset of searchresult based on multiple keywords. The where string is dynamicallybuilt on the server page and that part work quite well. However I wantto also return the number of records in the recordset and I can notmanage to get any output parameter working. Even if I assign SELECT@mycount=100 (or SET @mycount=100) in the SP the only value set inmycount is always NULL. I tested various theories (e.g. early exit,order & naming of parameters etc. but I can not make the SP set theoutput parameters - has it anything to do with the execute?). @mycountalso returns NULL if I test it in SQL QA.What's wrong with this SP (as regards mycount):CREATE PROCEDURE dbo.spFindProducts@mycount integer OUTPUT,@whereString varchar (1000)AS--SET NOCOUNT ON--Set a Default value for the Wherestring which will return all recordsif the Wherestring is blankIF @whereString is NullSELECT @whereString = 'AND TblProduct.ProductID is not null'--Declare a variable to hold the concatenated SQL stringDECLARE @SQL varchar(2500)-- AND (((UPPER([TblProduct].[ProductName] + [ProductGroupCode] +[AttributeValue1] +-- [SearchWords] + [tblSupplier].[SupplierCode] + [SupplierDesc]))Like '%screw%'))SELECT @SQL = 'SELECT TblProduct.ProductID, TblProduct.ProductName,TblProduct.ChapterCode, tblProduct.ProductGroupCode' +' FROM (TblProduct LEFT JOIN TblStockItem ON TblProduct.ProductID =TblStockItem.ProductID) ' +' LEFT JOIN tblSupplier ON TblProduct.SupplierCode =tblSupplier.SupplierCode' +' WHERE 1=1 ' + @whereString +' GROUP BY TblProduct.ProductID, TblProduct.ProductName,TblProduct.ChapterCode, TblProduct.ProductGroupCode'SELECT @mycount = 200; -- testexecute (@SQL);-- next line seems to be ignored ?SELECT @mycount = @@rowcount;GOtiaAxel
I have 2 parameter One is Office (Listbox) another Account(list box) , where Account list box filling is dependent on the Office selection.
In published report when I select the office from list box the page got refreshed but it not updating the account list, the account list box looks diabled?
I have define the two parameter and for the second parameter here is the query
="SELECT ACCOUNT_ID, ACCOUNT_NUMBER FROM MLGDB2.A_ACCOUNT " & "where OFFICE_ID =" & Parameters!paramOffice.Value & "ORDER BY ACCOUNT_ID "
I have this select statement: SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR [title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch + "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%" + strSearch + "%')
This statement works fine but when I turned it into a store procedure, it returns nothing. I created the store procedure as follows: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE sqlSearch -- Add the parameters for the stored procedure here @strSearch varchar(250) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR [title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" + @strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR [content_nws] LIKE '%" + @strSearch + "%') END GO
When I run the query below on my DB, it gives the following error: Server: Msg 8114, Level 16, State 5, Procedure partstlist ...Error converting data type varchar to float. Only after I replace the " + @d + " section with a float numnber such as 39020.5 does it work fineIt's reported that my temporary table named #tbl doesn't exist within the scope of the environment that the EXEC(UTE) command operates.Someone said that I couldn't combine dynamic SQL and temporary tables or table variables...and that I would have to use a global temp table "##tbl" or a permanent table. So here I have appearantly a "DYNAMiC SQL & TEMPORARY TABLE" problem. That's sure. But I don't have much knowledge and experience with SQL tables and have difficulty forming a global temp table "##tbl" or a permanent table.and I have to employ the datetime variable dynamically @d inside the Dynamic SQL string. So what kind of a code should I use here to properly combine Dynamic SQL string with my dynamic datetime variable? I'd be grateful if you could post your code suggestions so that I can try them on my DB .. alter PROCEDURE partslist@no INT,@dt DATETiMEASDECLARE @s VARCHAR(5000)DECLARE @d floatSET @d = CONVERT (float, @dt2)create TABLE #tbl(pageindex int IDENTITY(1,1) PRIMARY KEY , prt_name varchar(50), prt_country varchar(50) , prt_date smalldatetime, partID int , prt_cat varchar(50),prt_product_type nvarchar(10), producer_company nvarchar(50), producer_city nvarchar(50), prt_released DATETIME, dvalue float, datevalue varchar(26) )BEGiNSET ANSI_WARNINGS OFFSET ROWCOUNT @noSELECT @s = 'INSERT INTO #tbl SELECT prt_name, Countries.Val AS ''prt_country'' , prt_date, partID , partsCategories.Val AS ''prt_cat'' , partsProductTypes.Val AS ''prt_product_type'' , producers.producer_company AS ''producer_company'' , producers.producer_city AS ''producer_city'' , prt_released , CONVERT (float, prt_released) , CONVERT (varchar(26) , prt_released, 109) FROM producer_parts JOIN partsProductTypes ON (producer_parts.prt_product_type = partsProductTypes.ID) JOIN partsCategories ON (producer_parts.prt_cat = partsCategories.ID)JOIN Countries ON (producer_parts.prt_country = Countries.ID) JOIN producers ON (producer_parts.producerID = producers.producerID) WHERE prt_visible = 1 AND CONVERT (float, prt_released) > ' + @d + ' AND CONVERT (float, prt_released) < 39025.5ORDER BY prt_released DESC SELECT * FROM #tbl'EXEC (@s)SET ROWCOUNT 0ENDGO partslist 10 , '10.10.2006 10:10:10' -- This is just to test the SP. This code may not be syntatically correct, it's a re-edited version of my code which works fine in my DB.
I have a parameter @User givning a dropdown list from a dataset:
SELECT FullName FROM tvf_GetListOfUsers ('LDAP://OU=Business Users,OU=Users,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree') ORDER BY FullName
The parameter is passed to a second dataset when the User is chosen:
SELECT GroupName FROM tvf_GetUserMembership('LDAP://cn=' + @User + ',ou=business users,ou=users,ou=production,DC=MRP,DC=NET,DC=NZ') ORDER BY GroupName
This works fine in VS but when I deploy it only works the first time.
That is, I enter the username and run the report - it brings back the desired results. But when I enter a different username it just redisplays the old data from the first username I entered.
Any ideas?
Thanks Pete
PS The funcions above called in the datasets are for querying Active Directory but I think this is not the cause of the problem.
I have a working set of parameters, when they are all visible. However, when I change the second parameter to hidden it stops working.
First Parameter - Contains static values 'MTD' & Daily Second Parameter - has available values and a default. Set up to be invalidated when the First parameter changes... like i say it works beautifully when not hidden. Third Parameter - has an expression takes the value of the 2nd parameter value, removes the first character off and then converts the remainder to a date and uses it as its default value.
It based on this webpage: URL....However, when i set the second parameter to hidden it no longer works.
Hi, i am working on SSRS 2005. i have one problem while accessing report throgh ASP.net pages.
i have used multivalue parameters in the report. on the report server it works fine but on the .asp page when that report is accessed, drop down is not working,
Hi All i have created one report using SSRS 2005, in this report one parameter is multivalue parameter, this report preview working fine and user able to select multivalues. but the problem is i deployed this report in server and opened in client system (reports shown to user using report viewer in aspx pages), i am able to access the report but multivalue parameter dropdown is not working. when i clicked on multivalue parameter dropdown report refreshing and remaining parameters default vaues coming up.. i am unable to find the solution please anyone help me
Hi, I've got this ssrs 2005 report that works great passing a few security related parameters from asp.net codebehind. However, there are two date related parameters that won't be coming from my web form, but rather from the report form itself. When I test the report's date parameters from visual studio it work fine, but when I attempt the same report from a reportviewer no matter what input I place on the report's date fields or even if I select the date picker, the report simply resets to default and reloads. And actually the date picker from the reportviewer does not not even pop up.
Partial Class _ReportViewer Inherits System.Web.UI.Page Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String = GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser", "mypassword", "mydomain") ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user", Page.User.Identity.Name.ToString) Dim param2 As New ReportParameter("r_role", Roles(0)) Dim p() As ReportParameter = {param, param2}
on my reportviewer form, at the top I have parameters for startdate and end date, they are not set to internal or hidden.
If I select the calendar icon, i get a javascript error
Line: 606 Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified error. Check the documentation index for 'Just-in-time debugging, errors' for more information. So pretty much I can't see the error or javascript in question.
If i enter anything in the date fields, it disregards them setting them to the defaults.
Again, if i run the report from my vs.net client (not using reportviewer), i can select the calendar and enter dates and it respects them.
Any chance I need to patch sql server or reporting services? maybe ie. I'm on IE 7.0.5730.11 Could my problem be that selecting items on the report itself fails to send my credential information from my codebehind?
I am trying to test the concept of passing multi value parameters from one report using the Action Property of a textbox to open another report is a new Window with the Multi Value parameter values already passed. The test involves the following:
Report 1:
1. Multi Value Parameter @ReportParameter1 has three Values: Value1, Value2, Value3
2. Has a text box with the Action Property set to Go to URL with the following Expression: ="javascript:void(window.open('http://reportserver/Reports/Pages/Report.aspx?ItemPath=%2fReports%2fReport2&rs:Command=Render&ReportParameter1=" + JOIN(Parameters!ReportParameter1.Value,"&ReportParameter1=") + "'))"
Report 2:
1. Has Two Parameters @ReportParameter1 and @ReportParameter2
2. @ReportParameter1 has Available Values as follows: Value1, Value2, Value3
Does not have any default Values.
When I click on the textbox on Report1, it does navigate to Report2 but none of the Values are being set.
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
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 .
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1 available values: from query default values: non query (specify a value "<None>") Parameter 2 available values: Non query (no value specified) default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
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.
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.
Does 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
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
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.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
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
