Use a SQL Server Data Source to include Customer information on a template

This recipe will use the AdventureWorks data base as an example.  This is one of the Microsoft sample databases for SQL Server.  It can be downloaded from Download the AdventureWorks Database .

XpressDox can retrieve data from just about any data base.  Most data bases can be accessed via ODBC, but in the case of SQL Server, the XpressDox New Data Source wizard creates an XpressDox Data Source which connects to the SQL Server database using the .NET Framework Data Provider (it doesn’t matter if you don’t know what this means, but amongst other things it means that it is more efficient than ODBC).

The most complicated part of setting up a SQL Server Data Source for XpressDox is the connection string.  Assuming that you know how to access your SQL Server database (or you have your Database Administrator looking over your shoulder) (or you are the DBA), this should not be a problem.

Configure the Data Source

1. Open the XpressDox configuration form – in the Manage Settings group in the Word Ribbon.
2. In the drop-down box at the top of the form, choose the folder for which you want this Data Source configured.
3. Click on the Data Sources tab, and then click the “New” button on the Data Sources toolbar (the button on the left with a green + sign on it).
4. Choose the SQL Server Database radio button and press Next.
   You are now in the tricky part – the connection string.
5. The wizard has a link to the web site www.connectionstrings.com to help with this, but actually your DBA should have this information readily to hand.
6. Once the connection string has been entered, press Next.
7. You now need to choose the table or view in the database that this Data Source is intended for.  Choose this from the drop-down box.  (If the drop-down shows  list of tables and views, then you know you have configured the connection string correctly).  For this recipe, choose Sales.vIndividualCustomer.  It is a SQL Server View, not strictly a table, but for XpressDox Data Source purposes, a view functions as a table.
8. XpressDox will suggest a data element name for the table itself – in this case it will suggest the name of the view, i.e. Sales.vIndividualCustomer. If you were to ever refer to a collection of these data elements (i.e. when the data elements are repeated) you would probably want to say something like «ForEach(Customer)», not «ForEach(Sales.vIndividualCustomer)», and so you now have the chance to indicate this by changing the data element name to Customer.
   Press Next.
9. XpressDox needs to be provided with an “ID” for the table.  This is a column, or group of columns, whose value(s) uniquely identifies a row within the table.  This could be the Primary Key in the database, and it would probably make sense to choose this.  The Data Source wizard takes a stab at suggesting an ID column.  If it can’t find one column defined as unique, then it presents some columns for you do choose from.  But in the case of the Sales.vIndividualCustomer view, the wizard suggests CustomerID as the ID of the Data Source, and this is the one to accept.
   Press Next
10. At this point XpressDox needs to know the primary usage of this Data Source in terms of the number of database rows to be returned in the Data Source data set.  For purposes of this recipe, the first option is appropriate,  i.e. “The user must be able to choose a row from this table”.

    Note that it will still be possible for a template author to select all the rows, or a subset of rows from the table for a specific purpose, but if you choose the option to enable to the user to select a row, then XpressDox will automatically construct a Search dialog which will be presented to the user (you will be able to see what this Search dialog looks like when the wizard is complete and you use the Test Data Source feature).

11. In order to construct this search dialog, XpressDox needs to know a number of things, which are presented to you in the wizard.
12. Firstly you are asked to choose the columns which are presented in the Search Dialog.  The wizard will suggest a few, but in the end you will know best how to present the data so that the user will easily identify the row of the customer they would like to select.  In the AdventureWorks Customer instance, it would make sense to include the LastName column as one of the Columns to Display – do this by double-clicking LastName in the Columns in Table list.
13. Part of the Search dialog is a filter, which enables the user to narrow down the search and the amount of data displayed.  The filter works on one column, and for the AdventureWorks Customer Data Source which we are dealing with, it is probably best to choose the LastName column from the “Filter Column” drop down.
    Press Finish.
14. The Data Source will now be displayed in the main Data Sources configuration form, with the name “New SQL Server database”.  You need to give the Data Source a better name than this – just choose something meaningful like “AdventureWorks Customer”, type it in and press TAB.
15. The Configuration should now be saved.  Then you can use the Test Data Source button on the Data Sources toolbar (the one with the green check mark in it).  XpressDox will present the Search dialog to you.  No data rows will be listed in the Search form, as there are about 18,000 rows in that table.  So you need to use the Filter – type a string which you know appears in the customer’s last name, and press the Search button.  You can then choose a customer, and you will be shown, in a separate window, what the XML dataset for your choice looks like.
16. You will now be given the option of creating a schema for this data source.  It is recommended that you do this, because then you will be able to include that schema into the Command Editor, which will help you to choose the right column names from the data source (although they are at this point known as Data Elements, not columns).  Click Yes, and save the schema with a file name which will help you identify it.  The name suggested is the same name as the Data Source, which is probably a good idea.

Note that the steps for creating any Data Source using this wizard are almost exactly the same whether the underlying data base is SQL Server, MS Access, Excel or any ODBC database.  The only difference will be in the “Identify the database” step.

Using the Data Source in a Template

In the template that is to use this Data Source, use the The Command Editor to include the command:

«ChooseFromDataSource(AdventureWorks Customer,Choose the Customer)»

This will create a control in the interview for the template which will give the user the chance to choose the customer for this template.

If you agreed to the Test Data Source feature creating a schema for you, then open that in the right hand pane of the Command Editor (by pressing <right-click> and choosing Add Data Schema and then navigating to where you saved the schema file).  You will then be able to choose data element names from that Data Source just by double-clicking the name.