Skip to Content
avatar image
Former Member

Gift Receipt

Hopefully someone can help me with this issue.

I'm using Crystal Reports XI and have modified a gift receipt that was developed by my predecessor. The subreports are located in one large detail section. The main issue I am having is that the subreport is duplicating data on the main report. I have tried moving the subreport into separate group headers/footers but then the main report has all the subreport data listed for each constituent (even those it should not apply to). I have also tried splitting each subreport into a separate detail line but the duplication on the main report still happens.

I also have some basic SQL Expressions to pull the correct nametype into formulas that are in the main report:

Honor Roll Name (working properly): (select "NAME_HONOR_ROLL"."NAMEFORMN" from "MILLORA"."NAME_HONOR_ROLL" "NAME_HONOR_ROLL" where "NAME_HONOR_ROLL"."NAMEID" = "GENERIC"."ID_NUMBER")

Preferred Name (working but occasionally get a blank name): (select "NAME_PREFERRED"."NAMEFORMN" from "MILLORA"."NAME_PREFERRED" "NAME_PREFERRED" where "NAME_PREFERRED"."NAMEID" = "GENERIC"."ID_NUMBER")

Formula:

@Name to Use: If {GIFTS.GIFTYESNO} = 'N' then {NAME_PREFERRED.NAMEFORMN} else {NAME_PREFERRED.NAMEPLURAL}

A secondary issue is that the wrong nametype is pulling into the @name to Use. In the result set attached, you will see that sometimes the honor roll name (has years or formal title as suffix) is where the Preferred name should be even though I have a formula dictating where the Preferred name should be.

Let me know if I can provide more information. See attached result set (last page is where the duplicate resides).

Thank you.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 14, 2015 at 09:43 AM

    Hi Hiruy,

    Is the Subreport in question 'related' to the data that prints on the Main report's details section?

    If yes, then you would need to 'link' the Subreport with a field or fields from the Main Report so that each iteration of the Subreport prints separate data based on the details section records of the Main Report (More like a correlated subquery).

    Right-click the Subreport > Select Change Subreport Links > Select a field from the pane on the left and move it to the right > From the drop-down at the bottom right that says 'Select data in Subreport based on field', choose the corresponding field from the dropdown that can link to the field you just selected'.

    If the Subreport accepts prompts then you can directly link a value to the prompt so that the filter is processed on the database side. This option is towards the bottom left of the same window (Subreport parameter field to use).

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Double-click the Subreport > Once you're inside the Subreport click Refresh > You should be prompted for the fields you've linked on > Enter the values > How many records does the Subreport return?

      -Abhilash

  • avatar image
    Former Member
    Jan 15, 2015 at 07:51 PM

    It only returns one record per constituent. Since the Group is being selected by Gift Transaction Number (Gifts.gifttrnum) which is unique, only one transaction row should return for the main report which is what happens. The subreports link by a giftkey which is also unique for each row.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Abhilash Kumar

      I sent instructions on how to log into my machine remotely. Did you receive them? I cannot save this file for some reason. Please I need to make this report run correctly asap.