Data Sources and Datasets
A data source is a connection set up from a reporting tool to a database in order
to get data for a report. This data connection will include information needed to
access an external data source (connection string and credentials). Report data
can be obtained from a variety of external data sources including relational databases,
multidimensional databases, report models, and XML data. The data sources can be
saved in a report which makes it an embedded data source. Also data sources can
be saved at a shared location in a report server, which then becomes a shared data
source that is available to be used by multiple reports.
A dataset contains the data items you want to use from a data source. Using a
query (text or using a query window), the set of fields can be selected to be included
in a report. The dataset becomes shared or embedded, based on the data source selected.
The dataset also includes parameters, filters and other data options.
Contents
When using Report Builder 3.0, there are several ways of working with a data
source and a dataset. The features provided by the tool to reuse data sources also
fall in to this category. See below for brief descriptions on how to create/add
data sources and data sets:
- Adding report parts to a report. These report parts are dependent on the
dataset and therefore the dataset and the data source also get added to the
report along with the report part.
- Using wizards to create table, matrix or charts. These include guidance
for creating data sources and datasets.
- Using a predefined shared dataset from the report server.
- Using a predefined shared data source from the report server. Users can
define his/her own dataset using the data source.
- Create own data sources and datasets. These can be embedded in one report
or saved in report server to make them available for multiple reports.
Following data sources concepts will be used extensively within this guideline,
hence a brief explanation is given below:
An embedded data source is a data connection that is used by a single report.
In Report Builder, you can create only embedded data sources.
An embedded dataset is included in a report or in a report part. When you add
a report part to your report, any dependent datasets are automatically added as
embedded datasets.
A shared data source is saved on the report server and can be used among many
reports. Users with sufficient permissions can create shared data sources using
Report Manager. All report parts are based on shared data sources or report models.
A shared dataset is a dataset that is published on a report server. A shared
dataset can have a query, a filter, and a set of dataset parameters. After you add
a shared dataset to your report, you can create calculated fields, additional filters,
and change dataset properties.
For more information on TechNet on Data Sources and Datasets click
here.
A cube should be available in the MS SQL Server Analysis Services to work on
the following section. We will be connecting to a cube (GL Balance) created based
on Information Source views in the IFS Applications database.
To create the data source:
-
Go to IFS Enterprise Explorer. In Business
Reporting & Analysis/My Reports click on
Report Builder to start Report Builder 3.0
and create a blank report.
-
In the Report Data pane, select
Data Sources, right-click and then click Add
Data Source to open the Data Source Properties
dialog box.
-
Enter GL_Cube as the name of the data source in the Name text
box in General section.
-
Select Use a connection embedded in my report
option.
-
In the Select Connection Type list,
select Microsoft SQL Server Analysis Services.
-
Click Build to build the connection string. Type the host name of
the SQL server in the Server Name text box.
-
In Select or Enter a Database name
list, select the database which contains the cube.
-
Click Test Connection to see if the
connection is created successfully and then click
OK to save the data connection and to exit the
Data Source Properties dialog box.
To create the dataset:
-
Select the newly added GL_Cube data source, in the Report Data
pane, right-click and click Add Dataset to open the Dataset Properties
dialog box.
-
Enter GL_Balance_Dataset as the name of the dataset in the Name
field of the Query section.
-
Select Use a dataset embedded in my report
option.
-
Select GL_Cube for the
Data Source field.
-
Click Query Designer to open the
Query Designer dialog box.
- Select the GL Balance cube from the Cube Selection dialog
box. The screen below shows how to locate the Cube Selection dialog
box.
Figure 1: How to open cube selection dialog
-
Drag and drop items from Meta Data
section to the query area to develop the dataset.
- From Measures:
BALANCE
- From Account:
AccountTypeHy (which has
Company, Account type and
Account), Account Description,
Account Group, Account
Group Description, Account Type Description.
-
From Accounting Period: Accounting Period and Accounting Year
-
From Code_B (Cost Center),
Code_D (Business Unit):
Code <B/D> Code and Description
-
To create a dataset parameter for company, drag and drop
Company Code from
Company node to the filter area and
select operator equal and for filter expression select
900 company. See
the screen below for the completed query designer.
Figure 2: Query Designer window with the dataset from the cube
Note: Using dataset parameters and filters is
better when compared with report parameters and filters if higher
performance is expected when viewing the reports.
Dataset parameters and filters result in higher performance because the
dataset fetched for the report is limited at the dataset itself.
Click
here for
more details on TechNet about parameters.
- Click OK in the Query Designer dialog box as well as
in the Dataset Properties dialog box to add the dataset to the
Report Data pane.
An image of the Report Data pane
with the dataset and data source created and the dataset parameters you added is
shown below.
Figure 3: Data source, dataset and parameter added to the Report Data pane
of Report Builder