SSRS tablix show sales vs budget

Question

I am struggling to understand a way to get the following in a proper tablix in SSRS.

I would like to have a tablix report with the following.

           NOV 2016         DEC 2016 
           Sales Target     Sales Target 
CustomerA   100   200        400    300

My datasets are all in SQL Server 2008 R2

dataset1 to get the sales numbers.

Customer  InvoiceDate Salesvalue
CustomerA 05/11/2016  50
CustomerA 04/11/2016  50
etc

dataset2 has the targets.

   Customer   date      Target
   CustomerA  11/2016   200
   CustomerA  12/2016   300

I just cannot manage to "merge" these 2 tables together and get my target next to the sales for each month.

I managed to get it done in powerBI as there you can nicely add a relationship between the tables based on an extra DimTimTable where I link the date with the invoice date and the add a filter just based on month.

I am just wondering how my datasets should look to get sales (which is based on the invoicedates) next to the budgets which are linked to the month.

So I am struggling to understand how to model this and if SSRS and SQLserver is correct for this? Should I create a "cube" for this before I can achieve this?

Answer 1

You can do this in SQL.

First Sum your sales by customer and month, note I have used the datediff/dateadd, to find the first day of the month

SELECT Customer, DATEADD(month, DATEDIFF(month, 0, InvoiceDate, 0) as StartMonth, Sum(Salesvalue) as Sales
FROM dataset1
GROUP BY Customer, DATEADD(month, DATEDIFF(month, 0, InvoiceDate, 0)

Convert your Forecast tables date field so it is consistent and also has the first day of the month

SELECT Customer, CONVERT(date, '01/' + date , 103) as StartMonth, Target
FROM dataset2

You can also join in a calendar file if you don't want gaps in your data and join that with a list of your customers, that way you will get all possible values in your pivot. But for simplicity I will just do an outer join between the two, since you are likely to have either a sale or a target for each desired month in your range.

SELECT ISNULL(Sales.Customer, Targets.Customer) as Customer, 
 ISNULL(Sales.StartMonth, Targets.StartMonth) as StartMonth,
 Sales, 
 Target
FROM (SELECT Customer, DATEADD(month, DATEDIFF(month, 0, InvoiceDate, 0) as     StartMonth, Sum(Salesvalue)  Sales 
FROM dataset1
GROUP BY Customer, DATEADD(month, DATEDIFF(month, 0, InvoiceDate, 0)) as ConvertedSales 
OUTER JOIN
(SELECT Customer, CONVERT(date, '01/' + date , 103) as StartMonth, Target
  FROM dataset2) Targets
 ON Sales.Customer = Targets.Customer AND Sales.StartMonth = Target.SalesMonth

Now you can create your pivot using this dataset