nsepetys, Member
Hi myDBR Representative,
Is it possible to have an excel formula only in the footer of specific columns? If so, how would I go about doing that? The documented component of the formula command seems to indicate the formulas are only for columns in the body of a table.
- Noah
myDBR Team, Key Master
Noah,
you can use dbr.summary.text to insert an Excel formula. You just need to figure out how many rows you have in the result set if you want the formula to calculate column's values for all rows.
--
myDBR Team
nsepetys, Member
Hello,
Thank you for this suggestion. I have attempted to use the following query with the dbr.summary.text but it is throwing an error. Query:
SELECT 'dbr.summary.text','columnname', CONCAT('=SUM(INDIRECT(ADDRESS(ROW() - ', @rowcount, ',COLUMN())):INDIRECT(ADDRESS(ROW() - 1,COLUMN())))');
The above results in the error:
Warning: trim() expects parameter 1 to be string, array given in /var/www/html/reporting/mydbr/lib/external/phpExcel/PHPExcel/Calculation.php on line 3039 Warning: trim() expects parameter 1 to be string, array given in /var/www/html/reporting/mydbr/lib/external/phpExcel/PHPExcel/Calculation.php on line 3048 Fatal error: Uncaught exception 'Exception' with message 'Workbook Name!B22 -> Workbook Name!B22 -> Cyclic Reference in Formula' in /var/www/html/reporting/mydbr/lib/external/phpExcel/PHPExcel/Cell.php:288 Stack trace: #0 /var/www/html/reporting/mydbr/lib/export.php(295): PHPExcel_Cell->getCalculatedValue() #1 /var/www/html/reporting/mydbr/lib/export.php(883): mydbrExport->append_cell() #2 /var/www/html/reporting/mydbr/lib/export.php(1393): mydbrExport->print_summary_row() #3 /var/www/html/reporting/mydbr/lib/export.php(2547): mydbrExport->print_rows() #4 /var/www/html/reporting/mydbr/apps/showReport.php(6531): mydbrExport->print_report() #5 /var/www/html/reporting/mydbr/report.php(391): unknown() #6 {main} thrown in /var/www/html/reporting/mydbr/lib/external/phpExcel/PHPExcel/Cell.php on line 288
I have tried the formula directly in excel and it appears to work without any issues. Let me know if you have any ideas as to how we can work around this.
Edit: I do realize that the formula I'm trying to get into the footer is exactly the same as doing a sum command through myDBR, however, the end-user has specifically requested a formula to "see" the logic being handled independently of myDBR's logic.
myDBR Team, Key Master
This is bit closer to Excel support, but you could use a formula something like this:
SELECT 'dbr.summary.text','columnname', CONCAT('=SUM(INDIRECT(ADDRESS(ROW() - ', @rowcount, ',COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))');
--
myDBR Team
nsepetys, Member
Thank you. I think I figured out the issue that was causing the error above. I believe it was the sum command for the column that I was entering the summary text.
While the previously mentioned summary.text command does work to display the formula it only shows the calculated value when the user hits focuses on the cell and hits return. I am going to guess that there is no way to show the calculated value when the excel file is opened and have the formula behind the scenes like you can do with the formula command. Let me know if I am wrong though.
myDBR Team, Key Master
Noah,
you might want to try out the lastest version. If you add following line to user/defaults.php:
$mydbr_defaults['export']['excel']['aggregate_formula'] = true;
myDBR will use Excel formulas in summary rows instead of calculated constants. So there would be no need for complex Execl formulas and row count calculations.
This is still work in progress as it only calculates the formulas when there are no additional header levels, hence the setting.
Let us know if this works for you.
--
myDNR Team
nsepetys, Member
When exactly was that feature/option pushed? The reason I ask is because we updated myDBR a little over 7 days ago to the latest version and I am not seeing any difference in the handling of the footer formula in excel.
Also, is there any way to apply this aggregate_formula default to just one particular report? I am afraid applying it in the default.php file would call for refactoring/retesting of all other reports (which would be nice to avoid).
myDBR Team, Key Master
The feature was included in build 2804. If you run the updater now (for build 2808), you can also use:
select 'dbr.export.options','excel.aggregate_formula',1;
to enable the feature per report.
--
myDBR Team
nsepetys, Member
awesome. thank you to you and your team. I will let you know if that doesn't work.
You must log in to post.