It is relatively simple once you get the hang of it. It involves much manual stuff but it is an excellent solution to data access and FREE!!!!!! I assume you have the Crystal VCL components.
Examples below:
My reports, the SQL & TTX are stored in the database as blobs and recoveredfrom the database to a directory for use
by the program.
You have to create the SQL & TTX manually to support the report fields. Start with the sql and run it against DB to verify SQL and fields. Then make the TTX. Name both the same as the report will be named. Start new report and name/save. Add database (SQL/TTX) and you are off and running. Code examples below.
SQL:
SELECT
Contacts."ROA_ID", Contacts."STATUS_REC", Contacts."ID", Contacts."STATUS_MEM", Contacts."NAMELAST", Contacts."NAMEFIRST", Contacts."DBA", Contacts."NUMBER", Contacts."POBOX", Contacts."STREET", Contacts."CITY", Contacts."STATE", Contacts."ZIP", Contacts."PHONE", Contacts."FAX", Contacts."EMAIL"
FROM
"INFOLIST" Contacts
TTX:
ROA_ID String 5 AAIIN
STATUS_REC String 10 ARCHIVE
ID Number 1
STATUS_MEM String 13
NAMELAST String 40
NAMEFIRST String 30
DBA String 40 APARTMENT ASSOC. OF INDIANAPOLIS
NUMBER Number 9202
POBOX Number 0
STREET String 35 N MERIDIAN ST STE 250
CITY String 20 INDIANAPOLIS
STATE String 4 IN
ZIP String 12 46260-1834
PHONE String 14
FAX String 14
EMAIL String 25
Calling a report
void __fastcall TReportDM:
rintContactsReport(int Top, int Left, int Height,
int Width, int Report)
{
//Create the report background
CreateReportBackground(Top, Left, Height, Width, "Printing Contact/Information Reports");
// CreateReportBackground(200, 700, Height, Width, "Printing Contact/Information Reports");
//Set up report header
String ReportTitle, SortField, SelectionFormula;
String ReportHeader = SuP.suUserData->Values["Acronym"] + " Information/Contact Listings";
ReportHeader = "\"" + ReportHeader + "\"";
//Set up report name
String ReportName = "Contacts";
CrpeADO->ReportName = "";
String ReportsPath = SuP.ReportsPath;
CrpeADO->ReportName = ReportsPath + "\\" + ReportName + ".rpt";
//Recover the report from the database
if (!RecoverReportFile(true, ReportName, ReportsPath))
return;
//Initialize specific report parameters based on call
switch(Report)
{
case 1 :
{
SortField = "{Contacts_ttx.ID}";
ReportTitle = "\"Active Contact Id# Sort\"";
SelectionFormula = "{Contacts_ttx.STATUS_REC} = \"ACTIVE\"";
break;
}
case 2 :
{
SortField = "{Contacts_ttx.ID}";
ReportTitle = "\"All Contact Id# Sort\"";
SelectionFormula = CrEmptyStr;
break;
}
case 3 :
{
SortField = "{Contacts_ttx.NAMELAST}";
ReportTitle = "\"Active Contact Name Sort\"";
SelectionFormula = "{Contacts_ttx.STATUS_REC} = \"ACTIVE\"";
break;
}
case 4 :
{
SortField = "{Contacts_ttx.NAMELAST}";
ReportTitle = "\"All Contact Name Sort\"";
SelectionFormula = CrEmptyStr;
break;
}
}
//Retrieve the formulas
CrpeADO->Formulas->Retrieve();
//Set report headers
CrpeADO->Formulas->Name = "ReportHeader";
CrpeADO->Formulas->Formula->Text = ReportHeader;
CrpeADO->Formulas->Name = "ReportTitle";
CrpeADO->Formulas->Formula->Text = ReportTitle;
//Set selection formula
CrpeADO->Selection->Retrieve();
CrpeADO->Selection->Formula->Text = SelectionFormula;
//Set sort formula
CrpeADO->Formulas->Name = "SortField";
CrpeADO->Formulas->Formula->Text = SortField;
//Set print options
CrpeADO->PrintOptions->Retrieve();
CrpeADO->PrintOptions->Copies = 1;
CrpeADO->WindowParent = PrintForm;
//Run the print job
RunADOReport(CrpeADO->ReportName, true, 0, "NI");
while (!PrintWindowClosed)
Application->ProcessMessages();
}
This runs the report. The datasets and transaction are on a DM. There is special stuff imn jhere to do things unrelated to the basics of running a report. Ignore stuff that looks weird.
//---------------------------------------------------------------------------
void __fastcall TReportDM::RunADOReport(String sReportName, bool LoadSQL, int Control, String InvDate)
{
if (LoadSQL)
{
String sReportSQL = sReportName;
sReportSQL.Delete(sReportSQL.Pos(".rpt"), 5);
sReportSQL = sReportSQL + ".sql";
qrADOReports->SQL->Clear();
qrADOReports->SQL->LoadFromFile(sReportSQL);
if ((Trim(InvDate) != "NI") && (Control >= itDues) && (Control <= itCredit))
{
InvDate = "\'" + InvDate + "\'";
if (Control == itDues)
qrADOReports->SQL->Add(Format("WHERE MEMPAY2.REMIND_LET = %s", ARRAYOFCONST(((String)InvDate))));
else if (Control == itCredit)
qrADOReports->SQL->Add(Format("WHERE MEMPAY2.CRDREM_LET = %s", ARRAYOFCONST(((String)InvDate))));
}
}
PrintWindowClosed = false;
Screen->Cursor = crHourGlass;
TSCRObject *adoRecordSet = new TSCRObject();
ICRDataSource *tInterface;
String eMessage;
trADOReports->StartTransaction();
qrADOReports->Open();
if (CrpeADO->HasSavedData)
CrpeADO->DiscardSavedData = true;
try
{
adoRecordSet->SetDataSet(qrADOReports);
tInterface = *adoRecordSet;
CrpeADO->Subreports->Retrieve();
CrpeADO->Subreports[0]; //pointing to main report
CrpeADO->Tables->Retrieve();
CrpeADO->Tables[0].DataPointer = &tInterface;
CrpeADO->Subreports[0]; //point to Main Report before Executing
CrpeADO->Execute();
}
catch(...)
{
Screen->Cursor = crDefault;
adoRecordSet->FreeInstance();
if (CrpeADO->LastErrorString != "")
{
eMessage = ("There was an error running the report. The "
"report code was " + IntToStr(CrpeADO->LastErrorNumber));
eMessage = eMessage + " and the message was " + CrpeADO->LastErrorString;
ShowMessage(eMessage);
if (PrintForm != NULL)
{
delete PrintForm;
PrintForm = NULL;
}
return;
}
else
throw;
}
Screen->Cursor = crDefault;
while (!PrintWindowClosed)
Application->ProcessMessages();
adoRecordSet->FreeInstance();
trADOReports->Commit();
if (PrintForm != NULL)
{
delete PrintForm;
PrintForm = NULL;
}
}
, I have been pointed towards TTX files and TCrpeDS as a soultion for this task (Crystal V.9 Developer Ed is what im using) ,.. Does anyone know where the TCrpeDS exists in the VCL...? need to find instructions on how to install and access this object...