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!!
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
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.
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.
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
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...
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.
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?
Hi:I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter. I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?Here's my query: ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter] ( @firstLetter char(1))AS SELECT Media_ID, OrgName FROM Media WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetterAny help doing this would be greatly appreciated.Roger
I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero
I have a visible DateTime parameter on the Report that I would like to make optional.
When I open up the report and try to run it without specifying the date I get a validation error that parameter is required. If I set default value to null - it works, but I have to check for both '' and NULL in my query:
StartDateTime >= @StartDate OR @StartDate='' OR @StartDate IS NULL
I tried setting default parameter using expression ='' but I get an error that 'Default Value' of parameter 'StartDate' doesn't have expected time.
So the question is can I set DateTime parameters' default value to blank?
I build reports using reporting services 2005 sp2. I have 3 parameters---> 3 multi value combo box . However when I tick "allow blank value" within the report parameter properties for this drop menu, report WILL NOT run without making selection.(The xml is ok allow blank value set to true) the reports still require you to enter a value for all parameters in the report when the report can and should be able to be run with no parameter defined. the trick "default values for all of the params to "=String.Empty"" is not good .
I found this article but i can't find microsoft hotfix for it .
Looks like a very simple problem but not geeting it.. I have parameter as ALL and it has corresponding value as <BLANK>,which comes by default. Allow Blank to true
How I can select this parameter as default value in parameter??
Please make sure that if I am taking '' then it is getting treated differently.I don;t want Value of ALL as '' or NULL
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?
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);
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.
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?
CREATE PROCEDURE [Get_WirelessProducts_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, ProductID int, ProductCategoryID nvarchar(50), ProductBandwidthKB int, ProductOverridePrice nvarchar(50), UseWirelessOverridePrice bit, DedicationTypeID int, DedicationTypeName nvarchar(50) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID, DedicationTypeName ) SELECT W.ProductID, W.ProductCategoryID, W.ProductBandwidthKB, W.ProductOverridePrice, W.UseWirelessOverridePrice, W.DedicationTypeID, D.DedicationTypeName FROM tblWirelessProducts W INNER JOIN tblDedicationTypes D ON W.DedicationTypeID = D.DedicationTypeID --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID DedicationTypeName FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblWirelessProducts GO Here is the relevant VB Code: Dim parmReturnValue As SqlParameter 'conProducts is already open cmdProducts = New SqlCommand("Get_WirelessProducts_By_Page", conProducts) cmdProducts.CommandType = CommandType.StoredProcedure cmdProducts.Parameters.Add("@CurrentPage", intCurrentPage) cmdProducts.Parameters.Add("@PageSize", dgrdProducts.PageSize) parmReturnValue = cmdProducts.Parameters.Add("@TotalRecords", SqlDbType.int) parmReturnValue.Direction = ParameterDirection.Output conProducts.Open dgrdProducts.DataSource = cmdProducts.ExecuteReader()
If Not IsDBNull(cmdProducts.Parameters("@TotalRecords").Value) then Response.Write(cmdProducts.Parameters("@TotalRecords").Value) End IfThe rows are returned correctly, but the output parameter "@TotalRecords" doesn't return anything. Any ideas what I'm doing wrong? Thanks in advance for your help.
1, 2 and 3 parameter are text inputs. 4th is multi-value parameter and 5th is again a text input. I need to Pass a blank parameter to my 5th parameter.
1) I tried the below Expression in the "UploadedEnt" DataSet Properties and not in the "Main" DataSet.
I have taken the actual sql query (file) and I have just placed the select statement.
DataSet Name: UploadedEntselect distinct UploadEnt from ( SELECT DISTINCT col1, col2... ....) Ent
Order by 1OR logic has been applied in the Tablix Properties Filters as an expression.=Fields!value1.Value like Parameters!value1Param.Value Or Fields!value2.Value like Parameters!value2Param.Value Or Fields!value3.Value like Parameters!value3Param.Value Or Fields!value4.Value = Parameters!value4Param.Value(0) Or Fields!UploadEnt.Value = Parameters!UploadedEntParam.ValueFilter: Expression = TRUE2) I tried applying NULL check box which works perfectly but I do not want to apply that here.How to Pass a Blank Parameter?
Is it possible to enable parameter of type date to be blank (not null)?
I created parameter of type date, gave it no default value, when I pressed the preview tab I got error message "The property 'DefaultValue' of report parameter 'DateParamName' doesn't have the expected type" Thanks in advance!
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.
I have a Multivalue parameter with 4 available values and have 4 columns in the report that correspond to each of these values.I apply column visibility to each of the 4 columns with the following expression (the number changes for each column
What I want to do is that if I select a KPI and the Column value is NULL then to hide the row.Obviously if you select multiple KPIs and only 1 of the columns has null value then I wouldn't want the Row hidden.
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
With cmdCategoriesUpdate .Parameters("@Field1Tag").Value = txtFld1.Text .Parameters("@Field2Tag").Value = txtFld2.Text End With cmdCategoriesUpdate.ExecuteNonQuery() SqlConnection1.Close()
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.
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?
