SSRS - Dataset using different dates (Table)

Question

I'm creating a report with all of the sales made from each product category. I added a table that contains on the column side: Months and for Rows I have the Product Category (i.e. Chairs, Tables, etc.).

So it should be looking something like this:

Product | January | February | March | April
---------------------------------------------
Chairs  | value   | value    | value | value

Tables  | value   | value    | value | value

So the thing is that I need to calculate the total amount of chairs sold each month.

I created the query:

SELECT SUM(Quantity)
FROM Sales
WHERE PurchaseDate BETWEEN '2015-01-01' AND '2015-01-31'

That query is inside a Dataset, but I'm looking for a way to use this same Dataset but pass it or change the PurchaseDate range in order to calculate the rest of the sales per month.

You could try modifying they query to return the results how you want to display them. It looks like you will need to pivot. — Roberto

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-04-27T02:29:35

Add a Matrix with the following fields and groups settings:

In Row Groups add Category field and in Column group add a Parent Group, use the following expression:

=MONTHNAME(MONTH(Fields!PurchaseDate.Value))

Use the above expression in the column at the right side of Category column.

It will preview the following matrix:

Note my example only include dates in January and February, in your case it should show every all months returned by your dataset.

Let me know if this helps.

SSRS - Dataset using different dates (Table)

1 Answers