Display all returned values - 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)

Display all returned values

Author

Topic

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2008-10-23 : 15:17:47

I have a stored procedure, that when executed through RS requires parameters to be set before it returns values. I would like to change this a little so that when you execute the report it returns everything possible in the parameter selection and then start narrowing it down from there. IE have an option for all values first.

This is the code that I am using:
USE [ChMS_Dashboard]
GO
/****** Object: StoredProcedure [dbo].[RFC_AllChanges] Script Date: 10/23/2008 09:34:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RFC_AllChanges]
( @Tech int
, @Func int
, @Priority int
, @Impact int)
AS

set nocount on

select r.rfcid
, r.title
, p.name as priority
, i.name as impact
, s.name as status
, (select u.firstname+' '+u.lastname
from change_management_dev.dbo.tbluser u
where u.userid = r.creatorid) Initiator
, (select u.firstname+' '+u.lastname
from change_management_dev.dbo.tbluser u
where u.userid = r.functionalownerid) functionalowner
, (select u.firstname+' '+u.lastname
from change_management_dev.dbo.tbluser u
where u.userid = r.technicalownerid) technicalowner
, CASE WHEN l.locationid = 1 THEN e.name ELSE l.name END scheduledenv
, t.scheduledfromdate
, t.scheduledtodate
, CASE WHEN l.locationid = 1 THEN e.name ELSE l.name END implementedenv
, t.actualtodate
, t.actualfromdate
from rfc r
, priority p
, impact i
, status s
, timescale t
, environment e
, location l
where r.priorityid = p.priorityid
and r.impactid = i.impactid
and r.statusid = s.statusid
and t.environmentid = e.environmentid
and t.rfcid = r.rfcid
and t.locationid = l.locationid
and e.environmentid = 1
and s.statusid in (2,3,4,6,7,8,9,11,12,13)
and (r.functionalownerid = @Func or r.technicalownerid = @Tech)
and r.priorityid = @Priority
and r.impactid = @Impact
order by r.rfcid asc

set nocount off

/*
exec RFC_AllChanges '','75','4','3'
*/

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-10-23 : 18:34:14

If I have understood your requirements correcty, you can set your parameters in RS to "Allow Null Value". And you can change 3 lines your sql to:
and (r.functionalownerid = @Func OR @Func IS NULL or r.technicalownerid = @Tech OR @Tech IS NULL )
and (r.priorityid = @Priority OR @Priority IS NULL)
and (r.impactid = @Impact OR @impact IS NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 02:15:53

seeing your question, it seems like you want to cause filetering to happen only in reports. In that case, remove all params from stored procedure and add the filters to dataset properties fillters tab.

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2008-10-24 : 16:56:49

Dexter,

I tried using the IS NULL and setting the "Allow Null Value" and the stored proc on its own works when I pass NULL in the dataset tab testing the stored proc, but still the report will start off asking for the report parameters.

Ryan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:39:52

quote:
Originally posted by RyanAustin

Dexter,

I tried using the IS NULL and setting the "Allow Null Value" and the stored proc on its own works when I pass NULL in the dataset tab testing the stored proc, but still the report will start off asking for the report parameters.

Ryan

If you want report not to ask for parameter values, then your stored procedure should have any. thats why i told you to remove all parameters and then do the filtering you need after you fill datagrid in datatab by running the procedure.The filters can be given in filter tab of dataset properties window.
But one problem here is that you're bringing the entire data from db to report server in this case which might have performance impact as data in db grows. So its alawys better if you have filters to aply it in sp and bring only filtered data to report.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources