Crosstab additional columns

This public forum is for user-to-user discussions of PHP Report Maker. Note that this is not support forum.

Crosstab additional columns

Postby gipper » Wed Dec 18, 2013 4:47 am

I have a few CROSSTAB reports working just fine, however, I have a second table that I'd like to JOIN to the CROSSTAB.

My CROSSTAB has a colum "Serial_Number". I'd like to do a JOIN to another table that has some data that I want to show in the same report.

Does anyone have an idea on how I can do this? I 've done the JOIN first, and created a VIEW, however since CROSSTAB only brings two columns forward to the report it basically ignores all other columns and won't allow me to make them visible.
gipper
 
Posts: 1
Joined: Wed Dec 18, 2013 4:15 am

Re: CROSSTAB additional columns

Postby Webmaster » Wed Dec 18, 2013 9:48 am

If you just want to show additional information regarding the field "Serial_Number" (assuming a grouping field), you can use the "Row_Rendered" server event to render the additional information. For example:

$info = ewr_ExecuteScalar("SELECT <Field> FROM <Table> WHERE <Key> = '" . $this-><Field>->GroupValue() . "'");
$this-><Field>->GroupViewValue .= " (" . $info . ")";

Alternatively, you can create a view that contains the required data first. Read "Preparing Your Data" in the help file for more details.
Webmaster
 
Posts: 10731
Joined: Fri Aug 06, 2004 5:59 pm

Crosstab additional columns

Postby haemi » Sat Jun 24, 2017 3:53 am

Hello,

I just want to show additional information regarding the field "Employee_Id" from the table Employee in a crosstab report. I know I could do it with a view but I need more then 6 columns in the row heading.

I found this post and adapted to my needs.

$info = ewr_ExecuteScalar("SELECT Surname FROM Employee WHERE Employee_Id = '" . $this->Employee_Id->GroupValue() . "'");
$this->Surname->GroupViewValue .= " (" . $info . ")";

Employee_Id is a grouping field.

I have done it in the Row_Rendered section. Echo shows me the correct values - but no new column.

Any hints for this?

Thank you.
haemi
 
Posts: 29
Joined: Sat Apr 22, 2017 2:38 am

Re: Crosstab additional columns

Postby Webmaster » Sat Jun 24, 2017 7:59 am

Webmaster wrote:
> If you just want to show additional information regarding the field "Serial_Number" (assuming a grouping field), you can use the "Row_Rendered" server event to render the additional information.

The original post suggested user to append some additional info in existing field, the example code was/is not to add a new column.

If you prefer to add a new column, Webmaster wrote:
> Alternatively, you can create a view that contains the required data first. Read "Preparing Your Data" in the help file for more details.
Webmaster
 
Posts: 10731
Joined: Fri Aug 06, 2004 5:59 pm

Re: Crosstab additional columns

Postby haemi » Mon Oct 23, 2017 2:36 pm

Hello,

thank you for the answer.

I'm know - i could do it with views. The problem is that only 6 row headings columns are possible in a crosstab report.

I changed the code. Now the surname is displayed in the view - but in the same column as the employee_id.

Code:
$info = ewr_ExecuteScalar("SELECT Surname FROM Employee WHERE Employee_Id = '" . $this->Employee_Id->GroupValue() . "'");
$this->Employee_Id>->GroupViewValue .= " (" . $info . ")";

But i need one additional column for some calculation on the same row - not a second row and so on.

As I wrote:

$(".ewTable tbody").find("tr").append("<td class='ewTableRow'>Sales</td>");

I can append a column to the crosstab report. But how can I fill in some data?

Thank you.
haemi
 
Posts: 29
Joined: Sat Apr 22, 2017 2:38 am

Re: Crosstab additional columns

Postby arbei » Tue Oct 24, 2017 9:25 am

You can use server-side script combine with the client-side script to retrieve data from the database and pass to client side.

For example (In Startup Script):
<?php
$result = ew_ExecuteScalar("<SQL>");
?>
$(".ewTable tbody").find("tr").append("<td class='ewTableRow'><?php echo $result; ?></td>");
arbei
 
Posts: 1490
Joined: Wed Sep 09, 2015 10:13 am

Re: Crosstab additional columns

Postby haemi » Wed Oct 25, 2017 11:48 pm

Thank you. This is very helpful. The column and the result is shown now.

But how can I do that for each row? I tried to it from cell_rendered event.

Thanks.
haemi
 
Posts: 29
Joined: Sat Apr 22, 2017 2:38 am

Re: Crosstab additional columns

Postby Webmaster » Fri Oct 27, 2017 8:04 am

haemi wrote:
> I changed the code. Now the surname is displayed in the view - but in the same column as the employee_id.
>
> Code:
> $info = ewr_ExecuteScalar("SELECT Surname FROM Employee WHERE Employee_Id = '" . $this->Employee_Id->GroupValue() . "'");
> $this->Employee_Id>->GroupViewValue .= " (" . $info . ")";
>
> $(".ewTable tbody").find("tr").append("<td> class='ewTableRow'>Sales</td>");
>
> I can append a column to the crosstab report. But how can I fill in some data?

Continue to use jQuery to move the info in parentheses to the new cell you added. Read:
http://api.jquery.com/text/ (get and set text for an element), or
http://api.jquery.com/html/ (get and set HTML for an element),
For example, you can use regular expression to extract info from the text of the Employee_Id, remove it from the original cell and set it to the new cell.

Try and post your code.
Webmaster
 
Posts: 10731
Joined: Fri Aug 06, 2004 5:59 pm

Re: Crosstab additional columns

Postby haemi » Sat Oct 28, 2017 5:38 pm

Hello

Thank you to all for the help and the hints.

Pre
It's a known fact that in a crosstab report you can have differend rows with sum fields on one group. This works perfect.
In some circumstance all sum fields are needed on one row.

The following solution is working for me. There a sure better solutions. I'm still learning phpr with php and jQuery.

How I have done it?

Row_Rendered event
In row_rendered event I made the calculation for example a percentag value ($perc).

I declared one global var to store it in one array:
global $percArr = array();

This array is filled in with some values:
$percArr[$this->empID->GroupViewValue] = array('perc' => $perc);

Now I have a key and a value in the array and can use this global array var in startup script to encode with JSON.

Startup Script
First I integrated a new column for header and body:

$(".ewTable thead").find("tr").append("<td class='ewTableRow'>Percentage</td>"); //Header title
$(".ewTable tbody").find("tr").append("<td class='ewTableRowPerc'>0</td>"); // New class

Now I need some global vars:
var empID; // must be global!
var perc;

// Start
$(document).ready(function () {
var jphpresult = <?php echo json_encode($percArr); ?>; // Encode

// Iteration
$('.ewTable > tbody > tr').each(function() {
// Key: value from first group field = Primary Key!
$(this).find(".ewRptGrpField1").each(function(){
empID = parseInt($(this).text()); // must be integer!
});

// Value from array - based on key value
$(this).find(".ewTableRowPerc").each(function() {
$.map(jphpresult, function(elem, key) {
if (key == empID)
{
perc = Math.round(elem.perc);
}
});
$(this).text(perc);
});
});
});

Kind regards,
Haemi
haemi
 
Posts: 29
Joined: Sat Apr 22, 2017 2:38 am


Return to General Discussion (PHP Report Maker)