将文本动态命令到Reporting Services中
本文关键字:Reporting Services 命令 文本 动态 | 更新日期: 2024-09-20 17:56:15
我在SQL Server Reporting Services和Visual Studio.NET.创建的报表中遇到查询问题
我有一个字符串类型的字段,有时在数据库中可能为NULL,这使我无法使用LIKE"%%"进行简单搜索。我的想法是在CommandText
IIF报告中插入一个子句,这样,如果该字段为NULL,实际上我指定为默认值"%",则不会进行任何研究,否则这些研究将添加到LIKE搜索的字段列表中。
字段是Parameters.NOTE
,我认为应该使用IIF
子句:
IIF (Trim (Parameters! NOTE.Value) = "%", "", "AND AD.NOTE ILIKE%" + Parameters! NOTE.Value + "% ").
主要问题是报告没有给我任何错误,但即使我也没有返回任何值。我不明白该怎么做。
<Query>
<DataSourceName>DS1</DataSourceName>
<CommandText>
SELECT
ANG.Cognome || ' ' || ANG.Nome AS NomeDipendente,
R.Descrizione AS Reparto,
AD.Anno,
AD.Mese,
AD.DataInserimento,
TP.Descrizione AS TipoPagamento,
AD.Importo,
AD.Note,
getRetribuzioneDipendente(AD.Dipendente, AD.Mese, AD.Anno) AS Retribuzione,
(SELECT
SUM(AD1.Importo)
FROM
AccontiDipendenti AD1
WHERE
AD1.Dipendente = AD.Dipendente AND
AD1.Mese = AD.Mese AND
AD1.Anno = AD.Anno AND
AD1.DataInserimento <= AD.DataInserimento
)
AS Acconti,
U.NickName
FROM
AccontiDipendenti AD
INNER JOIN
AnagraficaDipendenti ANG ON (AD.Dipendente = ANG.CodiceDipendente)
INNER JOIN
MansioniDipendenti MD ON (ANG.CodiceMansione = MD.CodiceMansione)
INNER JOIN
Reparti R ON (MD.CodiceReparto = R.CodiceReparto)
INNER JOIN
Utenti U ON (AD.UserName = U.UserName)
INNER JOIN
TipiPagamento TP ON (AD.CodiceTipoPag = TP.CodiceTipoPag)
WHERE
ANG.CodiceDipendente ILIKE COALESCE(@CODICEDIPENDENTE, '%') AND
R.CodiceReparto ILIKE COALESCE(@CODICEREPARTO, '%') AND
AD.CodiceTipoPag ILIKE COALESCE(@CODICETIPOPAG, '%') AND
to_date(to_char(AD.DataInserimento, 'DD-MM-YYYY'), 'DD-MM-YYYY') >= to_date(@INIZIO, 'DD-MM-YYYY') AND
to_date(to_char(AD.DataInserimento, 'DD-MM-YYYY'), 'DD-MM-YYYY') <= to_date(@FINE, 'DD-MM-YYYY')
IIF(Trim(Parameters!NOTE.Value)="%", "" , " AND AD.NOTE ILIKE %" + Parameters!NOTE.Value + "%")
</CommandText>
<QueryParameters>
<QueryParameter Name="NOTEASSEGNO">
<Value>=IIF(Trim(Parameters!NOTE.Value)="%", "%" , "%" + Parameters!NOTE.Value + "%")</Value>
</QueryParameter>
<QueryParameter Name="CODICEDIPENDENTE">
<Value>=Parameters!CODICEDIPENDENTE.Value</Value>
</QueryParameter>
<QueryParameter Name="CODICEREPARTO">
<Value>=Parameters!CODICEREPARTO.Value</Value>
</QueryParameter>
<QueryParameter Name="INIZIO">
<Value>=Parameters!DATAINIZIO.Value</Value>
</QueryParameter>
<QueryParameter Name="FINE">
<Value>=Parameters!DATAFINE.Value</Value>
</QueryParameter>
<QueryParameter Name="CODICETIPOPAG">
<Value>=Parameters!CODICETIPOPAG.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
使用参数传递给查询。假设参数名称为@Note
。将@Note
值设置为Parameters!NOTE.Value
。并确保通过dfault将Report和查询中的参数都设置为Null
。你可以做这样的条件,
AND (@Note IS NULL OR AD.NOTE LIKE +'%'@Note+'%')
这将首先检查如果@Note值为null(如果为null),那么它将不会进入下一个过滤器(如果不为null)。然后它将进入Like
的过滤器。