.NET

22 jan, 2013

Explorando Microsoft SQL Reporting Services – Parte 05

Publicidade

Olá, pessoal! Continuando nossa série sobre reporting services, nesse penúltimo artigo apresentaremos como consumir os relatórios, bem como outros exemplos com funcionalidades incríveis. Lembrando que todos os códigos aqui implementados estão disponíveis no meu blog. Bom divertimento.

Remote mode – consumindo o relatório

Relatórios hospedados no RS podem ser consumidos sob demanda, ou entregues via serviço de delivery. No cenário mais comum, consumo sob demanda, o RS disponibiliza, via Report Manager (URL, http://<servidor>/Reports), os relatórios para visualização. Outra maneira de visualização dos relatórios é através do nosso já conhecido controle ReportViewer. Para o serviço de entrega dos relatórios, o RS dispõe de um serviço de assinatura que veremos mais adiante.

Controle reportviewer

Para a interação dos relatórios com os clientes, temos o controle ReportViewer, disponível no visual Studio em duas versões: uma para web e outra para Windows forms. Com ele é possível disponibilizar para as aplicações um conjunto de funcionalidades tais como:

  • Exportação dos dados nos formatos Excel, PDF, CSV, TIFF, XML;
  • Suporte a impressão e visualização de impressão do relatório;
  • Rica Interatividade com navegação, document map, bookmarks e ordenação;
  • Pesquisa de texto no corpo do relatório;
  • Zoom;
  • Permite validações de parâmetros customizados.

Exemplos de relatórios

A seguir, implementaremos alguns relatórios para exemplificar o que já vimos até então, bem como alguns recursos que veremos com os relatórios que criaremos. Nossos exemplos são baseados no modelo de dados apresentado no segundo artigo da série, que relaciona pedidos, itens de pedido, produtos e vendedores. Tentaremos aqui construir alguns exemplos de modo a complementar os exemplos criados nos relatórios Local Mode, com algumas funcionalidades especificas, tais como parâmetros multivalorados com datasets, funcionalidades não exploradas, como relatórios Matrix e o uso de parâmetros na formatação dos relatórios.

Relatório tabular – com uso de parâmetros para formatação

Nesse exemplo criaremos um relatório que nos permite visualizar a relação de produtos, seu total em estoque e valor unitário. A grande novidade aqui é fazer uso de parâmetros para formatação do relatório, onde o usuário, de duas formas distintas, especifica quais colunas gostaria de visualizar. O relatório também faz uso de formatação condicional através de uma função do relatório (tab Code, das propriedades do relatório) para selecionar que imagem exibir conforme o valor da quantidade em estoque do produto.

Criando o relatório

Esse relatório exibe as colunas com o nome, total em estoque (com uma imagem) e valor unitário de cada produto.

  • Primeiro passo: Construção do DataSet

Para criação do DataSet, acessamos a tab Data do nosso ambiente de desenvolvimento e criamos um novo DataSet com as seguintes propriedades:

Nome: EstoqueProdutos
Command Type: Text
Query string: select * from tb_produto order by ds_produto
  •  Segundo passo: Criação e modelagem do relatório

Incluiremos um novo relatório em nosso projeto, chamado TBEstoqueProdutos. Usando um objeto Table, da toolbox dos relatórios, criaremos a estrutura do relatório formatado conforme a figura abaixo (destaque para o objeto Image):

Figura 3.15 –formatação
Figura 5.1 –formatação

Nas propriedades do relatório tab Code, criaremos uma função que será utilizada para a formatação da nossa imagem. Essa função tem o seguinte codificação:

Public Shared Function GetImage(ByVal Estoque As Integer) As String

If Estoque > 100

GetImage= "happy.jpg"

Else

GetImage= "sad.jpg"

End If

End Function

Certifique-se de incluir as duas imagens no projeto. E especificar no objeto image a propriedade Source igual à External, e Value igual à seguinte expression : =Code.GetImage(Fields!QT_ESTOQUE.Value)

  • Terceiro passo: Criar os parâmetros

Nosso relatório disponibiliza para os usuários a possibilidade de exibr/ocultar suas colunas. Para tal criamos os parâmetros “ExibePreco” e “coluna” com as propriedades descritas na tabela abaixo:

Nome: ExibePreco
Data Type: Boolean
Prompt Exibe Valor Unitário ?
 
Nome: Coluna
Data Type: String
Prompt Exibir Colunas
Atributo: Multi-value
Available values Non-queried: Produto (1)
  • Quarto Passo: Configurar colunas

Finalmente configuramos as colunas do nosso relatório para serem exibidas conforme os parâmetros anteriormente criados. Para tal, especificamos as propriedades VisibilityàHidden das colunas de Produto e Estoque, respectivamente, com as seguintes instruções em nosso editor de expressions:

=InStr(Join(Parameters!coluna.Value,", "),"1") = 0 e =InStr(Join(Parameters!coluna.Value,", "),"2") = 0

E na coluna de valor unitário a seguinte instrução:

=Not Parameters!exibePreco.Value

Finalmente, eis nosso relatório em ação:

Figura 5.2 - relatório em ação
Figura 5.2 – relatório em ação

Relatório matrix

Nesse exemplo, criaremos um relatório que correlaciona, por dia, os vendedores e suas respectivas vendas, apresentando por produto a quantidade em estoque, bem como o valor da venda.

Aqui usaremos um dos recursos mais poderosos dos relatórios que é o componente Matrix da caixa de ferramentas, utilizado para agrupamentos e totalizações das informações.

  • Primeiro passo: Construção do DataSet.

Para criação do DataSet, acessamos a tab Data do nosso ambiente de desenvolvimento e criamos um novo DataSet com as seguintes propriedades:

Nome: Vendas
Command Type: Text
Query string:

Select p.id_pedido, p.dt_pedido, p.id_vendedor,  v.no_vendedor , pr.ds_produto        ,sum(pr.qt_estoque) as ‘em estoque’

 ,sum(pr.vl_preco*qt_produto) as ‘valor venda’

from tb_pedido p inner join tb_itens_pedido i on p.id_pedido = i.id_pedido

inner join tb_produto pr on i.id_produto = pr.id_produto  inner join tb_vendedor v on v.id_vendedor = p.id_vendedor group by p.id_pedido, p.id_vendedor, v.no_vendedor,pr.ds_produto,p.dt_pedido

order by p.id_vendedor

  • Segundo passo: Criação e modelagem do relatório.

Incluiremos um novo relatório em nosso projeto, chamado MxVenda. Usando um objeto Matrix da toolbox (que o chamaremos de mtx), criaremos a estrutura do relatório formatado conforme a figura abaixo:

Figura 5.3 – formatação
Figura 5.3 – formatação

Iniciaremos a formatação desse relatório, apresentando alguns conceitos do componente matrix. Uma matriz é composta por linhas e colunas (Rows e Columns, vide figura abaixo) e como já dissemos, por um ou mais grupos associados às linhas e colunas que podemos criar e/ou editar nas propriedades da matriz (tab Groups).

Figura 5.4 –matrix suas linhas e colunas
Figura 5.4 –matrix suas linhas e colunas

Criaremos nosso primeiro agrupamento em coluna pelo campo de data do pedido. Para isso, arrastamos e formatamos o campo data do pedido (dt_pedido) para a posição da coluna, o que gerará automaticamente um grupo em coluna chamado mtx_dt_pedido. Em seguida, criaremos um segundo e terceiro grupos, esses em linha, respectivamente com o nome do vendedor e a descrição do produto, nomeados de mtx_no_vendedor e mtx_ds_produto (para criar um segundo grupo, basta clicar com o botão direito sobre um dos grupos e selecionar a opção “Insert Group…”). Devemos garantir que os textbox dos nossos grupos tenham os seguintes nomes – dt_pedido, no_vendedor e ds_produto. Finalmente, adicionamos as duas colunas na sessão Data da matrix, com os campos em_estoque e valor_venda (observe que o RS utiliza a função de agregação SUM automaticamente para os campos da sessão Data). Nossa matrix terá a aparência parecida à figura abaixo.

Figura 5.5 –matrix com seus respectivos dados
Figura 5.5 –matrix com seus respectivos dados

Proveremos um recurso interessante que é a capacidade de exibir/ocultar o agrupamento em linha dos produtos. Para isso, editaremos o grupo mtx_ds_produto modificando sua visibilidade (tab Visibility) para oculto, e a sua visibilidade poderá ser habilitada/desabilitada no campo no_vendedor, como mostra a figura abaixo:

Figura 5.6 –matrix  visibilidade dos grupos
Figura 5.6 –matrix visibilidade dos grupos

Outro recurso muito interessante do componente matrix é a possibilidade de criar totalizadores por grupos. No nosso exemplo incluiremos um subtotal no grupo do nome do vendedor (para isso, basta clicar com o botão direito sobre a caixa de texto no_vendedor, selecionar a opção Subtotal). E finalmente, para preenchermos com 0 (zero) os campos sem valor, usamos as seguinte instruções nos campos “Total Estoque” e “Valor da Venda”:

=IIF(IsNothing(Fields!em_estoque.Value),0,Sum(Fields!em_estoque.Value))
=IIF(IsNothing(Fields!valor_venda.Value),0,Sum(Fields!valor_venda.Value))

Formatando as caixas de texto para valores moeda e numérico, temos nosso relatório com a seguinte forma:

Figura 5.7 –matrix em ação
Figura 5.7 –matrix em ação

Relatório tabular – com uso de parâmetros multivalorados

Nesse exemplo criaremos um relatório que nos permite visualizar a relação de produtos, suas respectivas categorias, total em estoque e valor unitário. Até ai nada diferente do que já fizemos em outros relatórios, no entanto faremos uso de um recurso até então não utilizado, que são os parâmetros multivalorados para o filtro dos produtos a serem exibidos. Nosso relatório disponibilizará uma coleção de categorias para os usuários, e uma vez selecionadas essas categorias, serão disponibilizados os produtos dessas categorias, sendo que o próprio relatório selecionará por padrão os produtos das categorias anteriormente selecionadas que tenham sido parte integrante de algum pedido.

Os parâmetros multivalorados não são interpretados por stored procedures somente por instruções T-SQL do tipo text, uma vez que eles são repassados com a seguinte formatação: “param1,param2,param2,…,paramN” utilizadas na clausula IN do SQL. No entanto, quando utilizadas em stored procedures, o SQL não interpreta corretamente esses valores, sendo necessário criar artifícios para remediar a situação, como veremos em nossos exemplos.

  • Primeiro passo: Construção dos DataSets.

Criaremos um conjunto de DataSets que serão usados tanto para parametrizar os dados do relatório, como para criar um conjunto de valores possíveis para os campos multivalorados. Nosso primeiro DataSet é o primeiro de nossos parâmetros, que é a relação de categorias. Esse DataSet tem as seguintes características:

Nome: dsCategorias
Command Type: Text
Query string: SELECT * FROM TB_CATEGORIA

O próximo DataSet retorna um conjunto de todos os produtos, provenientes da tabela de produtos, dado as categorias anteriormente selecionadas. Esse DataSet tem as seguintes características:

Nome: dsProdutosinCategorias
Command Type: Text
Query string:

SELECT * FROM tb_Produto prod

INNER JOIN TB_CATEGORIA cate

ON [prod].ID_CATEGORIA = [cate].ID_CATEGORIA

WHERE

[prod].ID_CATEGORIA in (@idCategorias )

Agora, o DataSet que carrega os valores default para os produtos na lista de produtos. Nosso critério serão os produtos das categorias anteriormente selecionadas que já constarem em pelo menos um pedido. Para resolvermos a limitação do RS com parâmetros multivalorados em stored procedures, criamos a função “Split”. Esse DataSet tem as seguintes características:

Nome: dsProdutosDefaultValue
Command Type: StoredProcedure
Query string: getProdutosinItensPedido, cujas instruções T-SQL são:

SELECT DISTINCT ped.ID_PRODUTO FROM tb_itens_pedido ped

                INNER JOIN tb_produto pro ON

                ped.id_produto = pro.id_produto

,Split(‘I’,@idCategorias, ‘,’) as CategoriasId

Finalmente criaremos o DataSet para preenchimento do relatório em uma consulta simples tendo como parâmetro as identificações dos produtos. Novamente utilizaremos a função “Split”. Esse DataSet tem as seguintes características:

Nome: dsDetalhesdoProduto
Command Type: StoredProcedure
Query string: getProdutosporCategoria, cujas instruções T-SQL são:

SELECT [prod].DS_PRODUTO, [cate].DS_CATEGORIA, [prod].QT_ESTOQUE

, [prod].VL_PRECO

 from TB_PRODUTO prod INNER JOIN TB_CATEGORIA cate ON [prod].ID_CATEGORIA = [cate].ID_CATEGORIA, Split(‘I’,@idProdutos, ‘,’) as ProdutosId

WHERE [prod].ID_PRODUTO in (Convert(int, ProdutosId.ValorIni))

ORDER BY [prod].DS_PRODUTO

  •  Segundo passo: Criação e modelagem do relatório

Incluiremos um novo relatório em nosso projeto, chamado TBProdutosporCategoria. Usando um objeto Table, da toolbox dos relatórios, criaremos a estrutura do relatório formatad, conforme a figura abaixo:

Figura 5.8 –formatação
Figura 5.8 –formatação
  • Terceiro passo: Criar os parâmetros

Como já dissemos, nosso relatório permite que os usuários selecionem um ou mais produtos a partir de um filtro prévio das categorias. Ambos os parâmetros são exibidos em dropdowns, cujos valores são provenientes dos DataSets previamente criados. O primeiro parâmetro é a relação de categorias, que é configurado conforme a figura abaixo, 5.9:

Figura 5.9 –parâmetro multivalorado
Figura 5.9 –parâmetro multivalorado

O próximo parâmetro é a relação de produtos com valores default, todos com dados provenientes de DataSets, e que é configurado conforme a figura abaixo, 5.10.

 Figura 5.10 –parâmetro multivalorado com valores default
Figura 5.10 –parâmetro multivalorado com valores default

Finalmente, eis nosso relatório em ação:

 Figura 5.11 –parâmetros multivalorados em ação
Figura 5.11 –parâmetros multivalorados em ação

 Deploy dos relatórios

Uma vez criados, faremos o deploy dos nossos relatórios. Para tal, basta selecionar no Visual Studio o menu Build à Deploy <<nome do projeto>>. Uma vez que o projeto esteja configurado (ver sessão “Configurações das Propriedades do Projeto”, figura 5.12), nossos relatórios estarão disponíveis na url: http://<<nomedoservidor>>/Reports, que corresponde ao aplicativo Report Manager.

  Figura 5.12 –Report Manager
Figura 5.12 –Report Manager

Nossos relatórios estão disponíveis na pasta “Treinando”.

Limitações

Os relatórios remote-mode suprem com grande eficiência uma demanda muito comum e pouco explorada que são os relatórios, especialmente em ambientes web. No entanto, temos algumas limitações que esperamos serem sanadas em próximas versões. Abaixo relacionamos alguns dos itens que apontamos como limitações desse modelo de relatórios.

  • Dependência do IIS;
  • Requer uma infra-estrutura mais sofisticada;
  • A API OLEDB, de acesso a dados possui algumas restrições funcionais;
  • Interface padrão com restrições no FireFox.

Por hoje é só pessoal. No ultimo artigo, apresentaremos funcionalidades adicionais do reporting services muito úteis, tais como serviços de delivery, cache e os importantíssimos aspectos de segurança.

Grande abraço e até lá!