Displaying All Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Displaying All Data

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-05 : 12:03:38

Hi Guys

I am using the following query in Reporting Services:
CREATE PROCEDURE [dbo].[uspLogReturnedItems] (@ReasonCode Int)
AS

SELECT
ri.receiptid
,ri.receiptitemid
,ri.sku
,ri.shortdescription
,vr.reasonname
,CONVERT(VARCHAR(8),ri.dateentered,112) AS 'DateEntered'
FROM [dbo].[voiditem] vi (nolock)
INNER JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid
INNER JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid
WHERE ri.dateentered >= DATEADD(week, -4, GETDATE())
AND enteredbyreasonid = @ReasonCode
ORDER BY enteredbyreasonid, receiptid, CONVERT(VARCHAR(8),ri.dateentered,112)

I have a drop down box which allows the user to choose what value they want to use for the parameter and display the data which matches that value. The trouble I am having is that I dont know how to display all the data ,i.e. I dont want the filter to apply. Is this possible?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 12:12:21

you mean make fileter optional? set the default value of filter as null in parameter tab and make procedure as below


CREATE PROCEDURE [dbo].[uspLogReturnedItems] (@ReasonCode Int)
AS

SELECT 
ri.receiptid 
,ri.receiptitemid
,ri.sku
,ri.shortdescription
,vr.reasonname
,CONVERT(VARCHAR(8),ri.dateentered,112) AS 'DateEntered'
FROM [dbo].[voiditem] vi (nolock) 
INNER JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid
INNER JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid
WHERE ri.dateentered >= DATEADD(week, -4, GETDATE()) 
AND (enteredbyreasonid = @ReasonCode OR @ReasonCode IS NULL)
ORDER BY enteredbyreasonid, receiptid, CONVERT(VARCHAR(8),ri.dateentered,112)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-05 : 17:45:17

Hi

What I want to achieve is when the report opens it automatically shows all the information available.

Is this possible?

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-11-06 : 13:21:50

Yes, as long as all of your parameters have default values, the report will immediately generate results upon being opened.

---------------------------
EmeraldCityDomains.com

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2008-11-14 : 12:55:13

I'm having the same problems, my parameters in my stored procedure have default values of NULL and in my where clause I am looking for the parameter or where the parameter is null.

I have also set the report parameters to allow null and nothing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 00:14:12

quote:
Originally posted by RyanAustin

I'm having the same problems, my parameters in my stored procedure have default values of NULL and in my where clause I am looking for the parameter or where the parameter is null.

I have also set the report parameters to allow null and nothing.

then whats the problem? have you also set default values of parameters as NUll. then report should run automatically.
Also in future, post your question as seperate thread rather than appending to a old thread. it will certainly get more attention and you will get quicker responses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources