Cross-Tab Full of Zeros

[mikebird]

I have a good history of hundreds of reports. Usually I get past the hurdle of hundreds of zeros I don't want by suppressing empty rows and columns in cross-tabs. Here there are the odd 1s and 2s so the rows and colums have to remain.

I can't see how to introduce a formula for every cell to have to be > 0

If I could it would be perfect - just show a few rows and columns. Right now it's 1000s of rows & columns of zeros. Doing this in SQL it's great - grouping on two fields, which are the left and top field for the cross. The summary is a count.

Tell me

 

[Kempes]

is it only in the cross tab that you don't want zeros, or the whole report?

If it's the whole report, you can use the select expert to filter the zeros out of the report data selection.

 

[mikebird]

Well, tell me if I'm wrong but it's the cross-tab which does the business. I can run a SQL query pulling the two fields and a count, where I'll get no zeros there. But the cross-tab is key. The set of values for one field goes all down the left side. The values for the other field go across the top. The counts depend on cross matching the result for what you get between those two field values. More than SQL can give you. And there will be zeros in there sometimes. Can't set >0 I don't think. Dependent on the fields.

Have you used cross-tabs?

It's great to eliminate whole rows or columns which are all zero. But can't if there's one value in there - would be like saying I NEVER made a sale although there was one, one year. Although that would be using HAVING for the total to have to be above a threshold..? to include a row or column. Want to have at least a total count of 10 in a row or column of 1000. That's what I'm asking for I guess

 

[Kempes]

Unless I am misunderstanding the question, the only way to eliminate the blanks on a cross tab would be to pull your column header into the row header, so both attributes are rows, then the column will be purely the total by row. This should only show the relevant data then as it won't be displayed in the "battleships grid" format.

eg, if you was doing sales by year and supplier, both year and supplier would be added into rows, and the sum of whatever your counting falls into the summarized fields part.

or have I completely misunderstood what you want on this?