question

MiguelMarques-2974 avatar image
0 Votes"
MiguelMarques-2974 asked WilliamsJeffreyA-3555 answered

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

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',
@END 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 @END AND
NEG_ATIVO = @ATIVO AND
NEG_TIPO_NEGOCIO = @TIPO AND
NEG_ACAO = @CATEGORIA

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @MiguelMarques-2974 ,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiguelMarques-2974 avatar image
0 Votes"
MiguelMarques-2974 answered

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',
@END AS DATE = '2021-01-26',
@ACTIVE AS NVARCHAR (10) = '',
@CATEGORIA AS INT = 99,
@TYPE AS NVARCHAR (10) = ''

SELECT * FROM NEGOCIO WHERE
NEG_DATA BETWEEN @START AND @END 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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

WilliamsJeffreyA-3555 avatar image
0 Votes"
WilliamsJeffreyA-3555 answered

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')
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.