cancel
Showing results for 
Search instead for 
Did you mean: 

How to delete duplicates in crosstab in crystal report?

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (8)

Answers (8)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

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