Join Filter Columns

Feedback and Requests
1

Good Day to every one

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.

any suggestion will be much appreciated.

2

Can you share the query you are using?

3

Hi Jornh

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

where [[{{SalesTeritory}}]]

order by
[%Moved] desc

drop table #lynet

image
image1320×530 56.7 KB

4

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