Buenas una semana mas, hoy os quiero compartir un descubrimiento que recientemente he tenido que trastear.
No es otro que los informes creados con Excel, Esto no es nuevo, en verdad aunque yo no los había usado hasta ahora.
Lo que se hace es crear un informe, como siempre pero el lugar de poner un layouts de RDLC o de Word(podéis encontrar un ejemplo aquí), lo que hacemos es crear un layouts con una Base de Excel y para que descargue un Excel.
Veamos como funciona ¡Vamos manos a la obra!🤩
Estructura del informe
Este no varia mucho de como se hace un informe en cualquier de los tipos de layouts, la única mención que voy ha hacer es que la tabla de configuraciones, que es la de información de empresa la pongo a parte para que solo me devuelva un registro. Y que los labels los pondré en el grupo de labrels para que aparezcan en el Excel.
Por el resto como he dicho es muy similar a cualquier informe.
report 50100 "ReportExcel"
{
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
DefaultRenderingLayout = LayoutExcel;
ExcelLayoutMultipleDataSheets = true;
Caption = 'Sales Invoice', Comment = 'ESP="Factura de venta"';
dataset
{
dataitem(SalesInvoiceHeader; "Sales Invoice Header")
{
RequestFilterFields = "No.";
column(CustAddr1_SalesInvoiceHeader; CustAddr[1]) { }
column(CustAddr2_SalesInvoiceHeader; CustAddr[2]) { }
column(CustAddr3_SalesInvoiceHeader; CustAddr[3]) { }
column(CustAddr4_SalesInvoiceHeader; CustAddr[4]) { }
column(CustAddr5_SalesInvoiceHeader; CustAddr[5]) { }
column(CustAddr6_SalesInvoiceHeader; CustAddr[6]) { }
column(CustAddr7_SalesInvoiceHeader; CustAddr[7]) { }
column(CustAddr8_SalesInvoiceHeader; CustAddr[8]) { }
column(DocNo_SalesInvoiceHeader; SalesInvoiceHeader."No.") { }
column(PostingDate_SalesInvoiceHeader; SalesInvoiceHeader."Posting Date") { }
dataitem(SalesInvoiceLine; "Sales Invoice Line")
{
DataItemLink = "Document No." = field("No.");
DataItemLinkReference = SalesInvoiceHeader;
column(No_SalesInvoiceLine; SalesInvoiceLine."No.") { }
column(Description_SalesInvoiceLine; SalesInvoiceLine.Description) { }
column(Quantity_SalesInvoiceLine; SalesInvoiceLine.Quantity) { }
column(UnitPrice_SalesInvoiceLine; SalesInvoiceLine."Unit Price") { }
column(Amount_SalesInvoiceLine; SalesInvoiceLine.Amount) { }
column(AmountVAT_SalesInvoiceLine; SalesInvoiceLine."Amount Including VAT" - SalesInvoiceLine."VAT Base Amount") { }
column(AmountIncludingVAT_SalesInvoiceLine; SalesInvoiceLine."Amount Including VAT") { }
}
#region triggers Header
trigger OnAfterGetRecord()
var
Language: Codeunit Language;
FormatAddress: Codeunit "Format Address";
begin
Clear(Language);
CurrReport.Language := Language.GetLanguageIdOrDefault(SalesInvoiceHeader."Language Code");
Clear(FormatAddress);
FormatAddress.SalesInvBillTo(CustAddr, SalesInvoiceHeader);
end;
#endregion
}
dataitem(CompanyInformation; "Company Information")
{
column(CompanyInFormation_Name; CompanyInFormation.Name) { }
column(CompanyInFormation_Address; CompanyInFormation.Address) { }
column(CompanyInFormation_City; CompanyInFormation.City) { }
column(CompanyInFormation_PostCode; CompanyInFormation."Post Code") { }
column(CompanyInFormation_County; CompanyInFormation.County) { }
column(CompanyInFormation_CountryName; CompanyInFormationCountryName) { }
trigger OnAfterGetRecord()
var
BankAccount: Record "Bank Account";
begin
if CountryRegion.Get(CompanyInFormation."Country/Region Code") then
CompanyInFormationCountryName := CountryRegion.Name
else
CompanyInFormationCountryName := CompanyInFormation."Country/Region Code";
end;
}
}
rendering
{
layout(LayoutExcel)
{
Type = Excel;
LayoutFile = './src/report/layouts/ReportExcel.xlsx';
}
}
labels
{
#region Labels para la factura
CompanyLbl = 'Company', Comment = 'ESP="Empresa"';
CustomerLbl = 'Customer', Comment = 'ESP="Cliente"';
DateLbl = 'Date', Comment = 'ESP="Fecha"';
PartNoLbl = 'Part Number', Comment = 'ESP="Referencia"';
DescriptionLbl = 'Description', Comment = 'ESP="Descripción"';
QtyLbl = 'Quantity', Comment = 'ESP="Cantidad"';
UnitPriceLbl = 'Unit price', Comment = 'ESP="Precio unitario"';
TotalAmountLbl = 'Total amount', Comment = 'ESP="Importe total"';
VatLbl = 'VAT:', Comment = 'ESP="IVA:"';
TotalInvoiceLbl = 'Total Invoice:', Comment = 'ESP="Total Factura"';
BankDetailsLbl = 'Bank details:', Comment = 'ESP="Detalles banco:"';
#endregion
}
var
CountryRegion: Record "Country/Region";
CompanyInFormationCountryName: Text[50];
CustAddr: array[8] of Text[50];
}Layout Excel
En este apartado es donde nos vamos a entretener un poco mas.
Lo primero es compilar el informe para que genere el Excel con las configuraciones iniciales. Lo abrimos y veremos que hay varias pestañas, en la que nos vamos a centrar es la que poner la cabecera de factura que es donde estará todo la información tanto de cabecera como de líneas.
Veremos que hay una tabla con los nombres de las columans que hemos configurado en los DataItems.
Seleccionamos cualquiera de las celdas que hay en la tabla y presionamos el botón de «una tabla o rango», este nos abrirá el Power Query con la estructura de dicha tabla.
Si requieres mas información sobre que es Power Query o como se usa, es fácil de encontrarlo en cualquier buscador.
Lo que realizaremos en este punto es quitar las columnas que no queremos que en este caso son las de cabecera. Para ello vamos a la acción de elegir columnas.
Después seleccionamos solo las columnas que nos interesan, en este caso solo las de las líneas.
Cuando lo tenemos cambiamos el nombre a la conexión y guardamos y cerramos.
Al realizar esta acción nos generara una nueva página, la podemos usar ya directamente o eliminar y crear la página de facturas des de 0, yo prefiero la segunda opción 😁
Estructura de la impresión
Creamos la nueva pestaña de Excel, yo le he puesto «Factura» como nombre. El nombre de la pestaña no puede ser dinámico o dará errores con las consultas de Power Query.
Creamos toda la estructura tal y como la queremos con los títulos.
Para añadir los datos de la tabla de configuraciones, que solo tendrá un único registro lo que hacemos es lo siguiente: «NombreDataItem[NombreColumna]»
Esto para cada uno de los campos que queramos mostrar del DataItem de configuración de empresa.
Para los datos de cabecera, será un poco diferente. usaremos la función de índice. La estructura es: «INDICE(«NombreDataItem[NombreColumna];1)»
Esto para cada uno de los campos que queramos mostrar del DataItem de la cabecera, Sobre todo esto se usa para la dirección del cliente y algunos datos como el numero de factura o la fecha de registros.
Por ultimo para el tema de la estructura, son los labels, estos son mas sencillos de usar y se pueden añadir poniendo entre ellos el símbolo «$», lo que realizara Business Central es cambiar lo que se ponga entre ese símbolo y pondrá el label que toque.
Líneas de la factura en el Excel
Llegados a este punto solo nos queda la parte de las líneas de la factura y lo realizaremos con la consulta de Power Query que habíamos realizado anteriormente.
Seleccionamos la primera celda donde estarán dichas líneas, vamos a la pestaña de datos y clicamos en la opcion de «Conexiones Existentes»
Esto nos mostrara un mensaje este tiene que estar con las configuraciones que nos salen predeterminadas.
Esto nos creara una tabla con las líneas y a mas tendrá las cabeceras, si queremos que no aparezcan porque ya las hemos configurado anteriormente lo podemos deshabilitar de la siguiente manera:
Ahora realizaremos unas configuraciones necesarias para el buen funcionamiento de la consulta de Power Query.
Lo primero es revisar que la tabla al ser rellenada no autoajuste las columnas y que se queden con el tamaño que hemos configurado y también que la insertar las nuevas columnas lo que se haya configurado por debajo de la tabla no sea eliminado.
Esto lo hacemos de la siguiente manera.
También debemos decirle a la consulta que se actualice al abrir el documento de Excel. Esto se realiza de la siguiente manera:
Totales de factura en el Excel
Estamos llegando al final, ahora podemos realizar unos totales para que la factura tenga todos los datos minimos y podamos verla correctamente.
Esto lo realizaremos con los labels y sumando los campos que necesitamos.
Para la suma de los campos usaremos la función de suma y haremos referencia a la tabla de las líneas, para cada campos. La estructura de la función: «SUMA(NombreTablaLineas[[#Todo];[NombreCampo]])»
Cuando hemos acabado de configurar los campos, como en mi caso no me interesa que se vean las dos ultimas columnas las voy a ocultar.
También ocultare todas las demás pestañas para solo dejar la de la factura.
Subimos la extensión, ejecutamos el informe y este tipo de informe veremos que al ejecutarlo es un poco diferente, ya que no nos deja imprimir y nos da la opción de descargar, esto lo que hará es rellenar todos los datos en un Excel y descargar dicho Excel.
El Excel resultante, podemos ver que ya tiene los datos rellenados con la estructura que le hemos configurado.
Si tenemos problemas con el centro de confianza o queremos configurar nuestros Excel para evitarlo hay mas información aquí.
En resumen, la integración de Excel como herramienta de generación de informes en Business Central ofrece una solución versátil y eficiente para la visualización y análisis de datos. Al aprovechar esta funcionalidad, los usuarios pueden agilizar procesos, mejorar la presentación de información y tomar decisiones más informadas.
Como siempre este ejemplo entero lo tenéis colgado en GitHub.