SSRS - Display latest value in column group

Question

tables - I can't post images yet, so I posted a link to the examples.

I have two tables - one for sales and one for inventory. The dates in these tables are week ending dates - so the sales are totals for 1 week and the inventory is the quantity counted at the end of the week.

I want to create an SSRS report, using a matrix, showing sales and inventory in a column group by month with a row grouping by product and product/color.

I wrote a simple SQL query that joins the tables by product ID and date (see 'dataset' in reference image).

The sales information can be aggregated the normal way - with a SUM. This works correctly.

However the inventory quantity should not be sum'ed - the quantities are for the end of the period; so I want to show the final quantity (the qty with the latest date) from the month in that column and row group.

The inventory quantities are affected not just by sales, but production values also, so I can't calculate the ending inventory.

Is there an easy way to do this? Perhaps it's better to calculate in SQL? The final report could cover a year, or more, of data so there would be many column groups.

Answer 1

If you're looking for something like this then you can use the expression SUM(Fields!Sales.Value) for your sales value and LAST(Fields!Inventory.Value for your inventory value. You'll need to sort your dataset by date so that you pick up the last value in the dataset.

Answer 2

SSRS has aggregate functions like First(Value), Last(Value). I assume you could do something like:

Month  |  Last(Fields!Inventory.Value) | SUM(Fields!Sales.Value)

And then in the Row Groups function, make sure it is grouping on Month. I would also make sure that your SQL is ordered by date.