Uso de parámetros en cascada en informes paginados

Este artículo está dirigido a los autores de informes que diseñan informes paginados de Power BI. Proporciona escenarios para diseñar parámetros en cascada. Los parámetros en cascada son parámetros de informe con dependencias. Cuando un usuario del informe selecciona un valor (o valores) de parámetro, se usa para establecer los valores disponibles para otro parámetro.

Nota

En este artículo no se incluye una introducción a los parámetros en cascada y cómo configurarlos. Si no está familiarizado con los parámetros en cascada, se recomienda que primero lea Agregar parámetros en cascada a un informe en Power BI Report Builder.

Escenarios de diseño

Existen dos escenarios de diseño para el uso de parámetros en cascada. Se pueden usar de forma eficaz para:

• Filtrar conjuntos grandes de elementos
• Presentar elementos pertinentes

Base de datos de ejemplo

Los ejemplos presentados en este artículo se basan en una instancia de Azure SQL Database. La base de datos registra las operaciones de ventas y contiene varias tablas que almacenan revendedores, productos y pedidos de venta.

Una tabla llamada Reseller almacena un registro para cada revendedor y contiene varios miles de registros. La tabla Reseller tiene las siguientes columnas:

• ResellerCode (entero)
• ResellerName
• País-región
• State-Province
• Ciudad
• Código postal

También hay una tabla denominada Sales. Almacena los registros de pedidos de ventas y tiene una relación de clave externa con la tabla Reseller, en la columna ResellerCode.

Requisito de ejemplo

Hay un requisito para desarrollar un informe de perfil del revendedor. El informe debe estar diseñado para mostrar información para un único revendedor. No se recomienda que el usuario del informe escriba un código de revendedor, ya que raramente lo memoriza.

Filtrado de grandes conjuntos de elementos

Echemos un vistazo a tres ejemplos que le ayudarán a limitar grandes conjuntos de elementos disponibles, como los revendedores. Son las siguientes:

• Filtrado por columnas relacionadas
• Filtrado por una columna de agrupación
• Filtrado por un patrón de búsqueda

Filtrado por columnas relacionadas

En este ejemplo, el usuario del informe interactúa con cinco parámetros de informe. Deben seleccionar país-región, estado-provincia, ciudad y código postal. A continuación, un parámetro final enumera los revendedores que residen en esa ubicación geográfica.

A continuación se muestra cómo puede desarrollar los parámetros en cascada:

1. Cree los cinco parámetros de informe ordenados en la secuencia correcta.

2. Cree el conjunto de datos CountryRegion que recupera valores de país y región distintos mediante la siguiente instrucción de consulta:

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

3. Cree el conjunto de datos StateProvince que recupera valores de estado y provincia distintos para la región y país seleccionada mediante la siguiente instrucción de consulta:

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

4. Cree el conjunto de datos City que recupera valores de ciudad distintos para la región y país seleccionada mediante la siguiente instrucción de consulta:

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

5. Continúe con este patrón para crear el conjunto de datos PostalCode.

6. Cree el conjunto de datos Reseller para recuperar todos los revendedores de los valores geográficos seleccionados mediante la siguiente instrucción 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 datos excepto el primero, asigne los parámetros de consulta a los parámetros de informe correspondientes.

Nota

Todos los parámetros de consulta (con el prefijo del símbolo @) que se muestran en estos ejemplos se pueden insertar en las instrucciones SELECT o pasarse a los procedimientos almacenados.

Por lo general, los procedimientos almacenados son un enfoque de diseño mejor. Se debe a que sus planes de consulta se almacenan en caché para una ejecución más rápida y permiten desarrollar lógica más sofisticada cuando sea necesario. Sin embargo, no se admiten actualmente para orígenes de datos relacionales de puerta de enlace, lo que significa SQL Server, Oracle y Teradata.

Por último, siempre debe asegurarse de que existan índices adecuados para admitir la recuperación eficaz de datos. De lo contrario, los parámetros de informe pueden tardar en rellenarse y la base de datos podría sobrecargarse. Para más información sobre la indización de SQL Server, consulte Guía de diseño y de arquitectura de índices de SQL Server.

Filtrado por una columna de agrupación

En este ejemplo, el usuario del informe interactúa con un parámetro de informe para seleccionar la primera letra del revendedor. Un segundo parámetro muestra a los revendedores cuando el nombre comienza con la letra seleccionada.

A continuación se muestra cómo puede desarrollar los parámetros en cascada:

1. Cree los parámetros de informe ReportGroup y Reseller, ordenados en la secuencia correcta.

2. Cree el conjunto de datos ReportGroup para recuperar las primeras letras usadas por todos los revendedores, mediante la siguiente instrucción de consulta:

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

3. Cree el conjunto de datos Reseller para recuperar todos los revendedores que empiezan con la letra seleccionada mediante la siguiente instrucción de consulta:

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

4. Asigne el parámetro de consulta del conjunto de datos Revendedor al parámetro de informe correspondiente.

Es más eficaz agregar la columna de agrupación a la tabla Reseller. Cuando se almacenan y se indexan, ofrece el mejor resultado. Para más información, consulte Specify Computed Columns in a Table.

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

Esta técnica puede ofrecer incluso mayor potencial. Considere el siguiente script que agrega una nueva columna de agrupación para filtrar a los revendedores por bandas predefinidas de letras. También crea un índice para recuperar de forma eficaz los datos requeridos por los parámetros de informe.

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

Filtrado por patrón de búsqueda

En este ejemplo, el usuario del informe interactúa con un parámetro de informe para especificar un patrón de búsqueda. Un segundo parámetro muestra después a los revendedores cuando el nombre contiene el patrón.

A continuación se muestra cómo puede desarrollar los parámetros en cascada:

1. Cree los parámetros de informe Search y Reseller, ordenados en la secuencia correcta.

2. Cree el conjunto de datos Reseller para recuperar todos los revendedores que contienen el texto de búsqueda, mediante la siguiente instrucción de consulta:

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

3. Asigne el parámetro de consulta del conjunto de datos Revendedor al parámetro de informe correspondiente.

Sugerencia

Puede mejorar este diseño para proporcionar más control a los usuarios del informe. Permite definir su propio valor de coincidencia de patrones. Por ejemplo, el valor de búsqueda "red%" filtrará a los revendedores cuyos nombres empiecen con los caracteres en "rojo".

Para más información, consulte LIKE (Transact-SQL).

A continuación se indica cómo puede permitir que los usuarios del informe definan su propio patrón.

WHERE
  [ResellerName] LIKE @Search

Sin embargo, muchos profesionales que no dominan las bases de datos no conocen el carácter comodín de porcentaje (%). Por el contrario, están familiarizados con el carácter asterisco (*). Mediante la modificación de la cláusula WHERE, puede permitirles utilizar este carácter.

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

Presentación de elementos relevantes

En este escenario, puede usar datos factuales para limitar los valores disponibles. Los usuarios del informe se presentarán con los elementos en los que se ha registrado la actividad.

En este ejemplo, el usuario del informe interactúa con tres parámetros de informe. Los dos primeros establecen un intervalo de fechas de pedidos de venta. El tercer parámetro muestra a continuación los revendedores que han creado pedidos durante ese período de tiempo.

A continuación se muestra cómo puede desarrollar los parámetros en cascada:

1. Cree los parámetros de informe OrderDateStart, OrderDateEnd y Reseller, ordenados en la secuencia correcta.

2. Cree el conjunto de datos Reseller para recuperar todos los revendedores que crearon pedidos en el período de fecha mediante la siguiente instrucción 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]
   

Recomendaciones

Se recomienda diseñar los informes con parámetros en cascada, siempre que sea posible. Se debe a que:

• Proporcionan experiencias intuitivas y útiles para los usuarios del informe.
• Son eficaces, ya que recuperan conjuntos más pequeños de valores disponibles.

Asegúrese de optimizar los orígenes de datos mediante:

• El uso de procedimientos almacenados, siempre que sea posible
• La adición de índices adecuados para una recuperación eficaz de datos
• La materialización de valores de columna (e incluso filas) para evitar evaluaciones de tiempo de consulta costosas

Contenido relacionado

Para obtener más información sobre este artículo, consulte los recursos siguientes:

• Parámetros de informe en el Generador de informes de Power BI
• Adición de parámetros en cascada a un informe (Report Builder)
• ¿Tiene alguna pregunta? Pruebe a preguntar a la comunidad de Power BI
• ¿Sugerencias? Ideas para contribuir a mejorar Power BI