writing custom sql

rbuser

Hello all,

We want to create a custom sql statement on the fly and launch the report
designer with this statement supplying the data for the report. The user
should then be able to create a report and save it as a template as a blob
field in our Advantage database. When the user returns and launches the
report from report explorer, we should not need to recreate the sql
statement again, I think that this should be saved with the template.

I think that this is possible using the TdaSQLBuilder class, but I have been
unable to find a good code example.

When I try the code below, I get an access violation on the first line.

aSql := TdaSQLBuilder.Create( ppReport1.DataPipeline.SQL );
aSql.Clear;
aSql.SelectFields.Add( 'aTable', 'DatePosted' );
aSql.SelectFields.Add( 'aTable', 'Category' );
aSql.SelectFields.Add( 'aTable', 'SubCategory' );


If I try this:

aSql := TdaSQLBuilder.Create( ppReport1 );

I get a message stating the aReport has no pipeline.

Is what we are attempling possible?

Thank you for your time.

Andy Roberts.

nicocizik

Hi Andy,

In order for your report to function correctly, it will need to be connected
to some data at the start. Ideally in your case, you would want to create a
generic query in DADE that selects all the records that may be used in your
custom query. Then connect the report to that pipeline and use the
SQLBuilder to alter it when the report is printed.

--
Regards,

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

rbuser

Thank you for your reply.

I have been able to open the designer and create a SQL statement through
DADE, connect some of the fields to a template and save it. When re-opening
the template and printing it directly from Report Explorer, the fields print
as expected.

Is there no way to create an SQL statement in code and connecting that
pipeline to a report template before opening it in the designer? I have
been able to create a pipeline and connect a datasource to it before opening
it in the desinger. The only issue is that we need to create that pipline
again before printing the report.

Thank you,

Andy

nicocizik

Hi Andy,

Below is an example of createing everything in code and using the SQL
builder.

http://www.digital-metaphors.com/tips/CreateDataModCustOrderLinkedDataViews.zip


--
Regards,

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

rbuser

Hello Nick,

This is excellent. I modified the example to work with Advantage and it
works great. There is one thing that I am trying to figure out. If I launch
the designer in the way you have it in the example, I can only save the
report as a file to disk. The options to save to the database are disabled.
If I launch the designer from Report Explorer, I have the option to save the
template as a blob to the database.

Thanks a lot,

Andy

nicocizik

Hi Andy,

In order (for the example) to save your template to a database, you will
need to alter the Report.Template settings. First you will need to set up
the DatabaseSettings, then change the SaveTo property to stDatabase. If you
are using the explorer, this is done for your automatically.

--
Regards,

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

rbuser

Thank you Nico. I will try this, this evening.

rbuser

Hello Nico,

I have tried what you suggested. I am using the Demo provide in the
Demos\4. EndUser Databases\Advantage folder. If I lauch the Report Explorer
I am able to save the template to the database, ( as you say, the settings
are provided for me). If I use the code below I run into issues.

ppReport1.SaveAsTemplate := True;
ppReport1.Template.DatabaseSettings.Name := 'New Report';
ppReport1.Template.DatabaseSettings.NameField := 'Name';
ppReport1.Template.DatabaseSettings.TemplateField := 'Template';
//ppReport1.Template.DatabaseSettings.DataPipeline := plItem;
ppReport1.Template.SaveTo := stDatabase;
ppDesigner1.ShowModal;
//ppReportExplorer1.Designer.ShowModal;

I have tried to launch from both the Explorer, or Designer, there is no
difference. In both cases, the Save and Save As options are not enabled.
If I uncomment the line to assign the DataPipeline, I get an error when
selecting the "File" option from the designer. The error is "Cannot store a
template to a field of this type". Again, if I launch the Report Explorer,
I am able to save templates to the database. All settings for "Storage"
appear to be the same in both cases. I have searched you website and it
would appear that I am doing this correctly, but obviously not.

Thank you for you time,

Andy

nicocizik

Hi Andy,

Are you loading a template before all of this?

I'm a bit unsure what exactly your environment looks like. I created a very
simple example of what can be done. You can download it from the link
below. Please let me know how this is different from your application.

http://www.digital-metaphors.com/tips/SaveToDBTest.zip

--
Regards,

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

rbuser

Hello Nico,

Thank you for the example. I am getting the same error with your example
that I get with mine. When the designer is launched, if I select the "File"
menu in order to save, I get the error. "Cannot store a template to a field
of this type". I am using Report Builder Pro v10.5 with Delphi 7, something
environmental?

Thanks,

Andy

nicocizik

Hi Andy,

I have not seen this behavior before. Are you able to successfully run the
main end-user demo located in the \RBuilder\Demos\3. EndUser\1. Report
Explorer\ directory? This demo uses the same table to store the template as
my example uses.

--
Regards,

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