Filtering out records in a CrossTab report

[diegoC11]

I've seen some topics on how to filter out records in a cross tab report, but have had no success. I am trying to do something similar to this:

http://www.tek-tips.com/viewthread.cfm?qid=1619621&page=1

I have a cross tab report that I would like to remove entire rows if the sum of the column values < 30.

The problem I experience with some of the solutions is that they mention: {table.col} or {table.name}...
When I try to create a formula with these, Crystal doesn't recognize the syntax (I'm working with Crystal Reports XI)

Any help you can provide would be great.Thanks!

 

[lbass]

{table.col} was just meant to indicate the column field--whatever it is, while {table.name} was the rowfield in that particular instance. That thread isn't the approach I would use in your case. Please identify by name your column field, your row field, and your summary field. I assume you mean the total for a particular row must be greater than or equal to 30 to display.

-LB

 

[diegoC11]

Thanks lbass,

You are correct, the sum of a particular row should be greater than or equal to 30 to display.

There are several rows in this report, but the one that is most important is @Order#

The summarized field is @qty sold

The column name is {OELINHST_SQL.item_no} and can be one of the following item numbers along the top of the cross tab report.. ["41031","41011", "41001", "41021", "41041", "41036", "41006", "41046"]

-DC

 

[lbass]

I would like to know the row fields in the crosstab, in order, please. Not the values, but the names of the fields. Please also show the content of the formulas.

-LB

 

[diegoC11]

Thanks lbass,

I hope the following information is what you were asking for. I've also uploaded a sample of the first page of the report (.pdf) given to me by the requesting department.

Row #1 Name: ARSLMFIL_SQL.slspsn_no (String)
Row #2 Name: ARSLMFIL_SQL.slspsn_name (String)
Row #3 Name: @ Customer # (Number)
Row #4 Name: ARCUSFIL_SQL.cus_name (String)
Row #5 Name: @Order# (Number)
Row #6 Name: OEHDRHST_SQL.ship_to_name (String)
Row #7 Name: OEHDRHST_SQL.ship_to_addr_3 (String)

Column #1 Name: OELINHST_SQL.item_no (String)
Column #2 Name: OELINHST_SQL.item_desc_1 (String)

Summarized field:

Sum of @qty sold (Number)

The following are the formulas listed above:

@ Customer #:
ToNumber({OEHDRHST_SQL.cus_no})

@Order#:
tonumber({OEHDRHST_SQL.ord_no})

@qty sold (Number)
// This formula calculates the quantity sold.

If {OEHDRHST_SQL.orig_ord_type} = "C" Then
({OELINHST_SQL.qty_ordered} * {OELINHST_SQL.uom_ratio}) * -1
Else
{OELINHST_SQL.qty_to_ship} * {OELINHST_SQL.uom_ratio};

 

[lbass]

First you would need a group in the main report on {@Order#}. Then create a formula like this:

if sum({@qty sold},{@Order#}) >= 30 then
{@qty sold}

Use this formula in your crosstab instead of {@qtysold}.

-LB

 

[diegoC11]

Wow, this worked great! Thanks lbass!