Create a script report

In this example we will create a small script type report to see the characteristics of the system.

First add a new report from the options, select the script type and press set to view the editor. Remember when you add the new report to set all the parameters in order to view correctly.

--create filter interface

filtertable.clear()

filtertable.addcategoryrow("Date")

filtertable.adddaterow("START","Start")

filtertable.adddaterow("END","End")

filtertable.addsubtablerow("CUSTOMER","Customers","customers","")

--create report grid

reporttable.clearcolumns()

reporttable.clearrows()

reporttable.adddatecolumn("date","Date",150,true)

reporttable.addtextcolumn("customer","Customer",200,true)

First of all, inside the interface script we insert all the commands to create the search filters and the columns that will be used to display the data.

The following rows, on the other hand, will be inserted into the processing script. While the interface script is launched only once, the processing script is performed every time we press the process button.

startdate = filtertable.getvalue("START")

enddate = filtertable.getvalue("END")

-- I do a basic search on a range of dates

iday = tostring(utility.dateday(startdate))

imonth = tostring(utility.datemonth(startdate))

iyear = tostring(utility.dateyear(startdate))

fday = utility.dateday(enddate)

fmonth = utility.datemonth(enddate)

fyear = utility.dateyear(endadate)

First, we recover the data values from the filtered table and prepare them for use in the search.

sql = "SELECT * FROM invoices WHERE eli=0 AND year(date) >=" .. iyear .. " AND month(date) >=" .. imonth .. " AND day(date) >=" .. iday

sql = sql .. " AND year(date) <=" .. fyear .. " AND month(date) <=" .. fmonth .. " AND day(date) <=" .. fday

-- I recover the remaining filters

rowcustomer = filtertable.getvalue("CUSTOMER")

if rowcustomer ~= nil then

sql = sql .. " AND gguid_name='" .. rowcustomer.getvalue("gguid") .. "'"

end

We prepare the sql string to perform the search by checking if a customer has been selected from the filter table. If yes we add it to the sql string for each search.

reporttable.clearrows()

table_rows= database.getsql(sql)

rows = table_rows.getrows()

for i = 1,table_rows.countrows() do

--output.print(rows[i].getvalue("date"))

row = reporttable.adddatarow()

row.setvalue("date",rows[i].getvalue("date"))

row.setvalue("customer",rows[i].getvalue("name"))

end

We perform the search and begin to compile the report table with the values found.