时间戳

首页 > 解决方案 > Running SSRS Query gives blank results

sql - Running SSRS Query gives blank results

问题描述

When I run this Query in SSRS the table does not fill out. There should be about 200 rows but all I get is this:

table with one blank row

Here is the Query:

SELECT
    tblDataPermit.ApplicationNumber
   ,tblDataParcel.StreetNum
   ,tblDataParcel.StreetName
   ,tblDataPermit.Applicant
   ,tblDataPermit.Status
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="Text4Size10"])[1]', 'nvarchar(max)') AS Cycle
   ,tblDataPermit.ApplicantPhone AS BusPhone
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="Text2Size50"])[1]', 'nvarchar(max)') AS ContactName,
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="Text1Size10"])[1]', 'nvarchar(max)') AS ConPhone
   ,tblDataPermit.ApplicationType AS Grp,
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo1"])[1]', 'nvarchar(max)') AS A
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo2"])[1]', 'nvarchar(max)') AS E
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo3"])[1]', 'nvarchar(max)') AS F
   ,tblDataPermit.XmlDataField.value('(//*[local-name()="YesNo3"])[1]', 'nvarchar(max)') AS S
   ,ExpirationDates.ExpirationDate AS Due
   ,IStatuss.Status AS ADate
   ,MaxCompletionDates.MaxCompletionDate
   ,SysCod.CodRcdTyp
   ,SysCod.CodLabel
   ,SysCod.CodOrder
FROM
   SysCod

INNER JOIN tblDataPermit
    ON SysCod.CodOrder = tblDataPermit.Type

INNER JOIN tblDataParcel ON tblDataPermit.ParcelID = tblDataParcel.ParcelID 

LEFT OUTER JOIN
(SELECT ProjectNumber, ExpirationDate FROM tblDataPermit AS tblDataPermit_2 WHERE (Type = '54')) AS ExpirationDates ON tblDataPermit.ApplicationNumber = ExpirationDates.ProjectNumber 

INNER JOIN
(SELECT ProjectNumber, Status FROM tblDataPermit AS tblDataPermit_3 WHERE (Type = '54')) AS IStatuss ON tblDataPermit.ProjectNumber = IStatuss.ProjectNumber 

LEFT OUTER JOIN
(SELECT MAX(tblDataActions.CompletionDate) AS MaxCompletionDate, tblDataPermit_1.Type, tblDataPermit_1.ProjectNumber

FROM tblDataActions INNER JOIN tblDataPermit AS tblDataPermit_1 ON tblDataActions.ApplicationNumber = tblDataPermit_1.ApplicationNumber

GROUP BY tblDataPermit_1.Type, tblDataPermit_1.ProjectNumber
HAVING (tblDataPermit_1.Type = '54')) AS MaxCompletionDates ON tblDataPermit.ApplicationNumber = MaxCompletionDates.ProjectNumber

WHERE (tblDataPermit.Type = '72') 
AND CodLabel IN (@AppStatus) 
AND XmlDataField.value('(//*[local-name()="Text4Size10"])[1]', 'nvarchar(max)') IN (@Cycle)

Here is the report data: report data

AppStatusDataSet

ActionStatusDataSet

The main tables are tblDataPermit and tblDataActions. These tables get the actual labels for Type and Status from the table SysCod. Here are exaples of each table:

tblDataPermit

tblDataActions

SysCod

标签： sqlsql-serverreporting-services

解决方案

推荐阅读