Adding row when using lookup without a match

Question

Adding row when using lookup without a match

alex_martinez

SSCrazy

Points: 2144
More actions
March 2, 2016 at 12:31 pm

#311856

Hello,
I am using SSRS 2013 I have a budget report getting displaying actual sales amount for the month and Budget using lookup against the product line (Budget will look up the product line in Actual Sales). Works great, but if there is no actual for a give period than the lookup for the budget will not appear. Actual is the main driver. See below for examle
Sales by Product Line for the Month of February 2016
Product Actual Budget Variance
Wedget 1 $500 $400 $100
Wedget 2 $500 $300 $200
This is oK, but if I have Wedget 3 for $600 with no actual amount for the period. How can I have Wedget 3 appear even if there is not Actual.
I would like to see:
Sales by Product Line for the Month of February 2016
Product Actual Budget Variance
Wedget 1 $500 $400 $100
Wedget 2 $500 $300 $200
Wedget 3 $0 $600 -$600
Thank you in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Sounds like you need an outer join between Sales and Budget instead of an inner join. Post your query so we can see what you're dealing with.

alex_martinez SSCrazy Points: 2144 More actions · Answer 2

Pietlinden,

Thank you for replying, I actual using two seperate queries in this one report one is for a chart the other is for a chart as well. I am using a lookup in a table below the chart. So I am not using a inner or outer join at all with the two seperate tables.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

I don't know of a way, outside of using T-SQL, to do what you want. Inner joins cause records without matches to drop out of the result set, so you need an outer join. And since I don't think that can be done in SSRS, I suggested doing it in your query.

Here's your basic schema:

Budget:

ProductLine--(1,M)--Product--(1,M)--Budget(Year,Month,$)

Actual:

ProductLine--(1,M)--Product--(1,M)--Sales(Year,Month,$)

an INNER join will, by definition, remove any records from the result set where there are no matches in the join clause, so that's why any Products without Actuals (Sales) don't show up. In order to force the Actuals for each month to show up, even if they're zero is to OUTER JOIN Budget to Sales. Then ALL of the Budget records will show, whether they have sales or not.

In a nutshell, I don't believe that a plain LOOKUP will solve your problem. Your problem is with the relationship between Budget and Actual, and you cannot, as far as I know, implement an outer join in SSRS. That's why I suggested changing your query to something along the lines of

SELECT B.ProductLine, B.Year, B.Month, SUM(B.BudgetDollars) AS MonthlyBudget, SUM(A.SalesAmount) AS MonthlySales

FROM Budget B LEFT JOIN Actual A ON (B.Year = A.Year AND B.Month=A.Month)

...

Then your report would be really easy.

If there are some SSRS Smarties out there, could you please confirm/refute my understanding of SSRS? Thanks!

Pieter

alex_martinez SSCrazy Points: 2144 More actions · Answer 4

pietlinden,

Thanks, I will do what you suggested I really appreicate your time in helping me out.

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Alex,

Glad to help. Give it a try and post back if you get stuck. If you need help. post the CREATE TABLE scripts for the Budget and Actual tables.

Here's a quick example (so some of the conventions are horrible...):

CREATE TABLE #Budget(

ProductName VARCHAR(10) PRIMARY KEY

,BudgetAmount SMALLMONEY);

CREATE TABLE #Actual(

ProductName VARCHAR(10)

,ActualSales SMALLMONEY );

GO

INSERT INTO #Budget(ProductName, BudgetAmount)

VALUES ('Widget 1', 500.00),

('Widget 2',1000.00);

INSERT INTO #Actual(ProductName, ActualSales)

VALUES ('Widget 1', 100.00),('Widget 1', 200.00),('Widget 1',300.00);

SELECT b.ProductName

, b.BudgetAmount

, COALESCE(SUM(a.ActualSales),0) AS TotalSales

FROM #Budget b LEFT JOIN #Actual a

ON b.ProductName = a.ProductName

GROUP BY b.ProductName

, b.BudgetAmount;

Good luck,

Pieter

alex_martinez SSCrazy Points: 2144 More actions · Answer 6

pietlinden,

I will give this a shot I see where you going and looks like what I needed. Thanks.

pietlinden SSC Guru Points: 63503 More actions · Answer 7

If, for some odd reason, you can have actuals records with no budget, and the reverse, then you would need a full outer join between the two tables, not an right/left outer join.

Mock it up with a few records and you'll see the difference.