Page 1 of 2
How to run multiple reports from one form
Posted: Tue Jun 23, 2020 4:28 pm
by 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.
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 12:02 am
by admin
kknm,
I don't understand your question.
Steven
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 9:00 am
by kknm
admin wrote: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
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 10:00 am
by 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
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 11:00 am
by 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
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 11:11 am
by 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, .....
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 12:07 pm
by kknm
Janusz wrote: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.
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 12:40 pm
by 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.
Re: How to run multiple reports from one form
Posted: Thu Jun 25, 2020 1:34 pm
by kknm
Janusz wrote: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
Re: How to run multiple reports from one form
Posted: Sun Jun 28, 2020 11:48 pm
by 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