Filter a cross tab total

[RichieK]

Hello,
I'm looking to filter the results of a Cross Tab if the total column if over a certain value. I've seen a post by Ibass

http://www.tek-tips.com/viewthread.cfm?qid=1339287

that describes how to sort the total column but I'm looking for a method where I specify a value rather than a Top n or Top %. Can anyone point me in the right direction?

Thanks in advance
Richie

 

[lbass]

Please explain more specifically what you want to do and also identify your row, column and summary fields.

-LB

 

[RichieK]

I have a table with contactID, customer name, date and articleid number that has been sent to the customer.
The cross tab shows me for each customer a column for each articleid and the summary information is a count of the contactid. The row total is for all of the articles sent in the period The overall report is filtered on the date in last 4 weeks.

What I would like the table to show me is any customer who has received more than 5 articles in this period.

Is this possible?

Richie

 

[lbass]

So you want to highlight the row total if it is > 5?

If so, select the row total->right click->format field->border->color->background->x+2 and enter:

if currentfieldvalue > 5 then
cryellow else
crnocolor

-LB

 

[RichieK]

I wasn't wanting to highlight the row, I was ideally wanting the table to just contain rows where the total was >5

 

[lbass]

In the main report, insert a group on customer name. Then create a formula:

if count({table.contactID},{table.customername}) > 5 then
{table.customername}

Use this formula as your row field instead of the customer name. Also go to report->selection formula->GROUP and enter:

count({table.contactID},{table.customername}) > 5

If you don't want the report proper filtered this way, you can create the crosstab as suggested in a subreport--so that you can use the group selection.

-LB