Solved

cr2008 stored procedure as data source

Posted on 2011-03-22
5
336 Views
Last Modified: 2012-05-11
I am trying to us a stored procedure as a datasource for a report. The report will prompt for the required parameters necessary for the SP but the report will not populate. Most likely something simple but I am unable to see it.
0
Comment
Question by:PenningtonCounty
5 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35194001
1)  Are you using Oracle?  If so, you need to modify the proc to be compatible: http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/1084c536-711e-2b10-e48a-924a60745253

2)  Crystal Reports requires the Stored Proc to return a single recordset via SELECT statement.  So, no matter what else your proc is doing, it needs to end with SELECT  something FROM something WHERE something...

If you post your proc we might be able to troubleshoot specific issues.

~Kurt
0
 

Author Comment

by:PenningtonCounty
ID: 35194038
Rhinok

I am using SQL SERVER 2008. I have attached the code for my SP and also a sample of the returned dataset.
 VoucherListing.xlsx
ALTER PROCEDURE [dbo].[voucherInfoByVendor_Date]

(
@vendorID int,
@voDate datetime,
@deptID int,
@cDate datetime
)

AS
BEGIN


SET NOCOUNT ON;

----********** FOR DEBUGGING PURPOSES ONLY ****************
----*******************************************************

----declare @vendorID int
----declare @voDate datetime
----declare @deptID int
----declare @cDate datetime

----set @vendorID =  1238
----set @voDate = '3/7/2011'
----set @cDate = '3/7/2011'
----set @deptID = 18

----*******************************************************
----*******************************************************
	
SELECT DISTINCT vr.vendorNum, vr.vendorName, vr.vendorname2, CASE WHEN vr.vendorAddress2 = '' THEN vr.vendorAddress1 ELSE vr.vendorAddress1 + CHAR(13) + CHAR(10) + vr.vendorAddress2 END as vendorAddress1,
		   vr.vendorCity + ', ' + vr.vendorState + ' ' + vr.vendorZip as vendorAddress3,
		   vr.vendorTaxInfo, f.fullFundNumber as fundNumber, vo.invoiceNumber
		   --,vf.amount as voucherAmount
		   ,(SELECT SUM(amount) FROM voucher_funding vf2 WHERE vf2.voucherID = vo.voucherID AND vf2.fundID = f.fundID) AS voucherAmount
		   ,vo.voucherDate, f.fundName, d.county, @cDate AS commishDate
		   ,vo.voucherID
-- Removed some lines because the voucher was printing all descriptions concatenated 
-- for each voucher, check RC Regional Hospital 6/15/09 for example if added back
		   ,STUFF((SELECT DISTINCT ', ' + v.description
		   FROM voucher v
		   --INNER JOIN voucher_funding vf2 on vf2.voucherID = v.voucherID
		   WHERE vr.vendorID = v.vendorID AND v.deptID = @deptID AND vo.invoiceNumber = v.invoiceNumber AND (v.voucherDate >= @voDate AND v.voucherDate <= @voDate)
		   --WHERE vr.vendorID = v.vendorID AND vf.fundID = vf2.fundID  AND (v.voucherDate >= @voDate AND v.voucherDate <= @voDate)
		   FOR XML PATH('')),1,1,'')AS description
		   --, vo.description
FROM vendor vr
INNER JOIN voucher vo on vo.vendorID = vr.vendorID
INNER JOIN voucher_funding vf on vf.voucherID = vo.voucherID
INNER JOIN fund f on f.fundID = vf.fundID
INNER JOIN department d ON d.deptID = f.deptID
WHERE vr.vendorID = @vendorID AND CONVERT(varchar, vo.voucherDate,101) = @voDate
	  AND f.deptID = @deptID AND vo.multiVoucherID IS NULL AND vo.witnessVoucher=0
ORDER BY f.fullFundNumber, vo.invoiceNumber

END

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35194255
Does the query return the correct information in Query Analyzer?

mlmcc
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 35194587
Try this:

ALTER PROCEDURE [dbo].[voucherInfoByVendor_Date] AS

DECLARE

@vendorID int,
@voDate datetime,
@deptID int,
@cDate datetime

SET NOCOUNT ON;

BEGIN


----********** FOR DEBUGGING PURPOSES ONLY ****************
----*******************************************************

----declare @vendorID int
----declare @voDate datetime
----declare @deptID int
----declare @cDate datetime

----set @vendorID =  1238
----set @voDate = '3/7/2011'
----set @cDate = '3/7/2011'
----set @deptID = 18

----*******************************************************
----*******************************************************
	
SELECT DISTINCT vr.vendorNum, vr.vendorName, vr.vendorname2, CASE WHEN vr.vendorAddress2 = '' THEN vr.vendorAddress1 ELSE vr.vendorAddress1 + CHAR(13) + CHAR(10) + vr.vendorAddress2 END as vendorAddress1,
		   vr.vendorCity + ', ' + vr.vendorState + ' ' + vr.vendorZip as vendorAddress3,
		   vr.vendorTaxInfo, f.fullFundNumber as fundNumber, vo.invoiceNumber
		   --,vf.amount as voucherAmount
		   ,(SELECT SUM(amount) FROM voucher_funding vf2 WHERE vf2.voucherID = vo.voucherID AND vf2.fundID = f.fundID) AS voucherAmount
		   ,vo.voucherDate, f.fundName, d.county, @cDate AS commishDate
		   ,vo.voucherID
-- Removed some lines because the voucher was printing all descriptions concatenated 
-- for each voucher, check RC Regional Hospital 6/15/09 for example if added back
		   ,STUFF((SELECT DISTINCT ', ' + v.description
		   FROM voucher v
		   --INNER JOIN voucher_funding vf2 on vf2.voucherID = v.voucherID
		   WHERE vr.vendorID = v.vendorID AND v.deptID = @deptID AND vo.invoiceNumber = v.invoiceNumber AND (v.voucherDate >= @voDate AND v.voucherDate <= @voDate)
		   --WHERE vr.vendorID = v.vendorID AND vf.fundID = vf2.fundID  AND (v.voucherDate >= @voDate AND v.voucherDate <= @voDate)
		   FOR XML PATH('')),1,1,'')AS description
		   --, vo.description
FROM vendor vr
INNER JOIN voucher vo on vo.vendorID = vr.vendorID
INNER JOIN voucher_funding vf on vf.voucherID = vo.voucherID
INNER JOIN fund f on f.fundID = vf.fundID
INNER JOIN department d ON d.deptID = f.deptID
WHERE vr.vendorID = @vendorID AND CONVERT(varchar, vo.voucherDate,101) = @voDate
	  AND f.deptID = @deptID AND vo.multiVoucherID IS NULL AND vo.witnessVoucher=0
ORDER BY f.fullFundNumber, vo.invoiceNumber

END

Open in new window


~Kurt
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 35204563
I'm assuming that the stored procedure works from a query window, since you posted a file with "a sample of the returned dataset".  If so, the basic structure is presumably OK.  But, FWIW, there are a couple of differences in how my stored procedures are structured.  I don't have () around the parameter declarations, and I don't have BEGIN-END around the procedure code.  But if your procedure works from a query window, I would expect it to work from a report too.

 I do see something else that could be a problem.

 In the WHERE near the end (line 53), you have:

 CONVERT(varchar, vo.voucherDate,101) = @voDate

 That's going to convert voucherDate to a string in the form mm/dd/yy.  The question is, what are you entering for the @voDate parameter in the report?  It's a datetime parameter, so CR will include a time, and the default will be the current time.  If you're not setting the time to 12:00 AM in that parameter, those values won't match (unless MS SQL decides to convert @voDate to a string using the same mm/dd/yy format).

 If you just want to compare the dates and ignore the times, you could use:

WHERE vr.vendorID = @vendorID AND
CONVERT(varchar (8), vo.voucherDate, 101) = CONVERT(varchar (8), @voDate, 101)


 Also, the WHERE in one of your sub-queries (on line 44) has:

(v.voucherDate >= @voDate AND v.voucherDate <= @voDate)

 That doesn't really make sense.  Obviously, v.voucherDate can't be both > and < @voDate, so it's the same as just having v.voucherDate = @voDate.

 And there's also the same issue concerning the time.  If the time that you enter in @voDate is not the same as the time in v.voucherDate, that sub-query won't produce any values.  If you want to ignore the time (which seems likely), you could change that WHERE to:

               WHERE vr.vendorID = v.vendorID AND v.deptID = @deptID AND
               vo.invoiceNumber = v.invoiceNumber AND
               CONVERT(varchar (8), v.voucherDate, 101) = CONVERT(varchar (8), @voDate, 101)


 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Report pass parameters with script 10 64
C# Crystal Reports Formula 6 79
Crystal Reports Groups need Hand Cursor on Hover 4 68
Business Object Server 2 27
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now