Solved

Invalid SQL Data Reads In Crystal

Posted on 2014-09-10
7
362 Views
Last Modified: 2014-09-11
The report below queries the same SQL data that feeds our Sage CRM software. Record selection simply looks for any "Item" record that has a Cost higher than the sell-for Price, or a zero value for either the Cost or the Price. The actual formula is...

{AZItem.azit_Cost}>={AZItem.azit_Price} or isnull({AZItem.azit_Cost}) or isnull({AZItem.azit_Price}) or {AZItem.azit_Cost}=0
or {AZItem.azit_Price=0

The anomoly that I'm experiencing is, while Crystal indicates that Item #1104950012 has a zero Cost (red record in first image below), when I search for that Item in CRM, CRM indicates a Cost of $23.74 (and yes; I've confirmed that Crystal is looking at the correct field in the correct table).

Before querying the SQL data
In an attempt to figure out what was going on, I launched the SQL Server Management Studio and queried the table directly with...

select * from dbo.AZItem where azit_Number='1104950012'

The query returned one record which showed a cost of $23.74...agreeing with CRM and contradicting Crystal.

Now; here's the stumper...

When I re-ran the report, Item #1104950012 no longer appeared (image below). It is as if my looking at the record, via the SQL Server Management Studio, somehow "refreshed" (for lack of a better word) the data that Crystal was reading. And before you ask, "Save Data With Report" is, and always has been, unchecked.

After querying the SQL data
Can anyone here give me a clue as to what's going on here?
0
Comment
Question by:Bad_Fish
[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
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40315937
an update occurred between report runs?

Crystal is reading uncommitted records?
0
 

Author Comment

by:Bad_Fish
ID: 40315944
Thanks...but none of the above. I can replicate the above procedure with (from what I've seen) about 80% of the records. This was just one example to keep it simple.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 40316041
If the costs can really be NULL then you need to reorder the selection formula to put the IsNull tests first.

If Crystal tries to use a NULL vaue in any other formula than IsNull the execution terminates and the result is indeterminate.

isnull({AZItem.azit_Cost}) or isnull({AZItem.azit_Price}) or {AZItem.azit_Cost}>={AZItem.azit_Price} or {AZItem.azit_Cost}=0
or {AZItem.azit_Price=0


You may be getting the NULL on the record with the cost

mlmcc
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 35

Accepted Solution

by:
James0628 earned 300 total points
ID: 40316593
Just to add a little to what mlmcc said, if the records are not explicitly sorted, then the report may get them in a different order from one run to the next.  That could explain the changing results.

 James
0
 

Author Comment

by:Bad_Fish
ID: 40316929
mlmcc; For testing purposes, I modified the selection query to eliminate any chance that isnull is causing the problem. The record selection query now reads...

{AZItem.azit_Cost}=0 or {AZItem.azit_Price}=0 or {AZItem.azit_Cost}>={AZItem.azit_Price}

...and I'm still experiencing the problem. I believe the key bit of information here is the fact that my querying the record outside of Crystal forces Crystal to see the data correctly...as strange as that sounds.

Here are the steps in an example that summarizes what is happening...

1.

Crystal erroneously reports a zero value in a field that contains a positive number.

2.

A query of that record outside of Crystal confirms that the field contains a positive number.

3.

Crystal now reports the correct value stored in the field.
Any other ideas?
0
 

Author Closing Comment

by:Bad_Fish
ID: 40316994
After re-reading James0628's comment about sorting, I removed the group (based on a True or False formula) and the sort (ItemNumber) and Voilà...it's working correctly!

I'm going to dig a bit to find out why though; I don't understand how this could have had any affect on the values being read and reported.

Thanks to all!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40317096
WIth the groups and sorting the records depending on the report logic you may be evaluating the data in an incorrect order some of the time.

Without seeing the report and getting a good understanding of the data it would be difficult to determine what is really happening.

mlmcc
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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