Conditional Suppression in a Cross Tab

[HowardHammerman]

My client has create a cross tab with two row fields and one column subtotal. There are 14 categories in the column field.
Each cell has two values: Units sold, average sale.
It works perfectly.

Now the client wants to suppress the values in each cell (but not the row totals) when the sum of the number of units is below a user-provided value from a parameter field.
I have not been able to do it using either the suppression expert or via conditionally changing the font color.

Is the only solution a manual cross tab?

They are using CR 2008

Thanks in advance.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[briangriffin]

You could use the Display String to do this - the only problem would be if the client wants to export to Excel since the numbers would come through as text.

Format field --> Common tab --> Display String --> X2

if currentfieldvalue < {?YourParameter} then '' else totext(currentfieldvalue)

You would probably want to add additional info to the totext function to get the format the way you want it (e.g. totext(currentfieldvalue,"#.##,0)

 

[HowardHammerman]

Been there, done that, it doesn't work. The reason it doesn't work is that I am comparing a subtotal created by the cross tab expert to the parameter value.
That subtotal does not exist. Remember it is in a cross tab. There are 14 values in my column field. I need to access the total at level two for each of the column values. The row values are:
Product
Customer
(there are many products and many customers)
The column values are:
Size
(there are exactly 14 sizes)

We need to apply the format that you describe for each customer/size combination. That subtotal does not appear when one right-clicks on one of the summary fields and then formats the field, goes to common tab, clicks on X2

Believe me, that was the first thing I tried.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[briangriffin]

Then I'd do it using a SQL query and a table variable to do all the comparisons before the data hits the report. You could still use a crosstab to make the formatting easier, although with only 14 columns a manual crosstab probably wouldn't be any more work.

 

[HowardHammerman]

There is a problem with a manual cross tab. One of the summary measures is an average. Within the xtab expert the averages are accurate. However, in the manual xtab they are not. They are not because I am summing on an indicator formula: If {table1.size} = size1 then 1 else 0. For the sales I am summing on a formula like this: if {table.size} = size1 then {table1.amount} else 0. When I create an average on that formula the denominator is equal to all sizes and the average is wrong. So I need to create a sum and then divide by the count. Then suppress the values when the count is less than the parameter. And sum at two groups and the grand totals. And get it all right.

I'm not familiar with SQL queries and table variables.

Thanks.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com