Hi everyone,
I’m customizing the BIRT templates and I was wondering if I could reference custom fields in the BIRT report. Is this possible? Thanks
Hello,
Yes, you can do it.
Take a look on this page :
Example :
SELECT SaleOrder.attrs->>'customerComment' as "customerComment",
SaleOrder.attrs->>'customerSpecificCode' as "customerSpecificCode",
to_char(to_date(SaleOrder.attrs->>'specificDate', 'YYYY-MM-DD'), 'YYYY') as "specificDate"
FROM sale_sale_order as SaleOrder
Regards
Would this go under Data Sets > InvoiceDB since that is the report I’m modifying and have added a custom json relational field for? I’m looking at the query text for that specific data set and can see this:
select distinct Invoice.id as inv_id,
coalesce(MetaFileTN.file_path, MetaFile.file_path) as logo_path,
Invoice.invoice_id,
Invoice.operation_type_select,
Invoice.status_select as status_select,
Invoice.invoice_date,
Invoice.operation_sub_type_select,
Partner.partner_type_select,
Partner.name as "partnerName",
Partner.first_name as "partnerFirstName",
Partner.partner_seq as "partnerSeq",
Partner.title_select,
Partner.siren,
ContactPartner.name as "contactName",
ContactPartner.first_name as "contactFirstName",
Invoice.address_str,
PaymentCondition.name as "paymentConditionName",
Invoice.due_date,
Currency.code as "currencyCode",
Invoice.in_tax_total as "invoice_in_tax_total",
Invoice.ex_tax_total as "invoice_ex_tax_total",
Invoice.tax_total as "invoice_tax_total",
Company.name as "companyName",
Company.height as "logo_height",
Company.width as "logo_width",
BankDetail.iban,
BankDetail.specific_note_on_invoice,
Bank.code as "bic",
BankAddress.address as "bank_address",
Invoice.financial_discount,
Invoice.note,
Invoice.specific_notes,
Invoice.origin_date,
Invoice.internal_reference,
Invoice.partner_tax_nbr,
Invoice.external_reference,
PrintingSettings.pdf_header as "header",
PrintingSettings.pdf_footer as "footer",
PrintingSettings.logo_position_select as "logoPosition",
AccountConfig.invoice_client_box as "ClientBox",
Accountconfig.sale_invoice_legal_note as "SaleLegalNote",
AccountConfig.display_del_addr_on_printing as "DisplayDelAddr",
AccountConfig.display_tax_detail_on_printing as "displayTaxDetailOnPrinting",
AccountConfig.terms_and_conditions as "termsAndConditions",
AccountConfig.display_partner_seq_on_printing as "displayPartnerSeqOnPrinting",
AccountConfig.display_no_financial_discount_applied_on_printing as "displayNoFinancialDiscountAppliedOnPrinting",
Invoice.hide_discount as "hideDiscount",
AccountConfig.display_product_code_on_printing as "displayProductCodeOnPrinting",
Invoice.subscription_from_date,
Invoice.subscription_to_date,
Invoice.amount_remaining,
Invoice.proforma_comments,
Invoice.head_office_address,
Invoice.delivery_address_str,
Invoice.origin_date as "originDate",
Invoice.supplier_invoice_nb as "supplierInvoiceNb",
AccountConfig.display_head_office_addr_on_invoice_printing as "displayHeadOfficeAddress",
AppSale.enable_pack_management as "enablePackMangement",
AppAccount.manage_financial_discount as "ManageFinancialDiscount",
ContactPartner.title_select as "contactPartnerTitleSelect",
Invoice.original_invoice as "originalInvoice",
OriginalInvoice.invoice_id as "originalInvoiceId",
StockMove.real_date as "StockMoveRealDate",
StockMove.stock_move_seq as "StockMoveSeq",
AppSupplychain.active as "isAppSupplyChainActive",
InvoiceStockMove is not null as "isStockMoveEmpty",
Invoice.display_stock_move_on_invoice_printing as "displayStockMoveOnInvoicePrinting",
Invoice.legal_notice as "legal_notice",
SupplierAccountingSituation.vat_system_select as "supplierVatSystemSelect",
CompanyAccountingSituation.vat_system_select as "companyVatSystemSelect",
(SELECT display_partner_siren_on_invoice_printing FROM account_account_config WHERE company = Company.id) as "display_siren",
Invoice.invoice_product_statement,
AccountConfig.display_items_categories_on_printing as "displayItemsCategoriesOnPrinting",
PrintingSettings.table_font_size as "tableFontSize"
from account_invoice as Invoice
left outer join base_company as Company on (Invoice.company = Company.id)
left outer join studio_app_sale as AppSale on true
left outer join studio_app_account as AppAccount on true
left outer join studio_app as AppSupplychain on AppSupplychain.code = 'supplychain'
left outer join base_partner as Partner on (Invoice.partner = Partner.id)
left outer join base_partner as ContactPartner on (Invoice.contact_partner = ContactPartner.id)
left outer join base_partner as CompanyPartner on (Company.partner = CompanyPartner.id)
left outer join account_payment_condition as PaymentCondition on (Invoice.payment_condition = PaymentCondition.id)
left outer join base_currency as Currency on (Invoice.currency = Currency.id)
left outer join base_bank_details as BankDetail on (Invoice.company_bank_details = BankDetail.id)
left outer join base_bank_address as BankAddress on (BankDetail.bank_address = BankAddress.id)
left outer join base_printing_settings as PrintingSettings on (Invoice.printing_settings = PrintingSettings.id)
left outer join meta_file as MetaFile on (MetaFile.id = Company.logo)
left outer join account_account_config as AccountConfig on (AccountConfig.company = Company.id)
left outer join base_bank as Bank on (BankDetail.bank = Bank.id)
left outer join base_trading_name as TradingName on (TradingName.id = Invoice.trading_name)
left outer join meta_file as MetaFileTN on (MetaFileTN.id = TradingName.logo)
left outer join account_invoice as OriginalInvoice on (Invoice.original_invoice = OriginalInvoice.id)
left outer join account_invoice_stock_move_set as InvoiceStockMove on (InvoiceStockMove.invoice_set = Invoice.id)
left outer join stock_stock_move as StockMove on (StockMove.id = InvoiceStockMove.stock_move_set)
left outer join account_accounting_situation as SupplierAccountingSituation on Partner.id = SupplierAccountingSituation.partner
left outer join account_accounting_situation as CompanyAccountingSituation on CompanyPartner.id = CompanyAccountingSituation.partner
where Invoice.id = ?
and (Invoice.company = SupplierAccountingSituation.company or Invoice.company = CompanyAccountingSituation.company)
Any more insight on this would be helpful. I’ll keep reading up on this. Thanks