End User Solution with Query Wizard

rbuser

Hi all,

I would like to implement an 'End User Solution' with report builder
Professional 10.4 into our Delphi 2005/Sql Server 2000/ADO application.
I'd like the idea of using the Query Wizard to allow end user building
SQL query for their own designed report as demonstrated in report
builder examples.

The following shows my 'Data Settings' setup in the Data tab:

Session Type= ADOSession
Database Name= ADOConnection1
Database Type=MSSQLServer

With this setting Report Builder populates user tables into 'Available
Tables' from the database configured in ADOConnection1.

1) Our application uses 3 databases, how can I make the 'Available
Tables' populated with all tables from 3 databases?


Thank you in advance for your reply.

Regards,



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

nardmoseley

- Internally RB calls ADOConnection.GetTableNames to build the list of
available table names. You can modify the DADE plug-in for ADO, it is
contained in daADO.pas, check out the method TdaADOSession.GetTableNames.

- You can set DataDictionary UseTableOwnerName to specify that the data
dictionary use the owner.table syntax.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

>
TdaADOSession.GetTableNames.

Hi Nard,

I modified TdaADOSession.GetTableNames to poppulate 'Available Tables'
in Designner with tables from 3 databases.

However as soon as a table from a different database is moved to
selected area then we have an exception:
TdaMetaData.GetFieldsFromDataSet: Unable to open dataset: MemberNotes.
(MemberNotes is a table from a 2nd database, which is not a default
database name in the designer's data settings)

1) I could not find the unit which contains
TdaMetaData.GetFieldsFromDataSet?? Should we try to 'modify' the code
again in order to switch DatabaseName? I can become very clumsy codes!

2) Am I travelling the right direction in using Designer's Query Wizard
with tables coming from 3 different databases to solve my problem?

3) What is other alternative solution?

Again thank you in advance for your support.

Regards,
Victor



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

nardmoseley

I misunderstood your original question.

Try using the DataDictionary with UseTableOwner set to true. Then the SQL
query that is generated will include owner.tablename type of syntax for the
different schemas.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi Nard,

We are trying to give end-user the ability to design their own reports
in a Delphi application that uses 3 databases (SQL Server 2000).

1) What is a recommended approach in this scenario ? using / or not
using a data dictionary?

2) The 'Available Tables' tab - when Query Wizard is executed - should
be loaded with all user tables from the 3 databases, so that end-user
can start selecting table and specify linking fields...to complete a SQL
query.

3) Please help as I still don't know how RB can handle this problem?

Thank you.

Victor



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

nardmoseley

Do you have one ADOConnection that your application is using? Or do you have
three?

I think that perhaps you are trying to so something that RB was not designed
to do. ReportBuilder is designed to be used with a single database
connection.

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi Nard,

The application has only one ADOConnection.

We use TADOQuery to perform operation on other database with this syntax:
ie: Select * from dbTransact..TransHeader TH
Left inner join dbConfig..MemberMaster MM
ON TH.memberid = MM.memberid

If RB is designed to be used with a single database then RB's end-user
samples paricularly with Query Wizard definitely won't work in our
environment which uses three databases?

Do you have any other suggestion for us if we like to give some basic
stuff to end user to do their own basic reporting with RB? (before we
can combine all databases into one...)

Regards,
Vic.



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

nardmoseley

- I researched this, the syntax you are using is databasename..tablename,
which is an abbreviation for databasename.owner.tablename in which the owner
is dbo (the default owner).

- The RB Query Tools and daQueryDataView, daSQL classes do not currently
support that type of syntax. I researched this quite a bit to determine
whether there was a simple/quick way to add this enhancement. However I was
not successful.

- One alternative might be to create a some views in the default database
that can represent the tables in the other database (not sure if that is
possible, just an idea).

- Another alternative approach I can think of would be to create some custom
dataview classes (See RBuilder\Demos\EndUser\Custom DataViews). Custom
DataViews lack take the adhoc flexibility away, but present pre-defined sets
of data that are simple for the end-users.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi Nard,

Your suggestion has solved our problem :-)

- One alternative might be to create a some views in the default
database that can represent the tables in the other database.

Thank you for your support.

Best regards,
Vic.



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

nardmoseley

Excellent

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com