Crosstab and groupings

[Travis]

Here's My data:

Account Fund    Amount      Group
Acct1   Fund1   $20     1
Acct1   Fund2   $30     1
Acct1   Fund3   $40     2
Acct1   Fund4   $50     2
Acct2   Fund1   $60     1
Acct2   Fund2   $70     1
Acct2   Fund3   $80     2
Acct2   Fund4   $90     2

I've created a crosstab report that looks like this:

    Fund1   Fund2   Fund3   Fund4
Acct1   $20     $30     $40     $50
Acct2   $60     $70     $80     $90

I created a group for my "Group" column, but then my report includes all of the fund columns like this:

Group 1
    Fund1   Fund2   Fund3   Fund4
Acct1   $20     $30     0       0
Acct2   $60     $70     0       0


Group 2
    Fund1   Fund2   Fund3   Fund4
Acct1   0       0       $40     $50
Acct2   0       0       $80     $90

But what I want is the report to look like this:

Group 1
    Fund1   Fund2
Acct1   $20     $30
Acct2   $60     $70


Group 2
    Fund3   Fund4
Acct1   $40     $50
Acct2   $80     $90

How do I do that?

[Hadib Ahmabi]

Try applying a filter over the crosstabs. In the first crosstab you  filter Fund3 and Fund4 and in the first  Fund1 and Fund2. 

[Travis]

I cannot filter by fund since fund is dynamic.  A report could have fund1, fund2, ...fundX.

[Hadib Ahmabi]

Try the following in the Column Group (Fund group) set the following filter:
=Sum(Fields.Amount) > 0.

[Travis]

Sometimes I will have a valid $0 amount, so that doesn't work either.

[Elian]

Hello Travis,

If you create report groups, which group by Category and all the grouped items do not contain specific Funds (for e.g. Fund3 and Fund4), then there shouldn't be columns in the crosstab for them. If however there is data for e.g. in the first group for Fund4, then naturally Fund4 column will appear. 
Please review the attached report showing your scenario implemented with a crosstab and report groups.
 
Regards,
Elian
the Telerik team