KoolReport's Forum
Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
How to add hyperlink in visual query builder report #3039
I think you can use DataTables or Table widget's column formatValue property to build the hyperlink like this:
DataTables::create(array(
...
"columns" => [
...
"sum(Order Cost)" => [
"formatValue" => function($value, $row, $cKey) {
return "<a href='#'>$value</a>"; // change this hyperlink to what you want
}
]
]
));
Here's the property docs for DataTables and Table widgets:
https://www.koolreport.com/docs/datagrid/datatables/#format-column-value
https://www.koolreport.com/docs/koolphp/table/#table-settings-format-column-value
MyReport.php
<?php //Step 1: Load KoolReport require_once "../../../load.koolreport.php";
//Step 2: Creating Report class class MyReport extends \koolreport\KoolReport {
use \koolreport\visualquery\Bindable;
public function defineSchemas()
{
return [
"salesSchema" => array(
"tables" => [
"customers"=>array(
"{meta}" => [
"alias" => "Table Customers"
],
"customerNumber"=>array(
"alias"=>"Customer Number",
),
"customerName"=>array(
"alias"=>"Customer Name",
),
),
"orders"=>array(
"{meta}" => [
"alias" => "Table Orders"
],
"orderNumber"=>array(
"alias"=>"Order Number"
),
"orderDay" => array(
"alias" => "Order Day",
"expression" => "date(orderDate)",
"type" => "date",
),
"orderDate"=>array(
"alias"=>"Order Date",
"type" => "datetime"
),
"orderMonth" => [
"expression" => "month(orderDate)",
]
// "customerNumber"=>array(
// "alias"=>"Customer Number"
// )
),
"orderdetails"=>array(
"{meta}" => [
"alias" => "Order Details"
],
// "orderNumber"=>array(
// "alias"=>"Order Number"
// ),
"quantityOrdered"=>array(
"alias"=>"Quantity",
"type"=>"number",
),
"priceEach"=>array(
"alias"=>"Price Each",
"type"=>"number",
"decimal"=>2,
"prefix"=>"$",
),
// "productCode"=>array(
// "alias"=>"Product Code"
// ),
"cost" => [
// "expression" => "orderdetails.quantityOrdered * orderdetails.priceEach",
"expression" => "quantityOrdered * priceEach",
"alias"=>"Cost",
"type"=>"number",
"decimal"=>2,
"prefix"=>"$",
]
),
"products"=>array(
"{meta}" => [
"alias" => "Table Products"
],
"productCode"=>array(
"alias"=>"Product Code"),
"productName"=>array(
"alias"=>"Product Name"),
)
],
"relations" => [
["orders.customerNumber", "leftjoin", "customers.customerNumber"],
["orders.orderNumber", "join", "orderdetails.orderNumber"],
["orderdetails.productCode", "leftjoin", "products.productCode"],
]
),
"separator" => ".",
];
}
public function settings()
{
//Get default connection from config.php
$config = include "../../../config.php";
return array(
"dataSources"=>array(
"automaker"=>$config["automaker"]
)
);
}
protected function setup()
{
// echo "post="; \koolreport\core\Utility::prettyPrint($_POST);
$params = \koolreport\core\Utility::get($this->queryParams, 'visualquery1');
$qb = $this->paramsToQueryBuilder($params);
$this->queryStr = $params ? $qb->toMySQL() : "select * from customers where 1=0";
$this->paramQuery = $params ? $qb->toMySQL(['useSQLParams' => "name"]) : "select * from customers where 1=0";
$this->sqlParams = $qb->getSQLParams();
$this
->src('automaker')
->query($this->paramQuery)
->params($this->sqlParams)
->pipe(new \koolreport\processes\ColumnMeta([
"Order Number" => [
"type" => "string"
],
"orderMonth" => [
"type" => "string"
],
]))
->pipe($this->dataStore('vqDS'));
}
}
and MyReport.view.php
<?php
use \koolreport\visualQuery\VisualQuery;
use \koolreport\datagrid\DataTables;
?> <form method="post">
<div class="report-content">
<div class="text-center">
<h1>VisualQuery</h1>
<p class="lead">
The basic working of VisualQuery
</p>
</div>
<?php
\koolreport\visualquery\VisualQuery::create(array(
"name" => "visualquery1",
"themeBase" => "bs4",
"schema" => "salesSchema",
"defaultValue" => [
"selectDistinct" => false,
"selectTables" => [
"orders",
"orderdetails",
"products",
],
"selectFields" => [
"products.productName",
],
"filters" => [
"(",
[
"field" => "orders.orderDay",
"operator" => ">",
"value1" => "2001-01-01",
"value2" => "",
"logic" => "and",
"toggle" => true,
],
[
"field" => "products.productCode",
"operator" => "nbtw",
"value1" => "2",
"value2" => "998",
"logic" => "or",
"toggle" => true,
],
["products.productName", "<>", "a", "", "or", "toggle" => false],
["products.productName", "nin", "a,b,c", "", "or"],
["products.productName", "ctn", "a", "", "or"],
")",
],
"groups" => [
[
"field" => "orderdetails.cost",
"aggregate" => "sum",
"toggle" => true
]
],
"havings" => [
"(",
[
"field" => "sum(orderdetails.cost)",
"operator" => ">",
"value1" => "10000",
"value2" => "",
"logic" => "and",
"toggle" => true,
],
["products.productName", "<>", "a", "", "or", "toggle" => false],
")",
],
"sorts" => [
[
"field" => "sum(orderdetails.cost)",
"direction" => "desc",
"toggle" => true
],
["products.productName", "desc", "toggle" => false]
],
"limit" => [
"offset" => 5,
"limit" => 10,
"toggle" => false,
]
],
"activeTab" => "filters",
));
?>
<button type='submit' class='btn btn-light' >Submit</button>
<style>
pre {
overflow-x: auto;
white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;
}
</style>
<div style="margin: 30px; width:800px">
<b>Select query:</b>
<pre style="width:800px"><?php echo $this->queryStr; ?></pre>
<b>Query with parameters:</b>
<pre style="width:800px"><?php echo $this->paramQuery; ?></pre>
<b>Parameters:</b>
<?php \koolreport\core\Utility::prettyPrint($this->sqlParams); ?>
</div>
<?php
// print_r($this->dataStore('vqDS')->meta());
DataTables::create(array(
"name" => "charttable1",
"dataSource" => $this->dataStore('vqDS'),
// "columns" => ["Quantity", "Product Name"],
"options" => [
"paging" => true
]
));
?>
</div>
</form>
In your report view file, in DataTables' create method, define "columns" property like this:
DataTables::create(array(
...
"columns" => [
// other columns here,
"sum(Order Cost)" => [
"formatValue" => function($value, $row, $cKey) {
return "<a href='#'>$value</a>"; // change this hyperlink to what you want
}
]
]
));
Build Your Excellent Data Report
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo