In a cross Tab report, I've three years data (2005,2006,2007)
I need the sales amount for the customer who purchased the items >3000 (I can use filter for this amount)
question is
i need it to show >3000 in ANY year
so for Customer X, it could show sales of $453 in 2005, $3,200 in 2006, and $783 in 2007
because in 2006, they had over 3000
but i still want the smaller amounts to show
but if customer Y has sales of less than 3000 in ALL those years, dont show customer Y
please send the solution
You need 2 queries, one to filter on the sales value and just return customer numbers who have >3000 in any year, and a second which joins to the first to populate your crosstab. Can't give you the structural details as I'm not on Cognos8 but that's the basic approach.
almeids is on the right path.
One thing you have to be careful when filtering for >3000 is that you do the filtering after auto aggregation. That way it'll look for individual years instead of a total number regardless.
Then you do a 1..1 join on customer number for report.
Another area to look is that when you join, the numbers may multiply by the number of years you have. If that happens, use a case statement to get sales from 1st query instead of using the sales directly as a measure item.
Right on Suraj.
Quote from: johny.cbi on 24 Jan 2008 10:28:56 AM
In a cross Tab report, I've three years data (2005,2006,2007)
I need the sales amount for the customer who purchased the items >3000 (I can use filter for this amount)
question is
i need it to show >3000 in ANY year
so for Customer X, it could show sales of $453 in 2005, $3,200 in 2006, and $783 in 2007
because in 2006, they had over 3000
but i still want the smaller amounts to show
but if customer Y has sales of less than 3000 in ALL those years, dont show customer Y
please send the solution