How do I load and print a template generated in report explorer?

rbuser

I've worked through many demos and forums trying to work this one out.

When the button event fires, the LoadFromStream generates "invalid
property value" exceptions for OnAssignPreviewSettings and
OnPreviewFormClose. If I ignore the errors, the report will attempt to
present/print but prompt me for a username and password for a
"DefaultADOConnection".

I don't understand why it's looking for those events, or how to tell the
report which database to use.

If it means anything, the report was created through Report Explorer on
Computer A (which hosts the database) and is being run on Computer B.


I have included the code below. Any/All help is greatly appreciated.


type
TForm1 = class(TForm)
Button3: TButton;
Memo1: TMemo;
procedure Button3Click(Sender: TObject);
procedure FormCreate(Sender: TObject);

private
{ Private declarations }
public
myADOConnection : TADOConnection;
myADOQuery : TADOQuery;
myDataSource : TDataSource;
myTppDBPipeline : TppDBPipeline;
myTppReport : TppReport;
{ Public declarations }
end;

var
Form1: TForm1;

implementation

procedure TForm1.FormCreate(Sender: TObject);
begin
myADOConnection := TADOConnection.Create(self);
myADOConnection.ConnectionString :=
'Provider=SQLOLEDB.1;Password=myPassword;Persist Security Info=True;' +
'User ID=myUser;Initial Catalog=myCatalog;Data Source=mySystem;';
myADOConnection.LoginPrompt := False;
myADOConnection.Connected := True;

myADOQuery := TADOQuery.Create(self);
myADOQuery.Connection := myADOConnection;
myADOQuery.SQL.Add('SELECT ItemName, Template FROM tblReports;');
myADOQuery.Active := True;

myDataSource := TDataSource.Create(self);
myDataSource.DataSet := myADOQuery;

myTppDBPipeline := TppDBPipeline.Create(self);
myTppDBPipeline.DataSource := myDataSource;

myTppReport := TppReport.Create(self);
end;


procedure TForm1.Button3Click(Sender: TObject);
var
lBinaryStream: TMemoryStream;
lTextStream: TMemoryStream;
ppReport3: TppReport;
begin
if not myTppDBPipeline.Locate('ItemName', 'Freds Report', []) then
raise Exception.Create('Unable to locate report');

lBinaryStream := TMemoryStream.Create;
lTextStream := TMemoryStream.Create;
ppReport3 := TppReport.Create(self);

try
myTppDBPipeline.GetFieldAsStream('Template', lBinaryStream);
lBinaryStream.Position := 0;
ObjectBinaryToText(lBinaryStream, lTextStream);
lTextStream.Position := 0;
try
ppReport3.Template.LoadFromStream(lBinaryStream);
memo1.Lines.LoadFromStream(lTextStream);
except
ShowMessage('Not Working');
end;
ppReport3.Print;
finally
lBinaryStream.Free;
lTextStream.Free;
end;
end;



--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Soral,

This error indicates that ReportBuilder cannot find a database object with
the same name as the one used to create the report. It therefore tries to
create its own default connection. Be sure the TADOConnection created has
the same name used when creating the original reports.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I'm unsure whether you mean the name of the actual object (in my sample
code, that would be "myADOConnection") or the connection details
("myCatalogue", "mySystem").

Fortunately the connection details for the data are the same as that of
the template. I renamed "myADOConnection" and ran the application again,
but I get the same errors. I've even tried using this snippet of code
(which I believe I found elsewhere on this forum) to rename the connection:

var
lDataModule : TdaDataModule;
lDataView : TdaDataView;
X : integer;
aSQL: TdaSQL;
begin
aSQL := nil;
lDataModule := daGetDataModule(myTppReport);
if (lDataModule <> nil) then
begin
for X := 0 to lDataModule.DataViewCount-1 do
begin
lDataView := lDataModule.DataViews[X];
if (lDataView <> nil) and (lDataView is TdaQueryDataView)
then begin
aSQL := TdaQueryDataView(lDataView).SQL;
aSQL.DatabaseName := myADOConnection.DefaultDatabase;
aSQL.DatabaseType := dtMSSQLServer;
aSQL.SQLType := sqSQL1
end;
end;
end;
end;





--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Soral,

When (what event) are you calling the code below? I assume that if you try
to load and preview a report that does not connect to your database, this
works successfully?

The name of the connection object is saved in the template when the report
is saved. Try saving one of your templates to file in ASCII format and
looking at the source to see the name of the ADOConnection. For instance,
the SQL Server ADO explorer demo located in the \RBuilder\Demos\ 4. EndUser
Databases\... directory is named "euDatabase".

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I embeded the routine into the click event as follows:


procedure TForm1.Button3Click(Sender: TObject);
var
lBinaryStream: TMemoryStream;
lTextStream: TMemoryStream;
ppReport3: TppReport;
lDataModule : TdaDataModule;
lDataView : TdaDataView;
X : integer;
aSQL: TdaSQL;
begin
if not myTppDBPipeline.Locate('ItemName', 'BlankReportDoesNothing',
[]) then
raise Exception.Create('Unable to locate report');

lBinaryStream := TMemoryStream.Create;
lTextStream := TMemoryStream.Create;

ppReport3 := TppReport.Create(self);

try
myTppDBPipeline.GetFieldAsStream('Template', lBinaryStream);
lBinaryStream.Position := 0;
ObjectBinaryToText(lBinaryStream, lTextStream);
lTextStream.Position := 0;
try
ppReport3.Template.LoadFromStream(lBinaryStream);
memo1.Lines.LoadFromStream(lTextStream);

aSQL := nil;
lDataModule := daGetDataModule(ppReport3);
if (lDataModule <> nil) then
begin
for X := 0 to lDataModule.DataViewCount-1 do
begin
lDataView := lDataModule.DataViews[X];
if (lDataView <> nil) and (lDataView is TdaQueryDataView)
then begin
aSQL := TdaQueryDataView(lDataView).SQL;
aSQL.DatabaseName := Form1.adoReportWriter.DefaultDatabase;
aSQL.DatabaseType := dtMSSQLServer;
aSQL.SQLType := sqSQL1
end;
end;
end;

except
ShowMessage('Not Working');
end;
ppReport3.Print;

finally
lBinaryStream.Free;
lTextStream.Free;
end;
end;


As you can see, I also changed which report I was loading to be a blank
report with the text "This is a blank report" in the detail band. The
exceptions noted in my original post still trigger but I am not prompted
for the database to connect to.


The relevant portion of the template is
object daSQL1: TdaSQL
CollationType = ctASCII
DatabaseName = 'adoReportWriter'
DatabaseType = dtMSSQLServer
DataPipelineName = 'Job'
IsCaseSensitive = True
LinkColor = clMaroon
MaxSQLFieldAliasLength = 25
SQLText.Strings = ('huge sql query removed for clarity')
SQLType = sqSQL1

But as you can see from my code snippet above I have renamed my
TADOConnection object to adoReportWriter so the connection should be made.


Thankyou for your help with this issue.




--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Soral,

There should be no need to access the SQL object. This information is kept
in the Template definition.

I would suggest starting over with a simpler approach...

1. Save one of your explorer templates to file.
2. Create a new delphi app with a report, button, and an ADOConnection
component. Add daADO to the uses clause.
3. Rename the ADOConnection object to the same name as the Connection
object in the explorer app and connect it to the same DB.
4. In the OnClick of the button, load the template into the report and
print the report.
5. Run the app, the report should access the db and print successfully.

In my testing I was able to follow the above steps and get a report to print
successfully. Once you have this working, getting your app to work in a
similar manner should not be very difficult.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I bring news, of a sort.

On the remote machine (storing the reports and the database) I opened
report explorer, opened the report in design mode, and saved the report to
a file. I took the copy to my local machine where I had followed your
steps 1, 2 and 3. I opened report designer by double clicking the report
on my form in delphi, and loaded the report from the file.

Here's where it gets interesting. Design explorer generated exactly the
same exceptions that I got when I loaded the report. I saved the report to
a new filename. Now when I reopen the report in designer there are NO
errors. It's also stored in binary (I assume, since it's not text).


I'm assuming that, since there is no "connection" in the template, you
were refering to the DatabaseName in step 3.

I went ahead and finished steps 4 and 5. I got the exceptions when I
loaded the template (expected) and a brand new error which I fixed by
putting a pipeline (named as in the template) onto the form.


My first concern is that the report generates exceptions.

My second is that the requirement to have a named pipeline makes it a
little tricky to load generic reports from the database.





--- posted by geoForum on http://delphi.newswhat.com

rbuser

A quick followup.

If the TADOConnection is on the form, it connects to the database.

If the TADOConnection is a public variable, it prompts for the connection
details (and gets the database name wrong).

I'm still working on those odd exceptions.



--- posted by geoForum on http://delphi.newswhat.com

nicocizik

> If the TADOConnection is a public variable, it prompts for the connection

I am not seeing this behavior. Take a look at the
TdaSession.GetDatabaseForName routine inside the daDB.pas file if you would
like to see how ReportBuilder finds the correct connection object. If the
TADOConnection is owned by the form, ReportBuilder will be able to find it.
As a test, the following code worked on my machine...

Note: The MyTestReport.rtm was created using the Explorer demo and saved
to file.

procedure TForm1.FormCreate(Sender: TObject);
var
myADOConnection : TADOConnection;
begin

myADOConnection := TADOConnection.Create(Self);
myADOConnection.ConnectionString := '...';
myAdoConnection.Name := 'euDatabase';

end;

procedure TForm1.Button1Click(Sender: TObject);
begin
ppReport1.Template.FileName := 'C:\Users\ncizik\Desktop\MyTestReport.rtm';
ppReport1.Template.LoadFromFile;

ppReport1.Print;
end;

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com