Solved

cr2008 stored procedure as data source

Posted on 2011-03-22
5
335 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
Comment Utility
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
Comment Utility
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
Comment Utility
Does the query return the correct information in Query Analyzer?

mlmcc
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

13 Experts available now in Live!

Get 1:1 Help Now