Page 1 of 2
Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 8:49 am
by kknm
In a subquery, the monthly daily summary
The date field contains the start time of the shift - 8:00 and 20:00
It is necessary to calculate the amounts by fields and the amount of shifts.
Everything works correctly in phpmyadmin.
What could be the reason for this behavior?
Code: Select all
SELECT DATE_FORMAT(h.adat,'%m') AS mes, h.fio, SUM(h.kol) as kol, SUM(h.gm) AS gm, SUM(h.ton) AS tn, SUM(h.km) AS km, SUM(h.den) AS den, SUM(h.night) AS night, h.mast
FROM
(SELECT DATE_FORMAT(tabel.tab_data,'%d.%m.%Y') AS adat,
vodit.vod_fio AS fio,
IF(TIME_FORMAT(tabel.tab_data,'%H')='08','1','') AS den,
IF(TIME_FORMAT(tabel.tab_data,'%H')='20','1','') AS night,
SUM(hodki.hod_kol) AS kol,
SUM(hodki.hod_gm) AS gm,
SUM(hodki.hod_ton) AS ton,
SUM(hodki.hod_km) AS km,
master.mas_fio AS mast
FROM
hodki
JOIN vodit ON hodki.hod_vod = vodit.vodit_id
JOIN tabel ON hodki.hod_tab = tabel.tabel_id
JOIN master ON tabel.tab_mast = master.master_id
WHERE DATE_FORMAT(tabel.tab_data,'%Y %m') = DATE_FORMAT('#rep_mes#','%Y %m')
-- WHERE DATE_FORMAT(tabel.tab_data,'%Y.%m') ='2021.06'
GROUP BY fio,1
ORDER BY fio ) AS h
GROUP BY fio, mast
ORDER BY 2,3
Doesn't see the field through the temporary table either
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 9:43 am
by kev1n
Did you also try using a
Procedure instead of an SQL?
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 2:08 pm
by kknm
kev1n wrote:Did you also try using a
Procedure instead of an SQL?
I have created a procedure according to your sample.
How to pass # TABLE_ID # to the report from the form?
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 2:17 pm
by kev1n
Pick the created Procedure under Builders -> Report
(Table Lookup)
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 2:37 pm
by kknm
kev1n wrote:Pick the created Procedure under Builders -> Report
(Table Lookup)
Again we do not understand each other ...
I created a procedure, when I call the procedure, I see that a table of type ___nu1610a7da19a4c0___ has been created with my request. I assigned this procedure in reports, but there is not a single field in the report designer ...
If you select this table in quick reports (___ nu1610a7da19a4c0___), then the query fields appear.
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 3:31 pm
by kev1n
Could you upload the row with the report in table zzzzsys_report? And also your tables (containing a few dummy data rows)
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 3:49 pm
by kknm
kev1n wrote:Could you upload the row with the report in table zzzzsys_report? And also your tables (containing a few dummy data rows)
Again we do not understand each other ...
How and where to do it?
Maybe there is an example of using # TABLE_ID # for a report in your demo database?
I want to make another query on this table in nuSQL.
Re: Quick report does not see fields nuSQL
Posted: Wed Aug 04, 2021 4:23 pm
by kknm
I figured it out, or rather rebooted the server and optimized the database.
Was able to run fast report with # TABLE_ID #. All fields appeared. But the issue remained not fully resolved. I want to make another query to # TABLE_ID #, because this is an expanded table, and in the report I want to display the summed colored fields by several groupings, not just all rows.
Re: Quick report does not see fields nuSQL
Posted: Thu Aug 05, 2021 9:12 am
by kknm
kev1n wrote:Pick the created Procedure under Builders -> Report
(Table Lookup)
I understood the reason for the misunderstanding -It was necessary to do so -
Code: Select all
Pick the created Procedure under Builders -> FAST Report
(Table Lookup)[
Re: Quick report does not see fields nuSQL
Posted: Sun Aug 08, 2021 3:35 pm
by steven
kknm,
BTW, without trying to sound like a nerd, its
#TABLE_ID# not
# TABLE_ID #.
Steven