Solved

Crystal Reports not returning all data from stored procedure

Posted on 2004-08-22
8
423 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
Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of 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. …
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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