I am using Crystal 8.5 (all I have access to) and am having trouble trying to create the groups I need in my report.  I am querying transaction data from two tables and then also creating some "on demand" sub-reports.

 

Two fields from one table that I am using are transaction date and transaction number.  Transaction number is only unique within a given date, so my first group is on transaction date and my second group is on transaction number.

 

In general, my report returns two rows for each transaction.  Row 1 contains one or more ids in a string that are used to create/link to the sub reports.  Row 2 contains a text string that may occur within multiple transactions.

 

My primary goal is to group on the text string from row 2 and show the Top N (probably top 25).  For each transaction, I need to keep rows 1 and 2 together.  If I only group on transaction date and transaction number, rows 1 and 2 stay together.  As soon as I add a group on the text string in row 2, I lose my row one data. 

 

Is there any way to define row 1 and row 2 as a set and then group the sets by the text string in row 2?

 

Thanks!

 

I'm a bit confused by your layout above.  I guess I should have added more detail.  I extract the text string from Row2 into a variable (call it var1) and this is what I actually want to use for my primary group and count/top n.  My other groups are are on tran date and tran num.

 

MESSAGE table

MESSAGE_HIST table

 

The two tables above are joined on tran_date and tran_num.  As far as the Details section goes, I am displaying the following fields from the corresponding tables:

 

MESSAGE.tran_date

MESSAGE.tran_num

MESSAGE.source

MESSAGE_HIST.que_line_id

MESSAGE_HIST.details

 

I've created 7 formula fields:

1.  OFAC_IDs - this field has a formula that extracts all of the OFAC IDs from certain MESSAGE_HIST.details records and then concatenates them together with a comma separating each.  There can be no more than 5 OFAC IDs for a given transaction.

 

2.  Stop_Entity - this field is the text string that is extracted from certain MESSAGE_HIST.details records.

 

3.  The remaining formula fields are labeld ID1, ID2, ID3, ID4 and ID5.  These fields are not displayed in the Details section, but are used to create subreports.  If an ID field is not null, a subreport is created.

 

Here is my select statement:

 

(({MESSAGE_HIST.QUE_LINE_ID} in ["STOP_ADM_LOG", "STOP_PAY_LOG"]) or
({MESSAGE_HIST.QUE_LINE_ID} = "*SYS_MEMO" and
(InStr ({MESSAGE_HIST.DETAILS}, "FLD")>0 or InStr ({MESSAGE_HIST.DETAILS}, "MATCH REF:")>0))) and
{MESSAGE.TRN_DATE} in DateTime (2008, 02, 11, 00, 00, 00) to DateTime (2008, 02, 16, 00, 00, 00) and
{MESSAGE.STOP_INTERCEPT} in ["O", "S"]

 

For each transaction, there may be zero or more MESSAGE_HIST.DETAILS records with the "MATCH REF:" string or the "FLD" string, but normally there are only two (one of each).  The record containing the "MATCH REF:" string is the one that provids the OFAC IDs, and the other record provides the text string that I extract for the Stop_Entity formula field.

 

I want to group on the Stop_Entity field so that I can see which text strings reoccur the most.  I then need the ability to drill down to see the subreport.

 

I think my issue is that the data needed to group on Stop_Entity comes from the record with "FLD" and the data needed to create the subreport (that accesses data from another table that can not be linked to the two tables above) comes from the record with the "MATCH REF:".

 

I hope I have not added more confusion with all of this detail.  I am a novice at Crystal and don't know what else to do.

 

Thanks.

You can use formating to hide the rows as you see fit (and based on a logic).

I try to "see" the entire report with all rows shown. Once you start to group leave the details as being shown. From there see how you can hide or show each row as needed.