I already was doing that by way of a call to a helper procedure
CALL sp_DBR_handle_pdf(clientdb, inExportFormat, inAutoReportProcedure);
But in any case, I tried to explicitly issue the landscape option as a first command in the report and it still did not work, when the Pie chart was being displayed.
Here is the full code of my report (thought please note that the grid display for the PDF is handled by a called procedure at the end of this block of code).
DROP PROCEDURE IF EXISTS sp_DBR_aging_report
$$
CREATE PROCEDURE `sp_DBR_aging_report`(IN clientdb VARCHAR(100),
IN _location_name VARCHAR(100), IN _portfolio_name VARCHAR(100), IN _status VARCHAR(50), IN _useLMS VARCHAR(10), IN _dealer VARCHAR(100), IN _batch VARCHAR(100), IN _loan_history_date VARCHAR(50),
inExportFormat VARCHAR(50), inAutoReportProcedure VARCHAR(100), GET_toggle1 VARCHAR(100))
BEGIN
# Important: "automatic myDBR parameters" such as inExportFormat, inAutoReportProcedure, and GET_toggle are populated by myDBR and thus should be the last parameters.
# Otherwise the report output might prompt you for input.
select 'dbr.export.options', 'orientation', 'landscape';
# The next line handles PDF Export according to Verifacto specifications.
CALL sp_DBR_handle_pdf(clientdb, inExportFormat, inAutoReportProcedure);
# Parse the _loan_history_date parameter. It could be a special date (via a 4 letter code) or it could be a regular calendar date.
IF (_loan_history_date = '((TODAY))') THEN
SELECT CURDATE() INTO @loan_history_date;
ELSEIF (_loan_history_date = '((YESTERDAY))') THEN
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) INTO @loan_history_date;
ELSEIF (_loan_history_date = '((FDTM))') THEN # # First day of this month
SELECT DATE_FORMAT(CURDATE() ,'%Y-%m-01') INTO @loan_history_date;
ELSEIF (_loan_history_date = '((LDTM))') THEN # # Last day of this month
SELECT LAST_DAY(CURDATE()) INTO @loan_history_date;
ELSEIF (_loan_history_date = '((FDLM))') THEN # # First day of last month
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ,'%Y-%m-01') INTO @loan_history_date;
ELSEIF (_loan_history_date = '((LDLM))') THEN # Last day of last month
SELECT LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) INTO @loan_history_date;
ELSEIF (_loan_history_date = '((FDTY))') THEN # First day of this year
SELECT DATE_FORMAT(CURDATE() ,'%Y-01-01') INTO @loan_history_date;
ELSEIF (_loan_history_date = '((LDTY))') THEN # Last day of this year
SELECT DATE_FORMAT(CURDATE() ,'%Y-12-31') INTO @loan_history_date;
ELSEIF (_loan_history_date = '((FDLY))') THEN # First day of last year
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR) ,'%Y-01-01') INTO @loan_history_date;
ELSEIF (_loan_history_date = '((LDLY))') THEN # Last day of last year
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR) ,'%Y-12-31') INTO @loan_history_date;
ELSE
SET @loan_history_date := _loan_history_date;
END IF;
# AS OF JULY 23, 2023 On testb, THIS REPORT WORKS WITH ARROW DB BUT WILL NOT DISPLAY OUTPUT ON AZ-ARK BECAUSE IT HAS NOT DATA IN THE bf_metric_history_loan table.
# ALSO IT WON'T WORK ON MY LOCAL VM UNTIL I GET IMPORTED bf_metric_history_loan DATE INTO clearfacto.bf_metric_history_loan FROM ga-arrow on testb.
# For _loan_history_date try Dec 1, 2021 2021-12-01
# First set default values for the parameters to the empty string so that "like" will return all the rows
IF (_location_name IS NULL) OR (_location_name = "") OR (_location_name = "((ALL))") THEN
SET @location_name_clause = "";
ELSE
SET @location_name_clause := CONCAT(' AND (bfv_re_account.location_name = "', _location_name, '")');
END IF;
IF (_portfolio_name IS NULL) OR (_portfolio_name = "") OR (_portfolio_name = "((ALL))") THEN
SET @portfolio_name_clause = "";
ELSE
SET @portfolio_name_clause := CONCAT(' AND (bfv_re_account.portfolio_name = "', _portfolio_name, '")');
END IF;
# Note that for the status filter for the Aging report the filter asks to find items NOT equal to the status
# However, I assume that if the user chose ((ALL)) he really meant to include ALL and not exclude it.
IF (_status IS NULL) OR (_status = "") OR (_status = "((ALL))") THEN
SET @status_clause = "";
ELSE
SET @status_clause := CONCAT(' AND (bfv_re_account.status != "', _status, '")');
END IF;
IF (_useLMS IS NULL) OR (_useLMS = "") OR (_useLMS = "((ALL))") THEN
SET @use_lms_clause = "";
ELSE
SET @use_lms_clause := CONCAT(' AND (bfv_re_account.use_lms = "', _useLMS, '")');
END IF;
IF (_dealer IS NULL) OR (_dealer = "") OR (_dealer = "((ALL))") THEN
SET @dealer_clause = "";
ELSE
SET @dealer_clause := CONCAT(' AND (bf_dealer.name = "', _dealer, '")');
END IF;
IF (_batch IS NULL) OR (_batch = "") OR (_batch = "((ALL))") THEN
SET @batch_clause = "";
ELSE
SET @batch_clause := CONCAT(' AND (bf_loan_batch.name = "', _batch, '")');
END IF;
# Execute a query and put results into a temporary table so we can use it for Bar Chart, Pie Chart, and Grid
SET @query := CONCAT(' CREATE TEMPORARY TABLE tmp_table_aging AS ',
'SELECT `bfv_re_account`.`account_number` AS "dbr.html:Account
Number",',
' `bfv_re_account`.`status` AS "Status",',
' `bfv_re_account`.`location_name` AS "dbr.html:Location
Name",',
' `bfv_re_account`.`portfolio_name` AS "dbr.html:Portoflio
Name",',
' `bfv_re_customer`.`first_name` AS "dbr.html:First
Name",',
' `bfv_re_customer`.`last_name` AS "dbr.html:Last
Name",',
' `bfv_re_loan`.`loan_method_precomputed` AS "dbr.html:Pre
computed",',
' `bfv_re_loan`.`principal_balance` AS "dbr.html:Principal
Balance",',
' `bfv_re_loan`.`gross_balance` AS "dbr.html:Gross
Balance",',
' `bf_metric_history_loan`.`principal_balanace` AS "dbr.html:Historical
Principal Bal.",', # Note DB field named principal_balanace
' `bf_metric_history_loan`.`gross_balance` AS "dbr.html:Historical
Gross Bal.",',
' `bfv_re_loan`.`payment_current_due` AS "dbr.html:Payment
Current Due",',
' DATE_FORMAT(`bfv_re_loan`.`payment_current_due_date`, "%m/%d/%Y") AS "dbr.html:Payment Current
Due Date",',
' ROUND(`bfv_re_loan`.`days_since_current_due_date`, 0) AS "dbr.html:Days Since
Current Due Date",',
' `bfv_re_loan`.`lateness_category` AS "dbr.html:Lateness
Category",',
' DATE_FORMAT(`bf_metric_history_loan`.`data_date`, "%m/%d/%Y") AS "dbr.html:Date
Recorded",',
' `bf_dealer`.`name` AS "Dealer Name",',
' `bf_loan_batch`.`name` AS "dbr.html:Batch
Number",',
' DATE_FORMAT(`bfv_re_loan`.`payment_last_date`, "%m/%d/%Y") AS "dbr.html:Last
Payment",',
' ROUND(`bfv_re_loan`.`days_since_last_payment`, 0) AS "dbr.html:Days Since
Payment"',
' FROM ', clientdb, '.`bfv_re_aacl`',
' JOIN ', clientdb, '.bf_loan ON (bf_loan.id = bfv_re_aacl.loan_id)',
' JOIN ', clientdb, '.bfv_re_account ON (bfv_re_account.id = bfv_re_aacl.account_id)',
' JOIN ', clientdb, '.bfv_re_customer ON (bfv_re_customer.id = bfv_re_aacl.customer_id)',
' JOIN ', clientdb, '.bfv_re_loan ON (bfv_re_loan.id = bf_loan.id)',
' JOIN ', clientdb, '.bf_metric_history_loan ON (bf_metric_history_loan.loan_id = bfv_re_aacl.loan_id)',
' LEFT JOIN ', clientdb, '.bf_dealer ON (bfv_re_aacl.dealer_id = bf_dealer.id)',
' LEFT JOIN ', clientdb, '.bf_loan_batch ON (bfv_re_aacl.batch_id = bf_loan_batch.id)',
' WHERE (1)',
@location_name_clause,
@portfolio_name_clause,
@status_clause,
@use_lms_clause,
' AND ( `bf_metric_history_loan`.`data_date` = "', @loan_history_date, '" )',
@dealer_clause,
@batch_clause,
' LIMIT 9999999'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/*
f I display the Pie chart, then when I export to PDF it doesn't export in landscape mode (resulting in some of the columns being cut off.
If I take out the Pie Chart then it does do landscape.
For now, I will display the Pie Chart only on the web page, and then when exporting to PDF I condition out the Pie Chart, and then it displays landscape.
*/
#IF (inExportFormat = "") THEN
# Display a title above the charts, as opposed to the default which is the report name
#SELECT 'dbr.title', 'Aging Report with charts showing group by Lateness Category';
SELECT 'dbr.title', 'dbr.html:<b>Aging Report with charts showing group by Lateness Category</b>';
# Display the Pie chart and Bar chart side by side.
SELECT 'dbr.keepwithnext';
# PIE CHART
select 'dbr.chart', 'Pie';
select 'dbr.colstyle', 'value', '{percent|1}% {value}\n[name]"';
SELECT `dbr.html:Lateness<br>Category` as 'name', COUNT(`dbr.html:Lateness<br>Category`) AS `value`, COUNT(`dbr.html:Lateness<br>Category`)
FROM tmp_table_aging
GROUP BY `dbr.html:Lateness<br>Category`;
#END IF;
# BAR CHART
/*
select 'dbr.chart', 'Column', '', '330', '330'; # Used hard coded sizes because chart.scale no longer works with new version of wkhtmltopdf
#select 'dbr.chart.options', 'chart.scale', 40; # With wkhtmltopdf QT version this causes the bar chart to disappear in PDF
SELECT `dbr.html:Lateness<br>Category`, COUNT(`dbr.html:Lateness<br>Category`) AS `value`, COUNT(`dbr.html:Lateness<br>Category`)
FROM tmp_table_aging
GROUP BY `dbr.html:Lateness<br>Category`;
*/
# SUMMARY - Laeeq said it could be instead of the Bar Chart
select 'dbr.resultclass', 'AlternatingRowColors';
select 'dbr.hidecolumns', '# Days Late';
SELECT `dbr.html:Lateness<br>Category` AS `Lateness Category`,
COUNT(`dbr.html:Lateness<br>Category`) AS `# Accounts`,
SUM(`dbr.html:Historical<br>Principal Bal.`) AS `Principal Balance`,
SUM(`dbr.html:Days Since<br>Current Due Date`) AS `# Days Late`
FROM tmp_table_aging
GROUP BY `dbr.html:Lateness<br>Category`
ORDER by `# Days Late`;
# Unless we are exporting the report, display a button which when pressed will run and display the detailed/grid part of the report.
IF (inExportFormat = "") THEN
/*
The myDBR format for this kind of functionality is:
1) A dbr.button command with a parameter for the button text and a parameter for the button style
2) A dbr.report command with the stored procedure name you want to invoke followed by name/value pairs as parameters
The name/value pairs must be such that name is the actual name of the parameter, and value is the name of the column (from the subsequent query) which contains the value you want to pass.
P.S. The value names (i.e. parameter value variables) should be different than the stored procedures parameter names.
3) A query which populates the parameter value variables with values that you want to pass.
*/
select 'dbr.button', 'Click for full report', 'btn-primary-verifacto';
select 'dbr.report', 'sp_DBR_aging_report_details', 'clientdb=the_clientdb', '_location_name=the_location_name', '_portfolio_name=the_portfolio_name', '_status=the_status',
'_useLMS=_useLMS', '_dealer=the_dealer', '_batch=the_batch', '_loan_history_date=the_loan_history_date', 'linked_output[]';
SET @query := CONCAT(
'SELECT "', clientdb, '" AS the_clientdb,',
' "', _location_name, '" AS the_location_name,',
' "', _portfolio_name, '" AS the_portfolio_name,',
' "', _status, '" AS the_status,',
' "', _useLMS, '" AS _useLMS,',
' "', _dealer, '" AS the_dealer,',
' "', _batch, '" AS the_batch,',
' "', _loan_history_date, '" AS the_loan_history_date'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
# select clientdb, _location_name, _portfolio_name, _status, _useLMS, _dealer, _batch, _loan_history_date, inExportFormat, inAutoReportProcedure, GET_toggle1;
# Output the Details section for the case of export
IF ((inExportFormat = 'xlsx') OR (inExportFormat = 'pdf') OR (inExportFormat = 'csv')) THEN
CALL sp_DBR_aging_report_details(clientdb, _location_name, _portfolio_name, _status, _useLMS, _dealer, _batch, _loan_history_date, inExportFormat, inAutoReportProcedure, GET_toggle1);
END IF;
END
$$
