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?
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
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
I tried putting the code in formula and using it as a field in cross tab, but having the same issue as above.
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
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.
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
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.
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.
Main group values = A and D
Sub group values for 'A' is b
Add a comment