Dashboard and Filter

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

Dashboard and Filter

Postby haemi » Sat Oct 14, 2017 1:37 am

Hello

I read a lot about dashboard, charts and filtering in the forum.

Is there a way to integrat filters directly in a dashboard or to generate some sql queries before the dashboard comes up?

Background:
I have three different charts in a dashboard report. All have a year filter in it. So - I should be able to choose a year from a generated dropdown list. The year - list is the same for all charts.

Thank you for any idea.

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

Re: Dashboard and Filter

Postby kirondedshem » Sat Oct 14, 2017 3:27 am

This is what I am using for now, although I would prefer a better way
am currently having like 6 graphs on dashboard where all filter by region, here is how I do it.

1. I select custom alignment of graphs on dashboard so I can align them in custom divs.
2. I draw the filter on top of the dashboard, in my case a select for regions. I always save my current filter value in session so I can tell what is active
<select id="dashboarddsb_selected_region" class="form-control">
<option value='All'>All Regions</option>
<?php



$dbhelper =& ReportDbHelper();
$region_reslut = $dbhelper->LoadRecordset("select id,name from region");
foreach( $region_reslut as $region_row)
{
if(isset($_SESSION["dashboarddsb_selected_region"]) && $_SESSION["dashboarddsb_selected_region"] == $region_row["name"])
{
echo "<option value='".$region_row["name"]."' selected>".$region_row["name"]."</option>";
}
else
{
echo "<option value='".$region_row["name"]."'>".$region_row["name"]."</option>";
}

}
?>

</select>

3. attach event handler to filter controll to force page to reload everytime someone changes the filter on dasbporad page, as wel as passing the selected filter value in url by using jquery, like this.

<script>

$("#dashboarddsb_selected_region").on("change", function () {
var value = this.value;
var url = 'dashboarddsb.php?dashboarddsb_selected_region=__value__';
window.location.href = url.replace('__value__', value);

});

</script>

4. capture and save the filter value everytime the filter is chaged by capturing it from url and saving in session to use later.put this code in page_load of dashboard page.
if(isset($_GET["dashboarddsb_selected_region"]))
{
$_SESSION["dashboarddsb_selected_region"] = $_GET["dashboarddsb_selected_region"];
//var_dump("saving it");
}

5. apply filter save din session to any reports sued to generate the graphs on dashboard, ofcourse add a check for if there is nothing saved in session yet, you can do this in Page_Selecting of report page
function Page_Selecting(&$filter) {
// Enter your code here
//formulate you filter here
if(isset($_SESSION["dashboarddsb_selected_region"))
{
$filter = " year = '".$_SESSION["dashboarddsb_selected_region"."'";
}

}
kirondedshem
 
Posts: 310
Joined: Sat Jan 28, 2017 7:13 pm

Re: Dashboard and Filter

Postby haemi » Mon Oct 16, 2017 3:46 pm

Hello kirondedshem

Where do you have placed point 3 (jquery script)?

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

Re: Dashboard and Filter

Postby kirondedshem » Mon Oct 16, 2017 4:00 pm

Thw jquery scropt is to attach an event handler to your select control or whatever control you are using.
so it goes in dashborad file, ie go to you dashboard file-> custom template->custom file.
This is where you normally define how charts are going to be ordered
1. put code to draw the select controll in dashbord file
2. Paste the juqery code right below it
Notice line $("#dashboarddsb_selected_region").on("change", function ()
where "dashboarddsb_selected_region" is the id of the select controll you have drawn above.
3. ALso ensure line (var url = 'dashboarddsb.php?dashboarddsb_selected_region=__value__';)
contains the actual url of your dashboard page and the get parameter name is what you are checking for after.

4. IF above is done well, when you change selection in the control above the page should reload and show current parameter value equaling to current selection.
OTHERWISE open console log and check for errors.

5. you can then put code to define how charts are ordered on dashboard page after drawing the select controll.
kirondedshem
 
Posts: 310
Joined: Sat Jan 28, 2017 7:13 pm

Re: Dashboard and Filter

Postby haemi » Mon Oct 16, 2017 4:34 pm

Hello kirondedshem

Thank you.

Does it mean you make a customs file inside php report maker?

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

Re: Dashboard and Filter

Postby kirondedshem » Mon Oct 16, 2017 4:39 pm

according to your inital qustion
>I read a lot about dashboard, charts and filtering in the forum.
>Is there a way to integrat filters directly in a dashboard or to generate some sql queries before the dashboard comes up?

From what you said before
I assumed that you already have report pages or nonrmal table pages that have charts on them. And that you have been able to create adsahboard pages and linked some of those charts to the dashboard page. Hence the question on how to apply the filters to the charts while on dashboard page the same way you would when viewing cahrt from its parent report.
If you havent then read up on dashboard page, Otherwise the same approach of custom filtering can work even on normal reports haveing charts

>Does it mean you make a customs file inside php report maker?
Yes. you can cretae dashboard pages as explained above as well as Custom files to do you won php stuff
kirondedshem
 
Posts: 310
Joined: Sat Jan 28, 2017 7:13 pm

Re: Dashboard and Filter

Postby haemi » Mon Oct 16, 2017 5:01 pm

Hello kirondedshem

Thank you again.

I don't know what I'm doing wrong.

Steps:
- First I made 3 crosstab reports with one chart
- In page selection of the above reports on page_selection I added the following lines inside the function:
if (isset($_SESSION["vYear"]))
{
$filter = "YEAR = '".$_SESSION["vYear"]."'";
}
- Third I made a dashboard report with the 3 crosstab reports.
- In page_load of the dashboard report added the following lines inside the function:
if(isset($_GET["vYear"]))
{
$_SESSION["vYear"] = $_GET["vYear"];
}
- In Custome Templates on the dashboard report under CustomTemplate I added the following lines:

<select id="vYear" class="form-control" style="text-align:center">
<script>
$("#vYear").on("change", function () {
var value = this.value;
var url = 'Dashboard_Testdsb.php?vYear=__value__';
window.location.href = url.replace('__value__', value);
});
</script>

<?php
$dbhelper = &ReportDbHelper("KJPD");
$FilterJahr = $dbhelper->LoadRecordset("SELECT DISTINCT YEAR([BL_DATUM]) as vYear from VMWORK ORDER BY vYear");
foreach( $vYear as $vYear_row)
{
if(isset($_SESSION["vYear"]) && $_SESSION["FilterJahr"] == $vYear_row["vYear"])
{
echo "<option value='".$vYear_row["vYear"]."' selected>".$vYear_row["vYear"]."</option>";
}
else
{
echo "<option value='".$vYear_row["vYear"]."'>".$vYear_row["vYear"]."</option>";
}
}
?>
</select>
</div>

<table>
<tr><td>{{{chart1}}}</td><td>{{{chart2}}}</td><td>{{{chart3}}}</td></tr>
</table>
haemi
 
Posts: 29
Joined: Sat Apr 22, 2017 2:38 am

Re: Dashboard and Filter

Postby kirondedshem » Mon Oct 16, 2017 5:14 pm

first things first.
1. does the select controll render with the correct year filled in on the Dashboard_Testdsb.php.
2. does the page reload with the selected yaer in the get parameter of url everytime you change a year.

Do these two work
kirondedshem
 
Posts: 310
Joined: Sat Jan 28, 2017 7:13 pm

Re: Dashboard and Filter

Postby haemi » Mon Oct 16, 2017 5:26 pm

Hello

1. Yes. On the page is displayed the correct year. But the calculation isn't done.
(Chart SQL): SELECT [Description], '', SUM([Amount]) FROM [dbo].[VMWORK] GROUP BY [Description]

If I run the crosstab report itself with the chart in it:
(Chart SQL): SELECT [Description], '', SUM([Amount]) FROM [dbo].[VMWORK] WHERE (YEAR([BL_DATUM]) = 2016) GROUP BY [Description]

2. Yes. The url is shown with the correct parameter.

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

Re: Dashboard and Filter

Postby kirondedshem » Mon Oct 16, 2017 7:37 pm

so what yo saying is the filtering is done correctly while in the crosstab report using the value in the session
but the filtering is not done when on the dashboard page.
Can you confirm that
1.The list on the report itself filter properly itself depending on the session value stored.
2. Do each of the charts on the report itself filter properly depending on the session value stored.
3. charts dont filter at all when on dashboard page.

If IF 1 is ok BUT CHARTS DONT FILTER AT ALL both on report and on dashboard page.
then we need to assign the filter at some other point where even the chart rendered by dashboard page can listen to it.
We can try to play with the chart rendering to apply the filter in chartsql for each cahrt and this should work for the report page as well

// Chart Rendering event
function Chart_Rendering() {

// Example:
// var_dump($this); // Chart
// if ($this->ID == "<Report>_<Chart>") {
// $this->SaveParm("formatNumber", "1"); // Format number
// $this->SaveParm("numberSuffix", "%"); // % as suffix
// }

//check for particular charts
if ($this->ID == "vw_90_30_7_temp_list_Cash_In_Transactions_Per_Day")
{
var_dump($this->ChartSql);
//add filter manually
$custom_filter = "YEAR = '".$_SESSION["vYear"]."'";
$this->ChartSql .= " ".$custom_filter;
var_dump($this->ChartSql);
}


}



Just create different ifs for all charts
kirondedshem
 
Posts: 310
Joined: Sat Jan 28, 2017 7:13 pm

Re: Dashboard and Filter

Postby haemi » Mon Oct 16, 2017 9:11 pm

Hello

I think I have one issue with the $_SESSION. The vYear var isn't stored in the session. I checked php.ini. The path for the session is set.

I couldn't find any POST for this var to the session.

Is something missing or what do I wrong?

On the page_load of my dashport report - I made the following changes:

if(isset($_GET["vYear"]))
{
$_SESSION["vYear"] = $_GET["vYear"];
// var_dump("saving it");
}


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

Re: Dashboard and Filter

Postby kirondedshem » Mon Oct 16, 2017 9:43 pm

you can also try to save a different session variable using some other page just to test things out.
Or maybe also var_dump both the get value and the session value right after saving.
If you say your session is not saving coz of something in php, then first try to google some possible solutions on that
kirondedshem
 
Posts: 310
Joined: Sat Jan 28, 2017 7:13 pm

Re: Dashboard and Filter

Postby haemi » Tue Oct 17, 2017 2:45 pm

Hello kirondedshem

The issue with the $_SESSION["vYear"] is solved. All reports run dependend on this session value.

Now I trying to implement some functionalities in chart_rendering event (server events). As I understand the rendering is running after SQL select but before the XML is built.

From the help file for "chart_rendering":
This event is fired before the chart XML is built. You can still change the chart properties using this event.

It looks like that only chart properties can be changed but not the data itself.

Do you have any ohter ideas?

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

Re: Dashboard and Filter

Postby haemi » Tue Oct 17, 2017 10:23 pm

Hello

The only way I found is to change directly in the <dashboard>dsb file. In it $Page->Filter is set to ''. In this case I changed to my needed filter including the session var. Works.

Like this:
$Page->Filter = "YEAR =" . $_SESSION["vYear"];

It's not really nice. I had to do it for every chart inside the dashboard file. After generating I have to do it again....

I know I could do it with custom files but I think this must / should be possible with phpr.

No other possibilities?

Thank you in advance for any hint.
haemi
 
Posts: 29
Joined: Sat Apr 22, 2017 2:38 am


Return to General Discussion (PHP Report Maker)