Crystal Reports and Events

Options
Kristin Smyth
Kristin Smyth ✭
Ninth Anniversary First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
We are trying to put together a crystal report of RSVP's for events and the team wants to see the particpant names and on the next column their guest names. The issue I am having is when I pull the data in the guest names appear in the main column - how to I pull in the partipant names minus the guest names?


Thank you!

Kristin
Tagged:

Comments

  • John Heizer
    John Heizer ✭
    Ancient Membership Facilitator 3 Photogenic
    Putting multiple guests into one column will probably require subreport because you'll have multiple guest Participant records you're trying to attach to a single detail line for the "host" Participant.
  • Kristin Smyth
    Kristin Smyth ✭
    Ninth Anniversary First Reply

    John Heizer:

    Putting multiple guests into one column will probably require subreport because you'll have multiple guest Participant records you're trying to attach to a single detail line for the "host" Participant.

    Thank you John, that is helpful. Do you happen to also know how to get the guest names to not show in my overall list? When I pull in my particpant names it also includes the guest name and I just want it to show the main registrant?


    Thank you so much for your quick response to my first question.

    Kristin

     
  • Alan French
    Alan French ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic



    If you create a participant query with the required criteria, plus "Participant Guest of blank", this will exclude anyone who is a guest of another participant. That should allow you to create a participant export containing only non-guests. In the export, expand the Guest section (second from bottom, between Sponsoring Pledges and Sponsorship List) and add the guests' names etc.


    When you use this export as a data source in Crystal, all your non-guests will be in the "Prt" table, and your guest information will be in "PrtGst_1"


    In Crystal, add your participant and guest fields all together in the details section:
    b992ea2fd6750e50edfa8efb48585346-huge-cr

    which then looks like this with the data:
    add4f1528ae7d05f53c4a4b52f6d054f-huge-cr

    It does cause duplicate rows where the participant has more than one guest (lines 2 & 3, and 7 & 8 above), but I think there's a way around that by using an array formula to concatenate the guest names into one long string. I've used something similar for attributes before - I'll see if I can dig it out if you'd prefer not to have duplicates.
  • Alan French
    Alan French ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    Something I forgot to mention in my previous post: the Prt and PrtGst_1 tables will need to have a join type of Left Outer Join. If you leave it as the default Inner Join, you'll end up with only the participants who have guests.
  • Kristin Smyth
    Kristin Smyth ✭
    Ninth Anniversary First Reply

    Alan French:



    If you create a participant query with the required criteria, plus "Participant Guest of blank", this will exclude anyone who is a guest of another participant. That should allow you to create a participant export containing only non-guests. In the export, expand the Guest section (second from bottom, between Sponsoring Pledges and Sponsorship List) and add the guests' names etc.


    When you use this export as a data source in Crystal, all your non-guests will be in the "Prt" table, and your guest information will be in "PrtGst_1"


    In Crystal, add your participant and guest fields all together in the details section:
    b992ea2fd6750e50edfa8efb48585346-huge-cr

    which then looks like this with the data:
    add4f1528ae7d05f53c4a4b52f6d054f-huge-cr

    It does cause duplicate rows where the participant has more than one guest (lines 2 & 3, and 7 & 8 above), but I think there's a way around that by using an array formula to concatenate the guest names into one long string. I've used something similar for attributes before - I'll see if I can dig it out if you'd prefer not to have duplicates.

    Thank you so much! This was super helpful.

Categories