I am using a Crystal report which uses 3 tables (Team, Members and TM). Fields are as mentioned below
TEAM TM MEMBERS
----- ------- -------
TeamID TeamID MemberID
TeamName MemberID MemberName
TEAM and MEMBERS are joined with TM on fields TeamID and MemberID respectively. There is one to many relationship between TM and MEMBERS. I have created a parameter TEAM.TeamName.
1. I want to display corresponding MEMBERS.MemberName on the report header. TM table contains around 50,00,000 records. I tried doing the same using a while..do but encountered an error: 'While Do Loop evaluated more than maximum number'. Is there any alternate way to do the same?
2. I want to pass MEMBERS.MemberID to another subreport (This subreport uses additional tables which are rlinked with MEMBERS). Any idea how to do this?
Try this:
1. In the main report, join from TEAM to TM. If all of the member data is in the subreport, you don't need MEMBERS in the main report. However, if you're going to sort by member name, you'll need to join from TM to MEMBERS. It's important that the joins happen in this order.
2. In the Select Expert, your formula should look something like:
TEAM.TEAMNAME = {?Team Name} <--Use the name of your parameter here.
This will filter the data so that only the team that's in the parameter will appear on the report.
3. Group by either TM.MemberID or MEMBER.MemberName (depending on what order you want the data to appear on the report.
4. Suppress the details section and the member group footer section. Put the subreport in the member group header section.
-Dell