I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.
SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'
It works fine in when i type it in manually.
But im using a stored procedure from VS and it will not work with the '%' part
SELECT *
FROM table
WHERE column_name LIKE @parameter%
Code Snippet SELECT Portfolio, RptSection, RepExcRsn, Report, SUM( Units) as TotUnits , FROM Exc_SummaryData_Custom WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') AND (Portfolio IN (@Portfolio)) GROUP BY Report, RptSection,Portfolio, RepExcRsn
UNION ALL SELECT DISTINCT 'ALL UP' AS Portfolio, RptSection, 'Grand Total' AS RepExcRsn,Report , (SELECT SUM(Units) FROM Exc_SummaryData_Custom WHERE Portfolio IN ('CBank','DTC','EDirect', 'InstLend')AND (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') ) as TotUnits , FROM Exc_SummaryData_Custom WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') AND (Portfolio IN (@Portfolio)) GROUP BY Report, RptSection, Portfolio, RepExcRsn
Values for Portfolio parameter are like DTC, CBank, ED and ALL UP But when I input ALL UP for portfolio parameter , I can not see anything. Originaly ALL UP is not in the table. Thats why i use union all here. can anyone tell me why I dont get values for ALL UP?Thanks
I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.
SELECT * FROM table WHERE column_name LIKE 'nvarchar%'
It works fine in when i type it in manually.
But im using a stored procedure from VS and it will not work with the '%' part
SELECT * FROM table WHERE column_name LIKE @parameter%
Code Snippet SELECT Portfolio, RptSection, RepExcRsn, Report, SUM( Units) as TotUnits , FROM Exc_SummaryData_Custom WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') AND (Portfolio IN (@Portfolio)) GROUP BY Report, RptSection,Portfolio, RepExcRsn
UNION ALL
SELECT DISTINCT 'ALL UP' AS Portfolio, RptSection, 'Grand Total' AS RepExcRsn, Report , CASE WHEN Portfolio = 'ALL UP' THEN (SELECT SUM(Units) FROM Exc_SummaryData_Custom WHERE Portfolio IN ('CBank','DTC','EDirect', 'InstLend','ALL UP')AND (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') ) ELSE 0 END as TotUnits , FROM Exc_SummaryData_Custom WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') GROUP BY Report, RptSection, Portfolio, RepExcRsn
My problem is when I input a value for Portfolio parameter, i get two rows instead of one. If I input 'ALL UP' then I get value only for ALL UP. But if I input smthing else , then I get values for that paramter value as well as ALL UP thus giving me two rows not just one as I want. Case stament always gives me 0 value for ALL UP which is not the true value. can anyone help me to correct this?
I have the following in my commandtext but it doesnt seem to replace the LanguageColumnName variable: Dim cmd As New SqlCommand("SELECT '+@LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _ "WHERE b.UserCode=@UserCode", MyConnection) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCode)) cmd.Parameters.Add(New SqlParameter("@LanguageColumnName", LanguageColumnName))I have tried '+@LanguageColumnName+' and also just @LanguageColumnName but this variable isnt replaced for some reason.The value of LanguageColumnName is "de"...the funny thing is that when I just type my command like the following it DOES work..:SELECT de FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _ "WHERE b.UserCode=@UserCodeWhat am I doing wrong?
Hello, I have what should be a very simple problem, but I cant solve it. I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)
Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:
DECLARE @Dv_id nchar(15)
SET @Drv_id = '%'
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'
OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows. The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between: WHERE Drv_id LIKE @Drv_id and WHERE Drv_id LIKE '%' ? The wildcard statement '%' is supposed match everything, correct?? It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.
Hi all: I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater. The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it. The page connects and displays correctly. My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database. The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below). In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5. That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it. It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it. I am extremely frustrated - any ideas would be greatly, greatly appreciated. Thanks! Bruck The table I'm pulling from and updating looks like this: SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int) Here's my ASPX for the main data source: <asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)"> <UpdateParameters>
<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" /> </UpdateParameters> And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later): <asp:Repeater ID="rptTaskSelections" runat="server">
<FooterTemplate></td></tr></table></FooterTemplate> </asp:Repeater> And here's the page load and submit VB: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()
SELECT SOURCE, TRANSDATE, LOCATION, DESCRIPTION, MOACTIVETIMESTAMP, MOINACTIVETIMESTAMP FROM CTS_Missing_Data_Report_VW WHERE LOCATION = @Location AND SOURCE = @Source AND (TRANSDATE BETWEEN @StartDate AND @EndDate) ORDER BY Transdate desc, Location
So the user can enter a value for Location and Source and select the date range.BUT I also want the user to be able to put in nothing for the Location and Source so the query would return everything for that date range.
So if they did this the query would be
SELECT SOURCE, TRANSDATE, LOCATION, DESCRIPTION, MOACTIVETIMESTAMP, MOINACTIVETIMESTAMP FROM CTS_Missing_Data_Report_VW WHERE (TRANSDATE BETWEEN @StartDate AND @EndDate) ORDER BY Transdate desc, Location
I have set the parameters @Location and @Source to "Allow blank value" in the datasets for the location and source I have :
SELECT NULL AS Source UNION SELECT DISTINCT RTRIM(LTRIM(SOURCE))AS Source FROM CTS_OPS_SOURCE_LOCATION_TBL_VW ORDER BY SOURCE
So a blank will show on the drop down and when I run the query for the Query Designer in the Dataset Properties the results does show a blank record for the first record.BUT when I Run the report there are no blanks in the drop downs for the location or source. And there is no '<blank>' selection in the drop down either. And the drop down insist the user selects a value from both of the drop downs.
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
in a asp .net application, I call a stored procedure which have a output parameter. the output parameter works find in sql session, but not in the asp .net application.
if I put select msg_out = "error message" in position A(see below for stored proc), it works fine if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session The stored proc was created like this:
Create procedure XXXXXXX (@msg_out varchar(80) OUTPUT ) as begin
while exists (*******) begin //position A if certain condition begin
select msg_out = "error message" return 1 end
end
end
end
It seems to me that anything inside if - the second begin...end - it wont get executed.
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'. USE [merck_acronyms] GO
I have a parameter that chooses its available items from a query (with a label and a value column). I set the default for the parameter to the a particular value.
It works in Preview from design mode, but when I deploy it and run the report, it does not set the default.
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
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet select @ReportingId = max(ReportingId) from [dbo].[GuidToReportingid] where Guid = @UniqueIdentifier and EffectiveEndDate is null
if @ReportingId is null BEGIN insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() END ELSE BEGIN select @rowcount = count(*) from [dbo].[someTable] where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet if @rowcount > 0 and @joinsMatch = 1 begin set @insertFlag = 0 end else begin update [dbo].[GuidToReportingId] set EffectiveEndDate = getdate() where ReportingId = @ReportingId insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() end
...and before the return it's value is inserted to different tables.
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters.
What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible?
As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment.
Seems like this should be possible... Any idea?
Cheers, Mike
PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
I have a parameter (hidden) that gets its value using an expression base on another parameter. When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.
When I change the parent parameter again, the value of the child parameter does not seem to change.
How can I make this parameter change automatically when the parent is changed ?
