Bad_Fish
asked on
Invalid SQL Data Reads In Crystal
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}>={AZIte m.azit_Pri ce} 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).
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.
Can anyone here give me a clue as to what's going on here?
{AZItem.azit_Cost}>={AZIte
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).
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.
Can anyone here give me a clue as to what's going on here?
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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}>={AZIte m.azit_Pri ce}
...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...
Any other ideas?
{AZItem.azit_Cost}=0 or {AZItem.azit_Price}=0 or {AZItem.azit_Cost}>={AZIte
...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?
ASKER
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!
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!
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
Without seeing the report and getting a good understanding of the data it would be difficult to determine what is really happening.
mlmcc
Crystal is reading uncommitted records?