September 18, 2010 at 2:24 am
i have a matrix in report, which shows yearly sales for items..
item 2010 2009 2008 2007 2006
abc 88 49 33 5 70
xyz 288 292 202 5 70
i want to have last two year, last 5 year total,
item 2010 2009 2008 2007 2006 last 2 yr last 5 yr
abc 88 49 33 5 70 137 212
xyz 288 292 202 5 70 580 655
how to achieve this ?
the query is simple,
select item, sales_date,qty from sales where sales_date >@givendate
...then i am keeping item in rows and year(sales_date) in column of matrix...
IMPORTANT: the years can vary depending on user parameter @givendate...i have to show only last 2 and 5 year sum
September 20, 2010 at 2:33 pm
Rather than doing the 2/5 year logic in RS, it seems like a simpler approach would be to do it in the SQL statement.
You could join the table to itself with the joined table containing columns for the item; two-year sum; five-year sum. It'd be something like SUM(CASE WHEN sales_date >= DATEADD(yy, -2, @givendate) THEN Qty ELSE 0 END) as TwoYearSum. The downside is you'd be essentially querying that table twice. Or, you could do SUM(CASE... statements for all columns -- kind of like a pivot.
As a guideline, I try to take as much processing out of the hands of the report for the sake of performance, unless it's really convenient to have RS do it.
Hope that helps.
September 21, 2010 at 12:26 am
i am wondering why we have to do these things in query itself...if i check Cognos, you can do all these things in report design...
by having to do these things in query, i cannot use same data set for multiple report data regions....and also, i need to apply logic myself...like summing with condition, etc..
September 21, 2010 at 7:39 am
Sorry, I just assumed you had a SQL back-end, not Cognos. What are the expressions for the two latest year columns? Could you just add those expressions together in another column? And the same with all five years?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply