Report with criteria

Questions related to nuBuilder Forte Reports and the Report Builder.

Re: Report with criteria

Postby treed » Tue Sep 01, 2020 4:39 am

The info from the link works to pass data from a launch form. This is useful and the hash tags in the SQL is an interesting concept. Is the MSAccess notion supported where a filter or where clause can be passed to the report via the code under the button? Or can the SQL for the report be changed from the code under the button? The reason I ask is that I frequently like to design reports where the user can select an item or not and get all of the records. I think MySQL will support conditional logic in the WHERE clause, but I've never needed to write SQL that way before and would make debugging a challenge.
treed
 
Posts: 31
Joined: Mon May 18, 2020 7:32 am

Re: Report with criteria

Postby kev1n » Tue Sep 01, 2020 11:15 am

Create a PHP Procedure to create a temporary table.

https://wiki.nubuilder.net/nubuilderfor ... orts#Table

The point of using a Procedures is to create a temporary table that can be manipulated before it is used by the Report.

(If a nuTABLE or a nuSQL table is chosen, this temporary table is created automatically)


The temp table created must be called #TABLE_ID#.


#TABLE_ID# simply gets replaced by the name of a temporary table, created by nuBuilder, that will be used by the Report.

Using nuSetProperty() you can set Hash Cookies and use them in the Procedure. This is very powerful.

Example Procedure:

Code: Select all
$sql   = "CREATE TABLE  #TABLE_ID#";

$sql .= "

SELECT
    zzzzsys_object.*

FROM
    zzzzsys_object where sob_all_type = '#field00#'
    AND sob_all_table like '%#table_like#%'

";

nuRunQuery($sql);



Where field00 could be a field on your form and table_like a Hash Cookie that can be set (e.g. in the onclick event of a button) like this:

Code: Select all
nuSetProperty('table_like','zzzz')


Let me know if you have any questions.
kev1n
 
Posts: 978
Joined: Mon Oct 15, 2018 2:13 am

Previous

Return to Reports