Usar parâmetros em cascata em relatórios paginados

Este artigo destina-se a você como um autor de relatório projetando relatórios paginados do Power BI. Ele fornece cenários para projetar parâmetros em cascata. Parâmetros em cascata são parâmetros de relatório com dependências. Quando um usuário de relatório seleciona um valor de parâmetro (ou valores), ele é usado para definir valores disponíveis para outro parâmetro.

Nota

Uma introdução aos parâmetros em cascata e como configurá-los não é abordada neste artigo. Se você não estiver completamente familiarizado com parâmetros em cascata, recomendamos que leia primeiro Adicionar parâmetros em cascata a um relatório no Construtor de Relatórios do Power BI.

Projetar cenários

Há dois cenários de design para usar parâmetros em cascata. Podem ser utilizados de forma eficaz para:

• Filtrar grandes conjuntos de itens
• Apresentar itens relevantes

Base de dados de exemplo

Os exemplos apresentados neste artigo baseiam-se numa Base de Dados SQL do Azure. O banco de dados registra as operações de vendas e contém várias tabelas que armazenam revendedores, produtos e ordens de venda.

Uma tabela chamada Revendedor armazena um registro para cada revendedor e contém muitos milhares de registros. A tabela Revendedor tem estas colunas:

• ResellerCode (inteiro)
• ResellerName
• Country-Region
• State-Province
• City
• PostalCode

Há também uma tabela chamada Vendas. Ele armazena registros de ordem de venda e tem uma relação de chave estrangeira com a tabela Revendedor , na coluna ResellerCode .

Exemplo de requisito

Há um requisito para desenvolver um relatório de perfil de revendedor. O relatório deve ser projetado para exibir informações para um único revendedor. Não é apropriado que o usuário do relatório insira um código de revendedor, pois raramente o memoriza.

Filtrar grandes conjuntos de itens

Vamos dar uma olhada em três exemplos para ajudá-lo a limitar grandes conjuntos de itens disponíveis, como revendedores. Eles são:

• Filtrar por colunas relacionadas
• Filtrar por uma coluna de agrupamento
• Filtrar por padrão de pesquisa

Filtrar por colunas relacionadas

Neste exemplo, o usuário de relatório interage com cinco parâmetros de relatório. Eles devem selecionar país-região, estado-província, cidade e, em seguida, código postal. Em seguida, um parâmetro final lista os revendedores que residem nessa localização geográfica.

Veja como você pode desenvolver os parâmetros em cascata:

1. Crie os cinco parâmetros de relatório, ordenados na sequência correta.

2. Crie o conjunto de dados CountryRegion que recupera valores distintos de país-região, usando a seguinte instrução de consulta:

   SELECT DISTINCT
     [Country-Region]
   FROM
     [Reseller]
   ORDER BY
     [Country-Region]
   

3. Crie o conjunto de dados StateProvince que recupera valores distintos de estado-província para o país-região selecionado, usando a seguinte instrução de consulta:

   SELECT DISTINCT
     [State-Province]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
   ORDER BY
     [State-Province]
   

4. Crie o conjunto de dados Cidade que recupera valores de cidade distintos para o país-região e estado-província selecionados, usando a seguinte instrução de consulta:

   SELECT DISTINCT
     [City]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
     AND [State-Province] = @StateProvince
   ORDER BY
     [City]
   

5. Continue esse padrão para criar o conjunto de dados PostalCode .

6. Crie o conjunto de dados Revendedor para recuperar todos os revendedores para os valores geográficos selecionados, usando a seguinte instrução de consulta:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
     AND [State-Province] = @StateProvince
     AND [City] = @City
     AND [PostalCode] = @PostalCode
   ORDER BY
     [ResellerName]
   

7. Para cada conjunto de dados, exceto o primeiro, mapeie os parâmetros de consulta para os parâmetros de relatório correspondentes.

Nota

Todos os parâmetros de consulta (prefixados com o símbolo @) mostrados nesses exemplos podem ser incorporados em instruções SELECT ou passados para procedimentos armazenados.

Geralmente, os procedimentos armazenados são uma melhor abordagem de design. Isso ocorre porque seus planos de consulta são armazenados em cache para uma execução mais rápida e permitem que você desenvolva uma lógica mais sofisticada, quando necessário. No entanto, atualmente não há suporte para fontes de dados relacionais de gateway, o que significa SQL Server, Oracle e Teradata.

Por fim, você deve sempre garantir que existam índices adequados para suportar a recuperação eficiente de dados. Caso contrário, os parâmetros do relatório podem demorar a ser preenchidos e o banco de dados pode ficar sobrecarregado. Para obter mais informações sobre indexação do SQL Server, consulte SQL Server Index Architecture and Design Guide.

Filtrar por uma coluna de agrupamento

Neste exemplo, o usuário de relatório interage com um parâmetro de relatório para selecionar a primeira letra do revendedor. Um segundo parâmetro lista os revendedores quando o nome começa com a letra selecionada.

Veja como você pode desenvolver os parâmetros em cascata:

1. Crie os parâmetros de relatório ReportGroup e Reseller , ordenados na sequência correta.

2. Crie o conjunto de dados ReportGroup para recuperar as primeiras letras usadas por todos os revendedores, usando a seguinte instrução de consulta:

   SELECT DISTINCT
     LEFT([ResellerName], 1) AS [ReportGroup]
   FROM
     [Reseller]
   ORDER BY
     [ReportGroup]
   

3. Crie o conjunto de dados Revendedor para recuperar todos os revendedores que começam com a letra selecionada, usando a seguinte instrução de consulta:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     LEFT([ResellerName], 1) = @ReportGroup
   ORDER BY
     [ResellerName]
   

4. Mapeie o parâmetro de consulta do conjunto de dados Reseller para o parâmetro de relatório correspondente.

É mais eficiente adicionar a coluna de agrupamento à tabela Revendedor . Quando persistente e indexado, proporciona o melhor resultado. Para obter mais informações, consulte Especificar colunas computadas em uma tabela.

ALTER TABLE [Reseller]
ADD [ReportGroup] AS LEFT([ResellerName], 1) PERSISTED

Esta técnica pode oferecer um potencial ainda maior. Considere o script a seguir que adiciona uma nova coluna de agrupamento para filtrar revendedores por faixas predefinidas de letras. Ele também cria um índice para recuperar com eficiência os dados exigidos pelos parâmetros do relatório.

ALTER TABLE [Reseller]
ADD [ReportGroup2] AS CASE
  WHEN [ResellerName] LIKE '[A-C]%' THEN 'A-C'
  WHEN [ResellerName] LIKE '[D-H]%' THEN 'D-H'
  WHEN [ResellerName] LIKE '[I-M]%' THEN 'I-M'
  WHEN [ResellerName] LIKE '[N-S]%' THEN 'N-S'
  WHEN [ResellerName] LIKE '[T-Z]%' THEN 'T-Z'
  ELSE '[Other]'
END PERSISTED
GO

CREATE NONCLUSTERED INDEX [Reseller_ReportGroup2]
ON [Reseller] ([ReportGroup2]) INCLUDE ([ResellerCode], [ResellerName])
GO

Filtrar por padrão de pesquisa

Neste exemplo, o usuário de relatório interage com um parâmetro de relatório para inserir um padrão de pesquisa. Um segundo parâmetro lista os revendedores quando o nome contém o padrão.

Veja como você pode desenvolver os parâmetros em cascata:

1. Crie os parâmetros do relatório Pesquisa e Revendedor , ordenados na sequência correta.

2. Crie o conjunto de dados Revendedor para recuperar todos os revendedores que contêm o texto de pesquisa, usando a seguinte instrução de consulta:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     [ResellerName] LIKE '%' + @Search + '%'
   ORDER BY
     [ResellerName]
   

3. Mapeie o parâmetro de consulta do conjunto de dados Reseller para o parâmetro de relatório correspondente.

Gorjeta

Você pode melhorar esse design para fornecer mais controle para os usuários do relatório. Ele permite que eles definam seu próprio valor de correspondência de padrões. Por exemplo, o valor de pesquisa "red%" será filtrado para revendedores com nomes que comecem com os caracteres "vermelho".

Para obter mais informações, consulte LIKE (Transact-SQL).

Veja como você pode permitir que os usuários do relatório definam seu próprio padrão.

WHERE
  [ResellerName] LIKE @Search

Muitos profissionais que não são de banco de dados, no entanto, não sabem sobre o caractere curinga percentual (%). Em vez disso, eles estão familiarizados com o caractere asterisco (*). Ao modificar a cláusula WHERE, você pode permitir que eles usem esse caractere.

WHERE
  [ResellerName] LIKE SUBSTITUTE(@Search, '%', '*')

Apresentar itens relevantes

Nesse cenário, você pode usar dados de fatos para limitar os valores disponíveis. Os usuários do relatório serão apresentados com itens onde a atividade foi registrada.

Neste exemplo, o usuário de relatório interage com três parâmetros de relatório. Os dois primeiros definem um intervalo de datas de ordens de venda. O terceiro parâmetro lista os revendedores onde os pedidos foram criados durante esse período de tempo.

Veja como você pode desenvolver os parâmetros em cascata:

1. Crie os parâmetros de relatório OrderDateStart, OrderDateEnd e Reseller , ordenados na sequência correta.

2. Crie o conjunto de dados Revendedor para recuperar todos os revendedores que criaram pedidos no período de data, usando a seguinte instrução de consulta:

   SELECT DISTINCT
     [r].[ResellerCode],
     [r].[ResellerName]
   FROM
     [Reseller] AS [r]
   INNER JOIN [Sales] AS [s]
     ON [s].[ResellerCode] = [r].[ResellerCode]
   WHERE
     [s].[OrderDate] >= @OrderDateStart
     AND [s].[OrderDate] < DATEADD(DAY, 1, @OrderDateEnd)
   ORDER BY
     [r].[ResellerName]
   

Recomendações

Recomendamos que você projete seus relatórios com parâmetros em cascata, sempre que possível. É porque eles:

• Forneça experiências intuitivas e úteis para os usuários do relatório
• São eficientes, porque recuperam conjuntos menores de valores disponíveis

Certifique-se de otimizar suas fontes de dados:

• Usando procedimentos armazenados, sempre que possível
• Adicionando índices apropriados para recuperação eficiente de dados
• Materialização de valores de coluna — e até mesmo linhas — para evitar avaliações dispendiosas em tempo de consulta

Conteúdos relacionados

Para obter mais informações relacionadas a este artigo, confira os seguintes recursos:

• Parâmetros de relatório no Construtor de Relatórios do Power BI
• Adicionar parâmetros em cascata a um relatório (Construtor de Relatórios)
• Perguntas? Tente perguntar à Comunidade do Power BI
• Sugestões? Contribua com ideias para melhorar o Power BI