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:
Cree los cinco parámetros de informe ordenados en la secuencia correcta.
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]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]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]Continúe con este patrón para crear el conjunto de datos PostalCode.
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]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:
Cree los parámetros de informe ReportGroup y Reseller, ordenados en la secuencia correcta.
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]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]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:
Cree los parámetros de informe Search y Reseller, ordenados en la secuencia correcta.
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]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:
Cree los parámetros de informe OrderDateStart, OrderDateEnd y Reseller, ordenados en la secuencia correcta.
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: