WDReportGen

How to export data from database to Microsoft Word

WDReportGen is a powerful and flexible reporting tool that gets data from database and outputs reports in Microsoft ® Word ® document format. WDReportGen accesses database using SQL.

Connecting to databases through ODBC

Open Database Connectivity (ODBC) is a standard protocol for accessing relational databases based around SQL. Because of accessing data through ODBC, WDReportGen can access a wide range of data sources. WDReportGen works with all kinds of data, from simple text files to advanced client-server SQL databases.

The databases and files that WDReportGen supports includes Oracle, Sybase, Informix, IBM DB2, Teradata, Microsoft SQL Server, MySQL, Microsoft Access, Microsoft Visual FoxPro, dBase, Paradox, Microsoft Word and text file.

WDReportGen supports more than one data sources in one report. You can get data from some different databases such as Oracle, IBM DB2 and Microsoft SQL Server, and put them into one report.

Accessing databases using SQL

Structured Query Language (SQL) is the industry standard language for communicating with Relational Database Management Systems. To extract data from database into reports, you need to write SQL. WDReportGen can execute SQL statements, and supports all features of SQL.

SELECT, INSERT, UPDATE, DELETE, INSERT SELECT
Functions
Subqueries
Joins
EXECUTE Stored Procedure
CREATE, DROP, ALTER
Temporary Tables
GRANT, REVOKE

Executing multiple SQL statements

In one report building process, WDReportGen can execute multiple SQL statements. To create one complex report, you can create a temporary table, perform one or more queries on database, insert data into the temporary table, and then get data from the temporary table into your report.

Example

This example is for Microsoft SQL Server, and tested on WDReportGen.

/*********************************************************
Compare with Last Month by Countries
*********************************************************/
/* Create temporary table tmp_country_sales */
CREATE TABLE #tmp_country_sales (
CountryCode INTEGER,
Quantity INTEGER,
Amount NUMERIC(19,4)
);

/* Get the sales amount by countries in the current month */
INSERT INTO #tmp_country_sales (CountryCode, Quantity, Amount)
SELECT o.ShipCountryCode
    , SUM(d.Quantity)
    , Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
    ,OrderDetails d
WHERE o.OrderID = d.OrderID
AND YEAR(o.OrderDate) = YEAR('1996-04-01')
AND MONTH(o.OrderDate) = MONTH('1996-04-01')
GROUP BY o.ShipCountryCode
;

/* Show the sales amount by countries in the current month */
SELECT c.CountryName
    , ISNULL(t.Quantity,0)
    , ISNULL(t.Amount,0)
FROM Countries c LEFT JOIN #tmp_country_sales t
ON c.CountryCode = t.CountryCode
ORDER BY c.CountryName
;

/* Delete from table tmp_country_sales */
DELETE FROM #tmp_country_sales;

/* Get the sales amount by countries in the last month */
INSERT INTO #tmp_country_sales (CountryCode, Quantity, Amount)
SELECT o.ShipCountryCode
    , SUM(d.Quantity)
    , Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
    ,OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderDate >= DateAdd(m,-1,'1996-04-01')
AND o.OrderDate < '1996-04-01'
GROUP BY o.ShipCountryCode
;

/* Show the sales amount by countries in the last month */
SELECT ISNULL(t.Quantity,0)
    , ISNULL(t.Amount,0)
FROM Countries c LEFT JOIN #tmp_country_sales t
ON c.CountryCode = t.CountryCode
ORDER BY c.CountryName
;