Enforce Join required

[-cush-]

I have been creating Crystal Reports for probably 14 years. I've written them against different versions of Access and SQL Server. Only now did I run into something that I have never seen before. There was extra data showing up in my report that made no sense to me. I would query the database and not come up with the same dataset that crystal reports was. The solution to the problem was to modify the links and to Enforce Join (Both). For some reason after enforcing the join I got the dataset I was looking for. Then I found that Enforcing the Join is not the default, so know I wonder why I haven't run into this before, and how many other reports I have out there that are returning incorrect data. I am using Crystal XI on SQL Server 2008. Maybe this combination is the problem? Let me explain further.

The report in question links the Case to a lookup table for the cases status and also to a lookup table for the user assigned to the case. The report groups by User and then by Case Status. Notice both groups are in the linked tables. The problem is that when I open one of the cases of a particular case status (grouped) I find that the cases status is something entirely different. But after enforcing the join, everthing seemed to be grouped correctly.

I always thought enforcing the join would pull the tables data even if not being used, I did not think that failing to enforce the data would bring back incorrect data. Can anyone explain this?

 

[lbass]

One way of limiting data is by using inner joins which require a match in both tables. If you only reference fields from one of the tables, the link is essentially not activated--unless you have enforced the joins. I can't recall how this worked in earlier versions. Typically you would only add a table if you needed to reference a field in the report, so enforcing wouldn't then be a factor.

It's hard to determine why you would get incorrect data without understanding more clearly the tables used, how they are joined, and the kinds of values that were returned under each condition.

-LB

 

[-cush-]

In my case, I was using inner joins, but not until enforcing the link was the data in the related table correct.

 

[lbass]

Yes, that's what I understood you to be saying.

-LB

 

[-cush-]

I am guessing that performing grouping on the server would have also corrected the problem, and that can be set by default in settings.

 

[lbass]

I don't see why that would have any impact. I just tested this and saw no impact of this.

Are your groups based on fields in the Case table? Do you have fields from all three tables used somewhere? Have you hidden the details (you mention "opening" a case, and I'm unsure what you mean by this). Are your tables joined with equal joins?

-LB

 

[-cush-]

Both of the fields that were bringing back false data were also groups. They are not in the primary table, but in the related tables. I just tested the report in question. I took off the force join and had it group on the server and the cases statuses are correct. The groups were the User Name and the case status.

Case
======
User_ID
Case_Status_ID
Case Number
.....

Users
=======
User_ID
User_Name
.....

Case_Status
===========
Case_Status_ID
Status_Code
Case_Status
.....

 

[lbass]

Were there any fields from Case on the report? Were you using equal joins from Case to Users and from case to case status? I'd like to mock this up to see for myself.

Again, what do you mean by opening a case? Have you hidden the details? How do you know the returned data is incorrect? Can you show examples of what is returned?

-LB

 

[-cush-]

There were fields from Case (creation date and case number). They were the only fields on the report. The groups were user name and status code, both from the related tables. After the records were not showing up in the correct groups, I added the case status field to verify that it showed the same as the group. It did. I don't remember if I did a refresh or not after adding the case status field. I then opened the case in the software program by searching for the case number. The case status in the software was not the same as the report. I then queried the data in SQL Server Management Studio and verified that it was the program that was correct and not the report. I checked the links in the report. They were both inner, equal type joins, but the join was not enforced. I switched to "enforced both" and refreshed the data and then the report showed the records in correct groups.

All that being said, after the data is pulling correctly, I have a hard time getting it to pull incorrectly again, even after taking the enforcement off and not grouping on the server. If someone else had reported this to me it would lead me to believe that they were looking at saved report data, but I am sure that I refreshed it. Yet at this moment, I am having a hard time recreating the scenario. But this has happened to me twice now on two different reports on two different databases where the solution to getting correct data was to enforce the links. Either way, my defaults are now not to save data with reports and to group on the server.

 

[lbass]

Okay. I can't recreate this either, but I'll take your word for it. It might be worth checking with SAP to see if there have been other reports of this kind of behavior.

-LB