Mapas com Drilldown utilizando o Report Builder 3.0

Veremos como utilizar o Report Builder 3.0 para criar um relatório com mapa e mostraremos como realizar um Drilldown em um relatório com mapa.

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.

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.

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 (http://www.gismaps.com.br/english/shape.htm), 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”(http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx).

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.

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”(http://technet.microsoft.com/en-us/library/bb933876.aspx) 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”(http://sqlspatialtools.codeplex.com/). Este projeto é uma coleção de ferramentas para uso com os tipos de dados espaciais que estão disponiveis a partir da versã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 que está na figura 4 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 = '') -- @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.

Até a próxima!!!