Values In Define Query Parameters Box
Nov 21, 2007
How do I create value from the dropdown list of each parameter within the "Define Query Parameters" box, which appears when I run a query/dataset from the "Data" tab of the SSRS Report Designer ?
It usually list all the "Parameter Name"s I defined, and has another column for "Parameter Value", but it always shows only <Null> or <Blank> even though I have assigned the report parameters with data (from a Query, not hard coded).
The parameter values work fine when in "Preview" mode, but it would be of convenience to have them during design time.
Thanks in advance.
View 7 Replies
ADVERTISEMENT
Apr 13, 2007
Hi
I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId
The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>"
SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId">
<SelectParameters>
<asp:ControlParameter ControlID="Drop1" Name="SourceId" />
<asp:ControlParameter ControlID="Drop1" Name="CatId" />
</SelectParameters>
</asp:SqlDataSource>
Can anyone help me to define the parameters?
thanks
View 2 Replies
View Related
Feb 15, 2007
Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:
ReportParm1 String [Deal Dim].[Shelf].&[AAM]
But, how would i define it with multiple values as a 'default value' ?, for example:
ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]
NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.
Any help would be greatly appreciated.
thank you.
View 1 Replies
View Related
Feb 11, 2008
Hi,
I would like to set up cascading report parameters but need to supply the list of values in my dataset query as they are not being pulled from a table or stored procedure.
I would like to set up something like
select 'LS' Source
Union
select 'RH' Source
Union
select 'UK' Source
as the value list for my first parameter, then, I would like the second parameter to default to '1' if the first parameter has 'RH' or 'UK' selected but default to blank if the first parameter has 'LS' selected. I can get all to default to blank or 1 but can not seem to make it dependent.
thanks!
Martha
View 2 Replies
View Related
Nov 13, 2014
I have this query that calculates the next possible shipping day, based on 3 conditions:
- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green
- There might be extra days (@xdays) required by the process - marked blue
- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate
from WORKCALENDARDATE
where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and
[code]...
So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.
DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456'
for xml path('')),1,1,''))
View 2 Replies
View Related
Oct 24, 2007
Hi everyone,
Primary platform is sql25k running with sp2.
I'd like to define for a concrete field which only could have three values:
"a","b","c"
TIA
View 7 Replies
View Related
Sep 1, 2015
I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.
SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information],
user8 AS [Transaction Type], user2 AS[Invoice Number],
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]
[Code] ....
View 2 Replies
View Related
Feb 23, 2008
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
View 7 Replies
View Related
Oct 1, 2007
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
View 9 Replies
View Related
Feb 2, 2008
Hi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
View 6 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Jan 29, 2008
I want to run some sql code from Query Analyzer. The sql code uses parameters.How do I define the parameter before setting it to a value? I don't want to hardcode the input parameteres.
Ex:
create @Prim -- This is the line I need help on. Instead of "create", what is -- the proper way of setting this value ?
set @Prime=13
SELECT Prime.Quaternion, Gaussian.RegionId, Laplace.Primer,
View 1 Replies
View Related
Mar 18, 2007
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
View 8 Replies
View Related
Jul 6, 2015
Is there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?
So I can create a query like this:
SELECT *
FROM Table
WHERE MyFunction()
I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.
Example:
ID - Name - State
1 - Jozef De Veuster - Mad
2 - Piet Husentruut - Not Happy
3 - Jeroen Meus - Angry
Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.
I want to handle this by doing:
SELECT *
FROM PeopleStates
WHERE --Some stuff--
AND CheckUserExclusions(SomeID)
And CheckUserExclusions will translate to "NOT (ID = 1)"
View 14 Replies
View Related
Jul 2, 2007
hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS
the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard.
but my requirement seems can't be done by DTS wizard.
here are my requirement below.
[move online Database to offline Database ]
1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..)
2. we will have only 2 kind of preserve time one is 6 months another is 12 months
3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data
4. We will have to reference the history data preserve time to delete data after finished data movement
those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?
thank you very much and have a nice day
View 4 Replies
View Related
Feb 21, 2007
hy all,
i'm using the object transform data task from a server (where i'm not dbo) to another server (where i'm dbo).
i'm getting data from a table. i need to select just some records from this table. i need to use a query like this one below...
select * from {table_name} where operationedate in ('20070101', '20070205', '20060524')
... cause everytime i run the dts the operationdate field must be filtered on different date.
so i tried to use the parameter from a global variables. i've tried lots of things but everytime i failed.
i used to try:
select * from {table_name} where operationedate in (?)
but it doesnt work!
any one can understand what i try to explain and even help me?
bye
nicola
View 7 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Oct 25, 2006
i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus. I am wanting to pass the Membership.GetUser.ProviderUserKey.ToString() to the asp:parameter but I cant get it to work.So How do I pass a variable to a stored proc parameter using a bound data grid.I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.please helpjim
View 2 Replies
View Related
Sep 18, 2007
Is it possible to have a parameter with one label but multiple values. For example:
Label Value
---------------------------------------------
Machinist (100,200,300)
Is it possible to set up an expression that when the user selects this label it will look for the job codes 100, 200 and 300
and return all employees in those codes?
Thanks,
A
View 2 Replies
View Related
Apr 4, 2008
Hello,
Using a Report Designer of the SQL Server 2008 connecting to an ORACLE database:
- I want to use Parameters filters with multiple Values but i get the error :
"FilterExpression for the data set 'DATAset1' cannot be performed. Cannot compare data of types System.String and System.Object[]. Please Check the Data Type Returned by the FilterExpression"
without the multiple Values it works but don't resolve my problem.
The filter configuration is =Fields!DT_OPERACAO.Value = =Parameters!FLT_Ano_Lectivo.Value
how can i solve this situation ?
View 3 Replies
View Related
Oct 24, 2006
HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT
View 1 Replies
View Related
Dec 28, 2007
I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?
View 4 Replies
View Related
Apr 11, 2006
I want to make a filtering by using a string parameter (a,b).
The initial result of my query is this:
Name Letter
Luis a
Juan b
Michel c
Note: There are 2 columns (Name) and (letter)
So after filtering the result pane should be like this
Name Letter
Luis a
Juan b
If i send a diferent filtering parameter (b), the result should change like this
Name Letter
Juan b
How can I do this??
Thyanks
View 2 Replies
View Related
Aug 1, 2006
This is regarding Report Services 2005 designing the report in Visual Studio.
Currently I am using a query to populate 2 date selectors in the parameter section at the top of a report. The start and end dates are determined by a query which returns the values based on yet another parameter. If I change the value of the paramter which is supposed to be used by the date selectors they don't seem to feel the need to re-calc themselves. I have 3 other dependent param's in the same report which behave reasonably.
Has anyone seen this before or is there way to somehow force a parameter re-caluclation?
jSiegel
View 1 Replies
View Related
Mar 13, 2008
Is it possible to get the reportdataset field values into parameters. dynamically when the report is generated.
Thanks.
View 1 Replies
View Related
Feb 3, 2007
Hallo
is it possible to perform an insert/update query upon the execution of a report, using the report Visual Basic expressions?
I mean, the user enters some values for the report parameters, and i would like to store them, along with the resulting recordset data, into a dedicated table in the database.
View 1 Replies
View Related
Oct 4, 2007
Hi,
I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, DataAccess=DataAccessKind.None)]
[return: SqlFacet(MaxSize = 30, IsNullable=true)]
public static string FormatGeneralDecimal([SqlFacet(Precision = 28, Scale = 8, IsNullable = true)] SqlDecimal sqlDc,
[SqlFacet(MaxSize = 10)] string cultureName)
{
string result = null;
if (!sqlDc.IsNull)
{
CultureInfo ci = CultureInfo.CreateSpecificCulture(cultureName);
result = sqlDc.Value.ToString("G", ci);
}
return result;
}
It works great unless I call it with a NULL value for sqlDc, in which case I get this:
select [dbo].[FormatGeneralDecimal](NULL, 'de-DE')
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "FormatGeneralDecimal":
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at System.Data.SqlTypes.SqlDecimal.ToDecimal()
at System.Data.SqlTypes.SqlDecimal.get_Value()
at MyFunctions.FormatGeneralDecimal(SqlDecimal sqlDc, String cultureName)
View 1 Replies
View Related
May 8, 2007
Hoping someone may be able to help with a problem I'm having with SSRS parameters....
My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments.
Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter.
The second parameter is to appear as a drop-down of Departments to which the User has access.
The report is to be produced for the selected Department.
Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?
Any help is much appreciated.
View 3 Replies
View Related
Oct 10, 2007
HI All,
I want to send a reports to two person. Reports are going to be delivered automatically. I hope to use snapshot option. In my report there is one column which can be seen by only one person. Can I use parameters to hide one column from one person? If its possible, can you explain how to do that please. If its not possible, what are the other option excet creating two different reports.
Also If I use parameters , can reports be executed automatically?
Thanks
View 2 Replies
View Related
Sep 17, 2007
Hi all,
I've developed few reports
I'm passing values to few parameters in a report from menu report.
when I click on "View Report" button values are changed to default for parameters eventhough I've not changed specifically any values for parameters. thus report is missing few parameters and not able to execute properly..
this error occurs only in web environment.. after publishing reports.. they are working fine in developer suite(Visual Studio)
please suggest any ways to overcome this issue..
seniors.. pls throw some light ..
thanks in advance
View 1 Replies
View Related
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Jul 23, 2005
I'm generating a list of parameters needed by stored procedures, and
I'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value,
'COLUMN_DEF' always returns as NULL, even when that column has a
default value assigned to it.
i.e.
CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Thanks
View 1 Replies
View Related
