Skip to Content
avatar image
Former Member

How to delete duplicates in crosstab in crystal report?

I have a cross tab where I have two groups (main group and sub group). If my main group doesn't have a subgroup, then it repeats twice and I don't want it to repeat twice. Thanks in advance!

main group sub group

A ---------------- xyz

B ---------------- tst

C ---------------- ati

D ---------------- D

In the above example Main group and Sub group have the same value 'D'. Therefore, I want 'D' to appear only once in the main group, and not appear in the sub group. How could I do that?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Dec 31, 2017 at 02:25 PM

    Hi Amy,
    1. Create a formula with this code:

    If {subgroup} = {group} then '' else {subgroup}

    2. Use this formula field as the Subgroup in the crosstab.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 31, 2017 at 04:08 PM

    You'd need a formula for the summary field as well.

    If {subgroup} = {group} then 0 else {measure field}

    Use this field as the Summarized field in the Crosstab.

    Then right-click one of the Summary cells > Format Field > Number tab > Customize > Check 'Suppress if zero'.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 01 at 04:59 AM

    I created a formula (@subgroup_formula) and wrote following code

    if {sub_group}= {main_group} THEN '0' ELSE{sub_group}
    

    I used @subgroup_formula as a row field in my cross tab. This is working and displaying 0 for the duplicates.

    Then I created a formula (@client_formula) for the summarized field and wrote following code

    if {@subgroup_formula}= '0' THEN 0 ELSE{client}
    

    The problem is when I use @client_formula as my summarized field, I'm not getting 0.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 01 at 04:59 AM

    I used a formula for the summary field as well.

    If {subgroup} = {group} then 0 else {measure field}

    but it's not changing to 0. What could I be doing wrong. Once it changes to 0 then I can suppress it easily

    capture4.png

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 01 at 04:58 AM

    Just in case if someone is looking for a solution.

    Here was my solution: Open Cross-Tab Expert. In Cross-Tab under Rows select your row which should be conditional suppressed. Then select Group Options. This will open Cross-Tab Group Options dialog. Go to Specified Order and add filter. In this case, rows that match your criteria will be displayed in your cross-table all other rows will be placed in one row "Others". Then select tab Others and check Discard all others.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 02 at 05:29 AM

    capture.png capture2.png

    I have this code in my sql command. The problem is that cross tab is still showing the sub total. How do I get rid of that?The second image is with the indentation.

    Main group values = A and D

    Sub group values for 'A' is b

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 31, 2017 at 03:36 PM

    I have this code in my sql command. The problem is that cross tab is still showing the sub total. How do I get rid of that?The second image is with the indentation.

    Main group values = A and D

    Sub group values for 'A' is b

    capture.png capture2.png

    I tried putting the code in formula and using it as a field in cross tab, but having the same issue as above.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 01 at 04:59 AM

    I'm able to suppress the subtotal of Main group 'D' by right clicking on the field formula and in suppression I used following code;

    if CurrentRowIndex = 5 then true else false

    I'm also able to Hide the sub group 'D' as well by using the syntax similar to you have mention in my sql command.

    The problem is how to hide/suppress the blank row? I checked on suppress blank row, but it's not working. Thanks in advance! Any help would be appreciated.

    capture3.png

    Add comment
    10|10000 characters needed characters exceeded