report doesnt run with no selection

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

Development Tools

Reporting Services Development

report doesnt run with no selection

Author

Topic

jhermiz

3564 Posts

Posted - 2004-11-10 : 10:20:41

I have a report with two parameters: ClientID, and CustomerID.
When you select a ClientID it lists all the CustomerIDs associated with that ClientID. Fine and dandy, when I select both the report runs looks great...however many times a user does not want to select a customer...so they select a client and leave the customer blank.
However, the "View Report" button when I click it doesnt display the report. Its almost like RS requires me to select BOTH a customer and a client. Even in my stored procedure I have the following:


CREATE PROCEDURE rsp_issue_actions @ClientID bigint, @CustomerID bigint=NULL
AS
BEGIN
SET NOCOUNT ON
SELECT        dbo.Issue.IssueID, dbo.Issue.ClientID, dbo.Issue.CustomerID, dbo.Issue.SiteID, dbo.Issue.CommissionID, dbo.Issue.IssueTypeID, 
                      dbo.Issue.Originator, dbo.Issue.OpenDate, dbo.Issue.TargetDate, dbo.Issue.ClosedDate, dbo.Issue.Issue, dbo.ActionItem.ActionItemNumber, 
                      dbo.ActionItem.ActionItem, dbo.ActionItem.OpenDate AS AOpenDate, dbo.ActionItem.TargetDate AS ATargetDate, 
                      dbo.ActionItem.ClosedDate AS AClosedDate, dbo.ActionItem.ResponsiblePerson, dbo.ActionItem.OptionalResponsiblePerson, 
                      dbo.FunctionalGroup.FunctionalGroup, dbo.Client.Client, dbo.Customer.Customer, dbo.Site.Site, dbo.IssueWBSElements.WBS, 
                      dbo.Commission.Commission, dbo.IssueType.IssueType, dbo.CommissionWBSElements.WBSDescription
FROM            dbo.ActionItemType RIGHT OUTER JOIN
                      dbo.FunctionalGroup RIGHT OUTER JOIN
                      dbo.ActionItem ON dbo.FunctionalGroup.FunctionalGroupID = dbo.ActionItem.FunctionalGroupID ON 
                      dbo.ActionItemType.ActionItemTypeID = dbo.ActionItem.ActionItemTypeID RIGHT OUTER JOIN
                      dbo.Client RIGHT OUTER JOIN
                      dbo.Issue LEFT OUTER JOIN
                      dbo.Site ON dbo.Issue.SiteID = dbo.Site.SiteID LEFT OUTER JOIN
                      dbo.Customer ON dbo.Issue.CustomerID = dbo.Customer.CustomerID ON dbo.Client.ClientID = dbo.Issue.ClientID LEFT OUTER JOIN
                      dbo.IssueWBSElements LEFT OUTER JOIN
                      dbo.CommissionWBSElements ON dbo.IssueWBSElements.WBS = dbo.CommissionWBSElements.WBS RIGHT OUTER JOIN
                      dbo.Commission ON dbo.CommissionWBSElements.CommissionID = dbo.Commission.CommissionID ON 
                      dbo.Issue.IssueID = dbo.IssueWBSElements.IssueID AND dbo.Issue.CommissionID = dbo.Commission.CommissionID ON 
                      dbo.ActionItem.IssueID = dbo.Issue.IssueID LEFT OUTER JOIN
                      dbo.IssueType ON dbo.Issue.IssueTypeID = dbo.IssueType.IssueTypeID
WHERE     	(dbo.ActionItem.Accepted = 1) AND (dbo.Issue.ClientID=@ClientID) AND (@CustomerID IS NULL OR dbo.Issue.CustomerID=@CustomerID)
ORDER BY dbo.Issue.IssueID, dbo.IssueWBSElements.WBS, dbo.ActionItem.ActionItemNumber
Set NOCOUNT OFF
END
GO

I need to be able to run this report if a customer is selected, and run the report if a customer is not selected...its almost like RS requires both ?

jhermiz

3564 Posts

Posted - 2004-11-10 : 10:29:44

By the way I have the option "Allow Null Values" selected in the parameter as well...still not working...

jhermiz

3564 Posts

Posted - 2004-11-10 : 10:38:42

If I goto the data tab and run the sproc in vs.net / rs it comes up with the parameters screen:
ClientID
CustomerID

If I type in 1 for clientID and leave <NULL> for customerID it returns the records. So how come in the preview I get nothing ?

jhermiz

3564 Posts

Posted - 2004-11-10 : 13:06:31

Hey I got it!!!! :-)

This stuff is absolutely great:

Changed my second data set:

CREATE PROCEDURE rsp_client_customers @ClientID integer AS
SET NOCOUNT ON
SELECT DISTINCT Customer.CustomerID, Customer.Customer
FROM Customer
INNER JOIN
ClientCustomerSites ON Customer.CustomerID = ClientCustomerSites.CustomerID
WHERE (ClientCustomerSites.ClientID = @ClientID)
UNION SELECT Null, 'All'
SET NOCOUNT OFF
GO

All I needed a UNION since it required a value :-)
hooooooooooray:-)

jhermiz

3564 Posts

Posted - 2004-11-10 : 14:45:13

Dope Dope Dope....

I thought I had it working but apparently something is wrong...
I have like 6 combo boxes including: Client, Customer, Site, Commission, Issue Type, and Originator.

I noticed if I run the procedure I posted in the first thread post with this:

EXEC rsp_issue_actions 1,null,null,null,null,"JHERMIZ"

Then it works 100% fantastic in Query Analyzer...

But If I make the selection "JHERMIZ" in reporting services...it still displays all other records as well...shouldnt it filter using that stored procedure that I posted ??? Im confused...its a report parameter...

jhermiz

3564 Posts

Posted - 2004-11-10 : 14:45:23

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-10 : 14:49:57

It'll filter if you've got a parameter in RS that matches with a parameter in your stored procedure. Take a look at the parameters in the data set, as well as the parameters in the Report Parameters section.

Tara

jhermiz

3564 Posts

Posted - 2004-11-10 : 15:03:08

quote:
Originally posted by tduggan

It'll filter if you've got a parameter in RS that matches with a parameter in your stored procedure. Take a look at the parameters in the data set, as well as the parameters in the Report Parameters section.

Tara

Hi Tara, The thing is...all of the parameters are their own datasets which are stored procedures. The datasets are with their respective sprocs are:

DataSet: dsIssueActions
Stored Procedure: rsp_issue_actions (this is the original sproc I posted:


CREATE PROCEDURE rsp_issue_actions  @ClientID integer, 
					  @CustomerID bigint=NULL,
					  @SiteID bigint=NULL,
					  @CommissionID bigint=NULL,
					  @IssueTypeID bigint=NULL,
					  @Originator varchar(50)=NULL
AS
BEGIN
SET NOCOUNT ON
SELECT        dbo.Issue.IssueID, dbo.Issue.ClientID, dbo.Issue.CustomerID, dbo.Issue.SiteID, dbo.Issue.CommissionID, dbo.Issue.IssueTypeID, 
                      dbo.Issue.Originator, dbo.Issue.OpenDate, dbo.Issue.TargetDate, dbo.Issue.ClosedDate, dbo.Issue.Issue, dbo.ActionItem.ActionItemNumber, 
                      dbo.ActionItem.ActionItem, dbo.ActionItem.OpenDate AS AOpenDate, dbo.ActionItem.TargetDate AS ATargetDate, 
                      dbo.ActionItem.ClosedDate AS AClosedDate, dbo.ActionItem.ResponsiblePerson, dbo.ActionItem.OptionalResponsiblePerson, 
                      dbo.FunctionalGroup.FunctionalGroup, dbo.Client.Client, dbo.Customer.Customer, dbo.Site.Site, dbo.IssueWBSElements.WBS, 
                      dbo.Commission.Commission, dbo.IssueType.IssueType, dbo.CommissionWBSElements.WBSDescription
FROM            dbo.ActionItemType RIGHT OUTER JOIN
                      dbo.FunctionalGroup RIGHT OUTER JOIN
                      dbo.ActionItem ON dbo.FunctionalGroup.FunctionalGroupID = dbo.ActionItem.FunctionalGroupID ON 
                      dbo.ActionItemType.ActionItemTypeID = dbo.ActionItem.ActionItemTypeID RIGHT OUTER JOIN
                      dbo.Client RIGHT OUTER JOIN
                      dbo.Issue LEFT OUTER JOIN
                      dbo.Site ON dbo.Issue.SiteID = dbo.Site.SiteID LEFT OUTER JOIN
                      dbo.Customer ON dbo.Issue.CustomerID = dbo.Customer.CustomerID ON dbo.Client.ClientID = dbo.Issue.ClientID LEFT OUTER JOIN
                      dbo.IssueWBSElements LEFT OUTER JOIN
                      dbo.CommissionWBSElements ON dbo.IssueWBSElements.WBS = dbo.CommissionWBSElements.WBS RIGHT OUTER JOIN
                      dbo.Commission ON dbo.CommissionWBSElements.CommissionID = dbo.Commission.CommissionID ON 
                      dbo.Issue.IssueID = dbo.IssueWBSElements.IssueID AND dbo.Issue.CommissionID = dbo.Commission.CommissionID ON 
                      dbo.ActionItem.IssueID = dbo.Issue.IssueID LEFT OUTER JOIN
                      dbo.IssueType ON dbo.Issue.IssueTypeID = dbo.IssueType.IssueTypeID
WHERE     	(dbo.ActionItem.Accepted = 1) 
AND (dbo.Issue.ClientID=@ClientID) 
AND (@CustomerID IS NULL OR dbo.Issue.CustomerID=@CustomerID)
AND (@SiteID IS NULL OR dbo.Issue.SiteID=@SiteID)
AND (@CommissionID IS NULL OR dbo.Issue.CommissionID=@CommissionID)
AND (@IssueTypeID IS NULL OR dbo.Issue.IssueTypeID=@IssueTypeID)
AND (@Originator IS NULL OR dbo.Issue.Originator=@Originator)
ORDER BY dbo.Issue.IssueID, dbo.IssueWBSElements.WBS, dbo.ActionItem.ActionItemNumber
Set NOCOUNT OFF
END
GO

Dataset:dsClients
Stored procedure: rsp_clients


CREATE PROCEDURE rsp_clients AS
SET NOCOUNT ON
SELECT     ClientID, Client
FROM         dbo.Client
SET NOCOUNT OFF
GO

Dataset:dsClientCustomers
Stored Procedure: rsp_client_customers


CREATE PROCEDURE rsp_client_customers @ClientID integer  AS
SET NOCOUNT ON
SELECT DISTINCT Customer.CustomerID, Customer.Customer
FROM         Customer 
INNER JOIN
                      ClientCustomerSites ON Customer.CustomerID = ClientCustomerSites.CustomerID
WHERE     (ClientCustomerSites.ClientID = @ClientID)
UNION SELECT Null, 'All'
 ORDER BY Customer.Customer
SET NOCOUNT OFF
GO

Sites, Commissions, etc are VERY similiar so no need to post.

And my report parameters seem to be ok check this:
http://www.jakrauseinc.com/jhermiz/reportparameters.JPG

Im quite certain I have it setup right, and like I said in QA it runs fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-10 : 15:19:46

I would run SQL Profiler to determine exactly what is being passed to your stored procedures from Reporting Services.

Tara

jhermiz

3564 Posts

Posted - 2004-11-10 : 16:40:36

Hmm through profiler I see this:
exec rsp_issue_actions @ClientID = N'1', @CustomerID = N'1'

But Ive selected a client, customer and an originator for some reason its not passing the originator :(

jhermiz

3564 Posts

Posted - 2004-11-10 : 16:58:59

Tara,

Just answer one question..............

...........................
............................
........................
What..............
Would.............
I.................
Do................
Without...........
YOU!!!............

Hah..the profiler showed what wasnt being passed...then the BEAUTIFUL thing about rdl is the XML format so I just modified that :)

Got it!!!! Thanks to you good ol you...

Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources