API Query en Business Central. Ese gran desconocido
Robyn2018
545
Like
Share
ReportDesarrollo
El objeto Query ha existido durante mucho tiempo en Business Central, pero honestamente, yo no lo usaba mucho y seguro que muchos de los "Dinosaurios" de Navision tampoco.
Una Query, permite obtener datos de una o más tablas combinandolos para generar un dataset. Pero además, permite hacer agregaciones con los datos como medias o sumas de los valores.
Pero... y si además nos permitiese publicar datos y consumirlos en una API. Eso es lo que se llaman Query de tipo API.
Aunque el concepto es similar, el uso es distinto (igual que podemos tener una Page y una API Page).
API Query
Cuando imparto cursos de Power BI, siempre digo que todo lo que pueda obtenerse del origen de los datos, menos tenemos que hacer nosotros, por lo que si esos cálculos que queremos hacer para mostrar una gráfica, ya vienen de Business Central, mejor que mejor.
Creando un API Query en VS Code
Podemos usar las extensiones que tenemos disponibles o bien, usar el snipped tquery - Query of type API para crear el código correspondiente.
query 51100 "Best Customers"
{
APIGroup = 'aprendeBusinessCentral';
APIPublisher = 'rcb';
APIVersion = 'v2.0';
EntityName = 'customerSale';
EntitySetName = 'customerSales';
QueryType = API;
elements
{
dataitem(customer; Customer)
{
column(no; "No.")
{
}
column(name; Name)
{
}
dataitem(sales; "Cust. Ledger Entry")
{
DataItemLink = "Customer No." = customer."No.";
SqlJoinType = LeftOuterJoin;
DataItemTableFilter = "Document Type" = filter(Invoice | "Credit Memo");
column(totalSalesAmount; "Sales (LCY)")
{
Caption = 'Sales (LCY)';
Method = Sum;
}
}
}
}
}
Lo primero que debemos poner es la propiedad QueryType = API que me indicará que lo que hacemos
es indicarle si es API o si es una query normal:
Al igual que en las API Pages, tenemos que rellenar las propiedades del endpoint que tendrá la API Query:
APIGroup = 'aprendeBusinessCentral';
APIPublisher = 'rcb';
APIVersion = 'v2.0';
EntityName = 'customerSale';
EntitySetName = 'customerSales';
Que corresponderá a la URL:
https://api.businesscentral.dynamics.com/v2.0/entorno/api/APIPublisher/
APIGroup/APIVersion/companies({id})/EntitySetName
Que en nuestro caso sería:
https://api.businesscentral.dynamics.com/v2.0/Sandbox/api/rcb/aprendeBusinessCentral
/v2.0/companies({111111111-2222-333-4444-555aaaaaaa})/customerSales
Dentro de elements pondremos los diferentes dataitem (tablas) y columnas (campos) que
queremos exponer. Sería como hacer un report. Podemos anidar dataitems, para lo cual
deberemos usar la propiedad DataItemLink:
DataItemLink = "Customer No." = customer."No.";
Donde le estamos diciendo como relacionamos el dataitem principal (Customer) con el secundario.
En nuestro caso la tabla Customer con la tabla "Cust. Ledger Entry".
La siguente propiedad que tenemos en el dataitem sería SqlJoinType que nos indica que registros serán incluidos en el dataset. Las opciones son, basandonos en nuestro ejemplo:
LeftOuterJoin: El dataset contendrá todos los clientes, incluso si no tienen movimientos de venta.
InnerJoin: Solo mostrará los clientes que tengan movimientos de venta.
RightOuterJoin: El dataset contendrá todos los movimientos de venta, aunque no tuviesen cliente (esto no debería de ocurrir en nuestro ejemplo 😊 )
FullOuterJoin: El resultado contiene todos los datos de clientes y todos los datos de movimientos de clientes.
CrossJoin: Sería el resultado el producto cartesiano. Es decir una combinación de cada cliente con cada movimiento de cliente. En este caso, no debemos de usar la propiedad DataItemLink.
Tienes la documentación oficial aquí:
Otra propiedad que podemos incluir en nuestro dataItem es DataItemTableFilter donde indicamos que filtros queremos añadir a la tabla anidada:
DataItemTableFilter = "Document Type" = filter(Invoice | "Credit Memo");
En este caso, estamos filtrando los "Cust. Ledger Entries" que tengan el "Document Type" "Invoice" o "Credit Memo".
Métodos de agregación
La potencia de las Queries es que podemos agregar datos y para eso tenemos una propiedad en las columnas llamada Method:
column(totalSalesAmount; "Sales (LCY)")
{
Caption = 'Sales (LCY)';
Method = Sum;
}
Los más comunes serían Sum, Count, Average, Min y Max, cuyo resultado es bastante obvio.
Aquí te dejo la documentación oficial por si necesitas más ayuda.
Usando la API Query en Power BI
Una vez que tenemos configurada nuestra extensión, que en este caso solamente tiene esta API Query, ya podemos compilarla y subirla a nuestro entorno.
Aquí, al igual que las API Page, no tenemos que hacer nada. Se exponen directamente por lo que ya podemos acceder desde Power BI Desktop y desde "Obtener datos", seleccionamos Business Central:
Si no estamos identificados, nos pedirá que nos autentiquemos y a partir de ahí, ya podemos seleccionar dentro de Business Central, del entorno donde hayamos publicado nuestra extensión (BC24ES) y de la empresa que queramos (CRONUS ES), las API avanzadas, que es donde encontraremos la nuestra:
La seleccionamos y ya la podemos usar en nuestro Power BI para crear nuestras gráficas:
Como ves, una forma muy sencilla de exponer los datos, directamente con agregaciones, que nos va a permitir obtener información de Business Central de una forma muy potente.
Lógicamente, también vamos a poder llamar a estas API desde cualquier otro software como con cualquier otra API que tengamos, aunque serán de consulta y no de escritura.
This was originally posted here.
*This post is locked for comments