需要帮助理解为什么查询在ssrs报告中不返回列为空的行,在报表中指定4个属性(标题、姓名、经理、公司),报表只返回所有4个属性都包含值的行。ssms显示许多用户的管理器名称值为NULL。
这份报告是根据另一份报告重新构建的,所以我没有写原始的
CREATE TABLE #TempMacro_PV (mask VARCHAR(8000))
CREATE TABLE #TempMacro_PV2 (mask VARCHAR(8000))
CREATE TABLE #TempMacro_PV3 (mask VARCHAR(8000))
CREATE TABLE #TempMacro_PV4 (mask VARCHAR(8000))
DECLARE @FirstStr NVARCHAR(4000)
DECLARE @i INT, @j INT
SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList1_Values.Value) &"' AS NVARCHAR(4000))
SET @i = - 1
WHILE @i <> 0
BEGIN
IF @i < 0
SET @i = 0
SET @j = CHARINDEX(';', @FirstStr, @i + 1)
SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j END
INSERT INTO #TempMacro_PV VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))
SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END
SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList2_Values.Value) &"' AS NVARCHAR(4000))
SET @i = - 1
WHILE @i <> 0
BEGIN
IF @i < 0
SET @i = 0
SET @j = CHARINDEX(';', @FirstStr, @i + 1)
SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j
END
INSERT INTO #TempMacro_PV2 VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))
SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END
SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList3_Values.Value) &"' AS NVARCHAR(4000))
SET @i = - 1
WHILE @i <> 0
BEGIN
IF @i < 0
SET @i = 0
SET @j = CHARINDEX(';', @FirstStr, @i + 1)
SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j END
INSERT INTO #TempMacro_PV3 VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))
SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END
SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList4_Values.Value) &"' AS NVARCHAR(4000))
SET @i = - 1
WHILE @i <> 0
BEGIN
IF @i < 0
SET @i = 0
SET @j = CHARINDEX(';', @FirstStr, @i + 1)
SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j END
INSERT INTO #TempMacro_PV4 VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))
SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END
SELECT DISTINCT
CASE WHEN Parent.ObjectPath collate database_default LIKE '%/' collate database_default THEN SUBSTRING (Parent.ObjectPath, 1, len(Parent.ObjectPath) - 1) ELSE Parent.ObjectPath END collate database_default AS [ParentName],
Object.ObjectName AS [ObjectName],
CAST(
CASE
WHEN Object.objectpath collate database_default LIKE '%[^]/%' THEN SUBSTRING(Object.objectpath, 1, len(Object.objectpath) - patindex('%/[^]%', reverse(Object.objectpath))) collate database_default
WHEN Object.objectpath collate database_default LIKE '%,ou=%' THEN SUBSTRING(Object.objectpath, charindex(',', Object.objectpath) + 1, LEN(Object.objectpath)) collate database_default
ELSE ''
END
AS NVARCHAR(2000)
) AS [Container],
pd.Type AS [Type],
pd2.Type AS [Type2],
pd3.Type AS [Type3],
pd4.Type AS [Type4],
pd.pk AS [PropertyID],
pd2.pk AS [PropertyID2],
pd3.pk AS [PropertyID3],
pd4.pk AS [PropertyID4],
Object.pk AS [pk]
INTO
#TempMacro_V1
FROM
ObjectsObjects AS Object
INNER JOIN ObjectsPropertyValues AS pv ON Object.pk = pv.objectid
INNER JOIN PropertyDescriptions AS pd ON pd.pk = pv.PropertyID AND pd.PropertyName = N'"&Parameters!Parameter_AttrbitesList1.Value &"'
INNER JOIN(SELECT mask FROM #TempMacro_PV AS pf) AS t1 ON
pv.Value_String collate database_default LIKE t1.mask collate database_default
OR CAST(pv.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t1.mask collate database_default
OR CONVERT(NVARCHAR(255), pv.Value_Time, 120) collate database_default LIKE t1.mask collate database_default
OR CAST(pv.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t1.mask collate database_default
INNER JOIN ObjectsPropertyValues AS pv2 ON Object.pk = pv2.objectid
INNER JOIN PropertyDescriptions AS pd2 ON pd2.pk = pv2.PropertyID AND pd2.PropertyName = N'"&Parameters!Parameter_AttrbitesList2.Value &"'
INNER JOIN(SELECT mask FROM #TempMacro_PV2 AS pf) AS t2 ON
pv2.Value_String collate database_default LIKE t2.mask collate database_default
OR CAST(pv2.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t2.mask collate database_default
OR CONVERT(NVARCHAR(255), pv2.Value_Time, 120) collate database_default LIKE t2.mask collate database_default
OR CAST(pv2.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t2.mask collate database_default
INNER JOIN ObjectsPropertyValues AS pv3 ON Object.pk = pv3.objectid
INNER JOIN PropertyDescriptions AS pd3 ON pd3.pk = pv3.PropertyID AND pd3.PropertyName = N'"&Parameters!Parameter_AttrbitesList3.Value &"'
INNER JOIN(SELECT mask FROM #TempMacro_PV3 AS pf) AS t3 ON
pv3.Value_String collate database_default LIKE t3.mask collate database_default
OR CAST(pv3.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t3.mask collate database_default
OR CONVERT(NVARCHAR(255), pv3.Value_Time, 120) collate database_default LIKE t3.mask collate database_default
OR CAST(pv3.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t3.mask collate database_default
INNER JOIN ObjectsPropertyValues AS pv4 ON Object.pk = pv4.objectid
INNER JOIN PropertyDescriptions AS pd4 ON pd4.pk = pv4.PropertyID AND pd4.PropertyName = N'"&Parameters!Parameter_AttrbitesList4.Value &"'
INNER JOIN(SELECT mask FROM #TempMacro_PV4 AS pf) AS t4 ON
pv4.Value_String collate database_default LIKE t4.mask collate database_default
OR CAST(pv4.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t4.mask collate database_default
OR CONVERT(NVARCHAR(255), pv4.Value_Time, 120) collate database_default LIKE t4.mask collate database_default
OR CAST(pv4.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t4.mask collate database_default
INNER JOIN ObjectsObjects AS Parent ON Object.ObjectParent = Parent.pk
WHERE
Object.Tag = 1 AND ISNULL(Object.ObjectPath, '') <> ''
AND Object.ObjectType = 'User' AND pd.Type IN('VARCHAR','INTEGER','DATETIME','DOUBLE') AND pd2.Type IN('VARCHAR','INTEGER','DATETIME','DOUBLE') AND pd3.Type IN('VARCHAR','INTEGER','DATETIME','DOUBLE')
AND
(("& Code.ParamSQL_Parameter_OUObject_Like(Parameters!Parameter_OUObject_Like.Value) &") AND (NOT ("& Code.ParamSQL_Parameter_OUObject_NotLike(Parameters!Parameter_OUObject_NotLike.Value) &")))
AND
(("& Code.ParamSQL_Parameter_UserObject_Like(Parameters!Parameter_UserObject_Like.Value) &") AND (NOT("& Code.ParamSQL_Parameter_UserObject_NotLike(Parameters!Parameter_UserObject_NotLike.Value) &")))
SELECT Object.ParentName AS [ParentName],
Object.ObjectName AS [ObjectName],
Object.Container AS [Container],
CASE Object.Type WHEN 'VARCHAR' THEN pv.Value_String
WHEN 'INTEGER' THEN CAST(pv.Value_Integer AS NVARCHAR(1024))
WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv.Value_Time, 120)
WHEN 'DOUBLE' THEN CAST(pv.Value_Double AS NVARCHAR(1024))
ELSE '' END AS [Value],
pv.ValueNumber AS [ValueNumber],
CASE Object.Type2 WHEN 'VARCHAR' THEN pv2.Value_String
WHEN 'INTEGER' THEN CAST(pv2.Value_Integer AS NVARCHAR(1024))
WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv2.Value_Time, 120)
WHEN 'DOUBLE' THEN CAST(pv2.Value_Double AS NVARCHAR(1024))
ELSE '' END AS [Value2],
pv2.ValueNumber AS [ValueNumber2],
CASE Object.Type2 WHEN 'VARCHAR' THEN pv3.Value_String
WHEN 'INTEGER' THEN CAST(pv3.Value_Integer AS NVARCHAR(1024))
WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv3.Value_Time, 120)
WHEN 'DOUBLE' THEN CAST(pv3.Value_Double AS NVARCHAR(1024))
ELSE '' END AS [Value3],
pv3.ValueNumber AS [ValueNumber3],
CASE Object.Type4 WHEN 'VARCHAR' THEN pv4.Value_String
WHEN 'INTEGER' THEN CAST(pv4.Value_Integer AS NVARCHAR(1024))
WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv4.Value_Time, 120)
WHEN 'DOUBLE' THEN CAST(pv4.Value_Double AS NVARCHAR(1024))
ELSE '' END AS [Value4],
pv4.ValueNumber AS [ValueNumber4],
Object.pk AS [pk]
INTO #TempMacro_V
FROM
#TempMacro_V1 AS Object
INNER JOIN ObjectsPropertyValues AS pv ON Object.pk = pv.objectid AND Object.PropertyID = pv.PropertyID
INNER JOIN ObjectsPropertyValues AS pv2 ON Object.pk = pv2.objectid AND Object.PropertyID2 = pv2.PropertyID
INNER JOIN ObjectsPropertyValues AS pv3 ON Object.pk = pv3.objectid AND Object.PropertyID3 = pv3.PropertyID
INNER JOIN ObjectsPropertyValues AS pv4 ON Object.pk = pv4.objectid AND Object.PropertyID4 = pv4.PropertyID
SELECT PK, [Value], ValueNumber, Value2, ValueNumber2, Value3, ValueNumber3, Value4, ValueNumber4
INTO #TempMacro_VV
FROM #TempMacro_V
CREATE CLUSTERED INDEX idv ON #TempMacro_VV (PK)
/*Insertion of 08.02.2010 - beginning*/
DECLARE @CountVal INT, @ValLen INT
SELECT @CountVal = MAX(ValueNumber) FROM #TempMacro_VV
SET @ValLen = 1500
SELECT DISTINCT
P.PK AS PK,
CAST(P.[Value] AS NVARCHAR(1500)) AS PValue
INTO #TempMacro_P1
FROM #TempMacro_VV AS P
WHERE P.ValueNumber = 0
CREATE CLUSTERED INDEX id1 ON #TempMacro_P1 (PK)
SET @I = 1
WHILE @I <= @CountVal
BEGIN
UPDATE #TempMacro_P1
SET PValue = CASE WHEN (LEN(PValue) + LEN(P.[Value])) < (@ValLen - 2) THEN PValue + '; ' + P.[Value] ELSE PValue END
FROM #TempMacro_P1 AS P1
INNER JOIN #TempMacro_VV AS P ON P1.PK = P.PK
WHERE P.ValueNumber = @I
SET @I = @I + 1
END
DECLARE @CountVal2 INT
SELECT @CountVal2 = MAX(ValueNumber2) FROM #TempMacro_VV
SELECT DISTINCT
P.PK AS PK,
CAST(P.[Value2] AS NVARCHAR(1500)) AS PValue
INTO #TempMacro_P2
FROM #TempMacro_VV AS P
WHERE P.ValueNumber2 = 0
CREATE CLUSTERED INDEX id2 ON #TempMacro_P2 (pk)
SET @I = 1
WHILE @I <= @CountVal2
BEGIN
UPDATE #TempMacro_P2
SET PValue = CASE WHEN (LEN(PValue) + LEN(P.Value2)) < (@ValLen - 2) THEN PValue + '; ' + P.Value2 ELSE PValue END
FROM #TempMacro_P2 AS P2
INNER JOIN #TempMacro_VV AS P ON P2.PK = P.PK
WHERE P.ValueNumber2 = @I
SET @I = @I + 1
END
DECLARE @CountVal3 INT
SELECT @CountVal3 = MAX(ValueNumber3) FROM #TempMacro_VV
SET @ValLen = 1500
SELECT DISTINCT
P.PK AS PK,
CAST(P.[Value3] AS NVARCHAR(1500)) AS PValue
INTO #TempMacro_P3
FROM #TempMacro_VV AS P
WHERE P.ValueNumber3 = 0
CREATE CLUSTERED INDEX id3 ON #TempMacro_P3 (PK)
SET @I = 1
WHILE @I <= @CountVal
BEGIN
UPDATE #TempMacro_P3
SET PValue = CASE WHEN (LEN(PValue) + LEN(P.[Value3])) < (@ValLen - 2) THEN PValue + '; ' + P.Value3 ELSE PValue END
FROM #TempMacro_P3 AS P3
INNER JOIN #TempMacro_VV AS P ON P3.PK = P.PK
WHERE P.ValueNumber3 = @I
SET @I = @I + 1
END
DECLARE @CountVal4 INT
SELECT @CountVal4 = MAX(ValueNumber4) FROM #TempMacro_VV
SET @ValLen = 1500
SELECT DISTINCT
P.PK AS PK,
CAST(P.[Value4] AS NVARCHAR(1500)) AS PValue
INTO #TempMacro_P4
FROM #TempMacro_VV AS P
WHERE P.ValueNumber4 = 0
CREATE CLUSTERED INDEX id4 ON #TempMacro_P4 (PK)
SET @I = 1
WHILE @I <= @CountVal
BEGIN
UPDATE #TempMacro_P4
SET PValue = CASE WHEN (LEN(PValue) + LEN(P.[Value4])) < (@ValLen - 2) THEN PValue + '; ' + P.Value4 ELSE PValue END
FROM #TempMacro_P4 AS P4
INNER JOIN #TempMacro_VV AS P ON P4.PK = P.PK
WHERE P.ValueNumber4 = @I
SET @I = @I + 1
END
/*Insertion of 08.02.2010 - end*/
SELECT
Objects.ParentName AS [ParentName],
Objects.ObjectName AS [ObjectName],
P1.PValue AS [Value],
N'"&Parameters!Parameter_AttrbitesList1.Value &"' AS [PropertyName],
P2.PValue AS Value2,
N'"&Parameters!Parameter_AttrbitesList2.Value &"' AS [PropertyName2],
P3.PValue AS Value3,
N'"&Parameters!Parameter_AttrbitesList3.Value &"' AS [PropertyName3],
P4.PValue AS Value4,
N'"&Parameters!Parameter_AttrbitesList4.Value &"' AS [PropertyName4],
1 AS [S],
Objects.pk AS [pk]
FROM #TempMacro_P1 AS P1
INNER JOIN #TempMacro_P2 AS P2 ON P1.PK = P2.PK
INNER JOIN #TempMacro_P3 AS P3 ON P1.PK = P3.PK
INNER JOIN #TempMacro_P4 AS P4 ON P1.PK = P4.PK
LEFT JOIN #TempMacro_V AS Objects ON Objects.PK = P1.PK AND Objects.PK = P2.PK AND Objects.PK = P3.PK AND Objects.PK = P4.PK AND Objects.ValueNumber = 0 AND Objects.ValueNumber2 = 0 AND Objects.ValueNumber3 = 0 AND Objects.ValueNumber4 = 0
WHERE (" & Code.ParamSQL_Parameter_Domains_In_5_1_Item1(Parameters!Parameter_Domains_In_5_1.Value) & ")
ORDER BY 1, "& Parameters!Parameter_Sorting.Value &" ASC, 2 ASC
DROP TABLE #TempMacro_P1
DROP TABLE #TempMacro_P2
DROP TABLE #TempMacro_P3
DROP TABLE #TempMacro_PV
DROP TABLE #TempMacro_PV2
DROP TABLE #TempMacro_PV3
DROP TABLE #TempMacro_PV4
DROP TABLE #TempMacro_VV
```
SSRS讨厌null。他们也总是给我带来很多问题。它们不能在下拉菜单中工作,也不能很好地显示。我只是添加一个ISNULL(FIELDNAME, 'NULL')到SELECT中的所有字段以删除它们
我知道这不是你想要的,但它将解决你的非显示问题。