Hello gonpublic2k,
You are right, sometimes, the simple "Report" button is not enough and you need more flexibility on the data you send to the report.
You have to more options at your disposal to do so.
1 - The first one you already known : it's the "Report SQL" button.
With this option, you can write the query you want and add all the joints and calculated fields you need, cases and so on.
The main problem with this approach being that a complex query will narrow down the data you get in return.
Look at the query bellow :
SELECT DISTINCT
asset.asset_sku,
asset.asset_name,
asset.asset_price,
asset.asset_status,
keyword.kw_text
FROM
asset
INNER JOIN asset_kw ON asset_kw.id_asset = asset.id
INNER JOIN keyword ON asset_kw.id_keyword = keyword.id
ORDER BY keyword.kw_text,
CAST(asset_sku AS INTEGER) ASC
This query returns every asset in my database as long as they are linked with a keyword (or more) and prints out a report.
(I have 3 tables : a table called asset, a table called keywords and a joining table called asset_kw in between)
The INNER JOIN instruction does filter a lot the result of the query because it's result is exclusive. All my assets that are not linked with a keyword will not show up in the report. If I want them all, I need to replace the INNER JOIN with LEFT JOIN. This way, all assets are reported, and those having an assigned a keyword will show this extra information.
As for calculated fields, they are added the same way as you did them in you database except the come directly in the query. If I add
(SELECT COUNT(id_keyword) FROM asset_kw WHERE asset_kw.id_asset = asset.id) AS NBK
in my query, I have one more result column in my report counting how many keyword has been assigned to each asset.
But sometimes, one query for too many tables is too complicated to write and you can not link all needed tables with JOIN and so on.
2 - Then you can simply write you queries in script and send them to the report.
Add an Onclick event to a button and use datasets.
You will need two different kind of datasets, the regular one you probably already use, and the ones for the reports.
Here is an example on how you could send the results of 3 different queries to a single report :
procedure Form2_Button1_OnClick (Sender: string; var Cancel: boolean);
var
frxDBDataSet1, frxDBDataSet2, frxDBDataSet3 : TfrxDBDataset;
Results1, Results2, Results3 : TDataSet;
asset_id : Integer;
begin
//the variable I need to trigger the queries
asset_id := mark_list.TableGrid1.dbItemID;
//qyery one stored in dataset1
SQLQuery('SELECT '+
'asset_sku,'+
'asset_old_sku,'+
'asset_name,'+
'asset_url,'+
'asset_price,'+
'vendor_name,'+
'asset_status,'+
'asset_detail '+
'FROM '+
'asset '+
'INNER JOIN vendor ON asset.id_vendor = vendor.id '+
'WHERE asset.id = "'+IntToStr(asset_id)+'"', Results1);
//query two stored in dataset 2
SQLQuery('SELECT '+
'asset.asset_sku,'+
'asset.asset_name,'+
'asset.asset_status '+
'FROM '+
'asset '+
'INNER JOIN asset_req ON asset_req.id_asset1 = asset.id '+
'WHERE asset_req.id_asset = "'+IntToStr(asset_id)+'" ORDER BY CAST(asset_sku AS INTEGER) ASC ', Results2);
//query three stored in dataset 3
SQLQuery('SELECT '+
'artist.artist_name '+
'FROM '+
'asset '+
'INNER JOIN artist_asset ON artist_asset.id_asset = asset.id '+
'INNER JOIN artist ON artist_asset.id_artist = artist.id '+
'WHERE artist_asset.id_asset = "'+IntToStr(asset_id)+'"', Results3);
//transfer standard datasets results to report datasets
frxDBDataSet1 := TfrxDBDataset.Create(Form2);
frxDBDataSet1.UserName := 'Asset';
frxDBDataSet1.CloseDataSource := True;
frxDBDataSet1.OpenDataSource := True;
frxDBDataSet1.DataSet := Results1;
frxDBDataset2 := TfrxDBDataset.Create(Form2);
frxDBDataset2.UserName := 'Reqs';
frxDBDataset2.CloseDataSource := True;
frxDBDataset2.OpenDataSource := True;
frxDBDataset2.DataSet := Results2;
frxDBDataset3 := TfrxDBDataset.Create(Form2);
frxDBDataset3.UserName := 'Artists';
frxDBDataset3.CloseDataSource := True;
frxDBDataset3.OpenDataSource := True;
frxDBDataset3.DataSet := Results3;
//assign the three report datasets to the same report
Form1.frxReport.Clear;
Form1.frxReport.DataSets.Clear;
Form1.frxReport.DataSets.Add(frxDBDataSet1);
Form1.frxReport.DataSets.Add(frxDBDataset2);
Form1.frxReport.DataSets.Add(frxDBDataset3);
//DESIGN MODE - comment the following two lines and uncomment the preview mode lines to preview report
Form1.frxReport.LoadFromFile('.\Report\full_asset_report-backup.fr3');
Form1.frxReport.DesignReport;
//PREVIEW MODE - comment the following four lines and uncomment the above design mode lines to design report
//frxDBDataset1.DataSet.Close;
//frxDBDataset2.DataSet.Close;
//Form1.frxReport.LoadFromFile(ExtractFilePath(Application.ExeName)+'Report\MyReport.fr3'); //this is my path and my report name, you need to use yours
//Form1.frxReport.ShowReport;
Results1.Free;
frxDBDataSet1.Free;
Results2.Free;
frxDBDataset2.Free;
Results3.Free;
frxDBDataset3.Free;
Form1.frxReport.Clear;
Form1.frxReport.DataSets.Clear;
end;
NOTE : the customized report is called from Form2, but you will see calls to Form1 (the main application form). This is because the report handling is done by the main form of your application. Don't forget that 
This example is built with 3 queries, but you can use as many as you want. The datasets produced will appear in design mode in your report under the names you'll have given them (here Asset, Reqs and Artists).
I can not send you this example because it is part of a very large project that is more than 200 Mo once compressed but if things are unclear, I'll arrange something to give you an example.
Hope this helped a little and was not too long to read.
have a good day
Cheers
Mathias
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
