Custom Summary Values in report page

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
sangnandar
User
Posts: 980

Custom Summary Values in report page

Post by sangnandar »

Case: Project Demo, page: sales_by_customerreport.php

Summary for Order ID: 10643 (3 Detail Records)
Sum $75.60 $814.50

I want to add this line after Sum,
Sum_Diff ($814.50 - $75.60) // Extended Price - Unit Price

Could I do that?


mobhar
User
Posts: 11660

Post by mobhar »

Yes, you could do that.


sangnandar
User
Posts: 980

Post by sangnandar »

Where should I put the code within Server-Events/Client-Scripts ?
And how to call the variable?

I've modified the file sales_by_customerreport.php looks but like this line
$(report_name)->ResetAttrs(); // always reset the variable

I've modified the file successfully and produce the result as I want by defining new variable
$a = $(report_name)->(fieldname_1)->CurrentValue ;
$b = $(report_name)->(fieldname_2)->CurrentValue ;
$c = ew_FormatCurrency(($a-b), 0, 0, -1, -1) ;
And then insert $c manually within the file.

But I'm looking for Server-Events/Client-Scripts trick to avoid editing the file every time I regenerate the project.

Thanks.


arbei
User
Posts: 9288

Post by arbei »

In the Table/View, you can create a Custom Field to display the different between 2 field then use it to create the report.

Read help file topic: "Project Setup" -> "Custom Fields" for more information.


sangnandar
User
Posts: 980

Post by sangnandar »

arbei wrote:
In the Table/View, you can create a Custom Field to display the different between 2
field then use it to create the report.

I need the different between 2 aggregates (not between 2 fields).

In case: Project Demo, page: sales_by_customerreport.php it should be like this:

Summary for Order ID: 10643 (3 Detail Records)
Sum $75.60 $814.50 // 2 <td> tag. (Sum of Extended Price) and (Sum of Unit Price) // this is built-in summary values in report settings.
// I need to add,

Sum_Diff ($814.50 - $75.60) // 1 <td> tag. (Sum of Extended Price)- (Sum of Unit Price) // I need to add custom summary values.

Thanks.


mobhar
User
Posts: 11660

Post by mobhar »

What arbei meant is, you should be able to optimize the Custom Fields in order to get the difference between two values in each row in the report, and then activate Aggregate function (TOTAL) for that Custom Field in order to get the Total of Difference.


sangnandar
User
Posts: 980

Post by sangnandar »

The problem is I need this layout,
Sum <a value> <b value>
Sum_Diff <diff value>
What arbei suggested will produce this layout,
Sum <a value> <b value> <diff value>


mobhar
User
Posts: 11660

Post by mobhar »

Currently it is not supported, unless you customize the generated script files as you mentioned previously above.


arbei
User
Posts: 9288

Post by arbei »

You can update the text of the aggregated value with the Startup Script

For example:
$("tr:has(td.ewGroupAggregate)").each(function(){ // Get the TR which showing aggragate values.
var item = $(this).children(":nth-child(5)"); // Getting the 5th TD to change it text.
item.text("Your text" + item.text()); // Adding "Your Text" before the value.
});

You can Google jQuery function nth-child, text() and :has functions for more information.


sangnandar
User
Posts: 980

Post by sangnandar »

Great arbei, it works.
Just one problem left. Is there built-in function to reverse ew_FormatCurrency ?
I need to convert this string $75.60 to its original value.
I can manually reformat the string but would be better if there is reverse ew_FormatCurrency that I can use.


sangnandar
User
Posts: 980

Post by sangnandar »

Here is my working code, hope it helps someone else.

Case

Summary for Order ID: 10643 (3 Detail Records)
Sum $75.60 $814.50

Sum_Diff ($814.50 - $75.60) // Extended Price - Unit Price

Solution

I manipulate the built-in summary values of Average

$("tr:has(td.ewGroupAggregate):contains('Average')").each(function(){ // Get the <tr> of the Average
var sum_1 = $(this).prev().children(":nth-child(4)").text(); // Get the <tr> of the Sum aggregate and the value of Unit Price
var sum_2 = $(this).prev().children(":nth-child(7)").text(); // Get the value of Extended Price
var val_1 = parseFloat(sum_1.replace(/[0-9-,]/g, '')); // I use comma as decimal, to use point as decimal use /[0-9-.]/g instead
var val_2 = parseFloat(sum_2.replace(/[0-9-,]/g, ''));
var diff = val_2 - val_1 ;
var curr = diff.toLocaleString('id-ID', {style:'currency',currency:'IDR'}) ; // sum_diff value, convert into local currency format
$(this).children(":nth-child(2)").text("Sum_Diff"); // Change text Average to Sum_Diff
$(this).children(":nth-child(4)").text(""); // Empty Average value at the 4th and 7th <td>
$(this).children(":nth-child(7)").text("");
$(this).children(":nth-child(3)").text(curr); // Put Sum_Diff value at the 3rd <td>

});

PS:

  • check your generated php report file, <tr> and <td> position may vary for different PHPMaker version/template.
  • put this code at Client Scripts -- Startup Script

Thanks to arbei for the pointer.


arbei
User
Posts: 9288

Post by arbei »

You can update the ViewValue with the CurrentValue of the field in Row_Rendered Server Event.

For Example:
$this-><Field>->ViewValue = $this-><Field>->CurrentValue;

CurrentValue is without any formatting, you can also format the CurrentValue by your own and assign to the ViewValue.


Post Reply