KoolReport's Forum

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

Google-ColumnChart: Showing values on the chart #116

Open bysystem opened this topic on on Sep 22, 2017 - 10 comments

bysystem commented on Sep 22, 2017

Dear support team,

I am using Google-Columnchart as follows in my view:

		google\ColumnChart::create(array(
			"dataStore"=>$this->dataStore('AutoRepGoogleChartUmsatz'),
			"options"=>array(
				"title"=>"Umsatz / Monat",
				"isStacked"=>true,
				"orientation"=>"horizontal", //vertical
			),
			"width"=>"100%",
		));

In frontend the values (here the Total-value 19.089,00) on the chart are only shown if the user goes with mouse over a bar/line:

Is there any way to show the values automatically on the chart?

Kind regards

KoolReport commented on Sep 22, 2017

Please follow this example for Sakila Rental report:

<?php
ColumnChart::create(array(
    "dataStore"=>$this->dataStore('sale_by_month'),  
    "columns"=>array(
        "payment_date"=>array(
            "label"=>"Month",
            "type"=>"datetime",
            "format"=>"Y-n",
            "displayFormat"=>"F, Y",
        ),
        "amount"=>array(
            "label"=>"Amount",
            "type"=>"number",
            "prefix"=>"$",
            "annotation"=>function($row)
            {
                return "$".number_format($row["amount"]);
            },
        )
    ),
    "width"=>"100%",
    "options"=>array(
        "legend"=>array(
            "position"=>"bottom",
        )
    )
));
?>

By putting the "annotation" to the columns and set a function there, you can control the labels on the columns.

bysystem commented on Sep 22, 2017

Hmm, strange. As soon as I add the "columns"=>array(...) I get the following error message instead of the chart in frontend:

"Not enough columns given to draw the requested chart!"

Here is my view:

	<?php
		google\ColumnChart::create(array(
			"dataStore"=>$this->dataStore('AutoRepGoogleChartUmsatz'),

			"columns"=>array(
				"Umsatz"=>array(
					"label"=>"Umsatz",
					"type"=>"number",
					"prefix"=>"",
					"annotation"=>function($row)
					{
						return "$".number_format($row["Umsatz"]);
					},
				)
			),
			
			"options"=>array(
				"title"=>"Umsatz / Monat",
				"isStacked"=>true,
				"orientation"=>"horizontal", //vertical
			),
			"width"=>"100%",
		));
	?>	

And here my setup:

    $this->src('autorep')
	->query("
		SELECT
			WerbegruppeVerbund, Monat, Quartal, NikonKdName, NikonOrt, NikonProdGruppe, NikonArtBez, BestandHaendler_aktuell, BestandZentrale_aktuell, Umsatz, Verkaufsmenge
		FROM Reporting.autorep_v_noNulls
		WHERE 
			Sell = (:Sell) AND
			KdName IN (:KdName) AND
			$whereWerbegruppe AND
			$whereNikonKdName AND
			$whereNikonOrt AND
			$whereADBezirk AND
			$whereNikonProdGruppe AND
			$whereNikonArtBez AND
			$whereQuartal AND
			$whereMonat
			
		")->params(array(
		":Sell"=>$this->params["Sell"],
		":KdName"=>$this->params["KdName"],
		":Werbegruppe"=>$this->params["Werbegruppe"],
		":NikonKdName"=>$this->params["NikonKdName"],
		":NikonOrt"=>$this->params["NikonOrt"],
		":ADBezirk"=>$this->params["ADBezirk"],
		":NikonProdGruppe"=>$this->params["NikonProdGruppe"],
		":NikonArtBez"=>$this->params["NikonArtBez"],
		":Quartal"=>$this->params["Quartal"],
		":Monat"=>$this->params["Monat"]
		))
	->pipe(new ColumnMeta(array(
        "Umsatz"=>array(
			"align"=>"right",
            "type"=>"number",
            "prefix"=>"",
			"suffix"=>"",
			"decimals"=>2,
			"thousandSeparator"=>".",
			"decimalPoint"=>",",
        )
	)))			
    ->pipe(new Cube(array(
        "row"=>"Monat",
        "sum"=>"Umsatz",
    )))
	->pipe(new ColumnMeta(array(
		"{{all}}"=>array(
			"label"=>"Total",
		)
	)))		
    ->pipe(new Sort(array(
        "Monat"=>"asc"
    )))	

    ->pipe($this->dataStore('AutoRepGoogleChartUmsatz'));

Any idea what is wrong here?

KoolReport commented on Sep 22, 2017

It is because you enter only 1 column, it does not have x-axis value to draw chart, please add the Monat column there.

"columns"=>array(
    "Monat",
    "Umsatz"=>array(
        "label"=>"Umsatz",
        "type"=>"number",
        "prefix"=>"",
        "annotation"=>function($row)
        {
            return "$".number_format($row["Umsatz"]);
        },
    )
)
bysystem commented on Sep 22, 2017

Upps, you're absolutely right, I got it!

I added now the column "Monat". But as the column "Monat" a string (like "04.2017", "05.2017" etc.) I have now the error message:

"Data column(s) for axis #0 cannot be of type string!"

I added the property "type"=>"number", but no success:

			"columns"=>array(
				"Monat"=>array(
					"type"=>"number",
				),
				"Umsatz"=>array(
					"label"=>"Umsatz",
					"type"=>"number",
					"prefix"=>"",
					"annotation"=>function($row)
					{
						return "$".number_format($row["Umsatz"]);
					},
				)
			),

Any additinal hint for me?

Kind regards,

KoolReport commented on Sep 22, 2017

The columns for axis should be "string" actually. Do you have the report online. If not, please try to right click on the report page and choose "View page source", then save to file then sent to our support email.

bysystem commented on Sep 22, 2017

Thx a lot for your response.

Jus sent the page source code to your support email.

Kind regards,

KoolReport commented on Sep 22, 2017

I see the problem now, you do this:

"columns"=>array(
  "Monat",
  "{{all}}"=>array(
    "label"=>"Umsatz",
    "type"=>"number",
    "prefix"=>"",
    "annotation"=>function($row)
    {
      return "$".number_format($row["{{all}}"]);
    },
  )
),

It is because the column name is {{all}} not Umsatz. Please let me know result.

bysystem commented on Sep 22, 2017

Great! The values are shown now!

1 last issue related to the number format:

The format of the values are unfortunately in form of 6,822.4 instead of 6.822,40 allthough the properties for decimalPoint and thousandSeparator are set as follows (I tried also with number_format($row...) but the result was the same:

			"columns"=>array(
				"Monat",
				"{{all}}"=>array(
					"type"=>"number",
					"prefix"=>"",
					"suffix"=>"",
					"decimals"=>2,
					"thousandSeparator"=>".",
					"decimalPoint"=>",",
					"annotation"=>function($row)
					{
						//return "$".number_format($row["{{all}}"]);
						return $row["{{all}}"];
					},
				)
			),

Is there any way to set the number_format as it's shown in case of mouse over (here: 6.822,40):

bysystem commented on Sep 22, 2017

OK, I got it. The following does what I need:

return number_format($row["{{all}}"],2,',','.');

Kind regards,

KoolReport commented on Sep 23, 2017

Great!

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