Transact SQL :: Nulls In Parameters In Report
Oct 22, 2015
I got parameter1 and parameter2 in my reports. They both might have nulls in the parent column.
When I select a value in parameter1 the report needs to show all rows irrespective of a value or null in on parameter2 - parent column and similarly when I select a value in parameter2 then it needs to show me rows even if got nulls in parameter1 - parent column.
How do I achieve the same. I will be logging off from work now but if further details are needed from me, then I will furnish it tomorrow morning.
View 13 Replies
ADVERTISEMENT
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
Jun 12, 2008
I am trying to set up a stored procedure to return details about an item in my database. Say I use the following declarations for a stored procedure:
@Parameter1 int output,
@Parameter2 varchar(200) output,
@Parameter3 int output
and then I used a query like:
select @Parameter1 = table.field1, @Parameter2 = table.field2, @Parameter3 = table.field3 from table where itemID = 7
to populate the output parameters the problem I am running into is that if @Parameter1 is null, then @Parameter2 and @Parameter3 also return as null. But, If i rewrite the declarations to:
@Parameter3 int output,
@Parameter2 varchar(200) output,
@Parameter1 int output
then, with the same query, @Parameter3 and @Parameter2 get the values they should, even if @Parameter1 is null
does anyone have any ideas?
I guess another question is, I am right now in the code itself (not the demonstration here) returning 6 values, since this procedure will only ever return one record, I decided to implement it as output variables, rather than returning a 1 record result set to the application. Am I better off just returning the 1 record result set then parsing it out into variables in the application?
Thanks in advance
-madrak
View 4 Replies
View Related
Feb 13, 2008
Hi Folks,
Probably simple, but not to me!
I've built a report to pull data from a Parameterized SP, using a null value for a parameter to mean 'all' of that type (was using dynamic sql, but now doing boolean logic in the where clause).
It runs fine in SSRS VS, but when I deploy and try to run it I get:
An error has occurred during report processing.
Cannot add a query parameter to the parameter collection for data set 'DataSource1'.
Command parameter values of type 'System.DBNull' are not supported. Parameter name: value
Has anyone else run in to this?
Many thanks in advance,
Ron
View 2 Replies
View Related
Oct 29, 2013
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 Related
May 3, 2007
How 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
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3. Â
If the declined date is filled in. Â Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but  i got an error message when trying this out.  Against the code Â
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. Â What do I need to do to get both my outputs working?Â
View 9 Replies
View Related
Apr 30, 2007
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
View 1 Replies
View Related
Nov 18, 2007
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?
View 1 Replies
View Related
Sep 16, 2015
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,","),","))
View 3 Replies
View Related
Feb 8, 2015
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 Related
May 16, 2006
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
View 4 Replies
View Related
Mar 28, 2008
Hi,
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,
View 9 Replies
View Related
Nov 28, 2007
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
View 1 Replies
View Related
Oct 22, 2007
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,
View 4 Replies
View Related
Feb 7, 2007
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??
View 1 Replies
View Related
Feb 17, 2008
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies
View Related
May 15, 2008
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View 2 Replies
View Related
Sep 23, 2015
I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working. Â at the bottom of the SP is a line of code that calls a function, the function queries a View. Â When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function. Â This all works but I can't get the data returned by the function query onto a browser.
I am doing it this was as the parameters in my SP are also used when calling the function, works really well. Â Is GTotal a return value?
Here is my function:
USE [LOGONs]
GO
/****** Object: Â UserDefinedFunction [dbo].[FX_AnnaulBudget1] Â Â Script Date: 23/09/2015 12:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
...and here is my SP:
USE [LOGONs]
GO
/****** Object: Â StoredProcedure [dbo].[USP_AnnualBudget] Â Â Script Date: 23/09/2015 12:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AnnualBudget]
[Code] .....
View 2 Replies
View Related
May 28, 2015
I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 16 Replies
View Related
May 16, 2015
Below code gives the error "No value given for one or more required parameters"
strSQL = "insert into FILE_UPLOAD(RefNo, FileName, FileType, FileData, FileDescription, Created, CreatedBy) values "
strSQL = strSQL & "(?, ?, ?, ?, ?, ?, ?)"
Dim cmd As New OleDbCommand
cmd.Connection = objCommon.oConn
cmd.CommandText = strSQL
cmd.Parameters.Add("@RefNo", OleDbType.VarChar).Value = ""
cmd.Parameters.Add("@FileName", OleDbType.VarChar).Value = strFileName
[code]....
View 3 Replies
View Related
Jan 15, 2007
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?
View 1 Replies
View Related
Aug 28, 2015
I am having to debug a procedure that is called by a control in javascript that I do not have any control over accept for setting the procedure name to call.
I matched my parameters to my procedure to what I define as the parameters list, but somehow I keep getting a too many parameters specified.
If I had control over it in C# it would be easy to select which params are actually being sent, but I would like to give a procedure name to call and I can log the parameters sent somewhere.how to do.
View 5 Replies
View Related
Oct 26, 2007
Hello,
Is there any way to run a parameterized statement against SQL when you have an unknown ammount of inputs.
Instead of doing the following:
FOR i as integer = 0 to topValuestrWhere &= " OR myColName='"& myArr(i) &"'"
NEXT
wash up the string and submit this to SQL, I want to do it with parameters. I'm calling a sproc in SQL and I would prefer just to send in a string like "1,4,42',45" in a parameter split that in SQL and run my query.The query would be something like "...WHERE myColname=1 OR myColname=2 OR myColName=3" I only want to declare one parameter and not use string concatenation as described above.It doesn't matter if there's a split function in SQL or not, as long as it solves my problem in an efficient manner. Cheers!/Eskil
View 3 Replies
View Related
Oct 15, 2007
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
View 3 Replies
View Related
Sep 19, 2007
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.
View 3 Replies
View Related
Jun 7, 2007
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.
View 2 Replies
View Related
Jan 15, 2008
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.
View 5 Replies
View Related
Aug 8, 2007
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
View 7 Replies
View Related
Feb 21, 2008
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?
View 1 Replies
View Related
May 12, 2008
I am doing report development against cube. I have a main report and a sub report in the main report. I am collecting the parameters in the main report and passing into sub report which has the same exact parameters. (I am assigning parameters to the sub report from the Fields collection. If I assign from the Parameter collection, it works fine, but I need to do based on Fields collection because I have my grouping like that)
I am getting the error above, what could I be doing wrong?
View 1 Replies
View Related
Nov 16, 2007
Hi all,
I'm writing a web interface and calling reports from report server. I first used the ReportViewer control. But it seemed to be impressively faster to directly call the report URL and link that directly in a frame.
This all works fine. But I want to dynamically add the parameter values that are used by the report in my URL string. To find out those, I have written a stored procedure:
CREATE PROCEDURE dbo.spIIMGetReportParameters
@sReportname varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xmlParameter xml
SELECT @xmlParameter =
(SELECT Parameter
FROM [catalog]
WHERE [Name] = @sReportname)
SELECT parameter.Name.value('.','nvarchar(50)') AS ParameterName
FROM @xmlParameter.nodes('/Parameters/Parameter/Name') as parameter(Name)
END
Then I loop through the result and add parameters to the Url string.
But sometimes I get parameters that are not needed.
Is there another way to find at runtime which parameters a report expects?
Thanks,
Jan
View 1 Replies
View Related
Aug 8, 2007
Hi,
I have kept two Report Parameters "StartDate" and "EndDate" and i have selected From Query and viewed the preview. I got the reults but when i am selecting the two dates it is not validating the condition.
I need the results between StartDate and EndDate. For this i have to set any where in the parameters or is there any other way
can you please help me in this
Thanks
Dinesh
View 15 Replies
View Related
