I am not sure how to title this post but here is what I need to accomplish and I am having trouble figuring out how to pull it off.
I need to duplicate a report in either crystal or SSRS and I am trying to create the data source. The current report looks similar to the sample below. This is an operating expenses report and the amounts are in one table and the description in another. I need the user to select a month to run the report for and display the net change for that month (first amount) and the YTD balance. The issue I am having is if I select a month and there are no expenses (no net change) for that month how do I display the category and the YTD balance. How do I get that balance from the last row that exists for that category?
In addition I need to add a 3rd and 4th column to display the same amounts for the month the previous year.
I am just drawing a blank on how best to achieve this.
Here is a sampling of the rows:
and how they should be displayed: