Agregar datos usando QueryExpression

Artículo
07/22/2024

QueryExpression incluye características de agrupación y agregación que le permiten calcular la suma, media, mínimo, máximo y recuento a lo largo de varias filas de datos.

Para devolver un valor agregado, debe:

Deje la propiedad ColumnSet.AllColumns establecida en false. No lo configure usando el constructor ColumnSet(Boolean).
Agregue instancias de la clase XrmAttributeExpression a la colección ColumnSet.AttributeExpressions
Para cada columna que desee devolver, establezca estas propiedades:

Propiedad XrmAttributeExpression Descripción

AttributeName El LogicalName de la columna

Alias El valor del alias de la columna
Para cada columna que desee agregar, establezca el AggregateType al tipo de agregación que desea realizar. Información sobre los tipos de agregaciones

Propiedad XrmAttributeExpression	Descripción
AttributeName	El `LogicalName` de la columna
Alias	El valor del alias de la columna

Para cada columna para la que desee agrupar los resultados por, establezca estas propiedades:

Propiedad XrmAttributeExpression	Descripción
AggregateType	Establecido a XrmAggregateType`.None`
HasGroupBy	Establecido en `true`.
DateTimeGrouping	Cuando la agrupación involucra valores de fecha y hora, especifique el tipo de agrupación de fecha y hora que se aplicará usando los miembros de la enumeración XrmDateTimeGrouping. Más información sobre cómo agrupar por partes de una fecha

Tipos de agregación

Los tipos de agregación que puede hacer son miembros de la enumeración XrmAggregateType.

Miembro de XrmAggregateType	Descripción
`Avg`	El valor promedio de los valores de la columna con datos.
`Count`	Número de filas.
`CountColumn`	El número de filas con datos en esa columna.
`Max`	El valor máximo de las filas de esa columna.
`Min`	El valor mínimo de las filas de esa columna.
`None`	Utilícelo cuando la columna se utilice para agrupar los datos agregados. El valor HasGroupBy debe establecerse como `true`.
`Sum`	El valor total de los valores de la columna con datos.

Tenga en cuenta los aspectos siguientes:

Los valores nulos no se tienen en cuenta al calcular los valores agregados.
Puede utilizar datos de tablas unidas mediante la clase LinkEntity.
Puedes aplicar filtros para limitar los resultados como con cualquier consulta.

Ejemplo

Digamos que tiene 10 registros de cuenta con los siguientes datos:

Número de empleados	Name	Dirección 1 Ciudad	Creadas el
NULL	Cuenta de ejemplo	NULL	8/25/2023
1.500	Contoso Pharmaceuticals (muestra)	Sevilla	3/25/2023
2.700	Fabrikam, Inc. (muestra)	Lynnwood	3/25/2023
2.900	Blue Yonder Airlines (muestra)	Los Angeles	3/25/2023
2.900	City Power & Light (muestra)	Sevilla	3/25/2023
3.900	Coho Winery (muestra)	Phoenix	3/25/2023
4.300	Adventure Works (ejemplo)	Santa Cruz	3/25/2023
4.800	Alpine Ski House (muestra)	Missoula	3/25/2023
6000	Litware, Inc. (muestra)	Dallas	3/25/2023
6.200	R: Datum Corporation (muestra)	Sevilla	3/25/2023

La siguiente consulta devuelve datos agregados para la columna numberofemployees.

QueryExpression query = new()
{
    EntityName = "account",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Average",
                    aggregateType: XrmAggregateType.Avg)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Count",
                    aggregateType: XrmAggregateType.Count)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "ColumnCount",
                    aggregateType: XrmAggregateType.CountColumn)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Maximum",
                    aggregateType: XrmAggregateType.Max)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Minimum",
                    aggregateType: XrmAggregateType.Min)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Sum",
                    aggregateType: XrmAggregateType.Sum)
            }
        }
    }
};

Los resultados son una sola fila:

 --------------------------------------------------------------
 | Average | Count | ColumnCount | Maximum | Minimum | Sum    |
 --------------------------------------------------------------
 | 3,911   | 10    | 9           | 6,200   | 1,500   | 35,200 |
 --------------------------------------------------------------

Agrupación

Agrupe los resultados de una consulta agregada agregando una XrmAttributeExpression con la propiedad HasGroupBy AggregateType establecido en XrmAggregateType.None.

Al agrupar, debe especificar una QueryExpression.Orders con una OrderExpression que tenga la propiedad Alias establecida en el alias del grupo.

Por ejemplo, la siguiente consulta devuelve la suma de empleados y cuenta por ciudad:

QueryExpression query = new()
{
      EntityName = "account",
      ColumnSet = new ColumnSet(false)
   {
         AttributeExpressions = {
            {
                  new XrmAttributeExpression(
                     attributeName: "numberofemployees",
                     alias: "Total",
                     aggregateType: XrmAggregateType.Sum)
            },
            {
                  new XrmAttributeExpression(
                     attributeName: "address1_city",
                     alias: "Count",
                     aggregateType: XrmAggregateType.Count)
            },
            {
                  new XrmAttributeExpression(
                     attributeName: "address1_city",
                     alias: "City",
                     aggregateType: XrmAggregateType.None){
                     HasGroupBy = true
            }
         }
      }
   }
};
query.Orders.Add(new OrderExpression(
            attributeName: "address1_city",
            alias: "City",
            orderType: OrderType.Ascending));

La consulta agrupa los resultados por valor de City, combinando los resultados de las tres filas donde la ciudad es "Redmond".

Total	Total	City
0	1	NULL
6000	1	Dallas
2.900	1	Los Angeles
2.700	1	Lynnwood
4.800	1	Missoula
3.900	1	Phoenix
10.600	3	Sevilla
4.300	1	Santa Cruz

Agrupación por partes de una fecha

Puede seleccionar qué parte de la fecha usar al agrupar por fecha. Establezca XrmAttributeExpression.DateTimeGrouping en uno de los miembros de la enumeración XrmDateTimeGrouping.

Miembro XrmDateTimeGrouping	Descripción
`None`	Sin agrupación DateTime
`Day`	Agrupar por día
`Week`	Agrupar por semana
`Month`	Agrupar por mes
`Quarter`	Agrupar por trimestre
`Year`	Agrupar por año
`FiscalPeriod`	Agrupar por FiscalPeriod
`FiscalYear`	Agrupar por FiscalYear

La siguiente consulta agrupa registros de cuentas que muestran el número de empleados según el momento en que se crearon los registros:

QueryExpression query = new()
{
    EntityName = "account",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "numberofemployees",
                    alias: "Total",
                    aggregateType: XrmAggregateType.Sum)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Day",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Day
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Week",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Week
                }
            },
                                    {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Month",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Month
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "Year",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.Year
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "FiscalPeriod",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalPeriod
                }
            },
            {
                new XrmAttributeExpression(
                    attributeName: "createdon",
                    alias: "FiscalYear",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalYear
                }
            }
        }
    }
};
query.Orders.Add(new OrderExpression(
            attributeName: "createdon",
            alias: "Month",
            orderType: OrderType.Ascending));

La siguiente tabla muestra el resultado usando el conjunto de datos de ejemplo mencionado anteriormente:

 -----------------------------------------------------------------------
 | Total  | Day | Week | Month | Year  | FiscalPeriod     | FiscalYear |
 -----------------------------------------------------------------------
 | 35,200 | 25  | 12   | 3     | 2,023 | Quarter 1 FY2023 | FY2023     |
 -----------------------------------------------------------------------
 | 0      | 27  | 35   | 8     | 2,023 | Quarter 3 FY2023 | FY2023     |
 -----------------------------------------------------------------------

Período fiscal ejemplo de agrupación de fechas

El siguiente ejemplo muestra una QueryExpression que suma el número total de pedidos completados y agrupa el resultado por semestre y año fiscal.

QueryExpression query = new()
{
    EntityName = "salesorder",
    ColumnSet = new ColumnSet(false)
    {
        AttributeExpressions = {
            {
                new XrmAttributeExpression(
                    attributeName: "totalamount",
                    alias: "total",
                    aggregateType: XrmAggregateType.Sum)
            },
            {
                new XrmAttributeExpression(
                    attributeName: "datefulfilled",
                    alias: "date",
                    aggregateType: XrmAggregateType.None){
                    HasGroupBy = true,
                    DateTimeGrouping = XrmDateTimeGrouping.FiscalPeriod
                }
            }
        }
    }
};
query.Orders.Add(new OrderExpression(
            attributeName: "datefulfilled",
            alias: "Date",
            orderType: OrderType.Ascending));

Limitaciones de agregación de QueryExpression

Esta sección describe las capacidades que están disponibles mediante la agregación con FetchXml y que actualmente no están disponibles mediante QueryExpression.

Obtenga un número distinto con CountColumn

No puede obtener una cantidad distinta de valores usando CountColumn con QueryExpression. Obtenga más información sobre los distintos valores de las columnas utilizando FetchXml

Zona horaria al agrupar por fecha

La agrupación por partes de una fecha siempre usa la hora UTC y no hay forma de especificar que se debe usar la zona horaria del usuario en su lugar disponible en FetchXml

Agregación de filas

Cuando una tabla tiene una relación jerárquica definida, no puede devolver un agregado de filas en la columna de búsqueda para la relación jerárquica. Obtenga más información sobre los agregados de filas utilizando FetchXml

Límite por consulta

No hay forma de especificar un límite agregado configurable. Obtenga más información sobre los límites por consulta utilizando FetchXml

Limitaciones

Las consultas que devuelven valores agregados están limitadas a 50.000 registros. Este límite ayuda a mantener el rendimiento y confiabilidad del sistema. Si el criterio de filtra en su consulta devuelve más de 50.000 registros se mostrará el error siguiente:

Número: -2147164125
Código: 8004E023
Mensaje: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Mensaje de error al cliente: Se supera el límite de registros máximos. Reduzca el número de registros.

Para evitar este error, agregue filtros adecuados a la consulta para asegurarse de que no evaluará más de 50.000 registros. Después, ejecute su consulta varias veces y combine los resultados. Los filtros adecuados dependen de la naturaleza de sus datos, pero podrían ser un rango de fechas o un subconjunto de valores en una columna de elección.

Pasos siguientes

Aprenda a contar las filas.

Contar filas

Nota

¿Puede indicarnos sus preferencias de idioma de documentación? Realice una breve encuesta. (tenga en cuenta que esta encuesta está en inglés)

La encuesta durará unos siete minutos. No se recopilan datos personales (declaración de privacidad).

Compartir a través de