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

Miguel Marques 1 Reputation point
2021-01-27T23:45:08.28+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,855 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,818 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-01-28T02:47:46.707+00:00

    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.

    0 comments No comments

  2. Miguel Marques 1 Reputation point
    2021-01-30T23:40:56.43+00:00

    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

    62232-captura-de-tela-2021-01-30-as-202423.png

    This picture below show de query of SSRS
    62158-captura-de-tela-2021-01-30-as-202542.png

    0 comments No comments

  3. Williams, Jeffrey A 481 Reputation points
    2021-01-31T17:13:49.887+00:00

    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')  
      
    
    0 comments No comments