I have two parameters
I have dropdown with 2007 , 2008 values
the second dropdown should be disabled until the user select 2007 or 2008. if the user select 2007 i will populate the second dropdpwn a list with 2007 weeks. if the user selects 2008 the second dropdown will be populated with the 2008 weeks.
can i disable paprameter in sql server report
Thanks
create Procedure AddNewStyle @table_nm varchar (20)
declare @string varchar (5000)
select @string='Update product
Set Product_End_dt = '12/31/05'
from ' + @table_nm + ' c inner join Booktable b
On (c.book_id = b.book_id).............
exec (@string)
--
I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help!
Reddy.
I have read all the post about passing parameters from sql task in and out and have tried them all with no anvile. Frist of, they are too many types to deal with now, db types, SSIS types. Why the hausl. Can we make data type flow a bit more easy. Well, enough about the complain. I have been trying to dynamically set my sql query statement for my datasouce component using a variable set by a sql task. The tried a couple of methods.
Method 1
Using sql task, ole connection and stored proceduer( exec usp_mystoreprocedure ? OUTPUT) with output parameter (User::sql_query out varchar 0). My stored procedure generate a sql statement as nvarchar and sets the output variable with that. Now when i parse query, i get the error
TITLE: SQL Task
------------------------------
The query failed to parse. Syntax error, permission violation, or other nonspecific error
------------------------------
BUTTONS:
OK
------------------------------
So first of my parser is saying my syntax is wrong that that what most of the post in this form says.
When that didnt work, i decided to return the result and catch it with an returnvalue variable but that give the same error
Method 2:
I tried using ADO.NET instead of ole so did about the same thing except changed the parameter to @SQLQUERY which is the same name as my output parameter on my stored proceduere. Check the query to exe [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP] @SQLQUERY OUT and parameter settings on sql task as (User::sql_query out string @SQLQUERY OUT) and the error it generated when i run the task was
[[Execute SQL Task] Error: Executing the query "exe [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP] @SQLQUERY OUT" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@SQLQUERY"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Method 3:
I made a QUERYSTATEMENT bridge table. The stored procedue inserts the sql query statement into the QUERYSTATMET TABLE and then i use sql task to pull the statement, set it to a variable and then set my sqlstatement for my datasource component using experessions. Every things works fine till the sql task exectues to pull the sql statement from the QUERYSTATEMENT TABLE.
First, I used OLE connection so my statement was "SELECT ? = STATEMENT FROM QUERYSTATEMENT WHERE TBLNAME = ' MYTABLENAME' " and parameter was set up as (User::sql_query out varchar 0). I parsed this statement and got the error
TITLE: SQL Task
------------------------------
The query failed to parse. Syntax error, permission violation, or other nonspecific error
------------------------------
BUTTONS:
OK
------------------------------
So it looks like OLE doesnt like me. I tried ADO.NET connection and changed the variables ? to @SQLQUERY. So my statement was now "SELECT @SQLQUERY = STATEMENT FROM QUERYSTATEMENT WHERE TBLNAME = ' MYTABLENAME' " and parameter was (User::sql_query out string @SQLQUERY). When i executed the sql task, i got
[Execute SQL Task] Error: Executing the query "SELECT @SQLQUERY = QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" failed with the following error: "Must declare the scalar variable "@SQLQUERY".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So at this point, im really out of luck. I even tried setting an object variable with a record set from the result of sql task executing -> SELECT QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" but when i tried casting the object variable into a string in my datasource component expression, it indicated that variables of type User::Object cannot be used in expression. If someone can help me, I will be very glad. I would also like to know what types to use in SSIS sql task when getting data from a database. Example, I was returning a datatype of nvarchar from my db, do i recieve this datatype as string, AnsiString etc. I know int, numeric can map to Int32 (if from 86x) or Int64. Can we have a table of data mapping if possible.
PLEASE PLEASE PLEASE HELP !!!!!!
Hi,
I need to parameterize some values in the data flow so that i can chnage the values directly in parameter file and re run the data flow for new value in the passed in the parameter. This can be easy for other who do not know about the flow of data flow task as to where to change the variable/parameter.
How can this be accomplished. I want the data flow task to refer to this file before it starts executing and pick the appropriate value from the file.
Or is their any better way to accompalish what i want to do here in SSIS.???
tHNAKS FOR UR HELP FOLKS !!!
Hi,
I have declared an internal paramter and given the default non queried value as
select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end
and when i run it i am getting the error -- The property 'DefaultValue' of report parameter split doesnt have the expected type. What should i do to solve it. Any help is appreciated.
Regards,
Karen
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.
Please help me to start sql server.
Thanks.
Michel
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
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!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
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 .
Hi,
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?
Thanks.
Kong
Hi,
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.
Thanks !
JF
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
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?
View 2 Replies View RelatedI have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
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.
Thanks
I am using report builder 3.0.
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
Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
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.
Hi All.
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 ?
Any help will be appreciated.
Thanks,
Neil
Hi,
I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.
The idea is the create snapshot nightly and then display the report filtered based on parameters to users.
I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,
Right now I defined it this way :
Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)
I do this for the 4 parameters that are passed, if they are null, make then equals to the field.
I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.
Thanks
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
I know i can accomplish this by setting a session variable on page load on the server side (Session["UserName"]= User.Identity.Name) and then use a session parameter, but I was wondering how to do it without using session variables, i was hoping the following code would work, but it doesn't. Thanks for any ideas.
" Name="UserName" Type="String" />
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
View 11 Replies View Related