Using Invantive Cloud you can create application modules that relate and combine data from multiple Exact Online tables into a comprehensive and real-time overview. These modules can be executed without additional costs for users such as on Power BI or other platforms, plus they use real-time data.
Often, an export is done to Excel XLSX format for further processing. As described in Draaitabel of rapport baseren op gegevens in een ander (extern) Excel-bestand (Dutch) these Excel XLSX-files can be used as source for sales pivot tables.
However, the Microsoft Excel version for Mac does not support Excel files out-of-the-box as an external file format. One of the possibilities is to use a CSV-file as external file format for your pivot tables on Mac Excel.
The following module shows how to provide an output format selector and how to differentiate in output between Excel XLSX and CSV. This can easily be extended to include for xml, for json and for ndjson as illustrated in Generate JSON from Invantive SQL queries.
The web page is shown in French since this user was logged on using a French profile:
Module code:
declare
p_run boolean := false;
p_cfn_factuur_datum_vanaf date := null;
p_cfn_factuur_datum_tot date := null;
p_format varchar2 := null;
--
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_mime_type varchar2;
l_file_name varchar2;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http.get_request_query_parameter_value('p_run') as boolean);
--
p_cfn_factuur_datum_vanaf := to_date(cloud_http.get_request_query_parameter_value('p_cfn_factuur_datum_vanaf'), 'YYYY-MM-DD');
p_cfn_factuur_datum_tot := to_date(cloud_http.get_request_query_parameter_value('p_cfn_factuur_datum_tot'), 'YYYY-MM-DD');
p_format := cloud_http.get_request_query_parameter_value('p_format');
--
if coalesce(p_run, false) = false
then
cloud_http.set_use_template(true);
cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
cloud_http.append_line_to_response_body_text('<form>');
cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
cloud_http.append_line_to_response_body_text('<ul>');
cloud_http.append_line_to_response_body_text('<li><label for="p_cfn_factuur_datum_vanaf">' || translate_resources('{res:itgen_invoice_date_from}') || '</label></li><li><input id="p_cfn_factuur_datum_vanaf" name="p_cfn_factuur_datum_vanaf" type="date" value="' || to_char(add_months(trunc(sysdateutc, -1), -1), 'YYYY-MM-DD') || '"/></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_cfn_factuur_datum_tot">' || translate_resources('{res:itgen_invoice_date_to}') || '</label></li><li><input id="p_cfn_factuur_datum_tot" name="p_cfn_factuur_datum_tot" type="date" value="' || to_char(add_months(trunc(sysdateutc, -1), +1), 'YYYY-MM-DD') || '"/></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_format">' || translate_resources('{res:itgen_output_format}') || '</label></li><li><select name="p_format"><option selected="selected" value="xlsx">XLSX</option><option value="csv">CSV</option></select></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Start"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Check parameters.
--
-- No checks.
--
-- Create a list of sales invoice lines.
--
create or replace table rpt@inmemorystorage
as
select /*+ result_set_name("SalesInvoiceLines") */
sie.invoicenumber
label 'Invoice Number'
, sle.LineNumber
label 'Line Number'
, sie.invoicedate
label 'Invoice Date'
, sie.ordernumber
label 'Order Number'
, sie.orderdate
label 'Order Date'
, sie.SalespersonFullName
label 'Sales Person Full Name'
, actord.code
label 'Ordered by Code'
, actord.name
label 'Ordered by Name'
, cttord.fullname
label 'Ordered by Contact'
, amrord.FullName AccountManagerFullName
label 'Ordered by Account Manager'
, actord.Email
label 'Ordered by Email'
, actdlr.code
label 'Deliver to Company Code'
, actdlr.name
label 'Deliver to Company Name'
, sie.delivertocontactpersonfullname
label 'Deliver to Contact'
, adsdlr.addressline1
label 'Deliver to Address Line 1'
, adsdlr.postcode
label 'Deliver to Postcode'
, adsdlr.city
label 'Deliver to City'
, adsdlr.country
label 'Deliver to Country'
, actdlr.email
label 'Deliver to Email'
, amrive.FullName AccountManagerFullName
label 'Invoice Account Manager'
, active.name
label 'Invoice Company Name'
, active.Email
label 'Invoice Email'
, active.AddressLine1
label 'Invoice Address Line 1'
, active.Postcode
label 'Invoice Postcode'
, active.City
label 'Invoice City'
, active.Country
label 'Invoice Country'
, sie.duedate
label 'Due Date'
, gat.Code GLAccountCode
label 'General Ledger Account'
, igp.code itemgroupcode
label 'Item Group'
, sle.itemcode
label 'Item Code'
, sle.itemdescription
label 'Item Description'
, sle.description
label 'Description'
, sim.SupplierCode
label 'Main Supplier Code'
, sim.SupplierDescription
label 'Main Supplier Name'
, sle.StartTime
label 'Start'
, sle.EndTime
label 'End'
, sle.quantity
label 'Quantity'
, sle.UnitDescription
label 'Unit Description'
, sle.NetPrice
label 'Net Price'
, sle.unitprice
label 'Unit Price'
, sle.discount * 100
label 'Discount'
, sle.unitprice * ( 1 - sle.discount )
label 'Net Price per Unit'
, sle.netamountdc
label 'Net Price Total'
, sle.unitprice * sle.discount * sle.quantity
label 'Amount Discount'
, sle.VATAmountDC
label 'VAT Amount (EUR)'
, itm.costpricecurrency
label 'Cost Price Currency'
, itm.costpricestandard
label 'Cost Price Standard'
, itm.costpricestandard * sle.quantity
label 'Standard Cost Price Total'
, itm.NetWeight
label 'Net Weight'
, itm.GrossWeight
label 'Gross Weight'
from exactonlinerest..salesinvoicesbulk@eol sie
join exactonlinerest..salesinvoicelinesBulk@eol sle
on sle.division = sie.division
and sle.invoiceid = sie.invoiceid
join ItemsIncremental@eol itm
on itm.division = sle.division
and itm.id = sle.item
join ItemGroups@eol igp
on igp.division = itm.division
and igp.id = itm.ItemGroup
join GLAccountsIncremental@eol gat
on gat.Id = sle.GLAccount
join AccountsIncremental@eol actord
on actord.division = sie.division
and actord.id = sie.orderedby
left
outer
join exactonlinerest..users@eol amrord
on amrord.userId = actord.AccountManager
left
outer
join ContactsIncremental@eol cttord
on cttord.id = sie.orderedbycontactperson
join AccountsIncremental@eol actdlr
on actdlr.id = sie.deliverto
left
outer
join AddressesIncremental@eol adsdlr
on adsdlr.id = sie.delivertoaddress
join AccountsIncremental@eol active
on active.id = sie.invoiceto
left
outer
join exactonlinerest..users@eol amrive
on amrive.userId = actord.AccountManager
left
outer
join SupplierItems@eol sim
on sim.division = sle.division
and sim.ItemCode = sle.itemcode
and sim.MainSupplier = true
where sie.status = 50
and sie.invoicedate between coalesce(p_cfn_factuur_datum_vanaf, sie.invoicedate) and coalesce(p_cfn_factuur_datum_tot, sie.invoicedate)
order
by sie.invoicenumber
, sie.ordernumber
, sle.linenumber
;
if p_format = 'xlsx'
then
--
-- Create an Excel workbook.
--
l_workbook := excel.new();
--
-- Add a worksheet for Exact Online sales invoice lines.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'SALES SOURCE DATA'
);
--
-- Place the items in the Excel worksheet.
--
excel.fill_using_query(l_worksheet, 'select * except rowid$ from rpt@inmemorystorage');
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'FILTER'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Sales Invoices');
excel.set_cell_contents(l_worksheet, 'A3', translate_resources('{res:itgen_user}'));
excel.set_cell_contents(l_worksheet, 'B3', sys_context('USERENV', 'USER_FULL_NAME', 'eol'));
--
excel.set_cell_contents(l_worksheet, 'A4', translate_resources('{res:itgen_generated}'));
excel.set_cell_contents(l_worksheet, 'B4', to_char(sysdateutc, 'DD-MM-YYYY HH24:MI:SS'));
--
excel.set_cell_contents(l_worksheet, 'A6', translate_resources('{res:itgen_invoice_date_from}'));
excel.set_cell_contents(l_worksheet, 'B6', p_cfn_factuur_datum_vanaf);
--
excel.set_cell_contents(l_worksheet, 'A7', translate_resources('{res:itgen_invoice_date_to}'));
excel.set_cell_contents(l_worksheet, 'B7', p_cfn_factuur_datum_tot);
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
l_mime_type := 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
l_file_name := 'Sales Invoices Fixed Format.xlsx';
else -- CSV
select unicode_to_blob(csv)
into l_binary
from ( select *
from rpt@inmemorystorage
for csv
, output per 1000000 rows
)
;
l_mime_type := 'text/csv; charset=utf-8';
l_file_name := 'Sales Invoices Fixed Format.csv';
end if;
--
-- Return the XLSX file to the user.
--
cloud_http.set_response_body_binary(l_binary);
cloud_http.set_response_content_type(l_mime_type);
cloud_http.set_response_header_value
( 'Content-Disposition'
, 'attachment; filename="' || l_file_name || '"'
);
end if;
end;