Controlando o range de tempo em um relatório do Reporting Services

Boa noite pessoal, semana passada recebi uma pergunta de como controlar para um usuário não passar mais que 30 dias entre dois parâmetros de data, limitando o range para 30 dias. Não é a primeira vez que recebo perguntas como esta, e geralmente, respondo aos e-mails que recebo pontualmente. Desta vez resolvi escrever um rápido post de como controlar isso. Existem várias maneiras de se controlar a interação com o usuário, então irei mostrar como controlar a execução de
um relatório, levando em conta o mês do parâmetro da data de inicio passada para gerar o relatório.

Então, não estou limitando o range a 30 dias de filtro, mas sim ao mês em questão do parâmetro inicial. Mas acredito que com base no que vocês estarão vendo aqui, será possível gerar controles de acordo com as suas necessidades!

Para este exemplo, estou utilizando o banco AdventureWorksDW2016CTP3.

Tudo começa no data set que você estará passando para o relatório. Neste exemplo estarei utilizando 4 parâmetros: @dt_begin, @dt_end, @month_control e @flag.

Abaixo, segue o código para gerar o data set:

IF @flag < @month_control
BEGIN
    SELECT
  D.FullDateAlternateKey, 
  SUM(FIS.OrderQuantity) AS OrderQuantity, 
  SUM(FIS.SalesAmount) AS SalesAmount
    FROM
  FactInternetSales AS FIS 
  INNER JOIN
        DimDate AS D ON FIS.OrderDateKey = D.DateKey
    WHERE  
  D.FullDateAlternateKey BETWEEN @dt_begin AND @dt_end
    GROUP BY 
  D.FullDateAlternateKey
    ORDER BY 
  D.FullDateAlternateKey
END
ELSE
BEGIN
    SELECT
  D.FullDateAlternateKey, 
  SUM(FIS.OrderQuantity) AS OrderQuantity, 
  SUM(FIS.SalesAmount) AS SalesAmount
    FROM
  FactInternetSales AS FIS 
  INNER JOIN
        DimDate AS D ON FIS.OrderDateKey = D.DateKey
    WHERE  
  D.FullDateAlternateKey BETWEEN @dt_begin AND DATEADD(day, -1, DATEADD(month, 1, @dt_begin))
    GROUP BY 
  D.FullDateAlternateKey
    ORDER BY 
  D.FullDateAlternateKey
END

Ao adicionar o dataset no relatório, você irá notar que os parâmetros serão criado automaticamente no Data Tools (você também pode utilizar o Reporting Buider para gerar o relatório).

Os parâmetros @dt_begin e @dt_end serão do tipo Date/Time. Agora, já os outros dois parâmetros serão do tipo inteiro. Esses parâmetros precisam ter seu default value configurados.

Além do tipo Inteiro, os parâmetros de controle serão de execução interna do relatório, conforme imagem acima.

Em Default Values, clique no botão de expressões. Agora vamos definir no parâmetro @month_control a quantidade de dias que cada mês tem para controlar qual consulta será executada no data set.

Entre com a seguinte expressão:

=SWITCH
(
 Month(Parameters!dt_begin.Value) = 1, 31,
 Month(Parameters!dt_begin.Value) = 2 AND Year(Parameters!dt_begin.Value) MOD 4 <> 0, 28,
 Month(Parameters!dt_begin.Value) = 2 AND Year(Parameters!dt_begin.Value) MOD 4 = 0, 29,
 Month(Parameters!dt_begin.Value) = 3, 31,
 Month(Parameters!dt_begin.Value) = 4, 30,
 Month(Parameters!dt_begin.Value) = 5, 31,
 Month(Parameters!dt_begin.Value) = 6, 30,
 Month(Parameters!dt_begin.Value) = 7, 31,
 Month(Parameters!dt_begin.Value) = 8, 31,
 Month(Parameters!dt_begin.Value) = 9, 30,
 Month(Parameters!dt_begin.Value) = 10, 31,
 Month(Parameters!dt_begin.Value) = 11, 30,
 Month(Parameters!dt_begin.Value) = 12, 31
)

Note que a expressão trata o mês de fevereiro para anos bissextos.

Agora, vamos configurar o parâmetro @flag para pegar a quantidade de dias entre as datas passadas nos parâmetros @dt_begin e @dt_end.

Repita o procedimento acima para o parâmetro @flag, utilizando a seguinte expressão:

=DATEDIFF("d", Parameters!dt_begin.Value, Parameters!dt_end.Value)

Pronto, antes de falar como a consulta vai funcionar, criei um relatório básico com Page Header e Body.

No Page Header, inclui 4 text box com a seguinte função:

1. Mostrar os dados que são gerados pelos parâmetros @flag e @month_control;
2. Mostrar as datas configuradas nos parâmetros @dt_begin e @dt_end;
3. Mostrar a data e a quantidade da primeira e da ultima linha que é gerado na tabela do relatório.
4. Este text box só ira aparecer, caso o range entre as datas for maior que a quantidade de dias gerado pelo mês do parâmetro @dt_begin. 

Abaixo, segue as três primeiras expressões, seguindo a ordem acima:

="flag: " & Parameters!flag.Value & " < month_control: " & Parameters!month_control.Value

="@dt_begin: " & Parameters!dt_begin.Value & " - dt_end: " & Parameters!dt_end.Value

="First Line " & First(Fields!FullDateAlternateKey.Value, "InternetSalesAmount") & 
" Qtd: " & First(Fields!OrderQuantity.Value, "InternetSalesAmount") &
" - Last Line " & Last(Fields!FullDateAlternateKey.Value, "InternetSalesAmount") &
" Qtd: " & Last(Fields!OrderQuantity.Value, "InternetSalesAmount")

O Body, é só adicionar uma tabela, e colocar os campos. Abaixo um exemplo:

Agora, no ultimo text box, temos que configurar a propriedade para que ele só apareça no relatório, caso o range não ultrapasse a quantidade de dias do mês.

Nas propriedades do text box em questão, clique em Visibility, e clique no botão de expressão.

A expressão a seguir, define que se o parâmetro @flag for menor do que o parâmetro @month_control, então o text box não aparecerá no relatório, senão, a mensagem é apresentada.

=IIF(Parameters!flag.Value < Parameters!month_control.Value, TRUE, FALSE)

Pronto, com tudo configurado, agora é só gerar o relatório, passando as datas desejadas (lembrando, não estamos tratando controle caso o usuário passe a data final menor que a data inicial, etc).

No primeiro exemplo, veja que passei do dia 01/04/2013 à 05/04/2013 (meu note está configurado com um sistema em inglês, apesar de os dados estarem configurados para o formato do Brasil, os parâmetros acabam pegando a formatação do note (mm-dd-aaaa)). Este é o resultado:

Os cincos dias foram filtrados corretamente, o mês 04 tem 30 dias e as informações estão de acordo.

Agora, se eu mudar o parâmetro dt_end para 30/04/2013:

Coloquei a ordenação decrescente nesta execução, para mostrar primeiro a ultima linha da tabela, a primeira linha você pode conferir na primeira execução.

Agora, vou passar dois meses, de 01/02/2013 à 31/03/2013, veja o resultado:

Pronto, note que o relatório só trouxe as informações de fevereiro, e ainda mostrou a mensagem, onde o filtro foi alterado para não haver sobrecarga de dados na consulta, garantindo que o usuário não traga mais do que 1 mês de informação.

Com isso, agora você pode ter varias outras ideias (inclusive tratar a consulta para trazer 30 dias no máximo a partir da data de inicio passada) para controlar seu data set e garantir um limite no resultado de uma consulta.

Espero que ajude!

Abç.