Skip to Content
author's profile photo Former Member
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 a comment
10|10000 characters needed characters exceeded

Related questions

8 Answers

  • Posted on 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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on 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.


    capture.png (8.2 kB)
    capture2.png (2.8 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on 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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 01, 2018 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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 01, 2018 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


    capture4.png (7.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 01, 2018 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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 01, 2018 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


    capture3.png (2.6 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 02, 2018 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


    capture.png (8.2 kB)
    capture2.png (2.8 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.