I have a report that has DateTime parameters e.g. StartDate and EndDate. When I create these report parameters the "Allow Blank" option is disabled but checked. When I run the report and do not enter anything in these fields it pops a message asking me to enter the Date. How do I use the "Allow Blank" option for date parameters on my report?
View 4 RepliesCommunity:
This is my scenario: a Cube in SSAS, dimension time with an attribute "cdate" defined as datetime, model deployed using "Generate Model" option of Report Server.
Querying in Report Builder: only _date attribute of time dimension is selected. Filter use _date fields , from ...to option is selected, from the calendar I select from 1 january 2007 to 10 january 2007, results a list of dates between 1 january 2007 to 1 October 2007!!!
When I select from 1 january 2007 to 15 january 2007 the list is correct!!!
Clearly it interchange days and months in first case, and do it right in second case.
What is it about? it's about Report Server settings? SQL Server settings? .Net Settings? Windows Settings?
What do I have to do ?
Regards
Julio DÃaz C.
I have a report that I am building that consist of 4 multi-value selections from four different queries. When I choose "Select All" from all the drop downs and click on "View Reports", it blanks out all the selections from one of the drop downs.
Can someone please point me in the right direction on this problem. The report will run sometimes, but most of the time it will not.
Thanks
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies View RelatedHow do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies View Related
Hi ,
Iam using sqlserver reporting services 2005 and in one case I need to find out the difference between two datetime parameters. I need the difference with the time. Can you please help me in this....
Thanks in advance...
Hi friends,
I want a parameter with datetime along timestamp. For example end user sholud select datetime and timestamp i.e. 11/02/2007 11:10. Is datetime type is sufficient. or is any other way to solve this problem.
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
Here is a situation in my company:
We have an Office SharePoint 2007 site, we developed a couple of web parts and added them to our site. We used SQL Server Reporting services 2005 as our reporting solution. The reports are hosted on the report center and when we need a report, we open it by sending a URL requesting the report, passing the report parameters in the URL query string. So the report is filtered based on the parameters passed from the web parts.
But since best practices say that you should host reports directly under SharePoint, by configuring the reporting services to run in the SharePoint integrated mode. We followed the steps and installed SharePointRS and we finally succeeded to publish the reports to a SharePoint folder, but we had a limitation: we are unable to pass the report parameters internally to the report hosted in SharePoint. If we passed them in the query string as the report center case, SharePoint neglects them totally.
So the question is: how can I pass parameters internally between a SharePoint web part and a SQL server 2005 reporting services report hosted in SharePoint?
When I go to Report > Report Parameters... in VS, I create date parameters and specify the type as datetime. I then add the parameters to my SQL statement. I then go back to look at the parameters and they have changed to string. This happens every time I make a modification to the SQL statement. Is this a bug, or am I missing a step? I thought someone would have asked this before, but I can't find a post for it in this forum.
Thanks in advance,
Scott
I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
columns are **Level**  and        **Salesamount**. Â
Values are like Â
**[-]category** Â Â Â Â Â Â Â **100**
  **[-]subcat**       **50**
     **product**      **30**
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
Here i used the expressions in parameter values
**pCategory**----iif(inscope(category),!fields.category.value,split(join(Parameters!category.value,","),","))
**psubcat**----iif(inscope(subcat),!fields.subcat.value,split(join(Parameters!subcat.value,","),","))
**pproduct**----iif(inscope(product),!fields.product.value,split(join(Parameters!product.value,","),","))
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
View 3 Replies View RelatedHi,
I am having hard time in sending parameters to the report server through reportviewer control from my application. Could anybody help me on this issue?. Is there any way to send the parameters and also what kind of configuration do i need on my report project so that it can accept parameters from my application.
Thanks,
Good morning, all
I have a really cool Budget report which has two matrices on it. One is used/visible if the user selects four levels of grouping, and one is used if the user selects only three levels of grouping. The four choices for grouping are four fields, Par, FERC, Point, and Resource.
Since grouping is optional, I need a way to pass the parameter for a field that is not grouped on from the main report to the drillthrough report. For example if the user chooses to group on all fields but Point, I need to pass the report parameter that was used by the main report for Point to the drillthrough report as a report parameter.
So, I tried testing the three grouping levels values and if none were equal to "Point" then pass in the parameter by putting the following expression in the Parameter Value box for the Point parameter on the Navigation tab of the datacell of the main report that is the drillthrough link:
=iif(
Parameters!GroupLevel1.Value <> "Point"
and
Parameters!GroupLevel2.Value<>"Point"
and
Parameters!GroupLevel3.Value<>"Point"
,
Join(Parameters!PNTPoint.Value,",")
,
Fields!Point.UniqueName
)
But this is not working; the drillthrough report shows a blank dropdown for the Point parameter and I have to manually set it. Does anyone see an error in the syntax of the iif statement?
Any references, links, resources or good clean jokes would be greatly appreciated.
Thanks,
Kathryn
Dear All,
Is it possible to poplulate some control like Dropdownlist (for example, with Product names Or Product Ids) from database table in SSRS project type and then filter report by choosing various choice pre-populated in dropdownlist control.
Project Type is : SQL Server Reportings 2005.
Desired Action: Report will run in browser, User will choose specific product and Run the report(filter) to show that product relate results.
any help,
Thanks,
Is it possible to add custom controls in for parameters in report manager??
I have a parameter that requires the user to click a button and select a bunch of things in another form. How can i do this in reporting services??
I also would like to change the layout of the parameters toolbar, ie. make text boxes smaller widths, show/hide parameters based on other parameter values.
I know i could write my own web page that does this and hide the parameters toolbar when they run the report, but this would mean that we lose other functionality that report manager has, eg. scheduling.
Does anyone know if customising the report manager will become available in a later version??
In my SSIS package I am trying to connect to a database in Oracle 10.2.0.3 using an Oracle Provider for OLE DB.
I get the following error:
at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at System.Data.ProviderBase.DbBuffer.ReadDateTime(Int32 offset)
at System.Data.OleDb.ColumnBinding.Value_DBTIMESTAMP()
at System.Data.OleDb.ColumnBinding.Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbMetaDataFactory.PrepareCollection(String collectionName, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
at System.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName, String[] restrictionValues)
at Microsoft.DataWarehouse.Design.OleDbSchema.GetSchema(String collectionName, String[] restrictionValues)
at Microsoft.DataWarehouse.Design.DataSourceConnection.GetSchemaTable(DataTable& dataTable, String collectionName, String[] restrictions)
at Microsoft.DataWarehouse.Design.DataSourceConnection.ConvertGetSchema(DataTable& dataTable, Guid schemaEnum, Object[] restrictions)
at Microsoft.DataWarehouse.Design.DataSourceConnection.GetSchemaTable(DataTable& dataTable, Guid schemaEnum, Object[] restrictions)
at Microsoft.DataTransformationServices.Design.ComboBoxWithTables.GetTableInfos(DataSourceConnection dataSourceConnection, String connectionName)
at Microsoft.DataTransformationServices.Design.ComboBoxWithTables.FillTableOrViewComboBox()
Why this is not working for 10.2?
The same package works fine for Oracle 8.1.7
THANKS FOR YOUR HELP!
Greetings,
I have two reports, Report A and Report B.
Report A has two parameters defined as "Prompted", meaning the report input area is available for the user.
Report A parameters ---> StartDate, EndDate
When Report B invokes Report A using the Jump To Report Functionality and passing Report B's StartDate and EndDate parameters, Report A seems to think the parameters are implied and not needed and fails to drop down the parameter input area.
This is not desired because the user should be able to change report A's parameters.
Anyone know a workaround?
Nevil Mascarenhas writes "Hi SQL team,
I am just a beginer in SQL
Here is sample output of a SQL query
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00
In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX
Output required is
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD XXX XXX
5453 QWE 22-08-05 01:00
Can you please guide me."
I wrote a simple data flow with an OLE DB source and destination. I do a direct mapping of the columns ( colA - > colA) with no transformations needed. I found that colA on the destinatin does not allow NULLS (required by the program that accesses that database) while colA on the source supports and has NULLs, Is there any accomodaiton for handling NULLS (like mapping them to blanks) in the direct copy approach or do I need to read each row and test colA_ISNull?
TIA,
barkingdog
Quick Question I hope.
Does having a lot of blanks in a column cause errors ?
In 2 or 3 packages where I get 95% of the data in the Error Colomn I can find nothing wrong with the data at all ? it all either seems perfect or there is no data in the column in question usually I would have maybe 800 rows where data would have been inserted but in the other 40, 000 rows the column is blank
Using the "Retain null values from the source as null values in the destination" doesnt seem to make a differance.
This is the error description
The data value cannot be converted for reasons other than sign mismatch or data overflow.
Anyone know of a solution / reason why this keeps happening
Thanks
I have drop down Date Range(Last Month,Current Month and Custom)..
We need to set the values in our From Date Report Filter and To Date Report Filter.
We gave the Date Parameter Value in Condition to the Date Range Selected Value.
But the value always remain as on First Load of Report.
Is there any way i can set values in datetime text box..
.
hi there
I am using Reporting Services 2005.
I have 3 report parameters: startdate, enddate, callstatus
My problem is that once I added callstatus to the parameter list my dates were ignored?
What do I need to do to correct it?
My select code is as below:
SELECT Ticket.TicketNumber, Application.ApplicationName, Status.StatusName, Ticket.LastModifiedBy, CONVERT(datetime, Ticket.CreatedDate),
Company.CompanyName
FROM Ticket INNER JOIN
Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Status ON Ticket.StatusID = Status.StatusID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID
WHERE (CONVERT(datetime, Ticket.CreatedDate) >= @StartDate) AND (CONVERT(datetime, Ticket.CreatedDate) <= @EndDate) AND
(Status.StatusName IN (@CallStatus)) OR
('SELECT ALL' IN (@CallStatus))
My report Parameters have the values listed in the Non-queried part on report parameters
thanks
Dianne
Why is it that EVERY TIME you make any change at all to a data set or parameter, RS automatically changes your parameters from single value to Multi-value????
This is VERY annoying.
Is there a way to force report designer overlook parameter dependencies, so that a report could be displayed by either of the two parameters although one parameter accepts but not requires the other?
For Example:
In my report I have two parameters, DeptID and ProjectID. The two drop downs display Departments and Projects respectively but only if the department is selected, the Projects list becomes active as its expecting a DepartmentID in the stored proc but accepts a null value as well. So, what I would like to achieve is display ALL projects in the Projects list, even if no department is selected. Any ideas how to do that?
Thanks.
Hello,
I have an mdx report that uses 7 parameters.
How do I stop the parameters from being dependent on one another and cascading?
When a user changes a parameter value, the flashing and repopulating of the other parameter boxes takes longer than the actual report to run.
I've tried removing the dependencies from the parameter dataset mdx code as well as edititng the parameter datasets and removing the dependencies.
Is cascading parameter relationships a default by design?
Any insight would be appreciated.
Thank you.
Hi,
I have a report and the parameters Passed to the report are Cusip and PeriodId through a stored Procedure.
i want to populate the cusip Parameters using a dataset thats got by a sproc called usp_getCusips.
and the PerioId using a dataset thats been got by another sproc called usp_Getperiods...
So in my report i have added 3 datasets one for the main report, one to get the values of the PeriodId and other to get the value of the cusip.
So in my Report parameters for the Parameter Cusip and i have checked the Available value option and selected its corresponding Dataset and value .
and i have also done the same thing for the PeriodId.. But when i run my sproc i am getting this Error.
[rsInvalidReportParameterDependency] The report parameter €˜Cusip€™ has a DefaultValue or a ValidValue that depends on the report parameter €œCusip€?. Forward dependencies are not valid.
[rsInvalidReportParameterDependency] The report parameter €˜PeriodId€™ has a DefaultValue or a ValidValue that depends on the report parameter €œPeriodId€?. Forward dependencies are not valid.
Any Help will be appreciated.
Regards,
Karen
I've had a report working that uses a parameter that allows null values. So, of course, I have the "Allow Null Values" flag checked on the parameters dialog for the respective parameter. But recently I changed the "Available values" option for the parameter to "From Query" but then I lose the ability to allow a null value! Why is this happening? Why are they mutually exclusive?
I posted this scenario earlier and ndinakar said my code worked on (I guess) his own machine but has not worked on mine! Does that not sound funny? I need to ge through with this simple but frustrating stuff in time. I've implemented more complex snippets before this but I just can't figure the problem out.
Can anyone out there tell me what I could be doing wrongly? I wrote a simple stored procedure called 'proc_insert_webuser' in a database that has been moved to a remote server called LAGOS-NTS3. I executed it from SQL Query Analyser and it worked. When, however, I executed the same stored procedure from ASP.Net it gives an impression that a new record has been inserted and even displays the newly generated ID column to me. On checking the base table I found only blank columns with only the ID column having the generated IDs!
I'm perplexed. I checked the code and can't possibly spot the error. Can anyone help. Found below are snippets used.
CREATE PROCEDURE proc_insert_webuser
(
@applicantidnumeric(18) output,
@firstnamevarchar(18),
@midname varchar(18),
@lastname varchar(50),
@secretcodevarchar(18),
@my_errnumeric(18) output
)
AS
IF (SELECT COUNT(ApplicantID) FROM tbl_webuser WHERE Firstname = @firstname AND Midname = @midname AND Lastname = @lastname) = 0
BEGIN
begin transaction
INSERT INTO tbl_webuser (Firstname, Midname, Lastname, SecretCode)
VALUES (@Firstname, @Midname, @Lastname, @SecretCode)
commit transaction
SET @applicantid = @@IDENTITY
END
ELSE
BEGIN
RAISERROR('Error! Execution aborted. A matching user profile exist.', 16, 1)
SELECT @my_err = @@ERROR
END
RETURN
Then the ASP.Net stuff:
Sub PostData()
'connection string production
Dim cstring As String = "User ID = sa; Password = ; database = E-Recruitment; Server = LAGOS-NTS3; Connect Timeout = 60"
'connection instantiation
Dim cnx As SqlConnection = New SqlConnection(cstring)
Try
'open connection
cnx.Open()
'instantiates and execute a command object
Dim cmd As SqlCommand = New SqlCommand
With cmd
.Connection = cnx
.CommandText = "proc_insert_webuser"
.CommandType = CommandType.StoredProcedure
Dim param1 As New SqlClient.SqlParameter("@applicantid", 0)
param1.DbType = DbType.Single
param1.Direction = ParameterDirection.Output
.Parameters.Add(param1)
Dim param2 As New SqlClient.SqlParameter("@firstname", txtFirstNM.Text)
param2.DbType = DbType.String
param2.Direction = ParameterDirection.Input
.Parameters.Add(param2)
Dim param3 As New SqlClient.SqlParameter("@midname", txtMidNM.Text)
param3.DbType = DbType.String
param3.Direction = ParameterDirection.Input
.Parameters.Add(param3)
Dim param4 As New SqlClient.SqlParameter("@lastname", txtLastNM.Text)
param4.DbType = DbType.String
param4.Direction = ParameterDirection.Input
.Parameters.Add(param4)
Dim param5 As New SqlClient.SqlParameter("@secretcode", txtPWD.Text)
param5.DbType = DbType.String
param5.Direction = ParameterDirection.Input
.Parameters.Add(param5)
Dim param6 As New SqlClient.SqlParameter("@my_err", 0)
param6.DbType = DbType.Single
param6.Direction = ParameterDirection.Output
.Parameters.Add(param6)
.ExecuteNonQuery()
'pick the auto-number
Dim strAppID = .Parameters("@applicantid").Value
'notifies the user of record success
lblMessage.Text = "Profile created. Your ApplicantID is : " & strAppID & " Kindly take note of your UserID. You'll need it for future logins."
End With
Catch ex As Exception
'notifies the user of any error(s)
lblMessage.Text = Err.Description & " originating from " & Err.Source
Finally
cnx.Close()
End Try
End Sub
==================
I'm calling this procedure from the click event of a command button. What could be happening to the code? I have done similar things and got positive results! Any hint from anyone out there?
A table was created in version 6.5 that has 2 blank spaces and a slash '/' in it's name (i.e. Item Type w/Groups). This was done in error and now the table cannot be dropped or renamed and dropped.
Does anyone know how I can delete this table? Please let me know.
Hi,
I'm trying to create a view from a table in which I want to concatenate to columns to a new column. The concatenation part works fine when data "supports" calculations, i.e. no nulls. The data however is "infected" in two ways I have Null-values and I have blanks alongside with some real information. I concatenate via a CASE statement but I can't figure out how to take the 3 situations into account (nulls, blanks and data) and create one meaningfull column in my view.
The result should be
column1 + "-" if column2 is null or blank
column1 + "-" + column2 if column2 has meaningfull data
Both the involved columns are text (varchar).
If anyone has been in this situation before could you please provide som code that handles the situation.
Regards
Kim Hansen
Hello,
In my .NET.ASP application I generate a random five digit password, like so:
string password = Membership.GeneratePassword(5, 1);
The result could be a password like "0l$IE" and I the save this password to an SQL server. The problem is however that with the generated password five blanks are also added. Instead of "0l$IE" I get "0l$IE ". I hope you understand what I mean? Is there an easy way to correct this?
I appreciate any help!
I have an Excel spreadsheet with a field that may contain blanks (empty). When I filter on that field to show only the blanks, I get 4000 records returned. But if I import the spreadsheet into SQL Server, the test in there for blanks ('') returns nothing. However, if I test for Nulls in that field, I get 5000 records returned. I don't know which one (Excel or SQL Server) is giving me the right answer. Can anyone help me understand what's going on?
Thanks!