
		Forums Knowledge Base OfficeWriter Online


	This documentation is for OfficeWriter v3.5.2 SQL Server 2005 View Docs for Another Version or Platform

Advanced Tutorials > Automating OfficeWriter Reports

Automating OfficeWriter Reports

Note: This tutorial applies to SQL Server 2000.

This tutorial will help you automate your reporting without having to use the Subscription feature of Reporting Services. Subscriptions can allow you to deliver report content in the format of your choice, but sometimes Reporting Services subscriptions may not fulfill your needs. Here are a few cases:

Your data source has a failure that cannot be communicated to Reporting Services.
Suppose you have a data warehouse populated by your operational systems. At night, you have a process that updates the warehouse and builds data marts from which you derive your reports. Your reports are rendered in Excel format and emailed to their recipients. What happens if there is a failure in your warehouse process and the data marts don't get rebuilt with the newest data? If you schedule through Reporting Services, it will run the reports anyway and then email old reports to the recipients.
You want your reports generated to files not accessible by the Report Server.
Or perhaps you want to generate your report files, but you do not want to put them in a shared folder. Reporting Services can't help you with this.
You need external report control
You want to control your report generation from a scheduled Windows Task or from some other application because you may have some other process that needs to act on the report or be triggered by the report.

With OfficeWriter you can find solutions to these kinds of problems. Now, we will give you an example of how to do this.

Before you start, please verify the following:

Reporting Services is installed on a server you can access, working properly, and you have rights to publish and view reports.
OfficeWriter with the Reporting Services renderers is installed on the Reporting Services Server.

For more information on installing and configuring OfficeWriter Reporting Services Integration, read Installation or see your system administrator.

Prerequisites: you must be familiar with C# and the .NET framework to follow and use this example.

Let DTS Do the Driving

Microsoft SQL Server's Data Transformation Services (DTS) is a great tool for moving and processing data. This example will use DTS to address the concerns mentioned above. However, any tool or application that lets you call external processes should work as well.

For our example, we will use a standard data warehouse design as in the picture below.

This design is quite simple. You are taking data from your operational systems (accounting, CRM, etc.), moving it to a warehouse, and then moving it to data marts to get a specific view of parts of the warehouse. Notice there are two processes that work on the data: one between the operational data and the warehouse and another between the warehouse and the data marts. In the next picture, you can see how we will incorporate reporting into the design. Don't fret, the picture is more complicated than actually doing it.

Step 1. Create a Report Definition File

This example will build reports based on report definition files created with OfficeWriter Designer. Consult the Quick Start section to learn how to do this. This demo will work with report definitions created in either Word or Excel. After you have created a report definition, go to Step 2.

Step 2. Create a Controller

A controller is a program that will call Reporting Services and tell it to generate a report based on your report definition file. Create your controller in Visual Studio, using the sample code below. The code calls Reporting Services with the name of the report definition file and the type of report (Word or Excel). It will then take the report created by Reporting Services and write it to the file of your choice. The sample code was taken from a console application project. You will need to add a web reference to the reporting Services web service usually found at http://(your RS Server)/ReportServer/ReportService.asmx. Read the code carefully for other files you need to reference.

When you write your code, test it from the command line before proceeding. You should be able to take any report on your server and render it to the same format from which it was created.

using System;
using System.Web;
using System.Web.Services;
using System.IO;
using SoftArtisans.OfficeWriter.WordWriter;
using DTSRender.localhost; //This will point to your Reporting Services server

//SAWW3NET.dll and SAXW6NET.dll must be included in references. See your documentation
// about locating these .dll's after installation.

namespace DTSRender
{
    class Class1
    {
       /// The main entry point for the application.
       [STAThread]

       static int Main(string[] args)
       {
          //Syntax: DTSRender.exe source report(.rdl) file, output file (doc or xls), mode
          //Mode specifies whether the template was created in Word or Excel using
          //OfficeWriter Designer or another .rdl application and also the output file.
          // Mode: W = Word, X = Excel, (any other character)=HTML
          // Sample: DTSRender /MyFolderonRSServer/MyReport C:\MyExcelFile.xls X
          // (Do not use .rdl file extension for the first parameter, just the report path and name.

          if (args.Length<3)
             return -1;

          //Instantiate Reporting Services Class and set up parameters for Render command
          ReportingService m_Rs = new ReportingService();
          m_Rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
          string historyID = null;
          string deviceInfo = @"<DeviceInfo>< Toolbar>False</Toolbar></DeviceInfo>";
          ParameterValue[] paramsIn = null;
          ParameterValue[] paramsOut = null;
          Warning[] warnings = null;
          string encoding;
          string mimetype;
          string[] streamids;
          DataSourceCredentials[] creds = null;
          string showhidetoggle = null;
          byte[] report = null;

          //determine which format from the command line parameter
          string renderFormat ;
          switch (args[2])
          {
             case "X":
                renderFormat="XLTemplate";
                break;
             case "W":
                renderFormat="WordTemplate";
                break;
             default:
                renderFormat="MHTML";
                break;
          }

          try
          {
             //Change this line to point to your Reporting Services server.
             m_Rs.Url = "http://127.0.0.1/ReportServer/ReportService.asmx";

             //Tell Reporting Services to create the report using the Officewriter
             // Renderers
             report = m_Rs.Render(args[0],
                renderFormat,
                historyID,
                deviceInfo,
                paramsIn,
                creds,
                showhidetoggle,
                out encoding,
                out mimetype,
                out paramsOut,
                out warnings,
                out streamids);

             //--- Write the returned byte[] to a file
             FileStream stream = new FileStream(args[1], FileMode.Create);
             stream.Write(report, 0, report.Length);
             stream.Close();
          }
          catch(Exception ex)
          {
             return -1;
          }
          return 0;
       }
    }
}

Step 3. Calling Your Controller from DTS

Start SQL Server Enterprise Manager.
Navigate to your server to the Data Transformation Services folder.
Right-click Local Packages and select New Package.
From the task icon list on the left side of the screen, select Execute Process Task.
The Win32 Process will be your compiled controller program. For the sample code's parameters (your parameters may vary), remember the first one is the report file url path without the file extension, while the second is the output file, and the third is the file type. The example in the picture below calls DTSRender.exe (our compiled example) with the parameter line "/SoftArtisans/ExcelReports/Pivots C:\MyReports\MyExcelReport.xls X". The code will then tell Reporting Services to take the Pivots.rdl file from the /SoftArtisans/ExcelReports folder on the server and render it in Excel designed by Officewriter Designer format to a file called MyExcelReport.xls in the MyReports folder on the C: drive.
Try right-clicking the process and select Execute Step. You should find your new report file in the folder you selected.

Step 4. Getting Fancy

Now that you've set up a DTS package that can generate a call to Reporting Services and write the report to a file, let's do something with it. For this part, you will need to have SQL Server set up to send emails (see your SQL Server documentation).

From the task icon list on the left, select Send Email Task.
Fill in your recipient list, etc. and type in a general introduction that you will use when you send the report. (for example, "Hello, here is the latest Accounts Receivable report.")
For attachments, choose the report file you just created. When SQL Server emails the attachment, it will take the newest version of the file that was created each time the DTS package is run.
Now let's put things in order. Right-click Send Email Task and select Workflow/Workflow Properties.
Click New to add a workflow.
Make sure the Source Step is the Execute Process Task you created earlier and that the Precedence is set to Success.
Click Ok and you should see an arrow from your Process Task to your Email Task.
Save your DTS package.

To integrate this with your normal extract, transform, and load (ETL) process or other DTS processes, add these DTS elements to your existing DTS. You can also specify different actions depending on the success or failure of your other processes. For example, in the diagram below:

I added an Execute SQL Task to represent my normal DTS tasks.
I right-clicked my Execute Process Task and selected Workflow/Workflow Properties as before.
I added a workflow and set the Source Step to my SQL task and set the Precedence to Success.
I added another Send Email Task, right-clicked this task and added a workflow, but set the Precedence to Failure.

When my SQL process runs successfully, my Report Process and Email Process will execute. If my SQL step fails, I will skip the report production and send a different email (for example, "Sorry, no reports today, the data warehouse is having difficulties. Please use yesterday's reports.")

Automating Reports Without DTS

The example above helps you set up DTS to generate report files. But you don't need DTS to do this. Any application that lets you run an external process will work. As in the example, first build the report definition files and the controller program described above.

Windows Scheduled Task

To generate your reports as a scheduled task:

Select Start -> Settings -> Control Panel -> Scheduled Task -> Add Scheduled Task.
Choose the controller program you wrote and type in parameters as described above to get the report you want.
Save your task and test it from the Scheduled Task Manager.

Other Applications

To generate reports from another application, you can use code similar to the above. However, it would be wise to check the documentation for both SoftArtisans WordWriter and ExcelWriter as you have access to a more powerful set of tools to create your reports. The above code is just a sample and is very basic. WordWriter and ExcelWriter offer much more.