How to run multiple reports from one form

[kknm]

On the form there are two text fields 'year' and 'month'.
It is required to run many reports with these parameters, but with different indicators. Let it be separate buttons.

[admin]

kknm,

I don't understand your question.


Steven

[kknm]

kknm,

I don't understand your question.


Steven

I want a report with the criterion - 'YEAR MONTH'
This criterion applies to different tables.
Those. I want a selection from a table with this criterion, then another selection from another table with this criterion. I made a form with a criterion, I can also transfer the value of the criterion to different tables. How to show a criterion field (DATE_FORMAT(rep_mes,'%Y %m') in a report?

critery.png

SQL

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 DATE_FORMAT(tabel.tab_data,'%Y %m')= DATE_FORMAT('#rep_mes#','%Y %m')
GROUP BY ves_num ASC 

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

[Janusz]

Hi, if understood properly - you want the criteria to be displayed on the report printout.
If yes you need to add these criteria to the query and later to place them in the header section.
ex.:
let say the citeria are the par_... fields and details the rap_... fields

Code: Select all

SELECT con_part,par_number,par_name,par_zlecenie,rap_number,rap_type,rap_location from v_connect_sub left join parts on parts_id=con_part
WHERE con_part='#parts_id#'

raport.JPG

and the report can be like that:

raport2.JPG

[kknm]

[quote="Janusz"][/quote]

The question is how to format the criteria in SQL for display in the report. It should look like this - '2020 06'.

critery.png

[Janusz]

in the SQL you can use date format.
ex.

Code: Select all

SELECT *, DATE_FORMAT(rej_activity_month,'%Y-%m') AS act_month , sekcja.sek_nazwa AS sek_dzial,
REPLACE(REPLACE(rej_frozen,1,'OK'),0,'?') AS status1, .....

[kknm]

in the SQL you can use date format.
ex.

Code: Select all

SELECT *, DATE_FORMAT(rej_activity_month,'%Y-%m') AS act_month , sekcja.sek_nazwa AS sek_dzial,
REPLACE(REPLACE(rej_frozen,1,'OK'),0,'?') AS status1, .....

If you look at my SQL query above, you will see that I already use this construct
DATE_FORMAT ().
SQL query correctly fetch.

When applying DATE_FORMAT (criterion) in SELECT, the query does not work.SQL without UNION works with the criterion field correctly ... It looks like I need to understand this syntax.

[Janusz]

Difficult to answer without some trials with real data. Normally I test the querry directly in phpMyAdmin and if it works then I use in nuBuilder. So the question is - if you run your query in phpmyadmin can you see the the column with the YYYY-mm format?
In the SELECT I do not see the the date format - it's just only in the WHERE statement.

[kknm]

Difficult to answer without some trials with real data. Normally I test the querry directly in phpMyAdmin and if it works then I use in nuBuilder. So the question is - if you run your query in phpmyadmin can you see the the column with the YYYY-mm format?
In the SELECT I do not see the the date format - it's just only in the WHERE statement.

All figured out!
That's right - when SELECT has the same number of fields in the preliminary query and in the final one.

Code: Select all

SELECT DATE_FORMAT(tabel.tab_data,'%Y %m') AS adat ,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 DATE_FORMAT(tabel.tab_data,'%Y %m') = DATE_FORMAT('#rep_mes#','%Y %m')
GROUP BY ves_num ASC

UNION ALL 
SELECT DATE_FORMAT(tabel.tab_data,'%Y %m') AS adat,'Итого:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
	JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id 
ORDER BY sum_kol

[admin]

kknm,

If you create a criteria (Launch) Form with 2 Objects.

1. themonth
2. theyear

cs.PNG

you can use these values as Hash Cookies both in your PHP and on a Report.

PHP

Code: Select all

WHERE DATE_FORMAT(tabel.tab_data,'%Y %m')= '#theyear# #themonth#'

Report

dmr.PNG

Steven