[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Crystal Reports not returning values correctly based on Database view

We are using Crystal Reports XI to connect to an oracle 9 database.

We are using a view which basically the following:

Create or replace view transcript_list (ID, completion_date) as
Select ID, completion_date from transcript

When we run this view against the database in TOAD  106000 records are returned.
When we run a crystal report using this view, 106000 records are returned.

If we then change the view in the database to be the following
Create or replace view transcript_list (ID, completion_date) as
Select ID, completion_date from transcript where transcript.completion_date >='01-jan-2007'

When we run this second view against the database in TOAD only 16000 records are returned.
If we run the crystal report again based on this second version of the view we still get 106000 records.

We've tried another where clause e.g  where ID ='x' value and the view is correctly reported in Crystal. So there appears to be something different about the date in the where clause that crystal is interpreting differently to TOAD.

Values stored in the completion_date field in the transcript table are formatted as date time and we've also tried the following where clause round(transcript.completion_date) >='01-jan-2007' but we still get 106000 records via crystal and 16000 via TOAD.

Any ideas on what is happening would be greatly appreciated!! We need this view to only return the 16000 records to the report and not the full 106000!
thanks
0
hcshosting
Asked:
hcshosting
  • 4
  • 2
  • 2
  • +1
1 Solution
 
peter57rCommented:
Have you used the 'verify database' option to make sure the report is picking up the latest version of the view?
(Database>VerifyDatabase)
0
 
hcshostingAuthor Commented:
HI,

yes Ive tried this and the it comes back database is up to date but doesnt have any impact of the records returned.
0
 
habibbCommented:
try ( if using visula studio )

Right click on report select Design---> Default settings.....> Reporting
now check opton "Discard saved data when loading reports"

try ( if using Crysatl report designer)

File--->Options---> Reporting

now check opton "Discard saved data on Open"

Hope it will work
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
hcshostingAuthor Commented:
Thanks for the suggestion.
We are using crystal report designer so i tried the second option but its still bringing back all the records.
 
0
 
habibbCommented:
have you also tried  by unchecking "Saved Data with report" option?
0
 
hcshostingAuthor Commented:
yep, i think its more to do with the where clause having a date. If i change the where clause to be based on the ID field instead the records are restricted in crystal.but just cant work out what?
0
 
frodomanCommented:
Try changing your Crystal formula to:  ... to_date('01-jan-2007','dd-mon-yyyy')

I believe Toad may translate the text value to a date type for you, but the db driver Crystal is using isn't doing that.

If that doesn't work, let me know if there's any possibility of null values in this date field - if so you have to explicitly check for them or Crystal doesn't give accurate results.

frodoman
0
 
hcshostingAuthor Commented:
Thank you! that worked.. Im now only getting the 16000 records via crystal.
There is also a possibility of null values in this date field so would appreciate your advice on how to explicitly check for these as well?
0
 
frodomanCommented:
Sure try this:

...where transcript.completion_date is not null AND transcript.completion_date >='01-jan-2007'

Unless you want to include the null values in which case:

...where transcript.completion_date is null OR transcript.completion_date >='01-jan-2007'
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now