Sort in reports

Questions related to customising nuBuilder Forte with Javascript or PHP.

Sort in reports

Postby kknm » Tue Jun 23, 2020 7:42 pm

Why sorting does not work in reports ?
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn

FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC

UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
ORDER BY ves_num ASC
]
ves.png
ves.png (35.2 KiB) Viewed 107 times
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: Sort in reports

Postby Janusz » Tue Jun 23, 2020 8:08 pm

In such cases I am defining view directly on DB level with phpmyadmin and after I refer to the view as to any other table. For me it's very conveniant and easy to debug.
Janusz
 
Posts: 310
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Sort in reports

Postby kev1n » Tue Jun 23, 2020 8:39 pm

Code: Select all
The ORDER BY is just applied to the 2nd select. Try this:

Select *
from
(
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn

FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC

UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
) results
ORDER BY ves_num ASC
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Sort in reports

Postby kknm » Tue Jun 23, 2020 11:14 pm

kev1n wrote:
Code: Select all
The ORDER BY is just applied to the 2nd select. Try this:

Select *
from
(
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn

FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC

UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
) results
ORDER BY ves_num ASC

That doesn't work either.
ORDER BY does not work together with UNION in phpmyadmin, too.

This is how it works.
Code: Select all
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn

FROM vesyauto
    JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC

UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: Sort in reports

Postby kev1n » Tue Jun 23, 2020 11:58 pm

How does the sorting happen? I don't see a ORDER BY.
kev1n
 
Posts: 784
Joined: Mon Oct 15, 2018 2:13 am

Re: Sort in reports

Postby Janusz » Wed Jun 24, 2020 12:26 am

ORDER BY does not work together with UNION in phpmyadmin, too.

https://stackoverflow.com/questions/3531251/using-union-and-order-by-clause-in-mysql/32848661

the above maybe can help

or the simpler solution: make second view which will make just the sorting of the first view :-) maybe crazy but works
Janusz
 
Posts: 310
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Sort in reports

Postby admin » Mon Jun 29, 2020 6:28 am

Guys,

kknm wrote:Why sorting does not work in reports ?


When a Report is run it takes the data and orders it by the Group Properties in the Report.

gb2.png
gb2.png (31.46 KiB) Viewed 52 times



This Report is ordered by ves_num which is sorted as a String not a Number.

ves.png
ves.png (34.74 KiB) Viewed 52 times


Steven
admin
Site Admin
 
Posts: 3289
Joined: Mon Jun 15, 2009 9:53 am


Return to Custom Code



cron