1

Example:

RecordID ............ MemberName ........... SportID ......... Sport
1 .................. James ................. 1 ...............Hockey
1 .................. James ................. 2 ...............Football
2 .................. Jose .................. 5 ...............Basketball
3 .................. Jennifer .............. 2 ...............Football
3 .................. Jennifer .............. 4 ...............Dodgeball
4 .................. Jaqueline ............. 1 ...............Hockey
4 .................. Jaqueline ............. 3 ...............Baseball
4 .................. Jaqueline ............. 5 ...............Basketball
5 .................. John .................. 6 ...............Track

Hi all, so I am trying to make a report which will output a single page of data for a particular RecordID, depending on which RecordID I enter into the parameter field. To accomplish this I have made a parameter field for the RecordID and a group for the RecordID within the report, and so far the data has come out fine from the other tables (where things are arranged like "First name", "last name", etc, for a given RecordID).

Everything has been fine until now, because the data for this particular table is arranged different from the others.

My problem is, the report keeps outputting only one result per page, instead of all at once; so, for the James example I get only the "Hockey" box checked on the first page, and then only the "Football" box checked on the second page, and so forth.

I would like the output from this table to appear like this (for RecordID=1):

James:
[checkbox] Hockey.................. [empty checkbox] Dodgeball
[checkbox] Football................ [empty checkbox] Basketball
[empty checkbox] Baseball.......... [empty checkbox] Track

This is how I am making the checkboxes (set to Wingdings font):

If {RecordID} = 1 Then

Chr(254)

Else

Chr(111)

Can anyone help me with this?

Edit This is the solution my boss attempted (that does not work currently):

For the Declare a variable for each sport

      Shared BooleanVar Hockey:=False;
Shared BooleanVar Football:=False;
Shared BooleanVar Basketball:=False;
Shared BooleanVar Baseball:=False;
Shared BooleanVar Basketball:=False;
Shared BooleanVar Track:=False;

This for each variable:

Shared BooleanVar Hockey; 
        If {SportID} = 1 Then 
        Sport=True

and this for the checkbox:

Shared BooleanVar Hockey;
If Hockey = True Then
    Chr(254)
Trevor
  • 51
  • 9
  • May you show your design? Are your checkboxes in the group header section? – heringer Oct 01 '19 at 11:49
  • I don't think I can show the design, unfortunately. The checkboxes are not in the group header currently. I have GH#1 for the RecordID, and then I made a GH#2 for the Sport ID. The checkboxes are in a details section underneath the Group Header #2 (Sport ID). I am very new to Crystal Reports, so I apologize if I am not giving enough information. I appreciate the help! – Trevor Oct 01 '19 at 12:36

1 Answers1

1

If I am understanding, your structure is like this:

Group by RecordId Group by SportId Details section with all checkboxes

So, the report pick RecordId 1, then pick SportId 1. So, the data in the first occurrence of details section is:

RecordID ............ MemberName ........... SportID ......... Sport
1 .................. James ................. 1 ...............Hockey

Then, continue with RecordId 1, and pick next SportId, that is 2. So, the data in the first occurrence of details section is:

RecordID ............ MemberName ........... SportID ......... Sport
1 .................. James ................. 2 ...............Football

Then, the report pick next RecordId (2), and pick next SportId (5). So, the data in the first occurrence of details section is:

RecordID ............ MemberName ........... SportID ......... Sport
2 .................. Jose .................. 5 ...............Basketball

Can you see what is happening now? (if i did understand you).

It will always check one single sport that way.

I would try the following solution. It works you the sports are known, i mean, if you only have those 6 sports.

  1. Only group by RecordId. Remove the group by SportId.
  2. Put the checkboxes in group footer.
  3. Count the occurrences of each sport. Use one running total field for each sport. Remember to reset when the RecordId changes.
  4. Use formula to check the checkbox of each sport when the counter of that sport is greater than zero.

Let me know if you need help with the running total fields or if i misunderstood the problem.

EDITED: How to create the running total fields (step 3)

  1. On "Field Explorer", right click "Running Total Fields" and then "New...".
  2. Give it the nicest name (yeah, names are important): "HockeyCounter" for example.
  3. Set "Field to Summarize" = "SportId".
  4. Set "Type of Summary" = "Count".
  5. Use formula button to set "Evaluate": 
    {YourTableName.SportID} = 1 //where 1 is the Hockey SportId
  6. Set "Reset" = "on change of group", "group #1" (RecordId).
  7. Drag the brand new running total field to the group #1 footer section.
  8. Repeat these steps for the other sports (that will be boring, sorry).

I'm not sure if it will be helpful to you, but you may check this post that has something more about running total fields.

heringer
  • 2,698
  • 1
  • 20
  • 33
  • I think you understand clearly what I was trying to describe. Thank you for explaining it -- it helped me understand the problem a lot better! I do know all of the "Sports" (There are 7 SportIDs in total). Obviously I can do steps one and two, but I am not sure at all with step 3, other than I might need to declare a variable for each sportID. (I do not understand how to use most of the functions yet). I have added the solution my boss attempted to use... is this similar to what you mean? Thank you! – Trevor Oct 01 '19 at 18:52
  • Yes, i believe it has the same effect. I prefer to use running total fields, but it is just a preference. – heringer Oct 01 '19 at 20:55
  • The solution he attempted isn't actually working at the moment -- maybe it's a matter of placing the fields correctly? Could you show me how to use the running total fields? I don't know how myself. Again, the attempted solution is not working at the moment. Thank you! – Trevor Oct 02 '19 at 12:39
  • Hello again! Thank you for that, I didn't realize the Running Total Field was just another field-type. I have read through the other post, and it was indeed helpful, but I am wondering now how I am supposed to differentiate the running total field for each sport? The table is set up so there are multiple SportIDs for each recordID, so when I made the running total field for the SportID and put it in the group footer, I think I am just getting a count of the combined number of SportIDs for the given record (It just gives the number '18', for example)... How do I implement this? Thank you! – Trevor Oct 03 '19 at 13:07
  • Yeah, sorry, i forgot to mention. I will change the "evalute" option. See edition. – heringer Oct 03 '19 at 16:46
  • This, along with the other post you linked, solved my problems completely! In combination with the running totals change, I needed to move my other sections into the footer/header areas. Now it is not outputting the duplicate results. Thank you so much! – Trevor Oct 07 '19 at 12:29