Search the Community

For the SQLite developers... New version 1.0.0.6 This is a Report Generator for SQLite Database. The script supports up to two dynamic parameters per report, see the 'SQLite Reports.ini' file for more details. Single Date, period, string or number input are possible I provide a full functional example using the Chinook Demo Database (http://chinookdatabase.codeplex.com), so everyone can test it. All required files are contained in the zip file. You can download the zipfile in the link hereunder. I created 20 different reports, from which 4 are system reports and one is not linked to any table. Features: Up to 2 dynamic Parameters via input dialog box per reportMulti-line fields will display in a separate Window when you click on the cell containing multiline data (which cannot be displayed in a listview, if you click on a normal cell, the window will disapearDouble click on a row and a new Window will display the row vertically, usefull for rows with many columns.Export to ExcelFast, even with several thousands of rows (see Track report)SQL that do not deliver data (no rows, no columns) will display a popup message instead (for example using the VACUUM command)Of course you have to know SQL but I guess that everyone who programs SQLite will, right? The SQL scripts are stored in the ini file, where 3 sections ‘Titles, SQL and Parameters’ contain all the elements required for a report. For simplicity, I decided to store the SQL script in the ini file as a single line. The drawback is that the SQL is not easy to read or to maintain, but look at the example ' Invoice with details' where I join 8 tables. It's absolutely functional. Read the [sql readme] section for more details about dynamic parameters. I think its usage is pretty obvious. Know issues: When compiled as 64bit, double click does not function in the main menu, I don't know why but I cannot get this fixed. If anyone knows, I would be pleased to get some help on this. All the other functions, including the double click in the ListView do work. The script will use SQLite3.dll or SQLite3_x64.dll if compiled as a x64 App. The script is a simplified version of my multi-platform Database Report Generator using Active-X DB connection. This SQLite report script (1600 lines) has very basic features compared to the latter (for example it fully integrates the ExcelChart UDF developed by water) but it has 13000 lines and is not provided has open source (sorry L, an early version was released on this forum in 2009 but for Oracle only) I hope this one will be useful for you, I had some fun with it, took me 3 evenings to do the job. Enjoy. GreenCan Updates 1.0.0.6: Bug fixes (thanks jpm)Better GUI windows managementExcel export optimized_COMError implementedFull package: http://users.telenet.be/GreenCan/AutoIt/SQLite_Reports_1.0.0.6.zip (If you downloaded the full package of the previous version, you don't have to download it again) SQLite Reports 1.0.0.6.au3 SQLite Reports.ini : [General] ;Database=Chinook_Sqlite.sqlite Database=Chinook_Sqlite_AutoIncrementPKs.sqlite DateFormat=YYYY-MM-DD [Titles] Report=Album Report=sqlite_sequence (shows number of records for each table) Report=Artist Report=Customer Report=All Employees Report=Employees birthday - period Report=Employees birthday - Name contains Report=Genre Report=Invoice Report=Invoice - amount between Report=Track Report=MediaType Report=Invoice with details - one invoice selection Report=Playlist Report=PlaylistTrack Report=Non-Database - Date formats examples Report=System - Tables and fields (sqlite_master) Report=System - List Tables of DataBase (sqlite_master) Report=System - SQLite version Report=System - Database cleanup (Vacuum) [SQL] Report=SELECT AlbumId, Title, ArtistId FROM Album Report=SELECT name,seq FROM sqlite_sequence Report=SELECT ArtistId, Name FROM Artist ORDER BY Name Report=SELECT CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId FROM Customer ORDER BY CustomerId Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE BirthDate between '%d1%' AND '%d2%' Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE LastName || ' ' || FirstName like '%%1%%' Report=SELECT GenreId, Name FROM Genre Report=SELECT InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice Report=SELECT InvoiceId, CustomerId, Date(InvoiceDate), BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice WHERE Total between %1% AND %2% Report=SELECT TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice FROM Track Report=SELECT MediaTypeId, Name FROM MediaType Report=SELECT Invoice.InvoiceId, Date(Invoice.InvoiceDate) as "Invoice Date", Customer.FirstName, Customer.LastName, Invoice.BillingAddress, Invoice.BillingCity, Invoice.BillingState, Invoice.BillingCountry, Invoice.BillingPostalCode, Track.Name as "Track Name", Album.Title as "Album", MediaType.Name as "Media", Genre.Name as "Genre", Artist.Name as "Artist", Track.Composer, InvoiceLine.UnitPrice, InvoiceLine.Quantity, Invoice.Total FROM Invoice INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId INNER JOIN Customer ON Invoice.CustomerId = Customer.CustomerId INNER JOIN Track ON InvoiceLine.TrackId = Track.TrackId INNER JOIN Album ON Track.AlbumId = Album.AlbumId INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId INNER JOIN Genre ON Track.GenreId = Genre.GenreId INNER JOIN MediaType ON Track.MediaTypeId = MediaType.MediaTypeId WHERE Invoice.InvoiceId = %1% Report=SELECT * FROM Playlist Report=SELECT * FROM PlaylistTrack Report=SELECT date('now') as "today", date('now','start of month','+1 month','-1 day') as "last day current month", datetime(1092941466, 'unixepoch') as "unix timestamp", datetime(1092941466, 'unixepoch', 'localtime') as "unix timestamp, compensate for, local timezone", strftime('%s','now') as "current unix timestamp", julianday('now') - julianday('1776-07-04') as "days since US Independence", strftime('%s','now') - strftime('%s','2004-01-01 02:34:56') as "Time elapsed", date('now','start of year','+9 months','weekday 2') as "first Tuesday in October", (julianday('now') - 2440587.5)*86400.0 Report=select * from sqlite_master Report=SELECT name FROM sqlite_master WHERE type='table' Report=SELECT sqlite_version() as "sqlite version" --, sqlite_source_id() as "sqlite source_id" Report=VACUUM [Parameters] Report= Report= Report= Report= Report= Report= Report=Name contains|string will match any position in FirstName or LastName Report= Report= Report=Total between|For fraction use a dot (eg: 5.95),and|For fraction use a dot (eg: 5.95) Report= Report= Report=Invoice ID| Report= Report= Report= Report= Report= Report= Report=