Solved

Crystal Reports not returning all data from stored procedure

Posted on 2004-08-22
8
416 Views
Last Modified: 2006-11-17
I am fairly new to Crystal Reports. I have a stored procedure on a server running sql server. If I run the procedure it gives me a summary total of a money field. If I create a Crystal report and point it to the same procedure on the server, I lose records and get a smaller total. Am I missing a setting that restricts records??
0
Comment
Question by:Redrocker663
  • 4
  • 2
  • 2
8 Comments
 
LVL 3

Expert Comment

by:DarrenZai
ID: 11867440
You can filter the records that are returned from a stored procedure into the report, but you can't filter the records used by the stored procedure except through the use of parameters passed across to the procedure, unless there is some sort of rowlevel security in place in the database.

Does the stored procedure take any parameters?
what sort of database are you using?
What version of CR are you using?


Darren
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11869034
If the procedure returns a set of records then go into report -> select expert and make sure you don't have anything restricting what's coming into Crystal.  If the SP just returns the single summary value then it must be one of the options Darren listed.

frodoman

0
 

Author Comment

by:Redrocker663
ID: 11876287
The stored procedure takes 3 parameters. A 'Bill To', 'Start Date' and 'End Date'. We are using SQL Server for the database and using Crystal reports 9. For testing, we used only 1 bill to, so we only return one value for a specific month. When the stored procedure runs on the database, it returns a value. When we run the same stroed procedure from the same database, the reports total is not the same as the total when it runs on the query analyzer. It seems some records are not being included in the total.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 42

Accepted Solution

by:
frodoman earned 100 total points
ID: 11876513
Crystal simply calls the stored procedure with the parameters specified - it can't eliminate records from being returned so if all you're returning is a total, and the total is different than when you execute the SP from enterprise mgr, then there are only two possible explanations:

1) The sp is being executed on a different set of data - either you're calling a different instance than the one tested or the data has changed since the test, etc.
2) The paramters being used are different.

I assume you'd know if it was #1 so I suggest that you might want to start by making certain that the parameters are being passed in correctly.  One simple way to do this is to modify your stored procedure so the first thing it does is insert to a table the 3 parameters values and then the SP continues to execute normally.  That way you can look at the values and make sure there wasn't a problem with datatype conversion, etc.

frodoman
0
 
LVL 3

Assisted Solution

by:DarrenZai
DarrenZai earned 25 total points
ID: 11876759
Are these parameters specified as actual date fields or string fields with date representations? There may be some problem with date formats? I've occasionally had problems passing the date across because in Australia  we use dd/mm/yy   and often it expects mm/dd/yy. For a large chunk of the year the values you pass still make valid dates either way just not what you expect.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11879488
Darren is absolutely correct and that's exactly why I made the recommendation that I did to validate the parameters inside the db.  In fact, it's often a good idea to send the dates as varchar and have your SP do the conversion to insure it is converted correctly.

frodoman
0
 

Author Comment

by:Redrocker663
ID: 11889403
Many thanks for the suggestions. I created a table to store the parameters when the stored procedure executes and I realized that the time was being passed as well. I changed the parameters to varchars and the time vanished. Things should work great. Thanks again for taking the time to help.

Dean
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11891022
Glad to help - frodoman
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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 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