Having An &&<All&&> Option In An Integer Parameter

Jan 29, 2008

I have cascading parameters in my report, and the vast majority of the fields on which the parameters are
based are varchar fields. In each of the drop-down menus on the report, I would like to have an option
at the top of the list called <All> which allows the user to select all of the possible values.

I have done this successfully for all the varchar fields, but there is one parameter which is based on an
integer. This obviously means it won't accept the value <All>. So, as a temporary workaround I have
created an option called -1, which when selected by the user, selects all the possible values in the
list. However, this is likely to be confusing for users if they don't realise that the -1 option means
"all". Is there any way I can make the -1 actually appear in the list as <All>? How would I go about
this?

The available values for the parameters come from an underlying dataset query.

View 1 Replies


ADVERTISEMENT

Allows Blank Value For Integer Parameter

Jul 3, 2007



Hi have a problem to solve and I hope that this is not a SSRS Bug.



I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.



One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.



I starts to define tha parameter:

Datatype = integer

Allow blank value

Available: Non queried

Default: Null



if I want to Preview the report I have to provide an integer to the parameter's field ...



If for instance I set:

Default: Not queried = 0

In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!



So I tried this solution:

Datatype = integer

Allow blank value

Allow null value

Available: Non queried

Default: Null



In the preview the checkbox: NULL is checked and I click on the View Report.

But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.



Do I forget something during my setting??I have to control it programmatically??



N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!



Thank you for any help!

View 1 Replies View Related

Can't Use Integer Parameter With Dateadd?

May 11, 2007

Hey guys I have the following in my SQL statement:

DATEADD(hh, @hours, @startTime)

hours is an integer and startTime is actually a string, but the thing is that this statement works fine if I use it like this:

DATEADD(hh, 8, @startTime)

The first version is what I need so that the user can control from a parameter and I get the following error:


Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.

Thanks!

BJ

View 2 Replies View Related

Multi Value Integer Parameter

Jul 11, 2007

I am trying to create a report with a multi value integer parameter. I have tried



="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Parameters!StatusCode.Value + ")"



I get an error stating



An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'ProjectRequestStatus'.

Error during processing of the CommandText expression of dataset 'ProjectRequestStatus'.



Has anybody worked with integers?



Thanks,



Fred

View 8 Replies View Related

Can I Add Integer Array To SQLCommand As Parameter?

Jun 3, 2008

I'm trying to build an SQL string that should look like this when executed:
UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.)
I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList;
But logically enough I cannot insert them as a text string as they have to be integers seperated by commas.
Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean?
As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.")
Note the quotes around the integers.
Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.

View 4 Replies View Related

Extracting Integer Part Of The Parameter

Apr 14, 2014

I need to extract the integer part of the string that exists on the right side of the string

So e.g.

I have BASIC56 then I only want 56 and if I have BASIC6 then I only want 6 and BASIC100 then I want 100 to be extracted out in sql.

Is it possible to do that?

View 3 Replies View Related

How Do I Convert A String To An Integer For A Parameter?

Dec 14, 2006

I want to convert a string into an interger so that my parameter can get one value, and have a seperate matrix list the value before the value selected.

My parameter is year. The user picks the year. And i want the crashcounts for the year displayed in the matrix. Then i have another matrix with a dataset similiar. I want this seperate Matrix/Dataset to display the previous year.

SO if the user selects 2004 from the dropdown. 2004 is displayed in the first matrix, and 2003 is displayed in the second matrix. The year attribute is in string format, and i cant change it in the cube. So i was told it could be converted in reporting services. with this



=CStr(CInt(Parameters!CrashStatisticalYear.Value)-1)



Question is, where do i put this...and how come when i put it in the parameters expression for the 2nd matrix, i get an error datatype messege? HELP !

View 2 Replies View Related

Sorting A Parameter With Integer Values

May 11, 2007

Just to be clear i'm using a cube here.

Okay if i understand correct if you want to sort your parameter list you have to write an MDX query.

Thats all good and well i've been able to sort my parameter list when its a string



WITH

MEMBER [Measures].[ParameterCaption] AS '[Time].[Week].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Time].[Week].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Time].[Week].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[DefaultValue] AS '[Time].[Week].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[DefaultBeginValue] AS ' ( "[Time].[Week].&[" + LEFT([Time].[Week].CURRENTMEMBER.MEMBER_CAPTION,4) + "-01]" ) '

SELECT {

[Measures].[ParameterCaption],

[Measures].[ParameterValue],

[Measures].[ParameterLevel],

[Measures].[DefaultValue],

[Measures].[DefaultBeginValue]

}

ON COLUMNS

, {

Filter ((ORDER({(Filter ([Time].[Week].MEMBERS,( [Time].[Week].CURRENTMEMBER.MEMBER_CAPTION ) <> 'Unknown'))},

([Time].[Week].CURRENTMEMBER.UNIQUENAME) , DESC)),

( [Time].[Week].CURRENTMEMBER.LEVEL.ORDINAL ) = 1)

}



ON ROWS FROM [Europe]



Now my problem is that this value of this string is actually an integer. The reason this is a data type string is because its a dimension and these are always string only measures are integer...



Can someone help me make this MDX query sort on integer value instead of string.



[Time].[Week].MEMBERS contains values like 8,11,20 but is declared are string because its a dimension please help me out because i'm getting the feeling this is impossible with this microsoft tool...

View 1 Replies View Related

Why Is There A Parameter That Returns An Integer In My Stored Proc?

May 9, 2008



I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.

One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.

Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.

Any help is appreciated.

View 1 Replies View Related

Multivalued Report Parameter That Is Integer And Should Be Optional

May 23, 2007

Hello,

my problem is that i have a integer report parameter that must be multivalued. The parameter is populated by query. The thing is that in the beginning, there is no data in the dataset of the specific parameter. The table which is source to the dataset will br populated after some time from an XML.





Reporting services prompts the user to select a value for the parameter. But there is no value to select, yet. I cannot have leave blank because it is a string and not an int and i cannot have null because the parameter is multivalued. Any suggestions?





Thank you for your time and help!





/luskan

View 4 Replies View Related

How To Write A Function With Integer Parameter Accepting Null

Jan 19, 2007

I've got a function which inserts into a database, and has arguments for each item being inserted
A couple of the items are integer datatypes (in SQL), but they will accept nulls
When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):.Add("@myParam", SqlDbType.Int).Value = myParam
In the header of the function, I assumed I could make the argument optional - Optional ByVal myParam as Integer=System.DBNull.Value
However, when the function runs, I always get an error:System.InvalidCastException was unhandled by user code  Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I make them all Optional (which won't happen, but various arguments may be, at different timesand I get this error, with the last item (which is on a separate line), in red:Constant expression is required.
How can I get around this?

View 3 Replies View Related

How To Execute A Dynamic SQL With Integer Parameter For Stored Procedure?

Sep 17, 2007

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.
Error:  Unterminated String Constant.
What is the problem?
Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);

View 3 Replies View Related

Reporting Services :: Multi Value Integer Parameter For A Report

Jul 2, 2015

I'm building a report and I want to have a parameter that has multiple integer values (not text).I can get it to work just fine if I create a value for each and every numeric value, but I want the user to only see two choices in the dropdown menu (plus the "Select All" that gets automatically created when I tick the box for "Allow multiple values"). How can I make this work?

For example, for my TYPE parameter I'd like something like this:
Label: Tigers
Value: 2
Label: Lions Bears Oh My
Value: 1,3,4,5,6

But I can't seem to set a multiple Value for an integer. And I can't seem to wrap the value with quotes and use Text as the Data Type because it says an error occured during processing (I assume because the field I'm going against is of the Integer format).My query would look something like this (the real one is a lot more complex):

SELECT
v_R_System.Netbios_Name0
,v_GS_COMPUTER_SYSTEM_EXT.PCSystemType0
FROM
v_R_System
INNER JOIN v_GS_COMPUTER_SYSTEM_EXT
ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_EXT.ResourceID
WHERE
v_GS_COMPUTER_SYSTEM_EXT.PCSystemType0 IN (@TYPE)

I've tried different Expressions, filters, options, syntax, and what-not, but I think I'm missing something basic.

View 2 Replies View Related

Execute Sql Task To Set Output Parameter Of Type Integer

Jun 1, 2006

I'm having a heckuva time with creating output parameters based on a query.

Here's what I'm doing. Every day, we copy rows from our mysql table to our sql server table. We only want to copy those rows that are new, so as to not have to recopy the entire table.

So I have a DataReader Source set to Ado.net/odbc provider that connects to the mysql db.
The destination is an OLE connection to the local sql server.

I create an Execute SQL Task.
The connection is set to the OLE connection
The type is direct input
The SQL Statement is "select max(id) from copy_table"

In Parameter Mapping, I create a user variable called maxId that is an int64. That variable is now used as the Variable Name. The Direction is Output. The Parameter Name is 0.

Whatever data type I use for the mapping does not work with the variable type. If the parameter was set to ULARGE_INTEGER, here's the error
[Execute SQL Task] Error: Executing the query "SELECT MAX(stats_id) AS max_id FROM copy_table" failed with the following error: "Type name is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If parameter is set to LONG:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "maxId": "The type of the value being assigned to variable "User::maxId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

I found that if variable and parameter were dates to use datetime for the variable and DBTIMESTAMP for the parameter.

There are an awful lot of combinations between all the possible variable types, and the possible parameter types. Does anyone know the secret combination for your typical integer?

Thanks,
Lori

View 5 Replies View Related

Pass Multivalue Parameter To A Stored Procedure For Integer Lookup

Feb 19, 2007

I'd like to pass a multi-value parameter to a stored proc to satisfy an integer field lookup.

example -

CREATE PROC SSRSQuery

@InPublicationId VARCHAR(500) = NULL AS

SELECT * from Table where PublicationId IN (@InPublicationId)

where PublicationId is defined as an int

I've seen various posts on how to split up the input string parameter to use in a string-based lookup but nothing useful for an integer-based lookup.

Any tips or tricks for this?

View 3 Replies View Related

T-SQL (SS2K8) :: Stored Procedure With Parameter Option

Aug 29, 2014

I know ran across this some time ago when researching some other topic, but I would like to add an option to our stored procedures (which have multiple parameters) so a user could do something like:

EXEC dbo.usp_MyStoredProcedure - EXEC dbo.usp_MyStoredProcedure -?

The result would just list the parameters in the stored procedure and the options available for each parameter/what the parameter was use for.

Here is an example of one stored procedure in use:

CREATE PROCEDURE [dbo].[usp_MyStoredProcedure]
( @BCP INT = 0---- 0 [Default]: No BCP Import, 1: Insert TempBCP Data
, @Debug INT = 0---- 0 [Default]: Run process, 1: Create temp tables, run queries
, @StartDate smalldatetime = '1/1/1900' ---- Enter StartDate; [Default]: otherwise use Max of Invoice date
, @Date INT = 0 ---- 0 [Default]: Use std process (get last Saturday date from @StartDate), 1: Use @StartDate as is
)
WITH EXECUTE AS 'ADBAccount'
AS
BEGIN
....
END

View 2 Replies View Related

Creating An All Option For Parameter Value In GUID Format

Jan 31, 2007

I am working with SRS 2005 SP1 which no longer has the "ALL" option available on parameters. I am trying to create an "ALL" entry in a picklist so it can be used in a where clause for a dataset. I have a dataset with a union statement that creates a list of CRM usersids and names and an entry with a dummy guid with the name "All". Parameter is defined as a string type, with a dataset providing a list of users (label field) and their corresponding GUID value (value field), along with the an "All" entry.

select systemuserid, fullname
from FilteredSystemUser
Union
Select '00000000-0000-0000-0000-000000000000' as systemuserid, ' All' as fullname
order by fullname

The issue I am running into is implementing logic in another dataset referencing my parameter.

All is fine in the where clause if it is structured "where ownerid in (@Users)" but if I try to add logic to check for the "All" option "where (ownerid in (@Users) or @Users = '00000000-0000-0000-0000-000000000000') it errors out.

How do you impement "All" when you're dealing with a GUID type field? Thanks.

View 3 Replies View Related

Multivalue Pamater With The Option To Ignore The Parameter

Feb 21, 2008

Is it possible in the ssrs tool to create a multivalue parameter with the option to ignore the parameter.

There would be three choices then: Select ALL, Select these Specific Values, or Value doesn't matter.

Thanks
ssrs user

View 3 Replies View Related

Check If (Select All) Option Was Selected In Multi-value Parameter.

Jan 22, 2008


I am working in .net 2.0.
How can I check if "(Select All)" option was selected in multi-value parameter?
Thanks in advance!

View 4 Replies View Related

Passing Parameter To Another Report Using Jump To URL Option In Reporting Services 2005

Sep 10, 2007

Hi,

I tried to pass parameter from one report to another report. I can send the parameter using following option:
I used jump to url option and write the following expression:


="javascript:void(window.open('http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=%2fNextGen+Reports%2fMAUA%2fSales+Order+Detail&rs:Command=Render&SalesOrderNumber="+Fields!SalesOrderNumber.Value+"'))"

and it shows me the following in browser url

http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=/NextGen+Reports/MAUA/Sales+Order+Detail&rs:Command=Render&SalesOrderNumber=SO43667

now the problem is how to get this ordernumber in my report any option ???pls urgent...any javascript function to take this no into my another report

View 37 Replies View Related

Integration Services :: Argument For Option (parameter) Is Not Valid - The Command Line Parameters Are Invalid

May 6, 2015

i am parameterize  my DB connection in sql sever 2012,it showing in agent job as parameter.but when i run job it giving me error:

Argument "" for option "parameter" is not valid.  The command line parameters are invalid.  The step failed.

what i need to do is pass different environment here while deploying to different env.

View 2 Replies View Related

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

Jul 20, 2015

Working on a new database where the Date and Time are stored in a Date Time Field.

Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:

transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)

How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?

This works well for converting the transDate Part in the select statement:

   dbo.IntegerToDate(at.transDate) as transDate

   * That returns: "2015-07-16 00:00:00.000"

* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!

Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.

View 3 Replies View Related

In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing

Apr 27, 2008

Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio  2005 Setup.

View 2 Replies View Related

SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option

Feb 10, 2015

So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.

From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.

View 3 Replies View Related

Integer / Integer

May 19, 2008

Hi All,

If I run "SELECT 610/100", query analyser returns 6! (wrong)

If I run "SELECT 610.0/100", query analyser returns 6.100000! (correct)

I know I can get the correct result by running "SELECT Cast(610.0 as Float)/100", but why doesn't the first example return 6.1?

Is there some setting on my server which says Integer / Integer = Integer ?

Regards

Xo

View 12 Replies View Related

UniqueID Vs Integer

Feb 13, 2008

My question is, i guess, a simple one:
When is it more convenient to use a uniqueid Data Type instead of a smallint, tinyint, bigint, etc (any type of int) when the field is gonna be the primary key for the table?
 

View 1 Replies View Related

Help With Integer Field

Oct 7, 2004

hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.

View 2 Replies View Related

Inserting An Integer

May 19, 2005

Public Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds
End Function
Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.

View 2 Replies View Related

Getting Value From Database Into Integer

Mar 16, 2006

I have an sql statmetn that counts all the votes in the table. i need this to calculate the quota. My problem is how to i get the value(i.e the count of the votes) into the area thats colored red below?
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota =  (count(Vote) + 1) / ((11) + 1);
Response.Write(quota);

View 1 Replies View Related

GUID Vs Integer

Apr 17, 2006

I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)

View 6 Replies View Related

Integer Datatype

Dec 4, 2000

Hi all,

I have a situation where I will have to insert a value(whole number) into the table where the the value is more than what the Integer can hold , I was wondering is there any other datatype which i can use other than integer


Thanks in advance.

Sanjeev Kumar

View 1 Replies View Related

Default Value For Integer

Dec 3, 2004

I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.

View 7 Replies View Related

Format Integer

May 18, 2008

I have some integers I want to format prefixed with zeros, e.g. 1235 would become 001235 and 445 would become 000445,

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved