Automating Reports with diverse data sources using Business Objects Enterprise Server - Part 1
 
Published: 10 Apr 2006
Unedited - Community Contributed
Abstract
In this article, Eric demonstrates how to automate a report with a remote XML datasource and Crystal Reports XI. The article also shows how to use Business Objects Enterprise Server to send the report.
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19537/ 28

Introduction

The idea of automating reports takes many shapes.  Some people may want to schedule a report before a weekly meeting and have the report emailed to all meeting participants.  When the report is scheduled and emailed automatically, there is a great time savings for the end user, as well as consistent quality of service.  Usually, this automation is done through an Enterprise Reporting Server solution, like Business Objects Enterprise Server or Microsoft Reporting Services. 

In the case of the Crystal Alliance section of ASPAlliance, we have some unique needs for reporting.  We capture information in an XML file and must then put the information from the XML file into an SQL Database.  Then we use a Crystal XI report to create the report.  We have automated this process using a .NET an assembly. 

A new wrinkle we have added is putting the report into Business Objects Enterprise to schedule when the report is sent.  This will help in making sure these reports get sent out on time.

The intent of this article is not to provide a prepackaged solution to be used in a similar environment.  Hopefully, parts of this article can be applied to other automation scenarios.  The first part of this article is concerned with getting the data in the appropriate place and the report.  The second part will involve setting up the Business objects Server correctly and the subscriptions successfully.

System Requirements

Microsoft Visual Studio 2005 (RTM)

.NET Framework 2.0

Crystal Reports XI

SQL Server 2000

Crystal Reports Server XI

System Design

As mentioned in the introduction, the System consists of 3 parts: the Data Transfer application, the report, and the Enterprise Server to schedule and manage reports. 

The data transfer application needs to ftp an xml file from the server.  Then we use a DTS package to import the xml file into a table in SQL Server.  This table is what the Crystal Report uses as the data source. 

The Crystal Report uses a graph and some drill down.

Business Objects Enterprise server is utilized to send this report once a month.  Also, it tracks if this is a successful send or not.   

Moving Data

To move the data we created an FTP client that utilizes the 2.0 .NET Framework.  It also uses FTP Client, a sample FTP application that I downloaded a while back that was written in VB.NET.  Unfortunately, I was not able to find the link to where I found this, but there are many FTP client apps for .NET 2.0 out there. Try checking here.  

With this client, we created a console application.  Since we only run the report once a month, a console application, which can be scheduled through the Windows Scheduler, makes sense. 

Figure 1 Screen Shot of Console application

The console application CAAutoTransferWorker uses the applications settings properties to set the different properties for FTP and the DTS packages.  The XML file gets renamed on the FTP site and then is downloaded to a local directory (code Listing 1).  From the local directory the DTS Package updates the table with the data.

Listing 1

static void Main(string[]args)
{
  this.RenameFile();
  this.CopyToImportDir();
 
}
 
#region Methods
public void CopyToImportDir()
{
  FTPclient ftp = newFTPclient(Settings1.Default.FTPServer,
    Settings1.Default.FTPUserID,Settings1.Default.FTPPWD);
  String strDate = ReturnFullDate();
  if(File.Exists(Settings1.Default.FTPFileToPath + Settings1.Default.FTPFile +
    "." + Settings1.Default.FTPFileExt))
  {
    File.Move(Settings1.Default.FTPFileToPath +Settings1.Default.FTPFile + "."
      + Settings1.Default.FTPFileExt,Settings1.Default.FTPFileToPath +
      Settings1.Default.FTPFile + strDate +"." + Settings1.Default.FTPFileExt);
  }
// this method assumes that rename file has beenrun.
  ftp.Download(Settings1.Default.FTPFile +"." + Settings1.Default.FTPFileExt,
    Settings1.Default.FTPFileToPath +Settings1.Default.FTPFile + "." +
    Settings1.Default.FTPFileExt, false);
}
 
public void RenameFile()
{
  String strDate = ReturnFullDate();
  String strFilePlusExt =Settings1.Default.FTPFile + strDate + "." +
    Settings1.Default.FTPFileExt;
  String strFileNew = Settings1.Default.FTPFile+ strDate + "." +
    Settings1.Default.FTPFileExt;
  FTPclient ftp = newFTPclient(Settings1.Default.FTPServer,
    Settings1.Default.FTPUserID,Settings1.Default.FTPPWD);
  Boolean bRenWork =ftp.FtpRename(strFilePlusExt, strFilePlusExt);
  System.Threading.Thread.Sleep(1000);
  ftp.Download(strFileNew, strFilePlusExt,false);
  ftp.FtpRename("crystalViews" +strDate + ".log""crystalViews.log");
}

The code listed in Listing 1 takes care of renaming the file then uses FTP to send the renamed file to the directory for data transfer (using the file name the DTS Package expects).  Next, we will show the code used to transfer the data into SQL Server. 

To transfer data using DTS, we are using SQL Server 2000.  We reference the Microsoft DTSPackage Object Library com object in our Visual Studio project.  In using Visual Studio 2005, when you specify the com object, the managed code wrapper for the com object is automatically created.  Once we have added the reference to DTSPackage Object Library, we can now load the DTS Package that imports the file and execute it.  See Listing 2 for the code that executes this.

Listing 2

public void RunDTSPackage()
{
  DTS.PackageClass myDTS = new PackageClass();
  object objTest = new object();
 myDTS.LoadFromSQLServer(Settings1.Default.DTSServer,
    Settings1.Default.DTSUser,Settings1.Default.DTSPwd,
   DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "",
    Settings1.Default.DTSPackageGUID,Settings1.Default.DTSPackageVersionGUID,
    Settings1.Default.DTSPackageName, refobjTest);
  myDTS.Execute();
}

Once this code is executed in our console application, the data is prepared and the report can run.

Summary

The first part of this article shows how we are automating the data transfer part of our reporting.  This includes downloading an XML file using FTP and utilizing a DTS package in code in a .NET 2.0 console application that will be scheduled.  In part 2 of this article, we will go over the set up the Business Objects Server, including adding a scheduled report and the email options. Until then, Happy Coding.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 5:39:43 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search