Home Server
New Blog Posts: Merging Reports - Part 1 and Part 2

Reports in a Database using ADO Connection

edited November 2006 in Server
Hi,

as above, seems to be a problem.

I've tried it using a BDE connection with no problem, but when I use ADO components I get an exception
"EReportServiceProviderError", "No report found in catalog: Report Templates\Orders" where "Report Templates" is the VolumeName and
"Orders" is the ReportName.


Also, I would like to allow users to modify standard reports and save them using their own Report Names. Is there any method of
identifying a "Class Name" of a Report stored in a database, therefore allowing SQL to be created externally.

TIA.

Comments

  • edited November 2006

    - the first step is to build and test an end-user application that uses the
    explorer database with ADO . Examples of this are installed to
    RBuilder\Demos\EndUser Databases. There are examples for MS Access and MS
    SQL Server. A ReadMe.doc is included with each examples and sample SQL
    scripts for creating the supporting rbItem and rbFolder tables.

    - the second step is to build some reports using the ADO DADE plug-in (in
    other words set the Designer.DataSettings to use the ADOConnection that you
    have defined).

    At this point you will have a working end-user application that enables both
    you and your end-users to define folders and reports. And to use the Query
    Tools to build queries against the database.

    - the third step is to build the server application. Examples of using ADO
    are installed to RBServer\Demos\Servers\Explorer Databases\MS Access and
    RBServer\Demos\Servers\Explorer Databases\MS SQL Server. A good quick way to
    build the report explorer volume is to copy/paste the ADOConnection,
    DataSet, DataSource, DataPipeline components from the end-user app to a new
    datamodule that is used to build the report explorer volume. Add the report
    explorer volume and configure it.

    - to enable your end-user to create reports, they will use the end-user
    application that you built in the first two steps. To simplify the data
    access task that they perform, there are several options:

    1. use the DataDictionary to define what tables and fields they can use,
    to provide user friendly table and field aliases and to pre-define
    join/linking conditions
    2. define logical groups of queries of save them to the report explorer
    database by using the File | Export option on the data workspace. Then the
    end-user can use File | Import to import the datamodules.
    3. create custom dataview templates. For an example, see
    RBuilder\Demos\EndUser\Custom DataViews. Custom DataView templates appear in
    the File | New.. dialog and contain predefined groups of queries.







    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2006
    Nard,

    thanks for the reply.

    Since you haven't replied saying, "yes, this is a known problem", I will work through this lot to be sure I haven't missed anything,
    though oddly the application works with BDE connectivity and not with ADO.

  • edited November 2006

    What database engine are you using?

    ReportBuilder with ADO work great for connecting to MS Access and MS SQL
    Server. This combination is in wide use by thousands of customers.

    I would not use ADO to connect to Oracle. Instead I recommend using Direct
    Oracle Access (DOA) which is available from Allround Automations
    http://www.allroundautomations.nl/doa.html

    Check out the RBuilder\Demos\EndUser Databases\ examples. Each of those
    examples has been tested.


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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2006
    D2005, RB 10.04, ADO 2.8, WinXP Sp2, connecting to Microsoft SQL Server 2000.

    It'll be a day or two before I can work through all this, but given your reply I'm guessing I've done something wrong ...


  • edited November 2006
    Ok, I found the problem.

    I'm requesting reports using the 'ClientReport' component, assigning the 'Template' name and 'ReportName' properties directly.

    When assigning the 'ReportName' property you need to pad out spaces to the full length of the r_name field length.

    So, "My Test Report" needs to be requested as "My Test Report ".

    It doesn't occur with BDE connectivity, and as ADO connections return string values padded with spaces, somewhere in the code I
    guess there's a "trim(r_name)" missing.




  • edited November 2006

    Check the configuration of the ADOConnection. Acccess the connection string
    dialog and select the Provider tab. Check that the provider is 'Microsoft
    OLE DB Provider for SQL Server'

    In my testing here with SQL Server and ADO, I do not encounter any issues
    such as you are describing.


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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2006
    Nard,

    yes, OLE DB Provider for SQL Server.

    A little searching on the net shows that it's a well known problem, with no configuration type workaround.

    Redefining the table to use VARCHAR instead of CHAR for the r_name field means you don't have to pad out the report name in the
    ClientReport request. I had declared the field as CHAR as per the Developer's Guide.


  • edited November 2006

    - that explains it. You need to use the SQL script installed to
    RBuilder\Demos\EndUser Databases\SQL Server\CreateEndUserTables.sql. (The
    SQL script does indeed use varchar.)

    - Perhaps I should have emphasized that a bit more. In my initial response
    to this thread, in the very first paragraph, I mentioned the example and the
    sql script...

    "-the first step is to build and test an end-user application that uses the
    explorer database with ADO . Examples of this are installed to
    RBuilder\Demos\EndUser Databases. There are examples for MS Access and MS
    SQL Server. A ReadMe.doc is included with each examples and sample SQL
    scripts for creating the supporting rbItem and rbFolder tables."


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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.