I have a SQL - Statement that returns amount, year and month and that is grouped by month and year. The date range is between two years. Thus the result contains both years, divided into months and the according amounts.
In a matrix I want to compare the amounts of the months of both years. The columns are grouped by months and are automatically repeated. The rows are grouped by year. Because I have 2 years in my range, 2 rows are generated automatically. Now
I want to substract the amount of year 2 from year 1 (or vice versa, it doesn't matter).
My question is: How can I refer to a certain year - let's say 2009 - if the rows are only created when the report is executed. Is there a way to work with a scope?
My former solution was to make two querys, each getting the amount of one of the years and then combining the results with a union. I had 2 separate fields, Amount2009 and Amount2010. In the first query Amount2009 was filled in, whereas I set Amount2010
to 0. In the second Query I did the same, just the other way round. Then I grouped again by month and year and had 2 Amount, that I substracted. That got quite complex, with more than just one value field.
Sorry if my post is incomprehensible. If you have any questions, just ask.
Any help is greatly appreciated
Thanks in Advance
View Complete Post