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?

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

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!

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

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!

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

Any thoughts on why the data set of

Mar 260 8554 2727

May 356 4018 342

i get the same error?

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

Thank you that was what i needed!