Reporting Services 2008 R2: Mapas com drilldown utilizando Report Builder 3.0 (pt-BR)

Artigo
17/01/2024

Do que se trata o artigo:

***Neste artigo veremos como utilizar o Report Builder 3.0 para criar um relatório com mapa e as principais mudanças e recursos que foram aprimorados. Em seguida, mostraremos como realizar um Drilldown em um relatório com mapa.

Em que situação o tema útil:

A apresentação de um relatório com mapa está se tornando uma maneira rápida de apresentação de dashboards com extrema facilidade, eficiência e apoio à gestão, essenciais para os processos de tomada de decisão.

Nos últimos anos o crescimento e a popularização do termo BI (Business Intelligence) tem se tornado rotina dentro das empresas e ultimamente elas estão preocupados em implementar tecnologias interativas e que monitorem informações de estratégia corporativa, e com isso estão a procura de projetos de BI de grande, médio e pequeno porte. Isto faz perceber o quanto o mercado está precisando de profissionais e há uma carência dessas pessoas capacitadas para ocupar vagas de nível tático e estratégico para suprir as necessidades das empresas.

Hoje vamos falar um pouco sobre o Reporting Services que atende uma ampla faixa de cenários de implementação do cotidiano que facilitam a vida não apenas do programador, mas a do usuário final, e com esse intuito essa ferramenta não é apenas para fazer relatórios de BI, como as pessoas têm associado.

O Reporting Services é uma solução de relatórios corporativos que é disponibilizada pela Microsoft e nessa a ferramenta você pode criar, implantar e gerenciar relatórios para a sua empresa. Há pouco mais de um ano a Microsoft lançou a versão mais atual da sua plataforma de dados, o SQL Server 2008 R2, junto com esse lançamento também foi lançado o Report Builder 3.0 que fornece um ambiente intuitivo de criação de relatórios para usuários corporativos e avançados, e tem uma interface parecida com o Office para facilitar a integração com o usuário que já está familiarizado com o ambiente. O motivo dessa interface amigável é porque cada vez mais o usuário final está fazendo seus próprios relatórios sem a intervenção ou com pouco auxilio da equipe de TI, dentro de um projeto de BI chamamos isso de “Self-Services BI”, ou seja, “BI feito por você mesmo”. E nos vamos utilizar o Report Builder 3.0 para desenvolver o relatório de mapa que iremos criar.

O que podemos fazer com o Report Builder 3.0?

No Report Builder 3.0 você pode criar diversos tipos de relatórios como: tabela, matriz, gráfico, mapa e layouts de forma livre. Para criar um relatório você pode usar várias fontes de dados, ou seja, usar dados provenientes de qualquer tipo de fonte que tenha um provedor de dados gerenciado pelo Microsoft .NET Framework, como por exemplo, um OLE DB ou uma fonte de dados ODBC. Esse tipo de conexão é muito interessante, pois assim você pode conectar-se a uma base de dados ORACLE ou DB2, entre outras, e acessar os seus dados e gerar o relatório.

Com o Report Builder 3.0 além de criar é possível modificar, personalizar e atualizar relatórios existentes. Existe a possibilidade de adicionar medidores, mini gráficos e indicadores para resumir dados em um formato visual e apresentar grandes volumes de informações em um Dashboard.

Uma das novas funcionalidades é o compartilhamento de partes do seu relatório para outras pessoas da sua equipe ou empresa. Isso mesmo, você pode ter pequenos pedaços do seu relatório disponibilizados para que outros usuários possam usa-los em novos relatórios. As partes que foram salvas para realizar o compartilhamento são itens publicados separadamente em um servidor de relatório ou em um site do SharePoint integrado a um servidor do Reporting Services. Os itens como tabelas, matrizes, gráficos e imagens podem ser publicados como partes de relatório e com isso você ganha em agilidade para desenvolver e entregar relatórios.

Outra funcionalidade para agilizar a criação de relatórios é o compartilhamento de um conjunto de dados que são consultas baseadas em uma fonte de dados compartilhada e salva em um servidor de relatório ou em um site do SharePoint integrado com o Repoting Services. Essa funcionalidade fornece uma maneira de compartilhar uma consulta para ajudar a fornecer um conjunto de dados consistente para vários relatórios.

Com o novo recurso: assistente de Mapa é possível criar relatórios que exibam dados agregados em um plano de fundo geográfico ou geométrico de forma rápida e pratica. Os dados de mapa podem ser dados espaciais de uma consulta Transact-SQL ou um arquivo de forma ESRI (Environmental Systems Research Institute, Inc.) e também é possível adicionar uma camada do Microsoft Bing Maps no mapa, ou seja, ter uma visualização de satélite em seu relatório.

O formato de apresentação é uma das principais preocupações do usuário e você pode selecionar formatos voltados para a Web ou aplicativos de desktop e entre eles podemos incluem HTML, MHTML, PDF, XML, CSV, TIFF, Word e Excel.

Uma outra maneira de apresentar o relatório é através do envio de e-mail onde você pode configurar o relatório para ser executado em um determinado horário e envia-lo por e-mail para uma lista de assinantes. Existe também a opção de gerar feeds de dados de seu relatório usando a extensão de renderização Atom do Reporting Services.

Agora que conhecemos um pouco sobre as funcionalidades do Report Builder, agora é hora de começar a utilizar e explorar a ferramenta. Iremos apresentar uma proposta de mapa com drilldown.

Mas o que é o Drilldown?

Drilldown nada mais é que o aumento do nível de detalhe da informação e consequentemente diminuição do nível de granularidade. Por exemplo, isso ocorre quando o usuário passa de uma informação contida em um gráfico onde temos teoricamente dados sumarizados para uma informação contida em uma lista com todos os detalhes que compõem esse gráfico.

Agora que já sabemos o que podemos fazer com o Report Builder e conhecemos o que é o Drilldown, vamos começar a construir o mapa. Para isso eu dividi em alguns passos para facilitar o entendimento e separar cada etapa necessária na construção do nosso relatório.

Passo 1 – Não tenho nenhum mapa e agora?

Não se preocupe. Para darmos inicio nada mais logico que ter as coordenadas geográficas. Para isso precisamos ter um arquivo SHAPE FILE que pode ser encontrado no site GIPSMAPS, você consegue realizar o download de alguns mapas diferentes e iremos utilizar o arquivo “Municipalities”.

Passo 2 – O que faço com esse arquivo Shape File?

Precisamos importar esse arquivo Shape File para dentro de uma base de dados SQL Server 2008 R2, e para isso vamos usar um programa chamado “Shape2SQL”. Navegue até a pasta onde você realizou o download, extraía os arquivos e execute o programa Shape2Sql.exe. Indique o local onde o arquivo Shape File (.shp) está localizado e configure uma conexão com o servidor SQL Server 2008 R2 ou SQL Server 2008, não existe suporte a dados geográficos em versões anteriores. Você tem que marcar a opção “Geography (Spheric)” pois vamos utilizar algumas funções baseadas nesse tipo de dados, também marque a opção “Set SRID” e desmarque a coluna “ID”. Se preferir você pode alterar o nome da coluna do tipo geométrico que será armazenado e o nome da tabela que será carregada no seu banco de dados. Clique no botão “Upload to Database” para iniciar o processo de carga para seu banco de dados conforme mostra a figura 1. Esse processo não é muito demorado cerca de 1 a 2 minutos em uma estação de trabalho comum.

Figure 1. Shape2SQL - Importar dados espaciais

Junto com o esse aplicativo estão disponíveis alguns exemplos e outra aplicação para visualização de dados espaciais que é muito interessante, porem não é o foco nesse artigo. Após terminar a carga de dados a primeira coisa que vem em mente é fazer uma querie para ver os dados. Mas será que os dados foram carregados corretamente? A sua tabela deve conter 5512 registros, essa é a quantidade de municípios que existem no Brasil.
Reparem na figura 2 que agora existem três abas. Isso porque na tabela de “Municipio” existe um campo do tipo geography chamado “geom”. Ao clicar na aba “Spatial Results”, o SQL Server Management Studio irá disponibilizar a informação em um mapa.

Figure 2. Mapa do Brasil

Na figura 2 vemos que o sul do país não foi apresentando, pois existe um limite de 5000 objetos a serem exibidos. Isso é um problema? Veja que o mapa ficou muito poluído onde é quase impossível encontrar um município. Agora vamos começar trabalhar com essas informações, pois temos informações sobre: Estado, Meso Região, Micro Região e Municipio. E esse vai ser no drilldown no mapa.

Passo 3 - Reinventar? Não, hora de ter agilidade!

Nesse momento, vamos começar a agregar as informações, mas como podemos trabalhar com esse tipo de dados? Hoje existem muitos posts sobre o assunto, então não iremos entrar em detalhes, mas a leitura do tópico “Working with Spatial Data” no site do TechNet é fundamental. Nesse momento não será necessário nenhuma experiência com a manipulação desse tipo de dados, por que não vamos reinventar a roda, e sim utiliza-la a nosso favor. Um site que se deve ser navegado com frequência é o site do CodePlex e para quem não conhece, o CodePlex é um site onde pessoas do mundo todo disponibilizam pequenos aplicativos ou funcionalidades para a comunidade Microsoft. Ou seja, um repositório repleto de códigos onde pessoas tiveram interesse em algum assunto e fizeram uma ferramenta para ajuda-los e depois compartilharam a todos. Isso é muito bom, pois pessoas podem encontrar ferramentas para ajuda-las em suas rotinas diárias e dessa maneira podemos acessar o projeto “SQL Server Spatial Tools”. Este projeto é uma coleção de ferramentas para uso com os tipos de dados espaciais que são novos para o SQL Server 2008. Com esse intuído de coleção de ferramentas esse projeto não prevê uma aplicação para o usuário final, mas sim um conjunto de funções reutilizáveis a quais os aplicativos podem fazer uso. Entre as funcionalidades podemos incluir rotinas de conversão de dados, novas transformações, agregados, entre outras.

Para utilizarmos esse conjunto de ferramentas nos vamos trabalhar com CLR (Common Language Runtime), esse é outro assunto fascinante que deve ser utilizado em seus projetos de banco de dados com frequência. Não iremos abordar profundamente o assunto que deve ser tratado por outros especialistas. CLR nada mais é do que compilar uma DLL dentro do servidor SQL Server, e essa funcionalidade está disponível desde a versão SQL Server 2005.

Depois que você fizer o download desse projeto navegue até a pasta e encontre um arquivo chamado “Register.sql”. Esse arquivo lhe dará todas as informações de como você proceder para implantar a CLR no seu servidor e nele você irá criar todas as funcionalidades como: tipo de dados, funções e agregações.

A primeira etapa a ser realizada é habilitar seu servidor para aceitar a CLR, isso é realizado através do comando “sp_configure” conforme está descrito dentro do arquivo “Register.sql”, para realizar isso é necessário ter permissão, caso não tenha solicite ao DBA. Depois de habilitar a funcionalidade vamos criar um assemble apontando para a DLL, mais uma vez o procedimento está descrito no arquivo ”Register.sql” A partir de agora é só criar os objetos no seu banco de dados conforme mostra a figura 3. No arquivo “Register.sql” existem mais objetos a serem criados do que está apresentado na figura 5. Para essa demonstração iremos utilizo apenas a agregação “GeographyUnionAggregate”.

Figure 3. Objetos com CLR

Está quase tudo pronto para começarmos a construir nosso relatório. Depois de criarmos os objetos referenciando o assemble é hora de utiliza-los! Para isso basta fazer um simples SELECT na tabela de “Municipio” conforme a figura 4.

Figure 4. Mapa do Brasil agrupado por estado.

Vejam que foi utilizado à agregação “GeographyUnionAggregate” passando como parâmetro o campo “geom” que é do tipo geography. Agora temos um mapa mais limpo onde podemos ver perfeitamente todos os estados do Brasil.

Baseado no comando anterior foi criado uma tabela chamada “ESTADO” conforme o comando:

SELECT

NOMEUF

, CODIGO_UF

, dbo.GeographyUnionAggregate(geom)

INTO ESTADO

FROM

dbo.Municipio

GROUP BY

NOMEUF

,CODIGO_UF

Passo 4 - Começando a construir o relatório

Chegou a hora esperada, vamos dar inicio a construção do nosso relatório. Apenas mais um detalhe, devemos ter configurado o Reporting Services.

Iremos utilizar o Report Build 3.0 que está acessível através do menu iniciar, dentro da pasta “Microsoft SQL Server 2008 R2 Report Builder 3.0” .

Para essa demonstração iremos apresentar um mapa com Drilldown e para isso iremos utilizar um único Dataset. Dessa maneira iremos utilizar a seguinte query:

SELECT * FROM

(

-- Inicia o mapa com todos os Estados

SELECT

'ESTADO' as Tipo

, CODIGO_UF [Codigo]

, NOMEUF [Nome]

, geom

FROM dbo.ESTADO

UNION ALL

-- Filtro o Estado e apresenta o mapa com todos as Mesos Regiões

SELECT

'MESO' as Tipo

, CODMESO [Codigo]

, NOMEMESO [Nome]

, dbo.GeographyUnionAggregate(geom) as geom

FROM dbo.MUNICIPIO

WHERE

CODIGO_UF = @ESTADO

GROUP BY

CODMESO

, NOMEMESO

UNION ALL

-- Filtro o Estado e a Meso Região e apresenta o mapa com todos as Micros Regiões

SELECT

'MICRO' as Tipo

, CODMICRO [Codigo]

, NOMEMICRO [Nome]

, dbo.GeographyUnionAggregate(geom) as geom

FROM dbo.MUNICIPIO

WHERE

CODIGO_UF = @ESTADO

AND CODMESO = @MESO

GROUP BY

CODMICRO

, NOMEMICRO

UNION ALL

Filtro o Estado, Meso Região e a Micro Região e apresenta o mapa com todos os Municipios

ou com apenas um Municipio

SELECT

'MUNICIPIO' as Tipo

, CODIGO_MUN [Codigo]

, NOME_1 [Nome]

, dbo.GeographyUnionAggregate(geom) as geom

FROM dbo.MUNICIPIO

WHERE

CODIGO_UF = @ESTADO

AND CODMESO = @MESO

AND CODMICRO = @MICRO

AND (CODIGO_MUN = @MUNICIPIO OR @MUNICIPIO = '')
GROUP BY

CODIGO_MUN

, NOME_1

) M

WHERE

M.Tipo = @tipo

Vocês devem estar se perguntado por que do campo “TIPO”? Esse campo é o que vai controlar em qual nível o relatório será exibido, ou seja, iremos realizar um filtro no mesmo dataset.

Vamos iniciar criando um Data Source, ou seja, nossa conexão com o servidor SQL Server onde se encontra nossa tabela. Clique com o botão direito em cima do campo “Data Source” e depois em “Add Data Source”, configure a sua conexão com o SQL Server e o banco de dados que será usado. Para demonstração iremos utilizar uma conexão local e o banco de dados “MAPA”. Testar a sua conexão é uma boa pratica!

Depois do Data Source criado, crie um Dataset e para basta clicar com o botão direito em cima da pasta “Dataset” e depois em “Add Dataset...”.

Configure o seu Dataset para utilizar o Data Source que você acabou de criar e cole o comando “SELECT” que criamos.

Depois que você clicar no botão “OK”, todas as suas variáveis serão transformadas em parâmetros, isso facilita muito a vida do desenvolvedor.

Passo 5 – Criando o Mapa

Para criar um mapa no Report Builder é super simples, basta entrar no menu “Insert”, opção “Map” e escolha o “Map Wizard” conforme mostra a figura 5. Dessa maneira um wizard irá facilitar a nossa vida para que o mapa fique pronto em pouco tempo.

Figure 5. Map Wizard

Escolha a opção “SQL Server spatial query”. Lembre que nós já criamos nossa querie e já criamos nosso dataset. Como nós já temos o Data Source e o Dataset criados, nesse momento devemos apenas que referenciar esse Dataset.

Uma mensagem de aviso parecida como está pode aparecer: “Unable to determine the spatial data type due to connection error”. Não é uma mensagem de erro, clique no botão “OK” e prossiga.

Escolha o campo que contem os dados espaciais, no exemplo é o campo “geom”, e aqui temos um ponto importante. Existe a opção para adicionar uma camada do Bing Maps no seu relatório e dessa forma você terá uma visualização do mapa semelhante e como se estivesse acessando diretamente o Bing Maps, infelizmente não iremos utilizar essa opção conforme mostra a figura 6.

Figure 6. Escolha de dados espaciais

Prossiga com o wizard e você terá algumas etapas complementares como a escolha um visual para seu mapa, um “dataset analítico”, que pode conter soma de valores, contagem entre outros dados sumarizados, iremos utilizar o mesmo dataset. E para finalizar o wizard você deve escolher um tema para seu relatório.

Passo 6 – Ajustando parâmetros

Agora que já criamos o esqueleto do mapa, vamos configurar os ajustes e vamos começar com os parâmetros. Os parâmetros: @ESTADO, @MESO, @MICRO e @MUNICIPIO devem ser configurados da mesma forma. Todos os parâmetros devem permitir valores em branco e devem ser parâmetros ocultos, tipo “Hidden” como descreve a figura 7.

Figure 7. Modificando parametros

Outro passo que todos esses parâmetros devem ter é um valor padrão, esse valor será branco, para isso acesse o menu “Default Values” escolha a opção “Speficy Value”, clique no botão “Add” e na opção “Value” utilize um valor em branco para que o parâmetro funcione corretamente já que ele será um parâmetro oculto, não utilize “(Null)” como é sugerido pelo assistente.

O parâmetro “tipo” será configurado de forma semelhante, ele também será um parâmetro oculto e deve aceitar valores em branco. Porém ele terá um valor padrão diferente que terá o valor: “ESTADO”

Passo 7 – Definindo polígonos

Mapa criado, parâmetros configurados agora vamos salvar o relatório do Reporting Services para poder usar algumas facilidades conforme mostra a figura 8.

Figure 8. Salvando relatório no Reporting Services

Escolha o servidor de Reporting Services, dê um nome para seu relatório se salve. Estamos quase finalizando o relatório, vamos configurar agora a chamada do relatório para ele mesmo possa fazer assim o drilldown. Para isso clique no mapa, um painel chamado “Map Layers” irá aparecer. Clique na seta apontada para baixo e escolha a opção “Polygon Properties...” conforme a figura 9 apresenta.

Figure 9. Propriedades do Mapa

Na aba “General” selecione o “Label text” e o “Tooltip” para exibirem o campo “Nome” isso irá fazer com que o mapa mostre o nome do “ESTADO” ou “MESO” ou “MICRO” ou “MUNICIO” isso irá depender de qual nível você estará. E também quando passar com o mouse em cima do mapa irá mostrar o nome dependendo do nível que você estiver.

Na aba “Action”, podemos considerar à parte mais importante do nosso relatório, vamos habilitar a ação para chamar um relatório, ou seja, a opção “Go to report”. Agora devemos especificar o relatório que será chamado, por isso salvamos o relatório anteriormente, pois iremos chamar ele mesmo nesse momento. Adicionamos todos os parâmetros e em cada um deles vamos criar uma “Expression” para preencher o parâmetro e aqui está a mágica do nosso relatório. A figura 10 mostra uma tela de apresentação para inserir a “Expression".** **

Figure 10. Propriedades do Polígono e “Expression”

Segue o código que cada parâmetro deve ser preenchido:

Parâmetro “ESTADO”- Expression

=iif(Parameters!tipo.Value="ESTADO",Fields!Codigo.Value,Parameters!ESTADO.Value)

Parâmetro “MESO” – Expression

=iif(Parameters!tipo.Value = "MESO",Fields!Codigo.Value, Parameters!MESO.Value)

Parâmetro “MICRO” – Expression

=iif(Parameters!tipo.Value= "MICRO", Fields!Codigo.Value, Parameters!MICRO.Value)

Parâmetro “MUNICIPIO” – Expression

=iif(Parameters!tipo.Value = "MUNICIPIO", Fields!Codigo.Value, Parameters!MUNICIPIO.Value)

Parâmetro “tipo” - Expression

=iif(Parameters!tipo.Value = "ESTADO", "MESO"

* , iif(Parameters!tipo.Value = "MESO" , "MICRO"*

* , iif(Parameters!tipo.Value = "MICRO" , "MUNICIPIO",Parameters!tipo.Value*

* )))*

Depois que você configurar cada parâmetro o relatório está quase pronto e agora vamos apenas enfeitar o mapa trocando as cores. Novamente vamos acessar o painel “Map Layers”. Clique na seta apontada para baixo e escolha a opção “Polygon Color Rule...”.

Nesse momento é hora de explorar as opções e colocar em pratica sua criatividade. Em quase todos os lugares é possível utilizar “Expression” para carregar, comparar, modificar e atualizar os componentes utilizados em seu relatório. Não iremos criar uma “Expression” para modificar as cores do mapa, iremos utilizar a própria funcionalidade do mapa que é a paleta de cores, isso já ira melhorar o visual do mapa. Para isso no menu Geral escolha a opção “Visualize data by using color palette” defina um Data field, e na opção Palette escolha “Bright Pastel”

Passo 8 – Acessando o mapa

Após configurar a cor do mapa, o layout irá mudar automaticamente mostrando uma pré-visualização do mapa. Não se esqueça de alterar o titulo do mapa, mais uma vez aqui você pode usar uma “Expression” para ver em qual nível você está e mudar o titulo do mapa dinamicamente. Salve o relatório e finalmente vamos executa-lo. A maneira mais rápida de visualizar é clicar no botão “Runs” dentro no Report Builder, mas como esse relatório já está publicado no nosso servidor do Reporting Services vamos acessa-lo pelo nosso navegador conforme a figura 11.

Acesse o endereço do servidor de relatório, que deve ser parecido com esse: “http://localhost/Reports

Figure 11. Reports Page

Clique no relatório “Mapa - Drilldown” para exibi-lo. O mapa irá aparecer conforme a figura 12 e agora você poderá navegar para dentro de cada estado e assim por diante.

Figure 12. Mapa do Brasil com Drilldown

Escolha um estado e clique em cima dele, o relatório irá mostrar a Mesos Regiões. O estado escolhido foi o Paraná conforme a figura 13

Figure 13. Mesos regiões do Paraná.

Novamente escolha uma meso região clique sobre ela e o relatório irá apresentar as Micros Regiões conforme a figura 14. A meso região escolhida foi a “Metropolitana de Curitiba”

Figure 14. Micro Região de Curitiba

Veja que o mapa foi detalhando as informações e agora a micro região escolhida foi a de Curitiba para que seja apresentado todos os municípios pertencentes a essa micro região conforme a figura 15.

Figure 15. Municípios pertencentes à micro região de Curitiba.

Dessa maneira podemos visualizar todos os municípios pertence à micro região de Curitiba, mas clicarmos sobre um município o mesmo será exibido como mostra à figura 16.

Figure 16. Municipio de Curitiba.

Dessa maneira você pode visualizar os dados da sua empresa distribuídos geograficamente e com os dados distribuídos dessa maneira podem, por exemplo, fazer seu gestor visualizar que na região A está vendendo mais que a região B e tome uma decisão para melhorar o relacionamento na região B.

A apresentação de relatórios com mapas irá se tornar um grande atrativo e facilitador dentro das corporações e para isso a equipe de TI deve estar bem preparada. As ferramentas apresentadas trazem para você uma vasta gama de oportunidades e aceleram o crescimento de divulgação de seus dados. Agora é a hora de explorar os recursos do Reporting Services e do Report Builder para ampliar seu conhecimento e melhorar a visualização dos dados da sua empresa.