Article
Get More out of SQL Server's Web Assistant Wizard
Page: 1 2
Tweak the HTML Output Using a Template
For the skilled HTML developer, this method may be preferable to the previous one.
Let's look at the "Format the Web Page" screen again. You'll notice the "No, use the template" option:
If you go for this option, the Web Assistant Wizard will let you navigate to a template that should be predefined by you. This can be done in very easily. The only thing you have to keep in mind is that you'll have to tell the Wizard where to insert the database data. The Wizard will look for three tags:
<%begindetail%><%insert_data_here%><%enddetail%>
This tag marks the beginning of a record. Everything you include after this tag will be repeated for each record.
This tag marks a field value in a record. You'll have to repeat the tag for all fields in your result set.
This tag marks the end of a record.
An example template will clarify this:
<html>
<head><style>h1 {font: bold 15px Verdana} li {font: 12px Verdana}</style></head>
<body>
<h1>Employees</h1>
<ol>
<%begindetail%>
<li><%insert_data_here%>, <%insert_data_here%></li>
<%enddetail%>
</ol>
</body>
</html>
Copy and paste this code into a text file and save it as northwind.tpl. Now, resume the "Format the Web Page" screen by checking "No, use the template", then select the northwind.tpl file you just saved to disk.
Wrap it all up by choosing Next, Next and Finish. If you reload your HTML file in your browser now, you'll see the following results:
You can play around with the template to get the layout results you want. Take into account that adjusting and saving the template is not enough to see the layout change. The underlying HTML file will only change when the data changes.
Tweak the SQL Queries
If you haven't got any SQL query knowledge, the Web Assistant Wizard is very convenient. But if you know your way around SQL, the Wizard can be ten times as powerful.
Let's rewind to Step 4 of my original article, in which we picked the data that SQL Server should publish for you. We opted for the first and most straightforward option: we just pointed SQL Server to the right table and let it do the hard labour for us. But if you want to manipulate your data sources just a little bit more, you'll need to use either stored procedures or Transact-SQL statements.
It would be far beyond the scope of this article to explain how to create Stored Procedures or how the Structured Query Language syntax works. Please refer to David Clark's Stored Procedures on PHP and Microsoft SQL Server to learn more about the matter.
If you choose "Result set(s) of a Stored Procedure I select" here, the Wizard will let you pick a predefined Stored Procedure as your data source.
The Northwind database consists of some example Stored Procedures. Choose "Ten Most Expensive Products", for instance, and follow the screen flow to the end. Your HTML result should look like the following table.
If you opt for "Data from the Transact-SQL statement I select", the Wizard will show you a text area in which you can type your own T-SQL query.
If you enter the following example code in this screen, the Wizard will generate for you a Web Page that contains an overview of the orders each Northwind employee has shipped in the month of August.
SELECT FirstName, LastName, COUNT(Orders.OrderID) AS TotalOrders
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE MONTH(Orders.OrderDate) = 8
GROUP BY FirstName, LastName
Wrapping Up
If you're a skilled HTML and/or SQL programmer, the possibilities to tweak the SQL Server Web Assistant Wizard's result are sky-high. In this article, we studied two of the more advanced options that the Wizard offers.
First, we examined how we could influence the layout of the results page along the way. We could make use of the wizard's built-in options like changing the font characteristics, but, preferably, we'd bring our own pre-defined HTML template into play.
Next, we had a look at how we could manipulate SQL's source data in order to publish more complex information. The Web Assistant Wizard offers you two advanced alternatives to settling on a single table. Either let SQL Server use any predefined Stored Procedure, or enter your own T-SQL queries directly into the Wizard.
With either of these options, you can clearly make the Web Assistant Wizard even more powerful. Good luck!