I am having a bit of a problem using joined columns as field filters
i get the bellow error when i use a joined tables column as my filter.
e.g.
i join using a key field
and the associated columns i want to use are only on the joined table the only column that is the same as primary table is is the key column used to join.
Error:
com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier “dbo.NandosHusseinDailySalesSummary.Financial Year” could not be bound.
the query is like the bellow and I get the error in the image
select
[SalesAmount] as Net
,fsi1.SalesTerritoryKey
,sl.SalesTerritoryRegion
into #lynet
from FactInternetSales fsi1
inner join DimSalesTerritory sl on fsi1.SalesTerritoryKey = sl.SalesTerritoryKey
inner join DimDate d on fsi1.DueDateKey = d.DateKey
where d.CalendarYear = 2011
--######################################TempTable###########################################--
select d.Net
,d.LYNet
,cast(d.[%Moved] as varchar(10))+'%' [%Moved]
,d.SalesTerritoryRegion
,d.WeekNumberOfYear
,d.CalendarYear
from
(
select
cast(Sum(fsi.[SalesAmount]) as Decimal(10,2)) as Net
,cast(sum(lyn.Net) as decimal(10,2) ) as LYNet
,cast((sum(fsi.SalesAmount) - sum(lyn.Net)) / sum(lyn.Net)*100 as decimal(10,2) ) as [%Moved]
,d.WeekNumberOfYear
,d.CalendarYear
,sl.SalesTerritoryRegion
from FactInternetSales fsi
inner join DimSalesTerritory sl on fsi.SalesTerritoryKey = sl.SalesTerritoryKey
inner join DimDate d on fsi.DueDateKey = d.DateKey
inner join #lynet lyn on lyn.SalesTerritoryKey = fsi.SalesTerritoryKey
where d.CalendarYear in (2012,2011)
group by
d.WeekNumberOfYear
,d.CalendarYear
,sl.SalesTerritoryRegion
,sl.SalesTerritoryCountry
)D
What i would suggest as a possible solution is to add an optional field in the Variable side menu that will allow the user to select their desired target join table using the join TABLE ALIAS
inner join DimSalesTerritory SL on fsi.SalesTerritoryKey = SL.SalesTerritoryKey
e.g. for the first response SL to be able to use the SalesTerritoryRegion as a filter. The database used is AdventureWorksDW2014