Solved

cr2008 stored procedure as data source

Posted on 2011-03-22
5
338 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

831 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