Stored Procedure com parâmetros, exibição com filtro ou todos os valores

Question

Boa noite pessoal.

Estou fazendo um relatório SSRS que faz uma consulta por uma proc. O relatório tem alguns parâmetros de filtro, porem não estou conseguindo fazer a consulta de forma correta quando o filtro fica em "todos" por exemplo.

O parâmetro @ATIVO teria a opção de ter um texto ou Todos e mostrar todos. Essa condição existe para os parâmetros @ATIVO, @CATEGORIA e @TIPO, ambos possuem a opção de todos ou um determinado valor.

Se alguém poder dar uma luz ae agradeço!!

Abaixo o código:

declare
@START AS DATE = '2021-01-04',
@Eslam Nader AS DATE = '2021-01-26',
@ATIVO AS NVARCHAR (10) = '',
@CATEGORIA AS INT = 99,
@TIPO AS NVARCHAR (10) = ''

SELECT * FROM NEGOCIO WHERE
NEG_DATA BETWEEN @START AND @Eslam Nader AND
NEG_ATIVO = @ATIVO AND
NEG_TIPO_NEGOCIO = @TIPO AND
NEG_ACAO = @CATEGORIA

Answer 1

Hi @Miguel Marques ,

Could you describe the issue again in English instead of Portuguese? Although I tried to use Google to translate the content, it was not very clear.
If you are filtering parameters in SSRS, could you attach the report structure?
Best Regards，
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Good night @Joyzhao-MSFT

I'm doing a SSRS report that queries with a storedprocedure. The report has some filter parameters, however I can't to make the query correctly when the filter is "all" for example.

The @ATIVO parameter would have the option of having a text or "All" and showing all or same text. This condition exists for the parameters @ATIVO, @CATEGORIA and @TIPO, both have the option "all"to show all itens or a choose certain value.

Below the code:

declare
@START AS DATE = '2021-01-04',
@Eslam Nader AS DATE = '2021-01-26',
@rooy AS NVARCHAR (10) = '',
@CATEGORIA AS INT = 99,
@type AS NVARCHAR (10) = ''

SELECT * FROM NEGOCIO WHERE
NEG_DATA BETWEEN @START AND @Eslam Nader AND
NEG_ATIVO = @ATIVO AND
NEG_TIPO_NEGOCIO = @TIPO AND
NEG_ACAO = @CATEGORIA

The picture below show a drop list with options to show. The option "Todos" show all the values, the option "Compra" filter to only this value

This picture below show de query of SSRS

Answer 3

What is the value for the parameter 'Todas'? Let's assume it is NULL:

 Select *  
   From NEGOCIO  
  Where NEG_DATA Between @START And @END  
    And (NEG_ATIVO = @ATIVO Or @ATIVO Is Null)  
    And (NEG_TIPO_NEGOCIO = @TIPO Or @TIPO Is Null)  
    And (NEG_ACAO = @CATEGORIA Or @CATEGORIA Is Null)  

What this does is check the parameter values - and if they are set to NULL from the drop-down then all values will be selected for that parameter. If you are defining a 'default' value that will be used for 'All Rows', for example - the string 'All' then change the above to this:

 Select *  
   From NEGOCIO  
  Where NEG_DATA Between @START And @END  
    And (NEG_ATIVO = @ATIVO Or @ATIVO = 'All')  
    And (NEG_TIPO_NEGOCIO = @TIPO Or @TIPO = 'All')  
    And (NEG_ACAO = @CATEGORIA Or @CATEGORIA = 'All')