Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crosstab : suppress empty lines

Status
Not open for further replies.

Gus01

Programmer
Sep 4, 2009
45
FR
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
 
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
 
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'
 
Remove the running totals, and instead try inserting maximums on the fields.

-LB

 
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 ?
 
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

 
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 ?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top