#sql #sql-server #reporting-services
#sql #sql-сервер #службы отчетов
Вопрос:
Необходима помощь, чтобы понять, почему запрос в отчете ssrs не возвращает строки, где столбец равен null, 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'"amp; Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList1_Values.Value) amp;"' 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'"amp; Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList2_Values.Value) amp;"' 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'"amp; Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList3_Values.Value) amp;"' 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'"amp; Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList4_Values.Value) amp;"' 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'"amp;Parameters!Parameter_AttrbitesList1.Value amp;"'
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'"amp;Parameters!Parameter_AttrbitesList2.Value amp;"'
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'"amp;Parameters!Parameter_AttrbitesList3.Value amp;"'
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'"amp;Parameters!Parameter_AttrbitesList4.Value amp;"'
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
(("amp; Code.ParamSQL_Parameter_OUObject_Like(Parameters!Parameter_OUObject_Like.Value) amp;") AND (NOT ("amp; Code.ParamSQL_Parameter_OUObject_NotLike(Parameters!Parameter_OUObject_NotLike.Value) amp;")))
AND
(("amp; Code.ParamSQL_Parameter_UserObject_Like(Parameters!Parameter_UserObject_Like.Value) amp;") AND (NOT("amp; Code.ParamSQL_Parameter_UserObject_NotLike(Parameters!Parameter_UserObject_NotLike.Value) amp;")))
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'"amp;Parameters!Parameter_AttrbitesList1.Value amp;"' AS [PropertyName],
P2.PValue AS Value2,
N'"amp;Parameters!Parameter_AttrbitesList2.Value amp;"' AS [PropertyName2],
P3.PValue AS Value3,
N'"amp;Parameters!Parameter_AttrbitesList3.Value amp;"' AS [PropertyName3],
P4.PValue AS Value4,
N'"amp;Parameters!Parameter_AttrbitesList4.Value amp;"' 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 (" amp; Code.ParamSQL_Parameter_Domains_In_5_1_Item1(Parameters!Parameter_Domains_In_5_1.Value) amp; ")
ORDER BY 1, "amp; Parameters!Parameter_Sorting.Value amp;" 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
```
Комментарии:
1. Я сомневаюсь, что кто-нибудь попытается прочитать / понять этот запрос без схемы или данных available…So , запустите запрос из конструктора запросов набора данных SSRS. Результаты, которые вы видите, — это то, что будет передано в отчет. Результаты выглядят нормально, затем посмотрите на фильтры в вашем отчете и т. Д. Если результаты отличаются от SSMS, запустите трассировку на сервере и точно зафиксируйте, что выполняется на сервере в каждом экземпляре, оттуда вы сможете определить, что отличается
Ответ №1:
SSRS ненавидит нули. они также всегда вызывали у меня много проблем. они на самом деле не работают в выпадающих списках и плохо отображаются. Я просто добавляю ISNULL(FIELDNAME, ‘NULL’) ко всем полям в SELECT, чтобы удалить их
Я знаю, что это не совсем то, о чем вы спрашиваете, но это решит вашу проблему недисплея.