Forcing columns in crosstabs

[Flybridge]

Hi

Using CR 11.
We need to force a column to be present in a cross tab. We have already used info from here to get data present when we query on all data (use left outer join to calendar table) but when we group the data it doesn't work.

e.g. we report on products by group. So when we say group by product group or filter on a product group we don't get all the months as columns - we just see those months for which orders exist for that product group.

I suspect we need some sort of formula but can't see what it should be.

Anyone out there with any ideas?

Thanks in advance.


'The world isn't round - it's bent!' Spike Milligan

 

[lbass]

The left outer join should be FROM the calendar table TO the product table. When you add the filter, though, you "undo" the left join. So instead of filtering, try using a conditional formula for the row field, like:

if isnull({product.productgroup}) or
not({product.productgroup} in ["A","B","C"]) then
"Other" else {product.productgroup}

You can then either use specified order in group options to eliminate the "Other" category or to suppress it. Not sure this will work, but it's worth a try.

-LB