Using SQL Reports2004 WebGUI User’s Conference

Huh?

SQL Reports are used to:Build alternate interfaces to WebGUI WobjectsDisplay internal WebGUI dataDisplay data from your business applications

Why?

Creating new wobjects is more difficult than just writing some SQLYou already have the data you need, and just need to output it

Why not?

Complex outputs should be handled by macrosIf you also need data entry / management then use a wobject

SQL Report Basics

Database Link

SQL query

Template

Database Links

A place to manage database connections (data sources)Uses Perl standard DSNs

Data Sources

Anything with a DBI DriverMySQL, Oracle, Postgres, SyBase, MSSQL, Firebird, Informix, CSV, BerkleyDB, ODBCReally, just about anything useful.

DSN

DBD:::;optionsDBD:mysql:WebGUI;host=localhost

Basic SQL Query

SELECT fieldsFROM tablesWHERE limiting claues

SELECT userId, usernameFROM userswhere username like ‘a%’

Template

()

Bring it TogetherSELECT userId, usernameFROM userswhere username like ‘a%’

()

Bring it TogetherSELECT userId, usernameFROM userswhere username like ‘a%’

()

Bring it TogetherSELECT userId, usernameFROM userswhere username like ‘a%’

()

Layout In Your Hands

Tables

Lists

CSS layout

Whatever

What have we learned?

Database link manages connectionSQL Query retrieves the dataTemplate displays the data

Gotcha: SQL Functions

SELECT username, from_unixtime(dateCreated)FROM userswhere username like‘a%’

Gotcha: SQL Functions

ProblemField name = Template Variable

SolutionUse an SQL alias in the Queryfrom_unixtime(dateCreated) as date_created

Example: Search Engine

Problem

Got data

Can’t code

Need to search

Solution

SQL Report!

The Trick

^FormParam();Accepts a value from a formPlace it into your query

Turn onPreprocess macros on query

The Template

Add a query form to your template:

The Output

Searchcalifornia

The Query

SELECTcity, county, zipcode

FROMmapinfo

WHEREstate like

‘%^FormParam(”query”);%’

The Resulting Query

SELECTcity, county, zipcode

FROMmapinfo

WHEREstate like

‘%california%’

The Output

Searchcalifornia

Beverly Hills Orange 90210

Silverado Broward 90032

Blue Hill Lake 91236

Example: Drill Down

Problem

Got lots of data

Can’t display it all at once

Solution

SQL Report!

The Trick

Multiple SQL ReportsChain them together with ^FormParam();The data from the first seeds the next

The Data

artistidname

albumidnameartist_id

songidnamealbum_id

The Template

Add a link to your template:

The Query

SELECTid, name

FROMartist

PrinceMetallicaHank WilliamsEminemGeorge Clinton and the P-Funk

The Output

The Template

Add a link to your template:

The Query

SELECTid,name

FROMalbum

WHEREartist_id = ’^FormParam(”artistId”);’

The Resulting Query

SELECTid,name

FROMalbum

WHEREartist_id = ’335’

And Justice For AllMaster of PuppetsThe Black AlbumLoadReloadGarage, Inc.

The Output

The Query

SELECTid,name

FROMsong

WHEREalbum_id = ’^FormParam(”albumId”);’

The Resulting Query

SELECTid,name

FROMsong

WHEREalbum_id = ’3913’

Enter SandmanThe UnforgivenHarvester of SorrowSad But TrueOf Wolf and ManNothing Else Matters

The Output

The Drill Down ReportPrinceMetallicaHank WilliamsEminemGeorge Clinton and the P-Funk And Justice For All

Master of PuppetsThe Black AlbumLoadReloadGarage, Inc.

Enter SandmanThe UnforgivenHarvester of SorrowSad But TrueOf Wolf and ManNothing Else Matters

6.3 and beyond

Sub-queries (6.3)Not the same as sub-selectsUp three additional queriesEach sub-query allows the use of data from all of the previous queries

6.3 and beyond

Exports (6.4+)Download query result as

XMLTabCSV

6.3 and beyond

Graphing (7.0+)Pie chartsLine graphsBar graphs

7%8%10%

11%

29%

35%

19992002

20002003

20012004

Questions?