March 2, 2016 at 12:31 pm
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
March 2, 2016 at 1:16 pm
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.
March 3, 2016 at 9:03 am
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.
March 3, 2016 at 9:10 am
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
March 3, 2016 at 11:53 am
pietlinden,
Thanks, I will do what you suggested I really appreicate your time in helping me out.
March 3, 2016 at 12:03 pm
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
March 18, 2016 at 4:36 pm
pietlinden,
I will give this a shot I see where you going and looks like what I needed. Thanks.
March 18, 2016 at 5:55 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply