crystal-reportscrystal-reports-2016

Crystal Reports -- combine multiple rows into one row


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)

Solution

  • 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.