KoolReport's Forum

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

Sequence of alphanumeric order of yAxis values #725

Closed Markus Mohr opened this topic on on Mar 4, 2019 - 15 comments

Markus Mohr commented on Mar 4, 2019

KoolReport 3.25.4: How can I alphanumerically order my hAxis values?

Here is my code (the MySQL format of "BauchumfangMaenner" is MEDIUMINT):

            <div class="col-md-12">
                <?php
                Table::create(array(
                    "title"=>"Bauchumfang bei Erstuntersuchung",
                    "dataSource"=>(
                        $this->src("patient")->query("
                            SELECT IFNULL(floor(ini.BauchumfangMaenner/15)*15, 'Noch nicht dokumentiert') as agerange, count(*) as Summe
                			FROM dat_patient p
                            LEFT JOIN dat_initialdaten ini ON ini.patID = p.ID
                            WHERE 1 = 1
                			-- AND (ini.BauchumfangMaenner IS NOT NULL AND ini.BauchumfangMaenner != -1)
							AND NOT EXISTS (SELECT 1
							                  FROM dat_initialdaten ini2
							                  WHERE ini2.patID = p.ID AND ini2.revision > ini.revision
							                 )
    						AND p.Testzwecke = 0
                            GROUP BY agerange
                			ORDER BY Summe ASC
                        ")
                        ->pipe(new CalculatedColumn(array(
                            "agerange"=>array(
                                "exp"=>function($row)
                                {
                                    return $row["agerange"]."-".($row["agerange"]+15);
                                },
                                "type"=>"string"
                            ))))
    				),
				    "options"=>array(
				        "chartArea"=>array(
				           right=> 130,   // set this to adjust the legend with, shift the chartarea to left
				           width=>'100%' // Adjust the chart Area.
	                    )
					),
					"cssClass"=>array(
						"table"=>"table table-striped table-bordered"
					),
			        "showFooter"=>"bottom",
			        "columns"=>array(
			            "agerange"=>array(
			                "footer"=>"Insgesamt"
			            ),
			            "Summe"=>array(
			                "footer"=>"sum",
			                "footerText"=>"@value",
			            )
			        )
                ));
                ?>
            </div>

The result looks like the enclosed screenshot. As you can see, the values of 90 - 105 are penultimate in order, but should be second.

How can I solve this?

Markus Mohr commented on Mar 4, 2019

What does not deliver a solution is:

->pipe(new CalculatedColumn(array(
                            "agerange"=>array(
                                "exp"=>function($row)
                                {
                                    return intval($row["agerange"])."-".intval($row["agerange"]+15));
                                },
                                "type"=>"string"
                            ))))

Setting "type"=>"string" to "type"=>"number" changes to a different less well "readable" chart.

KoolReport commented on Mar 5, 2019

In your query, you do:

ORDER BY agerange ASC,

You remove this portion of code:

                        ->pipe(new CalculatedColumn(array(
                            "agerange"=>array(
                                "exp"=>function($row)
                                {
                                    return $row["agerange"]."-".($row["agerange"]+15);
                                },
                                "type"=>"string"
                            ))))

and then add the formatValue for agerange like this on your chart

columns=>array(
    "agerange"=>array(
        "formatValue"=>function($value)
        {
            return $value."-".($value+15);
        }
    ),
    ...
)
Markus Mohr commented on Mar 5, 2019

No, it does not work:

<div class="col-md-12">
<?php
ColumnChart::create(array(
    "title"=>"Bauchumfang bei Erstuntersuchung",
    "dataSource"=>(
        $this->src("patient")->query("
            SELECT IFNULL(floor(ini.BauchumfangMaenner/15)*15, 'Noch nicht dokumentiert') as agerange, count(*) as Summe
			FROM dat_patient p
            LEFT JOIN dat_initialdaten ini ON ini.patID = p.ID
            WHERE 1 = 1
			-- AND ini.BauchumfangMaenner IS NOT NULL
			-- AND ini.BauchumfangMaenner != -1
			AND NOT EXISTS (SELECT 1
				FROM dat_initialdaten ini2
				WHERE ini2.patID = p.ID AND ini2.revision > ini.revision
				)
			AND p.Testzwecke = 0
            GROUP BY agerange
			ORDER BY agerange ASC
        ")
	),
	"columns"=>array(
	    "agerange"=>array(
	        "formatValue"=>function($value)
	        {
	            return $value."-".($value+15);
	        }
	    )
	),
    "options"=>array(
        "vAxis"=>array(
            "title"=>"Anzahl an Patienten",
        ),
        "hAxis"=>array(
            "title"=>"Bauchumfangswerte"
        )
    )
));
?>
</div>

It kills the query with the following error message:

"Not enough columns given to draw the requested chart.×"

KoolReport commented on Mar 5, 2019

You forget the "Summe" in columns:

    "columns"=>array(
	    "agerange"=>array(
	        "formatValue"=>function($value)
	        {
	            return $value."-".($value+15);
	        }
	    ),
            "Summe"
    )
Markus Mohr commented on Mar 5, 2019

Thank you, you are right, but even by substituting "Summe" as required there is no positional change:

KoolReport commented on Mar 5, 2019

Because of this IFNULL(floor(ini.BauchumfangMaenner/15)*15, 'Noch nicht dokumentiert'), your column is string type. The SQL query will order your data by alphabeta not by value.

Markus Mohr commented on Mar 5, 2019

Leaving IFNULL away, just "flooring" (the MySQL format of "BauchumfangMaenner" is MEDIUMINT):

KoolReport commented on Mar 5, 2019

In your chart, I still see the "Noch nicht..." at the end.

Markus Mohr commented on Mar 5, 2019

Sorry, Firefox apparently needs to be manually reloaded from time to time to delete the cache even if your settings do not allow for cache ...

Okay, here is the new screenshot:

Which delivers the same results as though I would have had the following code:

->pipe(new CalculatedColumn(array(
                            "agerange"=>array(
                                "exp"=>function($row)
                                {
                                    return $row["agerange"]."-".($row["agerange"]+15);
                                },
                                "type"=>"number"
                            ))))

This chart is worthless since it does not assign understandable values to the last two columns and does not show the single steps such as "135-150".

Markus Mohr commented on Mar 5, 2019

Sorry for being tenacious, but what I want to accomplish is the following:

  1. Display a nicely formatted chart:

  1. Display the corresponding table (how to tweak the "o-15" instead of merely "0" etc. is clear to me):

  1. Create an automatic error table in which all patients with missing or incorrect values are depicted and can be edited by calling the PatientID as a hyperlink (not realized in this screenshot):

"Körpergrösse" has the same MySQL format (MEDIUMINT), and all queries (chart and tables) are IFNULL() queries. Of course, here is no value of "95-105", but isn't there a way to circumvent this and display the hAxis numerically sorted?

KoolReport commented on Mar 5, 2019

You may try:

<?php
ColumnChart::create(array(
    "title"=>"Bauchumfang bei Erstuntersuchung",
    "dataSource"=>(
        $this->src("patient")->query("
            SELECT IFNULL(floor(ini.BauchumfangMaenner/15)*15, 1000) as agerange, count(*) as Summe
            FROM dat_patient p
            LEFT JOIN dat_initialdaten ini ON ini.patID = p.ID
            WHERE 1 = 1
            -- AND ini.BauchumfangMaenner IS NOT NULL
            -- AND ini.BauchumfangMaenner != -1
            AND NOT EXISTS (SELECT 1
                FROM dat_initialdaten ini2
                WHERE ini2.patID = p.ID AND ini2.revision > ini.revision
                )
            AND p.Testzwecke = 0
            GROUP BY agerange
            ORDER BY agerange ASC
        ")
        ->pipe(new CalculatedColumn(array(
            "agerange"=>array(
                "exp"=>function($row)
                {
                    if ($row["agerange"]==1000)
                    {
                        return "Noch nicht dokumentiert";
                    }
                    else
                    {
                        return $row["agerange"]."-".($row["agerange"]+15);
                    }
                },
                "type"=>"string"
        ))))
    ),
    "options"=>array(
        "vAxis"=>array(
            "title"=>"Anzahl an Patienten",
        ),
        "hAxis"=>array(
            "title"=>"Bauchumfangswerte"
        )
    )
));
?>
Markus Mohr commented on Mar 5, 2019

Aaaaahhh, much better, that solved it:

But I had to change

return $row;

to

return $row['agerange'];

so as to avoid the term "Array" as last column term in the chart.

Thank you again.

KoolReport commented on Mar 5, 2019

Ah yes,my mistake, writing code in blind. Great that you made it work.

Markus Mohr commented on Mar 5, 2019

Can I send a tip even when having closed this topic? Afterwards, so to say?

KoolReport commented on Mar 5, 2019

Thank you very much! You can send tip at anytime.

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
solved

None