CODEWITHSUNDEEP
c#reporting-servicesreporting
Vincenzo Di Roberto
Vincenzo Di Roberto

Reputation: 33

Dynamically command text into Reporting Services

I have a problem with a query in a report created by SQL Server Reporting Services and Visual Studio .NET.

I have a field of type string that sometimes in the database can be NULL and prevents me from making a simple search using LIKE '%%'. My idea would be to insert a clause in the CommandText IIF report so that if that field is NULL, actually I assigned as the default value '%', is not carried out any research that would otherwise be added to the list of the fields searched by LIKE.

The field is Parameters.NOTE and the IIF clause I thought would use:

IIF (Trim (Parameters! NOTE.Value) = "%", "", "AND AD.NOTE ILIKE%" + Parameters! NOTE.Value + "% ").

The main problem is that the report does not give me any error but even I returns no value. I can not understand how to do.

 <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 &lt;= 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') &gt;= to_date(@INIZIO, 'DD-MM-YYYY') AND
    to_date(to_char(AD.DataInserimento, 'DD-MM-YYYY'), 'DD-MM-YYYY') &lt;=   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>

Upvotes: 0

Views: 368

Answers (1)

Mahesh
Mahesh

Reputation: 8892

Use the parameter to pass to the query.Suppose the parameter name is @Note. Set the @Note value to the Parameters!NOTE.Value. And make sure you make both the parameter in Report and in the query at both places Null by dfault. You can do the condition like,

 AND (@Note IS NULL OR AD.NOTE LIKE +'%'@Note+'%')

This will first check that if the @Note value is null if null then it will not go for next filter if not null then it will go for the filter of Like.

Upvotes: 1

Related Questions