Quick report does not see fields nuSQL

[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

[kev1n]

Did you also try using a Procedure instead of an SQL?

[kknm]

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?

[kev1n]

Pick the created Procedure under Builders -> Report
(Table Lookup)

[kknm]

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.

[kev1n]

Could you upload the row with the report in table zzzzsys_report? And also your tables (containing a few dummy data rows)

[kknm]

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.

[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.

[kknm]

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)[

[steven]

kknm,

BTW, without trying to sound like a nerd, its #TABLE_ID# not # TABLE_ID #.



Steven