Creating a New Data Source for the Chart

For the external database linking example, we want to link to a different SQL Database A collection of data fields and related tables, storing information that facilitates access and retrieval.. The following procedure demonstrates how to create a new Data Source for the external database.

To Create the New Data Source

1.

Click New Data Source link at the bottom of the Dashboards Objects panel.

2.

On the New Data Source window, select Manually typed for the Type of data and Start with blank data source for the Creation Method.

3.

Click Next to view the General Properties page.

4.

Enter Accounting Demo for the Name, then click Next to view the Runtime Parameter page.

5.

Click the Add button to view the Add/Edit Runtime Parameter dialog box.

NOTE: The Runtime Parameter expression will enable the product line to be selected by the end user when viewing the Dashboard.

6.

Enter ProductLineParam for the Name.

7.

Select the Custom option and perform the following:

○ Open the Logical drop-down list and select: AND

○ Enter the following Expression: ProductLine = '<<VALUE>>'

8.

Click OK and then Next to view the Query dialog box.

9.

Enter the SQL query statement in the upper text box:

○ Start with a basic SQL query that returns a list of product and their corresponding quantities.

Select Product, Quantity from Accounting..Inventory order by product

Where “Accounting” is the name of the external SQL database and “Inventory” is the TableName.

○ Then we add the "Where" clause that will enable the runtime parameter to be used:

Where 1=1 <<ProductLineParam>>

○ Finally note that additional parameters can be used, for example to enable multiple drop-down lists in a single dashboard. For example:

Where 1=1 <<ProductLineParam>> <<Parameter2>> <<Parameter3>>

Each of these additional parameters (Parameter2, Parameter3) must be set up in the Data Source, on the Runtime Parameters tab.

○ The final SQL query should resemble the one below:

Select Product, Quantity from Accounting..Inventory Where 1=1 <<ProductLineParam>> <<Parameter2>> <<Parameter3>> order by product

10.

To validate the code, click the Check button.

11.

To preview the query output, click the Preview button.

12.

Click Finish.