Data Connectivity

Crystal Reports Forum : Crystal Reports .NET 2003 : Data Connectivity

Topic: Running Crystal Reports in a batch service

Page of 2 Next >>

Author Message

RichardP
Newbie

Joined: 26 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 9 Topic: Running Crystal Reports in a batch service
Posted: 26 Apr 2007 at 7:53am

I have set up a batch service to run a crystal report, as described on pages 381 - 384 of Mr. Bischof's book"Crystal Reports .NET Programming". But I have problems trying to access my database. To get it to log in, I've added the code shown below to my batch service:-

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Put user code to initialize the page here

Dim crReport As CrystalDecisions.CrystalReports.Engine.ReportObject

Dim crConnectionInfo As New ConnectionInfo

Dim crConnServer As String = ConfigurationSettings.AppSettings("CrConnServer")

Dim crConnDbName As String = ConfigurationSettings.AppSettings("CrConnDbName")

Dim crConnUserId As String = ConfigurationSettings.AppSettings("CrConnUserId")

Dim crConnPassword As String = ConfigurationSettings.AppSettings("CrConnPassword")

crConnectionInfo.ServerName = crConnServer

crConnectionInfo.DatabaseName = crConnDbName

crConnectionInfo.UserID = crConnUserId

crConnectionInfo.Password = crConnPassword

Dim myTable As Table

Dim myTables As Tables

Dim myLogonInfo As New TableLogOnInfo

CrystalReportViewer1.ReportSource = "http://localhost/WebServ5/STDPOReportService.asmx"

myTables = CrystalReportViewer1.ReportSource.Tables

For Each myTable In myTables

myLogonInfo.ConnectionInfo = crConnectionInfo

End Sub

But when I attempt to run it I get the following errors:-

Server Error in '/WebApp5' Application.
--------------------------------------------------------------------------------

Public member 'Tables' on type 'ProxyReportSource' not found.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.MissingMemberException: Public member 'Tables' on type 'ProxyReportSource' not found.

Source Error:

Line 53:
Line 54:         CrystalReportViewer1.ReportSource = "http://localhost/WebServ5/STDPOReportService.asmx"
Line 55:         myTables = CrystalReportViewer1.ReportSource.Tables
Line 56:         For Each myTable In myTables
Line 57:             myLogonInfo.ConnectionInfo = crConnectionInfo

Source File: c:\inetpub\wwwroot\WebApp5\WebForm1.aspx.vb Line: 55

Stack Trace:

[MissingMemberException: Public member 'Tables' on type 'ProxyReportSource' not found.]
   Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
   WebApp5.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebApp5\WebForm1.aspx.vb:55
   System.Web.UI.Control.OnLoad(EventArgs e)
   System.Web.UI.Control.LoadRecursive()
   System.Web.UI.Page.ProcessRequestMain()

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

Any ideas anyone?

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702 Posted: 26 Apr 2007 at 9:13am

In order to work with the "Tables" property, I think you need to work with the ReportDocument object model, not the CrystalReportViewer object model for your report (Brian, please correct me if I'm wrong...).

Here's the code we use for loading a report into the viewer:

private void Page_Init(object sender, EventArgs e)
{
     crReport = new ReportDocument();

    string reportName = Request.QueryString["ReportName"];
    string qServer = Request.QueryString["qServer"];
    string rptPath = Request.PhysicalPath.Substring(0, 2) + ConfigurationManager.AppSettings["rptDefinitionsPath"];

crReport.Load(@rptPath + reportName);

    ParameterDiscreteValue discreteVal = new ParameterDiscreteValue();
    ParameterRangeValue rangeVal = new ParameterRangeValue();
    ParameterValues curvalues = new ParameterValues();
    foreach (ParameterFieldDefinition parafld in crReport.DataDefinition.ParameterFields)
    {
      if (parafld.DiscreteOrRangeKind.ToString() == "DiscreteValue")
      {
        discreteVal.Value = Request.QueryString[parafld.ParameterFieldName];
        if (discreteVal.Value != null)
        {
          curvalues.Add(discreteVal);
          parafld.ApplyCurrentValues(curvalues);
        }
      }
    }

CrystalDecisions.Shared.ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

    connectionInfo.ServerName = qServer;
    connectionInfo.UserID = "reporting";
    connectionInfo.Password = "rlrootg";

    // set report connection for main report
    SetDBLogonForReport(connectionInfo, crReport, qServer);
    // set report connection for any subreports
    SetDBLogonForSubreports(connectionInfo, crReport, qServer);
    // set group tree function off
    crystalReportViewer.DisplayGroupTree = false;
    // view report

    crystalReportViewer.ReportSource = crReport;

}

private void SetDBLogonForReport(CrystalDecisions.Shared.ConnectionInfo connectionInfo, ReportDocument reportDocument, string qServer)
{

    Tables tables = reportDocument.Database.Tables;
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
      TableLogOnInfo tableLogonInfo = table.LogOnInfo;
      tableLogonInfo.ConnectionInfo = connectionInfo;
      table.ApplyLogOnInfo(tableLogonInfo);
    }
}

private void SetDBLogonForSubreports(CrystalDecisions.Shared.ConnectionInfo connectionInfo, ReportDocument reportDocument, string qServer)
{
    Sections sections = reportDocument.ReportDefinition.Sections;
    foreach (Section section in sections)
    {
      ReportObjects reportObjects = section.ReportObjects;
      foreach (ReportObject reportObject in reportObjects)
      {
        if (reportObject.Kind == ReportObjectKind.SubreportObject)
        {
          SubreportObject subreportObject = (SubreportObject)reportObject;
          ReportDocument subReportDocument = subreportObject.OpenSubreport(subreportObject.SubreportName);
          SetDBLogonForReport(connectionInfo, subReportDocument, qServer);

        }
      }
    }

}

Using the ReportDocument object model gives you MUCH more flexibilty in terms of setting properties of the report prior to viewing it. There's a comparison/explanation of the differences between the two object models here.

-Dell

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

RichardP
Newbie

Joined: 26 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 9 Posted: 27 Apr 2007 at 2:16am

Thank you for your help, Hilfy, but do you need to put any special "import" lines in VB code to get it to find request?

At the moment I have got the following import lines in my code:-

Imports System.Configuration 'Database Connection

Imports CrystalDecisions.CrystalReports.Engine 'Required For Crystal Reports
Imports CrystalDecisions.ReportSource.ReportSourceFactory
Imports CrystalDecisions.Shared 'Required for Crystal Reports
Imports System.Resources.ResourceReader
Imports CrystalDecisions.Web.ReportAgent
Imports System.IO 'Required to create directory

but when I type:-

Dim reportName As Request.QueryString = "STDPOReport"

I get it flagged with the error "Type 'Request.QueryString' is not defined.

Edited by RichardP - 27 Apr 2007 at 2:17am

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702 Posted: 27 Apr 2007 at 6:33am

I'm working in C#, so I have "Using" instead of "Imports". Here's the entire set from our app:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Collections.Specialized;
using System.Diagnostics;

"Request" is part of the URL coming from the page that called this one. We use it to send session information, the name of the report, the database server to connect to (same data structure, but different db name for each of our clients) and the path to where all of the report files are located. Your app may be getting this information from somewhere else, so you would need to modify that part of the code.

-Dell

Edited by hilfy - 27 Apr 2007 at 6:33am

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

RichardP
Newbie

Joined: 26 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 9 Posted: 30 Apr 2007 at 2:12am

Thanks again for your help Hilfy, but I am still unable to get the line:-

Dim reportName As Request.QueryString = "STDPOReport"

to compile

I've used all the import lines you suggested except "System.Web.UI.WebControls.WebParts" which I dont seem to have.

Edited by RichardP - 30 Apr 2007 at 2:15am

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702 Posted: 30 Apr 2007 at 7:25am

If you're not passing the name of the report into the page as part of the URL, you don't need to use the Request object. You would just declare reportName as a string and set its value.

If you are getting the report name as part of the URL, I suggest that you search Help for the Request class and find out which assembly you'll need to include to have it compile.

-Dell

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

RichardP
Newbie

Joined: 26 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 9 Posted: 02 May 2007 at 2:40am

I have now found a way round the previous poblem and my code is now:-

Public Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Put user code to initialize the page here

Dim crConnectionInfo As New ConnectionInfo

Dim crConnServer As String = ConfigurationSettings.AppSettings("CrConnServer")

Dim crConnDbName As String = ConfigurationSettings.AppSettings("CrConnDbName")

Dim crConnUserId As String = ConfigurationSettings.AppSettings("CrConnUserId")

Dim crConnPassword As String = ConfigurationSettings.AppSettings("CrConnPassword")

crConnectionInfo.ServerName = crConnServer

crConnectionInfo.DatabaseName = crConnDbName

crConnectionInfo.UserID = crConnUserId

crConnectionInfo.Password = crConnPassword

crReport.Load("http://localhost/WebServ5/STDPOReportService.asmx")

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "due_in")

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "stores_item")

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "unit_info")

CrystalReportViewer1.ReportSource = crReport

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

Dim MyLogonInfo As CrystalDecisions.Shared.TableLogOnInfo

For Each myTable In crReport.Database.Tables

'[With MyLogonInfo.ConnectionInfo

'End With

MyLogonInfo.ConnectionInfo = crConnectionInfo

myTable.ApplyLogOnInfo(MyLogonInfo)

End Sub

Private Sub SetLogOnInfo(ByVal server As String, ByVal database As String, _

ByVal userID As String, ByVal password As String, ByVal _

table As String)

Dim logOnInfo As New TableLogOnInfo

logOnInfo = crReport.Database.Tables.Item(table).LogOnInfo

' Set the connection information for the table in the report.

logOnInfo.ConnectionInfo.ServerName = server

logOnInfo.ConnectionInfo.DatabaseName = database

logOnInfo.ConnectionInfo.UserID = userID

logOnInfo.ConnectionInfo.Password = Password

logOnInfo.TableName = table

End Sub

End Class

But now I get the message:-

Server Error in '/WebApp5' Application.

Load report failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CrystalDecisions.CrystalReports.Engine.LoadSaveReportException: Load report failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[LoadSaveReportException: Load report failed.]
   .I(String , EngineExceptionErrorID )
   .E(String , Int32 )
   CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(String reportName, OpenReportMethod openMethod, Int16 parentJob)
   CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(String reportName)
   WebApp5.WebForm1.Page_Load(Object sender, EventArgs e)
   System.Web.UI.Control.OnLoad(EventArgs e)
   System.Web.UI.Control.LoadRecursive()
   System.Web.UI.Page.ProcessRequestMain()

Any ideas anyone?

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702 Posted: 02 May 2007 at 6:26am

You're trying to load a .asmx file - you need to load a .rpt file.

-Dell

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

RichardP
Newbie

Joined: 26 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 9 Posted: 02 May 2007 at 7:09am

Thanks, Hilfy. I've changed my code accordingly but am still getting the same message!

Revised code:-

Public Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Put user code to initialize the page here

'CrystalReportViewer1.ReportSource = New WebServ5.STPMBReportService

'Dim NewServ1 As New WebServ5.service

Dim crConnectionInfo As New ConnectionInfo

Dim crConnServer As String = ConfigurationSettings.AppSettings("CrConnServer")

Dim crConnDbName As String = ConfigurationSettings.AppSettings("CrConnDbName")

Dim crConnUserId As String = ConfigurationSettings.AppSettings("CrConnUserId")

Dim crConnPassword As String = ConfigurationSettings.AppSettings("CrConnPassword")

Dim err As String

Try

crConnectionInfo.ServerName = crConnServer

crConnectionInfo.DatabaseName = crConnDbName

crConnectionInfo.UserID = crConnUserId

crConnectionInfo.Password = crConnPassword

crReport.Load("http://localhost/WebServ5/STDPOReport.rpt")

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "due_in")

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "stores_item")

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "unit_info")

CrystalReportViewer1.ReportSource = crReport

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

Dim MyLogonInfo As CrystalDecisions.Shared.TableLogOnInfo

For Each myTable In crReport.Database.Tables

MyLogonInfo.ConnectionInfo = crConnectionInfo

myTable.ApplyLogOnInfo(MyLogonInfo)

End Sub

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702 Posted: 02 May 2007 at 7:23am

I'm not sure what the problem is at this point - does the .rpt file exist?

Also, I'm seeing other issues in your code -

SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "due_in")
SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "stores_item")
SetLogOnInfo(crConnServer, crConnDbName, crConnUserId, crConnPassword, "unit_info")

CrystalReportViewer1.ReportSource = crReport

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
Dim MyLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
For Each myTable In crReport.Database.Tables
'[With MyLogonInfo.ConnectionInfo
'End With
MyLogonInfo.ConnectionInfo = crConnectionInfo
myTable.ApplyLogOnInfo(MyLogonInfo)

The section of code after you set the report source does nothing meaningful - you've already set the connection info with your calls to SetLogonInfo. If you have other tables that need to be set, you need to do this before you set the report source.

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

Page of 2 Next >>

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum