Writing Code
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Writing Code
Message Icon Topic: Formulas/Summaries Post Reply Post New Topic
Author Message
StrayKatt
Newbie
Newbie
Avatar

Joined: 08 Jan 2011
Location: United States
Online Status: Offline
Posts: 3
Quote StrayKatt Replybullet Topic: Formulas/Summaries
    Posted: 09 Jan 2011 at 6:09am

Sorry I posted this in the wrong forum earlier. I am using Crystal Reports .NET 2008. I am a relative newbie and there are no Crystal Reports experts in my office, so I am flying solo. I was wondering if someone could give me some direction. I have 3 tables:
Table 1: all IDs
Table 2: some IDs from Table 1
Table 3: some IDs from Table 1
The tables are joined on ID. I need a distinct count of all the IDs that appear in Table 2 plus any other IDs from Table 3 that are not in Table 2. I tried to create a string variable in a formula that concatenated all of the IDs from Table 2. Then I did a distinct count of IDs in Table 2, set a variable equal to the count, and increased the count variable by 1 for any IDs in Table 3 that were not in the string. I put the formula in the Details section, suppressed the Details section, and printed the count variable in the group footer. This seems to work. The only problem is that some of the other summary fields in my report get messed up when I put the formula in the Details section. Can someone suggest another way that this could be done that won't mess up my other summaries? I might be making this too complicated. Thanks.
StrayKatt
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 14 Jan 2011 at 9:21am
You'll actually need to do this in either two reports, or a report with a sub report.
 
All ID's in Table 2
 
Just include Table 2.  In the report header or report footer, create a summary that is the distinct count of the ID field.
 
All ID's in Table 3 that aren't in Table 2
 
- Add Tables 2 and 3.
- Link from Table 3 to Table 2.
- Right-click on the link, and select "link properties".  Change the link to a "Left Outer Join".
- Set a Record Selection filter that looks like this (you'll have to edit the selection formula to do this):
 
IsNull({Table2.ID_Field})
 
- Create a summary to do a distinct count of the id field in Table 3.
 
-Dell
 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.