How to run multiple reports from one form

Questions related to customising nuBuilder Forte with Javascript or PHP.

How to run multiple reports from one form

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

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.
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: How to run multiple reports from one form

Postby admin » Thu Jun 25, 2020 7:32 am

kknm,

I don't understand your question.


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

Re: How to run multiple reports from one form

Postby kknm » Thu Jun 25, 2020 4:30 pm

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
critery.png (17.4 KiB) Viewed 154 times

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
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: How to run multiple reports from one form

Postby Janusz » Thu Jun 25, 2020 5:30 pm

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
raport.JPG (235.84 KiB) Viewed 148 times

and the report can be like that:
raport2.JPG
raport2.JPG (50.2 KiB) Viewed 148 times
Janusz
 
Posts: 310
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: How to run multiple reports from one form

Postby kknm » Thu Jun 25, 2020 6:30 pm

[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
critery.png (48.53 KiB) Viewed 144 times
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: How to run multiple reports from one form

Postby Janusz » Thu Jun 25, 2020 6:41 pm

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, .....
Janusz
 
Posts: 310
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: How to run multiple reports from one form

Postby kknm » Thu Jun 25, 2020 7:37 pm

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.
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: How to run multiple reports from one form

Postby Janusz » Thu Jun 25, 2020 8:10 pm

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.
Janusz
 
Posts: 310
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: How to run multiple reports from one form

Postby kknm » Thu Jun 25, 2020 9:04 pm

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
kknm
 
Posts: 139
Joined: Sat Apr 11, 2020 7:33 am

Re: How to run multiple reports from one form

Postby admin » Mon Jun 29, 2020 7:18 am

kknm,

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

1. themonth
2. theyear

cs.PNG
cs.PNG (21.36 KiB) Viewed 94 times



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
dmr.PNG (7.8 KiB) Viewed 94 times


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

Next

Return to Custom Code