I'm not very good at SQL or SSRS so please excuse any incorrect terminology. I work at a wood shop and I'm editing a parts report which has an existing query that returns separate fields that contain duplicate data. One of the fields is a direct select from some joins, the other is a sub-query that is aliased. I want to use the sub-query field only to be consistent.
I try to set the tablix filter to [MAT_DESC] <> (leave blank) but the tablix does not filter. [MATNAME] <> (leave blank) works. not(isnothing([MAT_DESC])) = True also works.
WITH ORDERLIST AS (SELECT ... FROM ... WHERE...)
SELECT
IDBGPL.MATNAME, --THIS ONE WILL FILTER
(SELECT MAT.TEXT FROM MAT WHERE MAT.NAME=IDBGPL.MATID) AS MAT_DESC, --THIS ONE WON'T FILTER
(SELECT MAT.ORDERID FROM MAT WHERE MAT.NAME=IDBGPL.MATID) AS MAT_DESC2, --THIS ONE IS ALSO USED AND COMES FROM THE SAME TABLE
FROM ORDERLIST
INNER JOIN...
INNER JOIN...
INNER JOIN...
When I try to filter a table with the sub-query field it doesn't work. When I use the directly selected field it does. Why does SSRS treat the sub-query field differently?
EDIT: For some clarification. The data is coming from a CAD/CAM program. The IDBGPL table has every part in every order in the system. The MAT table is a section of the program that describes each material. There are some parent/child parts where the parent does not have a material. I'm wanting to filter out those parent parts.
This could potentially return NULL:
(SELECT MAT.TEXT FROM MAT WHERE MAT.NAME=IDBGPL.MATID) AS MAT_DESC
You cannot evaluate a NULL value other than checking for it being NULL or not NULL.
So one solution is never let it be null:
ISNULL((SELECT MAT.TEXT FROM MAT WHERE MAT.NAME=IDBGPL.MATID),"") AS MAT_DESC
Another solution is check for it being NULL on the outside (in SSRS), so checking for NULL or blank. You just need to understand that they are not the same value.
Also you should consider doing a LEFT JOIN to mat, rather than using sub-queries.