Solved

Crystal Reports not returning all data from stored procedure

Posted on 2004-08-22
8
414 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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. …
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

18 Experts available now in Live!

Get 1:1 Help Now