Topic: Report SQL and GRID

Hi, everyone.

I'm doing a report from a button with "SQL Report" in a search form and a grid. I need that when I do the "search", the report shows the results of the search that are in the grid. I have configured in the "SQL Report" button, in "Select the Grid table (if necessary)" = Table grid1, but when I run the search, it asks me to "select a record in the grid". That is, only one record (the selected one) is shown in the report and not all the data resulting from the search that is in the grid. I need the report to work as the button does (EXPORT TO EXCEL), which shows all the records of the search that appear in the grid.

Re: Report SQL and GRID

Hello.


This option "Select the Grid table (if necessary)" allows you to record identifier for your SQL query of the necessary components of the table. Suppose you want to print only the selected record from a table component. To do this you need to add the SQL query: where tablename.id = $id, where $id is the identifier of a record from the table.


In your case no need to use this option. You should use all components in your SQL query which you  use for search button. Example how to do it:

SELECT
employees.lastname,
employees.firstname,
strftime('%d.%m.%Y', employees.dateofbirth),
strftime('%H:%M:%S', employees.timefield),
groups.groupname,
(CASE WHEN employees.smoke = 1 THEN 'Yes' WHEN employees.smoke = 0 THEN 'No' END),
employees.id

FROM
employees

LEFT OUTER JOIN groups ON groups.id=employees.id_groups

WHERE

(CASE WHEN '{edLastName}' = '' THEN 1=1 ELSE employees.lastname LIKE '{edLastName}' END) AND
(CASE WHEN '{edFirstName}' <> '' THEN employees.firstname LIKE '%{edFirstName}%'  ELSE 1=1 END) AND
(CASE WHEN {dtDateOfBirth} IS NOT NULL THEN date(employees.dateofbirth) = date({dtDateOfBirth}) ELSE 1=1 END) AND

(CASE WHEN {DateTimePickerFrom} IS NOT NULL THEN date(employees.dateofbirth) >= date({DateTimePickerFrom}) ELSE 1=1 END) AND
(CASE WHEN {DateTimePickerTo} IS NOT NULL THEN date(employees.dateofbirth) <= date({DateTimePickerTo}) ELSE 1=1 END) AND

(CASE WHEN {dtTime} IS NOT NULL THEN time(employees.timefield) = time({dtTime}) ELSE 1=1 END) AND
(CASE WHEN {chbSmoke} IS NOT NULL THEN employees.smoke = {chbSmoke} ELSE 1=1 END) AND
(CASE WHEN {cbGroups} <> -1 THEN employees.id_groups = {cbGroups} ELSE 1=1 END)  


if you can not do it, just attach your project and let me know name of component and form which you use to do the report.

Dmitry.

Re: Report SQL and GRID

Thanks a lot