?
Solved

Crystal Reports not returning all data from stored procedure

Posted on 2004-08-22
8
Medium Priority
?
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 
LVL 42

Accepted Solution

by:
frodoman earned 400 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 100 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

719 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