Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: No Activity Report Post Reply Post New Topic
Author Message
rwskinner
Newbie
Newbie


Joined: 06 Dec 2007
Location: United States
Online Status: Offline
Posts: 2
Quote rwskinner Replybullet Topic: No Activity Report
     Posted: 06 Dec 2007 at 7:58am
Say I have a table that contains a list of all my part numbers,  then I have another table with the part number and activity details.
 
I want to show a report of ALL part numbers then list the activity below it, even if there are no details.
 
I linked masterlist.partnumber to activitylist.partnumber.
I created a group by masterlist.partnumber, then the details below.
 
My problem is, if there is not any activity for that part number for the date range I'm looking at, then the group for that part number does not show up.  Since I'm looking for items that do not have any activity, or low activity then I kind of need them in my report.
 
I have a summary of events below each group.  I will base my selection on those.  IE = Count is less than 5
 
Example of what I want.
 
Part Number
   Date of activity - Type of activity
   Date of activity - Type of activity
-------------------------------------------
  2
 
Part Number   <- I still want this part number from the masterlist
-------------------------------------------
  0
 
Part Number
   Date of activity - Type of activity
   Date of activity - Type of activity
   Date of activity - Type of activity
-------------------------------------------
  3
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 06 Dec 2007 at 10:18am
If the link type is an Inner Join, then it will only show parts that have activity. You can change the link type to be an outer join so that it shows all records from part number even if there are no matching activity records.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
rwskinner
Newbie
Newbie


Joined: 06 Dec 2007
Location: United States
Online Status: Offline
Posts: 2
Quote rwskinner Replybullet Posted: 06 Dec 2007 at 11:27am
Originally posted by BrianBischof

If the link type is an Inner Join, then it will only show parts that have activity. You can change the link type to be an outer join so that it shows all records from part number even if there are no matching activity records.
 
Thanks for the reply.  I tried Left Outer and Right Outer and neither returned all the records.
 
I have 1 link
 
MasterList.Part# ---> ActivityList.Part#
 
Grouped like this....
 
Group1 = MasterList.Part#
Details = Activity Records for Part#
Summary = Count of Records
 
 
 
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 07 Dec 2007 at 6:51am
Do you have any Select criteria on the report?  For instance, restricting your activity to a certain date range?  If so, that will still mean that parts with no activity aren't being returned, because a result of "no activity" is not in that date range.

To correct for this, you can do two things.  If you have knowledge of SQL, you can do a conditional join.  You would either do it using the Add Command function when selecting your data source in Crystal, or build the query in your database and use it for the source.

The other option is to modify your Select criteria to allow for null values.  You'll have to click on Show Formula and edit it there.  Look for where it references the activity table.  Put an open paren "(" in front of that expression.  After the expression, put "OR IsNull({ActivityList.MyField}) )"
Repeat for every field from the ActivityList referenced in the Select criteria.
IP IP Logged
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