Utilizando parâmetros no Reporting Services II

Bom tarde pessoal, hoje vou mostrar outra forma de inserir um filtro em um relatório do Reporting Services, mas só que desta vez, estaremos criando o filtro direto na consulta T-SQL.

Essa forma de filtro pode melhorar a performance de alguns relatórios, porque ao contrário do primeiro exemplo, onde a consulta carrega todos os dados e a aplicação realiza o filtro, neste caso, o banco é quem realiza o filtro, então, os dados trafegados são controlados, e se o relatório for atualizado 5 vezes por exemplo com filtros diferentes, somente os dados necessários serão consultados. Já no exemplo anterior, sempre é carregado todos os dados, então, se tivermos 5 atualizações, teremos 5 vezes todos os dados da consulta sem filtros solicitado ao banco.

 Veja o plano de execução estimado da consulta:

 No final da consulta sem filtro, temos um número de linhas estimado em 10,0187.

Já na consulta filtrada, esse número cai para 4. Se temos menos linhas, com certeza, teremos menos trabalho para trafegar esses dados,  o que será mais eficiente para o relatório. 

Voltando ao Reporting, vamos utilizar a seguinte consulta T-SQL para o dataset:

SELECT

       E.EnglishProductCategoryName "CategoryName",

       B.CalendarYear "Year",

       B.MonthNumberOfYear "Month",

       SUM(A.SalesAmount - A.TotalProductCost) AS "Profit"

FROM

       FactInternetSales A

       INNER JOIN

       DimDate B

             ON A.OrderDateKey = b.DateKey

       INNER JOIN

       DimProduct C

             ON A.ProductKey = C.ProductKey

       INNER JOIN

       DimProductSubcategory D

             ON C.ProductSubcategoryKey = d.ProductSubcategoryKey

       INNER JOIN

       DimProductCategory E

             ON D.ProductCategoryKey = E.ProductCategoryKey

WHERE

       B.CalendarYear = @Year

       AND

       B.MonthNumberOfYear IN (@Month)

                   

GROUP BY

       E.EnglishProductCategoryName,

       B.CalendarYear,

       B.MonthNumberOfYear,

          B.EnglishMonthName

ORDER BY

       B.CalendarYear,

       B.MonthNumberOfYear

Neste exemplo, estarei utilizando um dataset incorporado no relatório, mas se quiser, você pode criar os shared datasets, e simplesmente adiciona-los ao relatório.

Na aba Report Data (Ctrl + Alt + D) do relatório, vamos adicionar o data source para conexão com o banco. Neste caso, estou adicionando o Shared Data Source criado no post anterior.

Entre com  a consulta T-SQL com os filtros de ano e mês.

Olhando a aba Parameters, você vai notar que os parâmetros foram criados, clique em OK.

Olhando a pasta Parameters, você vai notar que os parâmetros foram criados automaticamente.

 Vamos adicionar um novo dataset para trazer as informações de ano que irão alimentar o parâmetro Year.  Utilize o seguinte código T-SQL:

SELECT DISTINCT

       B.CalendarYear "Year"

FROM

       FactInternetSales A

       INNER JOIN

       DimDate B

             ON A.OrderDateKey = b.DateKey

ORDER BY

       B.CalendarYear

O mesmo vamos fazer para trazer dados dos meses, veja que neste caso, estamos filtrando o ano.  Utilize o seguinte código T-SQL:

SELECT DISTINCT

       B.MonthNumberOfYear "Month"

FROM

       FactInternetSales A

       INNER JOIN

       DimDate B

             ON A.OrderDateKey = b.DateKey

WHERE

             B.CalendarYear = @Year

ORDER BY

       B.MonthNumberOfYear

Na aba Fields, vamos criar o campo MonthName, clique em Add, selecione Calculate Field, coloque o nome do campo e use a seguinte expressão para trazer o nome do mês: =MonthName(Fields!Month.Value). 

Repita este passo no dataset da consulta principal, com isso, podemos deixar o relatório com uma interação mais amigável com o usuário final.

No parâmetro Year, altere o tipo para Integer.

Na aba Available Values, selecione a opção Get values from a query, e configure os campos utilizando o dataset Year_data. Neste exemplo, não irei configurar valores default para o parâmetro, você pode ver como realizar esta configuração no post anterior.

No parâmetro Month, altere o tipo para integer e marque a opção Allow multiple values na aba General , e na aba Available Values, vamos fazer igual a configuração do parâmetro ano, configure para pegar valores do dataset Month_data, conforme a imagem.

Agora, seguindo o exemplo de criação do post anterior, o relatório será o mesmo, então qualquer dúvida clique aqui para ver o passo a passo para configurar o relatório, vamos configurar o gráfico de barras, só que em Category Groups, ao invés do campo Month, utilize o campo MonthName.

Para o título do gráfico, utilize a seguinte expressão: ="Internet Sales Profit by Category in " & CStr(Parameters!Year.Value).

Na janela Series Properties , utilize a seguinte expressão para o Tooltip: =FormatCurrency(Fields!Profit.Value).

Em Category Group Properties, na aba Sorting, altere a opção MonthName para Month. Assim, os meses no gráfico apareceram ordenados corretamente, e não por ordem alfabética.

Com o relatório configurado, é só dar o Preview. Como você pode ver, os parâmetros estão encadeados, então só vou ter o Month habilitado quando o parâmetro Year for selecionado.

Como podemos ver, só tenho disponível para seleção no parâmetro Month os meses com informação disponível para o ano de 2008.

Pronto, o relatório exatamente igual ao do post anterior, só que agora com os parâmetros configurados para serem preenchidos automaticamente e encadeados.

Com isso, terminamos aqui com as configurações de parametrização no Reporting Services.

Em breve, como parametrizar relatórios que consomem informações de um cubo do Analysis Services.

Qualquer dúvida, estamos ai para ajudar. Se quiserem, sugestões sobre assuntos para novos posts são bem vindas.

Abraços!