Utilizando parâmetros no Reporting Services I

Acabou as férias, e a rotina de trabalho começou pegando fogo neste início de ano, e como sempre, devemos estar sempre preparados para qualquer problema.  A alguns dias, uma amiga me perguntou se ela poderia parametrizar um relatório que trazia informações do mês corrente, mas ela gostaria que o usuário pudesse selecionar outros meses. Dei uma rápida explicação de como ela poderia proceder neste caso, mas ainda assim, para ajudá-la, resolvi fazer um post mostrando como ela poderia adicionar um filtro no relatório dela.

Há duas formas de criar filtros, uma alterando a consulta SQL e adicionando parâmetros (@param) nas consultas nos datasets, isso irá gerar automaticamente os parâmetros, e ai temos que definir como será feita a entrada de informações para esses parâmetros, seja por digitação, uma lista com um único valor ou múltiplos valores.

A outra forma é criarmos os filtros na propriedade do dataset, sem precisar alterar consultas, e se formos pensar no caso da minha amiga, provavelmente ela está consultando informações de uma lista do SharePoint, sendo a opção mais viável para ela.

Vou utilizar o AdventureWorksDW2012 para o exemplo, e não faremos nada muito complicado, então segue uma consulta da base relacional. Lembre-se que nestes exemplos, ainda não estou me preocupando com performance de consultas, isso ainda será assunto para vários posts durante o ano.

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

GROUP BY

       E.EnglishProductCategoryName,

       B.CalendarYear,

       B.MonthNumberOfYear

ORDER BY

       B.CalendarYear,

       B.MonthNumberOfYear

O primeiro exemplo, irei criar um shared dataset, onde a informação não poderão ser alterada  na consulta do relatório, simulando o filtro direto na informação recebida para o relatório.

Utilizando o SQL Server Data Tools, vamos abrir um projeto para o Reporting Services. Você pode ver como criar um projeto e relatório no Reporting Services aqui.

 Na aba Solution Explorer (Ctrl + Alt + L), vamos criar um Shared Dataset para ser utilizado como camada de dados de acesso ao AdventureWorks 2012.

 Defina um nome e tipo para a conexão (no nosso caso, SQL Server), e clique em Edit para configurar a conexão.

Entre com o Servidor de Banco de Dados e selecione o banco AdventureWorksDW2012. Teste a conexão e clique em OK.

Agora vamos criar a camada de dados, criando um Shared Dataset. Depois de criado, esta consulta pode ser utilizada em diversos relatórios.

Entre com um nome para o dataset, selecione o data source criado a pouco, e entre com a consulta SQL a ser utilizada para os dados necessários para o relatório. Clique em OK.

Agora que temos os dados pronto, vamos criar um relatório com um gráfico de barras simples para o exemplo. Clique com o botão direito em Reports,  Add e New Item, para adicionar um novo objeto. Você pode criar o relatório no Wizard, mas não estarei utilizando esta opção agora.

Selecione a opção Report, entre com um nome para o relatório e clique em Add.

Abra a aba Report Data (Crtl + Alt + D) e vamos adicionar o dataset criado a pouco. Clique com o botão direito em Datasets e clique Add Dataset.

Como podemos ver, podemos selecionar a opção para usar um dataset incorporado ao relatório, não podendo ser utilizado por nenhuma outra camada, ou podemos utiliza o shared dataset que criamos. Como podem ver, aqui, não temos acesso a nenhum código de consulta para os dados. Somente acesso a eles. Vamos criar os parâmetros, e depois vamos configurar os filtros.

Ainda na aba Report Data, clique com o botão direito em Parameters e clique em Add Parameter...

Entre com um nome para o parâmetro, a forma como o usuário ira ver o nome do parâmetro no relatório, o tipo dele, como sabemos que na consulta a coluna ano retorna um valor inteiro, se não especificarmos o tipo certo e deixarmos como Text, o relatório irá gerar um erro no tipo de dados na hora da comparação.

Em Available Values, vamos definir os valores do ano manualmente. Poderiamos utilizar uma consulta para popular este parâmetro, e vamos fazer isso no próximo exemplo. Por enquanto, vamos entrar com os valores manualmente.

Na aba Default Values, vamos colocar manualmente o valor 2008, mas se isso estivesse que ser preenchido com informações do sistema, você poderia utiliza a expressão =Year(Now) para pegar o ano corrente das configurações de tempo do servidor. Clique em OK.

Vamos repetir a mesma operação para criar o parâmetro Mounth.

Seguindo a mesma linha de raciocínio do exemplo anterior, vamos configurar o parâmetro de meses, onde podemos ver que podemos selecionar múltiplos valores.

Vamos preencher os labels com os nomes dos meses e definir seus valores como inteiros, assim, assim não teremos problemas na hora de validação de tipo de dados nos filtros.

Da mesma forma que o ano, podemos utilizar a expressão =Month(Now)  para pegar o mês corrente do sistema. No exemplo estou utilizando o ano 2008 e o mês de julho como valores default, porque sei que são os últimos dados registrados no banco, simulando como se hoje fosse julho de 2008.

Agora vamos configurar o dataset para que os parâmetros passem seus valores como filtros no relatório. Clique no dataset do relatório criado e selecione a opção Dataset Properties.

Na aba Filters, é só configurar conforme a imagem, associando os parâmetros aos filtros desejados. Note que na opção Year só podemos utilizar um valor, na opção Month, podemos selecionar múltiplos valores, tendo operadores diferentes.

Vamos adicionar um gráfico de barras no corpo do relatório. Na aba Toolbox (Crtl + Alt + X), selecione a opção Chart, selecione a aba Column e selecione o primeiro gráfico da lista.

Para as configurações do gráfico, defina o campo Profit em Values, Month em Category Groups e CategoryName em Series Group. Com isso, temos os eixos e valores do relatório configurados.

Vamos customizar o título do relatório, clique sobre a área ChartTitle, cique com o botão direito do mouse e clique em Title Properties.

Para customizar o título do relatório, podemos concatenar o nome do relatório com o ano de exibição das informações. Para isso é só colocar a expressão ="Internet Sales Profit by Category in " & CStr(Parameters!Year_Param.Value) no campo Title text.

Podemos também alterar a forma como os valores serão apresentados na opção Vertical Axix Properties. Aqui, só alterei a forma de apresentação dos valores para Currency e defini o símbolo para a moeda americana. Também configurei para que os valores sejam apresentados em Milhares, então, para melhor visualizar, alterei o text box Axis Title para Thousands. Alterei também o Horizontal Axis Title para Month.

Clicando sobre as colunas com o botão direito do mouse e clicando em Series Properties, podemos editar para que quando passemos o mouse sobre uma coluna, ele apresente o valor da mesma.

No campo ToolTip, entre com a seguinte expressão: =FormatCurrency(Fields!Profit.Value)

Pronto, já temos o relatório pronto para uso. Clique em Preview para ver como ficou, deixe a seta do mouse sobre uma das colunas, e verifique o valor. Como podemos ver, temos os valores para o que deveria ser o ano e mês corrente, mas e se quiser ver todos os meses de 2008?

Agora vamos testar os parâmetros que criamos, selecione todos os meses no para Month e clique em View Report.

Pronto, podemos ver que os meses foram plotados no gráfico, agora só para finalizar, o cliente quer ver as informações do ano passado (no nosso caso, 2007), é só alterar o parâmetro de ano e executar o relatório novamente.

Pronto, conforme podemos ver no título do relatório, temos todos os meses de 2007. Agora é só fazer o deploy no SharePoint e deixar o usuário desfrutar do relatório parametrizado.

Como pudemos ver, não é complicado criar filtros no relatório, mas temos um problema, os valores dos parâmetros foram colocados manualmente, o que pode ter que gerar manutenção para a adição de novos anos. No próximo post, vou mostrar como parametrizar o relatório direto na consulta do relatório, e como fazer para alimentar os parâmetros com valores validos automaticamente, e ainda fazer os parâmetros serem visualizados em cascata, evitando valores que não existam, como no exemplo de marcação de todos os meses   relnoatório, sendo que os meses de agosto para cima eram desnecessários, uma vez que não há valor para eles em 2008.

Até lá pessoal!