Filtrando dados dinamicamente antes de importar do SQL Server no Excel

Question

Estou tentando descobrir se existe uma maneira de filtrar os dados de uma conexão do SQL Server antes de serem importados para o Excel. A importação inicial não importa (quando a conexão está configurada), mas eu preciso que o Excel tenha pouco ou nenhum dado quando aberto subseqüentemente e importe os dados relevantes somente após um parâmetro ser escolhido. As tabelas SQL podem ter muitos dados e o desempenho é um problema.

Portanto, se eu importar os dados em uma tabela, aplique um filtro para, digamos, Country para UK, feche o arquivo e abra-o novamente na próxima semana, idealmente, ele só deve consultar e importar os dados para o Reino Unido. E quando escolho outro país, ele deve importar os dados somente para esse país. Preciso das escolhas a serem enumeradas da tabela.

Agora, não acredito que as tabelas tenham essa capacidade. Mas e quanto a Slicers, PivotTables, PowerView ou PowerPivot?

Aprecie todas as ideias.

    

Answer 1

Você pode fazer isso com tabelas, bem como o Powerpivot e o Data Explorer. O mais fácil e meu método preferido atualmente é criar uma consulta parametrizada no Excel. Para fazer isso, use o Assistente de consulta e, no final, inclua restrições para cada parâmetro que você deseja transmitir. Feito isso, você pode editar as propriedades de conexão e atribuir o parâmetro a ser retornado como um valor estático, caixa de diálogo ou uma célula de planilha.

1. Selecione sua fonte de dados usando o MS Query - isso é necessário para poder passar os parâmetros de volta ao SQL; Data>Other Sources>Microsoft Query .
2. Escolha Data Source , selecione sua fonte de dados SQL aqui. Em geral, deixarei todas essas opções em branco até chegar à janela Finish e, em seguida, selecionarei View data or edit query in MS Query.
   • Observação: normalmente, pre-processo as informações desejadas do SQL em uma única exibição à qual posso me conectar, mas geralmente você pode passar qualquer instrução de seleção. Eu não tentei chamar um Stored Procedure, então não tenho certeza de como isso funcionaria.
3. Na janela do MS Query, clique no campo Show/Hide Criteria button e escolha um critério. Isso será o que chama seus parâmetros para passar de volta ao SQL.
   • Exemplo: Se você tiver um campo de data e selecioná-lo no campo Critérios, adicione um Valor entre [início] e [fim]. Isso criará um parâmetro e o MS Query solicitará que você preencha os dois valores e retorne imediatamente o conjunto de dados apropriado. Se você verificar a instrução SQL que ela criou, ela terá uma cláusula WHERE (datasource.date Between? E?).
4. Selecione Arquivo > Retornar dados para o Excel e, em seguida, Escolher tabela.
5. Para que a tabela retire seu parâmetro de consulta automaticamente, selecione uma célula em sua tabela, vá para Dados > Propriedades > Propriedades de conexão > Definição > Parâmetros.
6. Para cada parâmetro, você pode escolher para ele
   1. Solicitar que você forneça um valor; ou
   2. Use um valor específico; ou
   3. Obtenha um valor de uma célula da planilha, com a opção de atualizar automaticamente os dados da tabela sempre que o valor da célula mudar.
      • Como opção adicional, você pode usar a Validação de dados na célula escolhida para o seu parâmetro, permitindo limitar os possíveis valores a serem escolhidos. Você pode até ter as células de validação provenientes de uma tabela que é alimentada a partir de valores do banco de dados, garantindo que apenas os valores mantidos no banco de dados possam ser selecionados.
7. Defina seu valor de parâmetro para cada parâmetro e clique em OK em todas as caixas de diálogo.
8. Finalmente, para minimizar o tamanho do arquivo, na guia Propriedades de conexão > Uso, você pode selecionar Atualizar dados ao abrir o arquivo e Remover dados do intervalo externo antes de salvar a pasta de trabalho. Isso despejará os dados conectados quando o arquivo não estiver sendo usado ativamente, minimizando o tamanho do arquivo.

Com frequência, criamos uma tabela dinâmica com base em uma tabela alimentada a partir de uma consulta parametrizada para retornar valores para intervalos de datas específicos atualizados automaticamente (por exemplo, seis meses anteriores).

EDIT: Para retornar vários valores para uso em uma cláusula SQL IN, você só precisa modificar a janela Critérios na caixa MS Query. Por exemplo, se você tiver um nome de campo País, selecione-o no campo Critério e, em Valor, insira IN ([Primeiro], [Segundo], [Terceiro]). Escolha Retornar Dados para o Excel e o MS Query pedirá para inserir valores manualmente. Quando voltar ao Excel, você poderá modificar os parâmetros novamente e apontá-los para três células em sua planilha, cada uma das quais pode ser restringida pela Validação de dados. Embora provavelmente exista um limite para quantos parâmetros você pode passar dessa maneira, o Excel lida com três, portanto, mais alguns provavelmente não estão fora de questão.

    

Answer 2

Se você não se importar em usar o VBA, uma maneira "leve" de fazer isso é gerar dinamicamente a consulta SQL.

Eu uso algo similar ao abaixo bastante para gerar painéis ad-hoc que não precisam de nada tão complexo como o QlikView ou o Crystal

Por exemplo,

Folha oculta 'Variáveis' contém uma tabela 'Table_SQL_SALES_EMPLOYEES'. Isso é preenchido pelo SQL para retornar apenas o mínimo necessário para a seleção de parâmetros. Algo como SELECT DISTINCT AccountDirector FROM InvoicedSales ORDER BY AccountDirector .

Eu, então, tenho um intervalo nomeado 'Sales_Employees', o qual é Referido como =Table_SQL_SALES_EMPLOYEES[AccountDirector] . Isso é mapeado para uma caixa de combinação, que tem o intervalo de entrada definido como 'Sales_Employees'. Isso irá enviar um número para uma célula de sua escolha (E5 no meu caso) do item na lista selecionada. Para retornar o valor real de acordo com o extrato SQL original, você precisa usar =OFFSET(Table_SQL_SALES_EMPLOYEES[[#Headers],[AccountDirector]],$E$5,0) . Isso é chamado de 'Slp'.

: Agora, temos uma caixa de seleção suspensa de todos os possíveis administradores de contas. Há também outro para o trimestre financeiro, mas o mesmo princípio:.

Quando a seleção é alterada na caixa de combinação, ela chama uma macro 'changeFilter ()'

Sub changeFilter()
    Dim wb As Workbook
    Set wb = Excel.ActiveWorkbook
    Dim ws As Worksheet
    Set ws = Excel.ActiveWorkbook.Sheets("Lookups")
    Dim conn As Excel.WorkbookConnection
    Dim slp As Name
    Set slp = wb.Names("Slp")
    Dim qtr As Integer
    qtr = wb.Names("qtr").RefersToRange.Value2
    Dim query As String

    ' Adjusted Sales Consolidated
    Set conn = wb.Connections("SQL_ADJUSTEDSALES_CONSOLIDATED")
    query = "SELECT * FROM InvoicedSales WHERE AccountDirector=" & "'" & slp.RefersToRange.Value2 & "'"
    conn.OLEDBConnection.CommandText = query
    conn.Refresh

Eu basicamente pré-configurei as tabelas / pivots ligando-as ao banco de dados com variáveis codificadas e selecione as opções 'Atualizar dados ao abrir o arquivo' e 'Remover dados do intervalo de dados externo antes de salvar a pasta de trabalho' - isso mantém o tamanho do arquivo minúsculo como nada é realmente salvo.

Em seguida, a alteração do filtro substitui a variável codificada com uma consulta SQL gerada dinamicamente usando o parâmetro selecionado e, em seguida, atualiza a tabela.