KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Noob question #2209

Open David Richmond opened this topic on on Jul 15 - 14 comments

David Richmond commented on Jul 15

I just bought reports pro, and I'm just not understanding the tutorials at all.

The actual report I'm TRYING to make is a simple line graph using PDO datasource with the simple query:

$query = "SELECT Month,ExpenseType,StationName,Cost FROM qryIntranet_WebApplication_Reports_StationExpenses";

I can get that no problem with SQL but making the report is really stumping me. I just want a line graph that shows the station names on the line with cost on the x axis and month on the Y axis. How can I modify the morris example to use a datasource from mssql via pdo?

Can anyone help?

Sebastian Morales commented on Jul 16

David, would you mind posting your report setup and view php code as well as screenshots of your problem for us to check them for you? Tks,

David Richmond commented on Jul 16

I have nothing, I'm working off the morris line chart example. It works from a static .csv I have a pdo mssql query that produces the same amount of data points but i'm unclear how to take the datasource from the database, the first report salesbycustomer looks nothing like it, I can't even move the report to a different location on the sever and it work, I'm truly lost and I know php and SQL, but I'm having an awful time just making a real working example

KoolReport commented on Jul 17

No worry, I will tell the dev.team to make a small code template for you. I have not fully understood your below explanation of chart

 I just want a line graph that shows the station names on the line with cost on the x axis and month on the Y axis

So it will be:

  1. A line chart
  2. X-axis is cost -> I think it should be Y (vertical), right
  3. Month on Y-axis -> you mean the X, right.

So I guess you want a line chart for a station, the title of chart would be "Cost by month for My Station", the horizontal axis show the "month" like Jan, Feb. The vertical will be the cost. You will see the cost trend from Jan to Dec. Am I correct?

David Richmond commented on Jul 17

Yes using that SQL query above, I'm trying to put cost on the Y, Month on the X and Station names for the trend line.

Thanks!

KoolReport commented 7 days ago

You will have 3 files: index.php, MyReport.php and MyReport.view.php

index.php

<?php

require_once "path/to/koolreport/core/autoload.php";
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();

MyReport.php

<?php

class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "myDB"=>array(
                    "connectionString"=>"sqlsrv:server=localhost ; Database=myDB",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    }   

    protected function setup()
    {
        $this->src("myDB")->query("
            Your query go here
        ")
        ->pipe($this->dataStore("result"));
    }
}

MyReport.view.php

<?php use \koolreport\widgets\google\LineChart; ?>
<html>
    <head>
        <title>My Report</title>
    </head>
    <body>
        <?php
        LineChart::create([
            "dataSource"=>$this->dataStore("result")
        ]);
        ?>
    </body>
</html>

Since you have know SQL and your db structure so please query SQL to get following result format:

MonthStation XStation Y
Jan2233
Feb4453
.........
Dec5422

After changing your connection in settings() and provide SQL into query() function, you can run the "index.php" via browser, it will give you result that you want.

David Richmond commented 7 days ago

Thanks I'm very close, I got a complaint about driver not found so i modified it to use ODBC via PDO with

$mssqldriver = '{ODBC Driver 13 for SQL Server}';

class MyReport extends \koolreport\KoolReport {

function settings()
{
    return array(
        "dataSources"=>array(
            "myDB"=>array(
                "connectionString"=>"odbc:Driver=$mssqldriver;server=10.10.10.10; Database=mydbase",
                "username"=>"username",
                "password"=>"password",
                "charset"=>"utf8"
            ),
        )
    ); 
}   

that returned a good error message on the index.php

Data column(s) for axis #0 cannot be of type stringĂ—

So Definately closer I'm assuming data is bad format

an example row from my view is 4 columns:

Mar Gas Bill West End 357

Mar Power Bill Garage 839

Apr Power Bill Garage 723

so my end goal is to have the Months on the X axis and the costs plotted on the graph at Y with a filter available by station name (west end) or (garage) or by bill type (gas bill, power bill) but if no filter display all data

So it seems my view is not setup in the forrmat that koolreports wants but im not sure how to fix it

KoolReport commented 6 days ago

Yes you are very close, now your data is

MonthStationAmount
MarGas Bill West End357
MarPower Bill Garage839
AprPower Bill Garage723

which is not yet correct yet. The format that you need is:

MonthGas Bill West EndPower Bill Garage
Mar357839
Apr0723

To turn your data from the first to second table, you can use our Cube. You can find examples of Cube here

Let me know if you need further assistance.

David Richmond commented 5 days ago

Ok I get it, I need a crosstab, so I generated that crosstab like this:

SELECT Month, [Water Bill] as Water, [Power Bill] as Power, [Gas Bill] as Gas FROM ( SELECT Month, ExpenseType, Cost FROM dbo.qryIntranet_WebApplication_Reports_StationExpenses ) exp

PIVOT ( Sum(Cost) For ExpenseType in ([Water Bill] , [Power Bill], [Gas Bill] )
) as pvt

The results are:

Month Water Power Gas

Apr NULL 5375 2023

Jun NULL 3294 NULL

Mar 260 8554 2727

May 356 4018 342

I still get the error "Data column(s) for axis #0 cannot be of type stringĂ—"

I thought this might be problems with null values so i selected those out in the subquery but sitll got that issue.

What am I missing? With that asked I'm also trying to add a dropdown filter option for month and stationame so I added station name to the cross tab and returned:

Month StationName Water Power Gas

Apr Garage NULL 723 769

Jun Garage NULL 613 NULL

Apr Black River NULL 289 NULL

Mar Black River NULL 345 NULL

May Black River NULL 282 NULL

so first and foremost after crosstab i still have the same error, and secondly i'd like to add station name to the chart and html dropdowns to filter the graph by those values assuming i get it working

Thanks for all the support!

KoolReport commented 5 days ago

To make the chart work, your first column is "string type" represent category. The second, third etc. columns must be "number" type. So as I see in your data, the second column is "string" type. That's why it has issue.

David Richmond commented 5 days ago

the 2nd column is numeric

Here is the datastore query

"SELECT Month, Water, Power, Gas FROM qryIntranet_WebApplication_Reports_StationExpenses_XTAB"

Month Water Power Gas

Apr NULL 5375 2023

Jun NULL 3294 NULL

Mar 260 8554 2727

May 356 4018 342

even when the data is excluding nulls or I force a 0 for null values like Isnull(Water,0)

Mar 260 8554 2727

May 356 4018 342

i get the same error

Thanks,

David

David Richmond commented 4 days ago

Any thoughts on why the data set of

Mar 260 8554 2727

May 356 4018 342

i get the same error?

David Richmond commented 4 days ago

SELECT Month, isnull(Water,0) as WaterBill, isnull(Power,0) as PowerBill, isnull(Gas,0) as GasBill FROM qryIntranet_WebApplication_Reports_StationExpenses_XTAB

Returns the photo below, there is something else wrong here I think

KoolReport commented 4 days ago

Please use that above query, I see the data is correct. And then setup the LineChart like below:

<?php
LineChart::create([
    "dataSource"=>$this->dataStore("result"),
    "columns"=>[
        "Month"=>["type"=>"string"],
        "WaterBill"=>["type"=>"number"],
        "PowerBill"=>["type"=>"number"],
        "GasBill"=>["type"=>"number"],
    ]
]);
?>
David Richmond commented 4 days ago

Thank you that was what i needed!

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
solved

None