Skip to Content

Applies to:

SAP Business Objects Enterprise.

Summary

This article describes about Database Expert in Crystal Reports.

Author(s):    Renju.M.V

Company:    Cognizant Technology Solutions

Created on: 15 May 2013

Author Bio

The author has been working on Business Objects projects for the last four years.

Scenario

Crystal Reports is a business intelligence application used to design and generate reports from a wide range of data sources. Database Expert dialogue box in crystal reports allows the user to select data sources and database fields/ tables in a simple and easier way. In the Database Expert, users can select and link tables from a wide variety of data sources, including Microsoft Excel spreadsheets, SQL, Oracle databases, Business Objects Enterprise business views, and local file system information

For example, if the user wants to display the product details from product table residing in SQL database, then an ODBC connection needs to be established thru Database Expert. When you connect to an SQL database, Crystal Reports acts as an SQL client application, connecting to your SQL server through your network. When you design a report that accesses SQL data, Crystal Reports builds an SQL query. This query can be seen by choosing Show SQL Query from the Crystal Reports menu. This SQL query is a representation of the SQL statement that Crystal Reports sends to the SQL server.

By interpreting as much as possible from the report design into an SQL query, Crystal Reports can off-load much of the report processing onto the server machine. Rather than having to sift through an entire database to find the data you requested, Crystal Reports lets the server do the sifting and gets back a much smaller set of data, thus reducing the time and resources your workstation must use in order to finish the report.

Database Expert

•       The Database Expert provides a tree view of all data sources you can use with Crystal Reports

•       Data tab and Link Tab of Database Expert helps the user to select the database fields and also to link them together.

•       In the Database Expert, you can select from the following as a data source for your report:

Ø  A currently connected data source

Ø  An SQL command that has been saved to the Business Objects Enterprise Repository

Ø  A recently accessed data source

Ø  An existing data source

Supported Data Sources:

Ø  Databases such as PostgreSQL, Sybase, IBM DB2, Ingres, Microsoft Access, Microsoft   SQL Server, MySQL, Interbase and Oracle

Ø  Btrieve

Ø  Spreadsheets such as Microsoft Excel

Ø  Text files

Ø  HTML XML files

Ø  Groupware applications as Lotus Notes, Microsoft Exchange and Novell GroupWise

Ø  SAP: BW, Info Sets, Tables, and Business Objects Universes

Ø  Any other data source accessible through a web service, ODBC, JDBC or OLAP.

Data Tab

The Data tab of the database expert shows a tree view of possible data sources you can select when creating a new report. Following folders are available:

Current Connections

This folder shows a list of data sources you are currently connected to.

Favorites

This folder shows a list of data sources you commonly use and have maintained in your Favorites list.

History

This folder shows a list of data sources you have used recently. The last five data sources used are displayed.

Create New Connection

This folder shows subfolders for various data sources you can connect to.

Repository

This folder shows you the contents of your repository through the Business Objects Enterprise Explorer. Click Make New Connection to open the Business Objects Enterprise Explorer; from this dialog box, you can select an existing SQL command or Business View

Database Expert.png

The Create New Connection folder contains subfolders for the following data sources.

·         Access

·         Database Files

·         ODBC(RDO)

·         OLAP

·         OLEDB

For example, if the user opts for ODBC connection then either he can choose a data source from the list of available data sources or he can open a file dsn from the browse button. Then the user will be prompted to enter server name, database name, user name and password to log on to the chosen data source.

ODBC.jpg

/wp-content/uploads/2013/05/odbc2_217315.jpg

After finishing the database connection, the user can select the database tables from the connected data source. Multiple tables can be selected together.

In the below sample,

Data source : ODBC

Server Name : 10.248.25.30

Database Name : DbRel02t

Selected Table : PSMRptPODetail

/wp-content/uploads/2013/05/dbe_217320.jpg

Shortcut Menu

When you right click any item in the Available Data Sources list of the Database Expert, a shortcut menu appears with the following options.

Add to Report

Use this option to add a table or stored procedure to your report. This option is also available by clicking the > arrow on the Database Expert.

Add to Favourites

Use this option to add a selected data source to the Favourites folder.

Remove from Report

Use this option to remove a table or stored procedure from your report. This option is also available by clicking the < arrow on the Database Expert.

Properties

Use this option to obtain detailed information on the selected item.

Rename the Favourite

Use this option to rename a data source in the Favourite Folder.

Delete Favourite

Use this option to delete a data source in the Favourite Folder.

Remove from Repository

Use this option to delete an existing SQL command from the Business Objects Enterprise Repository.

Rename Repository Object

Use this option to rename an existing SQL command in the Business Objects Enterprise Repository.

Options

Us this option to set the global options that appears on the Database tab of the Options dialogue box.

Refresh

Use this option to refresh the list of available data sources in the Database Expert.

Set Data Source Location

·         The user can also set the data source location using the below mentioned “Set Datasource Location” dialogue box.

·         This option helps the user to change the location of the data source by selecting the current database/ table and choosing the database/ table to replace with.

·         Click on “update” button to change the data source location.

In the below sample, the user has selected table from Sapolo7 server to set the data source location.

Server Name : Sapolo7

Database Name : DbRel02t

Selected Table : PSMRptPODetail

PSMRptPODetail table has been replaced with the same table from another server.

Server Name : 10.248.25.30

Database Name : DbRel02t

Selected Table : PSMRptPODetail

/wp-content/uploads/2013/05/rdo_217381.jpg

/wp-content/uploads/2013/05/rdo2_217397.jpg

Link Tab

Database fields are linked together. So records from one database table may match records from another database table. The majority of reports will probably require data from two or more tables. In this case linking is necessary. The linking of the database tables can be implemented using the Link Tab of the database Expert.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

    1. Renju MV Post author

      I have created this document using a standard SCN document template which I received long back.

      We can remove this copyright section if it is not required.

      – Renju

      (0) 
  1. Ludek Uher

    Renju, please do comment on the comment from Abhilash as I believe we can remove the Copyright info(?).

    – Ludek

    SCN Moderator

    (0) 
  2. Mark Richardson

    What VERSION of Crystal Reports were you using when you created those screenshots…?

    Logo and Menu items look like a very old (*possibly un-supported) version.

    This data-connectivity interface is somewhat different in CR-2011 / 2013 – and in the Crystal Reports for Enterprise product.

    (0) 
    1. Renju MV Post author

      Hi Mark,

      I agree, it is an old version. I was using Crystal 9 when I created the screenshots.

      – Renju

      (0) 

Leave a Reply