Skip to Content
0

How to delete duplicates in crosstab in crystal report?

Dec 31, 2017 at 02:21 PM

94

avatar image
Former Member

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Abhilash Kumar
Dec 31, 2017 at 02:25 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Dec 31, 2017 at 04:08 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 01 at 04:59 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 01 at 04:59 AM
0

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)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 01 at 04:58 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 02 at 05:29 AM
0

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)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 31, 2017 at 03:36 PM
0

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)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 01 at 04:59 AM
0

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)
Share
10 |10000 characters needed characters left characters exceeded