Crosstab : suppress empty lines

[Gus01]

Hello,

In a crosstab with a single column (Year) and multi Summarized Fields (for example : # Dogs and # Cats), is it possible to suppress a line when the values for each year is Null (or Zero).

With this data :

Year Dogs Cats
2008 5 Null
2009 2 Null
2010 3 Null

crosstab =

2008 2009 2010
Dogs 5 2 3

and no line for cats (always Null value).

I tried 'suppress empty rows' (Customize style), but the line is not suppressed.
May be because in my case, the fields #Dogs and #Cats are 'Running Totals'.

Thanks

 

[lbass]

Suppress empty rows doesn't work consistently and I've never figured out under what cases it does work. The running totals might be the issue, so the question is why you need to use running totals. Please explain the set up of the running totals and whether you are using any group selection.

-LB

 

[Gus01]

Thank you for your answer.

In fact in the report, there is a main table 'M', wich is linked (left outer join) with secondaries tables S1, S2, ... on field 'Year'
The table M contains all the possibles values for 'Year', but 'Year' is not the primary key.
The secondaries tables contain independant data.
So the data returned by the query are (in case there is no data in table S2 for 'Year' = 2008 to 2010) :

M.YEAR S1.Dogs S2.Cats
2008 5 Null
2008 5 Null
2009 2 Null
2010 3 Null
2010 3 Null

The RunningTotals on {S1.Dogs} and {S2.Cats} suppress the double lines (Evaluate on change group 'Year').

So in the crossTab the data are :

Year Dogs Cats
2008 5 Null
2009 2 Null
2010 3 Null

and I try to suppress the line 'Cats'

 

[lbass]

Remove the running totals, and instead try inserting maximums on the fields.

-LB

 

[Gus01]

With 'maximum' directly on the field in the crosstab, the result is OK.
That's cleverer than the runningtotal ! Thanks.

but unhappy, the empty line is still in the crosstab ... (I verified that 'suppress empty rows' is on)

I don't really understand which value has the "empty" field.
This formula :
if isnull({S2.cats}) then 'NULL' else 'NOT NULL'
shows nothing ?
May be it's because of the Left Outer Join on the table, and no linked data with S2 ?

 

[lbass]

I guess the problem is really that there is no column field to suppress, just the summary itself and its label.

What is returned if you place your "null" formula in the detail section of the report (unsuppress it if necessary)?

-LB

 

[Gus01]

The field is already in the detail section and the formula is in the 'x+2' button of the field.

With a true formula (in the detail section), it shows 'NULL'.

What do you mean by 'no column field to suppress, just the summary itself and its label.'
There is always a summary on a field in a crosstab ? in which case the option 'suppress empty rows' works ?