Solved

Crystal Reports not returning all data from stored procedure

Posted on 2004-08-22
8
417 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 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