Help with sub-reports in Crystal Reports XI

The link between the main report and the subreport establishes a filter for the subreport.  It shouldn't have anything to do with the grouping.

What are you trying to group by?

mlmcc

I think I have figured it out, the grouping seems to be working now.... finally.

Could I ask you to answer this for me?  The group by name is simply the index referring to a record in another table.  How can I get the name in the table to show instead of the index?

For example, in the Group Name#1 field, I have a single integer between 1 and 9 showing.  I'd like this to show the name that the integers are referring to from another table.  Instead of 3, show 'Arrhythmia'.  Normally, it would be easy to construct a SQL statement, but that doesn't work...
I tried to construct a formula in the 'display string' dialog when I right click on the Group Name #1 field --> Format Object --> Common tab --> Display String, but I'm having issues.

WHen you set the group you can select a different field for the name.
Right click the group header left margin
Click CHANGE GROUP
Click CUSTOMIZE GROUP OPTIONS
Click use a field
Select the field from the drop down

mlmcc

I think I need a formula of some kind.
When I run the report, the Group Name#1 field shows as a "5".  I need it to say "Excessive Bleeding", which is record #5 in another table in SQL Server.

(SELECT "OutcomeCode"."OutcomeCodeName" WHERE "OutcomeCode"."OutcomeCodeID" = '5')

Obviously the '5' needs to be the Group Name#1 field, so that the OutcomeCodeName changes for each separate group of outcomes.  But I'm not sure how to do this.

Thanks

Should this be done in the "Display String" formula field in the Format Editor?

Do you have the outcomecode table in the report?
You should be able to drag the name field onto the report

mlmcc

I do have the OutcomeCode table in the report.  I dragged it onto the report but nothing appears in the preview...  I double-checked the links, deleted them, re-created them and made them all LEFT OUTER JOINS.  This is what helped me the last time I was running into a similar problem.

I followed your lead from your post on Friday under  CUSTOMIZE GROUP OPTIONS.  But I chose 'Use a Formula as Group Name' instead of choosing OutcomeCodeName from the drop down list.
As a formula I simply dragged OutcomeCodeName into the box.  It's probably the same as picking that field from the ddl, but thought I'd try it.

One thing:  while browsing the fields in the join table between the OutcomeCode table and the PatientComplete View from SQL (right-clicking on a field --> browse field), neither of the PatientID or OutcomeCodeID foreign key fields are displaying any data.  

....which makes sense since the fields are in an association table used to break up a many-to-many relationship between the OutcomeCode and PatientComplete tables.

Do I actually need data in the association table?  (I hope not, what would be the point of links?)

mlmcc-- (I hope you haven't given up on me!)

Another thing: do I need to explicitly include the OutcomeCodeName field in the "Fields to link to" box in the Subreport Links dialog if I want to use them?

Haven't given up on you.  Tool advantage of the nice weather here to fix a leaky roof.

You can use the field to link if that is the field to link with. I doubt that it is.

mlmcc

I don't know why this is giving me so much trouble.  I should just be able to drag the OutcomeCodeName onto the report.  The links between the tables should be able to interpret the numbers that are showing up in the Group headers as the corresponding OutcomeCodeName...!  What gives?

I've included links to images of the table links and what the report looks like... hopefully it helps.

Image of the table links: http://img117.imageshack.us/img117/2894/linksfa0.jpg

Image of the sub-report preview: http://img110.imageshack.us/img110/5069/groupingsss4.jpg
(all fake testing data)

Thanks again-

hope you got your roof fixed!

Why is the link FROM the OutcomeCode table to the i_PatientOutcome.  It should go the other direction.

mlmcc

I changed the direction, then right-clicked on Group Name --> Format Object --> Display String (Formula) and dragged OutcomeCodeName field onto the editor.  Saved / closed it and previewed the report.     The groupings remain the same, but the group header is now invisible  --> neither the OutcomeCode nor the OutcomeCodeName are showing.

Have you tried to VERIFY THE DATABASE under the DATABASE menu?

mlmcc

Yes, no change.

I am stumped.

mlmcc