Problem with Data Display and Connection

[vivek_ku]

Hi,

I am new to Crystal Reports. I am working on following platform:

-Windows 98 (for development. Would be deployed on 95/98)
-Oracle 9i r2
-Vb6
-Crystal Reports 8.0

I need to have an option for Users to view formatted data. I do have a .rpt that opens in VB to display. If the .rpt is saved with Save Data with Report option- query runs fine and result is displayed. But, without checking this option, it does not display any thing.

What I want is:

1. Every time a User clicks option for viewing, s/he should view fresh and updated data.

2. SCR should not ask for logging information (I want UseID, Password, Server etc information embedded somewhere)

Any help would be much appreciated.

Thanks in advance.

-Vivek

[malleyo]

You could run your query in VB and pass the recordset to Crystal. This would accomplish both of your questions, but it's limited in regards to formatting to how you format the return data within the query.

To gain even more control of the data through VB, you could use CDO (Crystal Data Objects). CDO creates it's own "recordset" based on an array of data that you create. This gives you control within VB to format that data and do whatever you want with it before passing the final result to be displayed in Crystal.

Both of these options rely on VB to do all the database stuff, Crystal would NEVER have to touch the DB. If either of these options sounds like something you would like more info on, respond to this post and I'll dig out all the info I have and post it here.

[vivek_ku]

Originally posted by malleyo
You could run your query in VB and pass the recordset to Crystal. This would accomplish both of your questions, but it's limited in regards to formatting to how you format the return data within the query.

To gain even more control of the data through VB, you could use CDO (Crystal Data Objects). CDO creates it's own "recordset" based on an array of data that you create. This gives you control within VB to format that data and do whatever you want with it before passing the final result to be displayed in Crystal.

Both of these options rely on VB to do all the database stuff, Crystal would NEVER have to touch the DB. If either of these options sounds like something you would like more info on, respond to this post and I'll dig out all the info I have and post it here.

[vivek_ku]

Thanks so much.

Yes, this is what I was looking out for. Please let me have more information. I would request you to suggest me site from where I could learn about CDO object.

BTW, since I am new to it, I may ask further questions/explanations which I hope you would not mind.

-Vivek

[malleyo]

The link is to a PDF File that I found on Crystals Website that explains how to pass an ADO recordset. The code that I have is what you would put into VB in order to pass your data to the Report via CDO.

A ttx file is a tab-delimited file that has all info about the fields you want to include. I check the Crystal Code Guru page several times a day during the week, so if you have additional questions, I'll do my best to help you or point you in the right direction.



First you need to create your report. Here's a link that shows you how to create a ttx file and pass it an ADO recordset.

http://support.businessobjects.com/..._ttxado.pdf.asp

Using CDO is the same as using ADO with a few more steps. CDO is Crystal's version of a recordset. You need to build an array of your data and stuff the array into the CDO rowset. Then pass the CDO Rowset to the report in the same way you pass a recordset.

Add the “Crystal Report Engine” and “Crystal Data Objects” references to the project


‘Declare the CDO Rowset
Dim cdoRowset As New CrystalDataObject.CrystalComObject

Private crApplication     As CRPEAuto.Application
Private crDatabase        As CRPEAuto.Database
Private crTables          As CRPEAuto.DatabaseTables
Private crTable           As CRPEAuto.DatabaseTable
Private crView            As CRPEAuto.View
Private WithEvents crReport          As CRPEAuto.Report

‘This is the array that gets filled with your data and added to the rowset
Dim varRowsArray() As Variant

‘Clear cdoRowset
Set cdoRowset = Nothing


‘Add Field names to the rowset, the names don’t have to match what’s in the report, but it makes it easier if they do
cdoRowset.AddField "Field1", vbString  
cdoRowset.AddField "Field2", vbString 
cdoRowset.AddField "Field3", vbString        

‘Redim the array
‘CDO does things backwards, 
'    Example from Website:
'    - Sally Brown (Female)
'    - Shannon Sharpe (Male)
'    MyArray(0, 0) = Sally       	MyArray(1, 0) = Shannon
'    MyArray(0, 1) = Brown      	MyArray(1, 1) = Sharpe
'    MyArray(0, 2) = Female      	MyArray(1, 2) = Male
‘ Make sure the columns matches up with the number of fields you added from above
ReDim varRowsArray(intRows, intColumns)

‘Add fields
varRowsArray(0, 0) = CStr("String1")
varRowsArray(0, 1) = CStr("String2")
varRowsArray(0, 2) = CStr("String3")

varRowsArray(1, 0) = CStr("String4")
varRowsArray(1, 1) = CStr("String5")
varRowsArray(1, 2) = CStr("String6")

varRowsArray(2, 0) = CStr("String7")
varRowsArray(2, 1) = CStr("String8")
varRowsArray(2, 2) = CStr("String9")

‘Add array to Rowset
cdoRowset.AddRows varRowsArray


Set crApplication = New CRPEAuto.Application

‘Set Report name and location
Set crReport = crApplication.OpenReport(App.Path & "\Report1.rpt")
Set crDatabase =crReport.Database
Set crTables = crDatabase.Tables
Set crTable = crTables.Item(1)
        
Call crTable.SetPrivateData(3, cdoRowset)

Set crView = crReport.Preview

[vivek_ku]

It's wonderful! So kind of you- Malleyo.

I need some time to incorporate what you have sent in and test in the present environment. I will revert to you soon.

Thanks again.

-Vivek

[selvakumarig]

Hi.. Am Creating Crystal Report using Field Definitions Only (ttx)..
When Executing these to invoke main report , there is no issues.. I want to call a sub report
When executing these code there is an shows as Invalid subreport name

Pls help me to solve this error

Dim RptApp As New CRPEAuto.Application
Dim RptFile As CRPEAuto.report
Dim RptSubFile As CRPEAuto.report
Dim mysubreport As SubreportObject
Dim RptFileDB As CRPEAuto.Database
Dim RptTables As DatabaseTables
Dim RptTable As DatabaseTable
Dim RptView As CRPEAuto.View
Dim RptPrntInst As CRPEAuto.PrintWindowOptions

Dim RptSubFileDB As CRPEAuto.Database
Dim RptSubTables As DatabaseTables
Dim RptSubTable As DatabaseTable

Set RptFile = RptApp.OpenReport(STATEMENT_PATH & "\MOVITEXPackingList.Rpt")

''' To invoke Main Report
Set RptFileDB = RptFile.Database
Set RptTables = RptFileDB.Tables
Set RptTable = RptTables.Item(1)
RptTable.SetPrivateData 3, rsPrint ''' Main Report RecordSet

''' To invoke sub report
Set RptSubFile = RptFile.OpenSubreport(STATEMENT_PATH & "\MOVITEXsubPackingList.Rpt")
Set RptSubFileDB = RptSubFile.Database
Set RptSubTables = RptSubFileDB.Tables
Set RptSubTable = RptSubTables.Item(1)
RptSubTable.SetPrivateData 3, rssubPrint '''' Sub Report RecordSet

RptFile.ParameterFields("sBuyerOtherConsignee").SetCurrentValue (sBuyerOtherConsignee)


Set RptPrntInst = RptFile.PrintWindowOptions
RptPrntInst.HasPrintSetupButton = True
Set RptView = RptFile.Preview("MOVITEXPackingList", 0, 0, 800, 600, RptStyle)
RptView.ZoomPreviewWindow (90)