KoolReport's Forum

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

Dashboard: how to use Metrics #1916

Open ccsmick opened this topic on on Feb 22 - 19 comments

ccsmick commented on Feb 22

My database of data not standard format ("Y-m-d H:i:s") , base Format is baseFormat("d-M-y"),

If want to use Metrics of Trend, how to setting group(Date::create("")),

I had try

$this->group(Date::create("OGA02")),
$this->group(Date::create("OGA02")->baseFormat("d-M-y"))
$this->group(Date::create("OGA02")->baseFormat("d-M-y")->displayFormat("Y-m-d"))

None of the above three will work.

KoolReport commented on Feb 22

May I know how many rows do you have in your "oga_file" table?

ccsmick commented on Feb 22

oga_file:
NUM_ROWS 145867 BLOCKS 6298 AVG_ROW_LEN 308S AMPLE_SIZE 145867

KoolReport commented on Feb 22

Is it the field OGA20 is DateTime type in your database? or it is just a char (string) type?

From the name of table, I guess you were importing the csv file to database and all columns are in string type, weren't you?

ccsmick commented on Feb 22

OGA02 is Date type

KoolReport commented on Feb 22

I see, that's awesome. So I guess you can use SQL to convert the date to standard form. Please use rawSQL(), something like below:

AutoMaker::table("oga_file")->rawSQL("
    SELECT
        to_char(OGA20,'YYYY-MM-DD') as OGA20_CONVERSION,
    ....
")

so basically, you will have a field name OGA20_CONVERSION with standard format to use with Date in Dashboard.

ccsmick commented on Feb 22
class PaymentRecievedTrend extends Trend
{
 protected function dataSource()
   {
        return AutoMaker::table("OGA_FILE")
            ->rawSQL("SELECT to_char(OGA02,'YYYY-MM-DD') as OGA02T,OGA50");
    }

    protected function fields()
{

    return [
        $this->group(Date::create("OGA02T")),
        $this->sum(Currency::create("OGA50")->USD()->symbol())
    ];
}

    protected function ranges()
{
    return [
        "Today"=>$this::today(),
        "Yesterday"=>$this::yesterday(),
        "This Week"=>$this::thisWeek(),
        "Las Week"=>$this::lastWeek(),
        "Last 7 Days"=>$this::last7days(),
        "Last 30 Days"=>$this::last30days(),
        "This Month"=>$this::thisMonth(),
        "Last Month"=>$this::lastMonth(),
        "This Quarter"=>$this::thisQuarter(),
        "Last Quarter"=>$this::lastQuarter(),
        "This Year"=>$this::thisYear(),
        "Last Year"=>$this::lastYear(),
    ];
 }
}

Is there something wrong with me? Still can't work, In the Table is can work

class PaymentTable extends Table
{
    protected function dataSource()
    {

        return AutoMaker::table("OGA_FILE")
            ->select("OGA01","to_char(OGA02,'YYYY-MM-DD') as OGA02T","OGA50")
            ->where("ROWNUM","<",20)->run();
    }

    protected function fields()
    {
        return [
            Date::create("OGA02T")
                ->label("DATE")
        ->displayFormat("j M Y"), // This is format to display,
            Currency::create("OGA50")->USD()->symbol()->label("PRICE"),
        ];
    }
}

KoolReport commented on Feb 23

Hi, this part is not correct yet:

        return AutoMaker::table("OGA_FILE")
            ->rawSQL("SELECT to_char(OGA02,'YYYY-MM-DD') as OGA02T,OGA50");

it should be:

        return AutoMaker::rawSQL("
            SELECT 
                to_char(OGA02,'YYYY-MM-DD') as OGA02T,
                OGA50
            FROM OGA_FILE
        ");

When you are using rawSQL, you will input your own query.

ccsmick commented on Feb 23

I also had try

return AutoMaker::table("OGA_FILE")

        ->rawSQL("SELECT to_char(OGA02,'YYYY-MM-DD') as OGA02T,OGA50");

but still not work

KoolReport commented on Feb 23

Your above code is not correct yet. Please see my previous post

ccsmick commented on Feb 23

Sorry I posted wrong, this is my try but can't work.

  return AutoMaker::rawSQL("
        SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
        ");

ccsmick commented on Feb 23

This is array output

#name: "PaymentRecievedTrend"

#events: array:1 [▼
  "Init" => array:1 [▼
    0 => Closure() {#677 ▼
      class: "koolreport\dashboard\metrics\TimeBasedMetric"
      this: App\Controller\PaymentRecievedTrend {#678}
    }
  ]
]
#magicHandlers: array:1 [▼
  0 => "__magicTProps"
]
#runList: array:1 [▼
  0 => "init"
]
#enabled: true
#actions: null
#params: null
#fields: array:2 [▼
  0 => koolreport\dashboard\fields\DateTimeGroupField {#674 ▼
    #props: array:17 [▼
      "name" => "field6034cfada0b011"
      "colName" => null
      "label" => "No Name"
      "sort" => null
      "valueType" => null
      "resolveUsing" => null
      "formatUsing" => null
      "suffix" => null
      "prefix" => null
      "stringCase" => null
      "baseFormat" => "Y-m-d H:i:s"
      "displayFormat" => "m/d/Y H:i:s"
      "field" => koolreport\dashboard\fields\Date {#676 ▼
        #props: array:12 [▼
          "name" => "OGA02T"
          "colName" => "OGA02T"
          "label" => "O G A02 T"
          "sort" => null
          "valueType" => null
          "resolveUsing" => null
          "formatUsing" => null
          "suffix" => null
          "prefix" => null
          "stringCase" => null
          "baseFormat" => "Y-m-d"
          "displayFormat" => "m/d/Y"
        ]
        #row: []
        #magicHandlers: array:1 [▶]
        #widget: App\Controller\PaymentRecievedTrend {#678}
        #application: App\Controller\App {#640 ▶}
        #events: []
        #enabled: true
      }
      "displayHourFormat" => "H:00"
      "displayDayFormat" => "M jS, Y"
      "displayWeekFormat" => "W"
      "displayMonthFormat" => "M Y"
    ]
    #row: []
    #magicHandlers: array:1 [▼
      0 => "__magicTProps"
    ]
    #widget: App\Controller\PaymentRecievedTrend {#678}
    #application: App\Controller\App {#640 ▶}
    #events: []
    #enabled: true
  }
  1 => koolreport\dashboard\fields\MeasuredField {#706 ▼
    #props: array:17 [▼
      "name" => "OGA50"
      "colName" => "OGA50"
      "label" => "O G A50"
      "sort" => null
      "valueType" => "float"
      "resolveUsing" => null
      "formatUsing" => null
      "suffix" => ""
      "prefix" => "$"
      "stringCase" => null
      "decimals" => 2
      "thousandSeparator" => ","
      "decimalPoint" => "."
      "useRaw" => false
      "field" => koolreport\dashboard\fields\Currency {#701 ▶}
      "method" => "sum"
      "showLatestValue" => false
    ]
    #row: []
    #magicHandlers: array:1 [▶]
    #widget: App\Controller\PaymentRecievedTrend {#678}
    #application: App\Controller\App {#640 ▶}
    #events: []
    #enabled: true
  }
]

} #raw: """

            SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
            
"""

+type: "select" +tables: array:1 [▼

0 => "__RAW__"

] +columns: [] +conditions: [] +orders: [] +groups: [] +having: null +limit: null +offset: null +joins: [] +distinct: false +unions: [] +values: [] +lock: null #events: [] #name: null #runList: []

KoolReport commented on Feb 23

If you use the same query for table below the metric, does table work?

  return AutoMaker::rawSQL("
        SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
        ");
ccsmick commented on Feb 24

table can't work

table use code this can work

        return AutoMaker::table("oga_file")
            ->select(to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50")
            ->where("ROWNUM","<=",20)->run();
 protected function fields()
{
           Date::create("OGA02T")
                ->label("DATE"),
            Currency::create("OGA50")->USD()->symbol()->label("PRICE"),
    ];
}

but metric use some code or

 return AutoMaker::rawSQL("
        SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
        ");
   protected function fields()
{
    return [
        $this->group(Date::create("OGA02T")),
        $this->sum(Currency::create("OGA50")->USD()->symbol()),
    ];
}

still can't work

KoolReport commented on Feb 24

If you use this below SQL:

SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T, OGA50 FROM oga_file

and run inside pgAdmin. Does the query work?

ccsmick commented on Feb 24

I run inside Oracle SQL Developer, is can work

KoolReport commented on Feb 24

Let try to use this table's working code for metric:

protected function dataSource()
{
    return AutoMaker::table("oga_file")
            ->select("to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50")
            ->where("ROWNUM","<=",20);
}

protected function fields()
{
    return [
        $this->group(Date::create("OGA02T")),
        $this->sum(Currency::create("OGA50")->USD()->symbol()),
    ];
}

Let me know.

ccsmick commented on Feb 24

protected function dataSource() { // return AutoMaker::rawSQL(" // SELECT // to_char(OGA02,'YYYY-MM-DD') as OGA02T, // OGA50 // FROM OGA_FILE // "); // return AutoMaker::table("OGA_FILE")->where("ROWNUM","<",20)->run(); // return AutoMaker::table("OGA_FILE") // ->select("to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50") // ->where("ROWNUM","<=",20)->run(); // return AutoMaker::table("oga_file")->rawSQL(" // SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 // FROM oga_file // ");

    return AutoMaker::table("oga_file")
        ->select("to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50")
        ->where("ROWNUM","<=",20);

// AutoMakerM::rawSQL("SELECT paymentDate, amount FROM payments");

}

protected function fields()

{

return [

// // $this->group(Date::create("OGA02T")->label("DATE")->baseFormat("d-M-y")), // $this->sum(Currency::create("OGA50")->USD()->symbol()),

    $this->group(Date::create("OGA02T")),
    $this->sum(Currency::create("OGA50")->USD()->symbol()),
];

}

still can't work

ccsmick commented on Feb 25

I try to use LineChart, code

class LineChartDemo extends LineChart
{
    protected function onInit()
    {
        $this->title("AutoMaker's Revenue in 2020")
            ->colorScheme(ColorList::random())
            ->height("360px");
    }

    protected function dataSource()
    {
        return AutoMaker::rawSQL("
            SELECT 
                to_char(OGA02,'MON') as month,
                sum(OGA50) as total
            FROM
                OGA_FILE
            WHERE
                YEAR(OGA02)=2019
            GROUP BY month, to_char(OGA02,'MM')
            ");
    }

    protected function fields()
    {
        return [
            Text::create("month"),
            Currency::create("total")
                ->USD()->symbol()
                ->decimals(0),
        ];
    }
}

get error message

Message: Query Error >> [OCIStmtExecute: ORA-00933: SQL command not properly ended 
(/Users/mick/Downloads/php-7.4.14/ext/pdo_oci/oci_statement.c:157)] >> SELECT * FROM ( SELECT 
to_char(OGA02,'MON') as month, sum(OGA50) as total FROM OGA_FILE WHERE YEAR(OGA02)=2019 GROUP BY month, to_char(OGA02,'MM') ) as t

Line: 433

File: /Users/mick/Documents/www/symfony/symfony4.4-
report/vendor/koolreport/core/src/datasources/PdoDataSource.php
KoolReport commented on Feb 25

If you put this query into pgAdmin, does it work?

            SELECT 
                to_char(OGA02,'MON') as month,
                sum(OGA50) as total
            FROM
                OGA_FILE
            WHERE
                YEAR(OGA02)=2019
            GROUP BY month, to_char(OGA02,'MM')

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
help needed

None