Supress rows from a cross-tab

[tawke]

Hi,

I'm working against a database that has a location (City, Building, Floor) for each entry, i have created a formula which groups these locations into their parent locations (City)

local stringvar Location;
Location:={Service_Calls.Location};
if location in ["BUILDING-A\2ND-FLOOR",
"BUILDING-A\GROUND-FLOOR"]
then
Location:="City"
else

i pretty much repeat that and finish with:

if location in [""] then
Location:="Location missing"

else
Location:="Other location"

I am not really that interested in having the Location missing field included in the report, but if i leave it out it will display in the report as a blank field (its also a pretty high % so it basically always appear as the first)

What i would like to have it look like would be to have the Top 10 locations, then have the rest grouped into "Other" (Problem here is that but the "Others" from the group sort expert, and the "Other location" from the undefined locations would need to be grouped)

Any advice on this?

 

[lbass]

Why not use a record selection formula like this:

not isnull({table.location}) and
trim({table.location}) <> ""

-LB

 

[tawke]

Cheers, that did the trick, but by using this i effectivly also drop all the other data from that table entry which kinda mess up the numbers.

Ideally it would just drop or not use the empty location field in the specific cross tab report im using this for (similiar to be using specified order grouping)

 

[lbass]

//{@Location}:
if isnull({Service_Calls.Location}) or
trim({Service_Calls.Location}) = "" then
{@null} else
if {Service_Calls.Location} in ["BUILDING-A\2ND-FLOOR","BUILDING-A\GROUND-FLOOR"] then
"City" else
"Other Location"

//{@Summaryfield}:
if isnull({Service_Calls.Location}) or
trim({Service_Calls.Location}) = "" then
tonumber({@null}) else
{table.amount} //whatever the field is you want to summarize

...where {@null} is a new formula that you open and save without entering anything. This assumes that you have NOT set up the report in file->report options to convert nulls to default values.

Then go into the customize style tab and check "suppress empty rows" and "suppress empty columns".

No need to use a variable for the location field.

-LB