It may be that you are developing a set of templates using data from a database, but you are not the database administrator, and so can’t create your own views or stored procedures in the database. Using the feature described here, you could at least address the database with a fairly complex SQL query.
This example assumes that you are familiar with configuring and using data sources. If this is not the case, please have a look at Configure a Data Source for a Database. Also, the example uses the Microsoft Adventureworks sample database, which you can download from Download the AdventureWorks Database.
The configuration follows the normal path until the step where you would provide the name of a database table for the data source. You will notice that there is a fairly large area into which you could enter a SELECT instead of choosing a table (or view or stored procedure). The example is a SELECT statement lifted from one of the AdventureWorks stored procedures, which you can copy and paste from here, if you would like to try it yourself:
SELECT DealerPrice = plph.[ListPrice] * @DealerDiscount
FROM [Production].[Product] p
INNER JOIN [Production].[ProductListPriceHistory] plph
ON p.[ProductID] = plph.[ProductID]
AND p.[ProductID] = @ProductID
AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, ‘99991231’, 112))
Notice that there are three SQL Server scalar variables defined in the SELECT statement, viz. @DealerDiscount, @ProductID, and @OrderDate. The datasource configuration wizard regards these as making up the ID of the data source, and your template will need to provide actual values for these at the time that the template is being run. Here is an example of a template which uses the LinkToDataSource command to access the DealerPrice using that datasource duing the interview phase of the assembly:
«
DealerDiscount»
«ProductID»
«FormatDate(OrderDate,'yyyy-MM-dd')»
«Button(Button)»
«LinkToDataSource(Button,DealerPrice,NoRefresh,id=<DealerDiscount>|<ProductID>|<OrderDate>)»
«DealerPrice»
If you run that template, and enter 0.6 for DealerDiscount, 707 for the ProductID and 2001-07-02 as the OrderDate, then, when you press the button, it should display the value 20.18652 as the DealerPrice.
Now, replace the LinkToDataSource and DealerPrice fillpoints with these:
«
LinkToDataSource(Button,DealerPrice,NoRefresh,range=@DealerDiscount=<DealerDiscount> and @ProductID=<ProductID> and @OrderDate=<OrderDate>)»
«Production.Product/DealerPrice»
Run the template again. Notice that when the ID variable values are supplied as “id=” in the LinkToDataSource, then a data element is returned into the root of the data set, but when they are supplied as a “range=”, then the same element is returned, but as part of a repeater. If you were the author of the SELECT statement, you would know whether the result of the SELECT was a single row or not, and so you would know which of the id= or range= is applicable.
