Accessing Report Field Collection

How can I access the properties of fields in a report which has fields from a data definition (ttx) file? I want to iterate through the field collection of the report in vb and get the field names.

Do I have to iterate through each field type eg Formula and Sort field collections?
Who is Participating?
DRRYAN3Connect With a Mentor Commented:
Use the CRAXDRT library

Dim crAppl As CRAXDRT.Application
Dim crRep As CRAXDRT.Report
Dim rs As ADODB.Recordset

' watch these two vars for answer to your question
dim field1DBFieldName as string
dim field1Name as string

' bunch of code to setup your recordset and load the data into it

Set crAppl = New CRAXDRT.Application
Set crRep = crAppl.OpenReport("C:\my documents\sample.rpt")
' bunch of code to setup your recordset and load the data into it
crRep.Database.Tables(1).SetDataSource rs, 3

field1DBFieldName = crRep.database.tables.item(1).fields.item(1).DatabaseFieldName
field1Name = crRep.database.tables.item(1).fields.item(1).Name

CRViewer1.ReportSource = crRep

Obviously, you would iterate through the available fields and pick out the information you want.

You could also just open up the ttx file as a textstream and read it.
rhubarbtwoAuthor Commented:
Ah this is getting the field info from the data source (rs), I'm wanting to check the names of the fields in the report.

I've setup a merge routine with Word where I check the doc for alias field names, replace the aliases in the doc with Word merge field names, and then merge the doc with the data. I was trying to use a similar model with Crystal, however, its obviously going to be different.

I can setup the ttx file with alias names, but then, assuming the report field names can be extracted, can I change the aliases in the report for field names so the merge works.

Also I had a problem with late binding, I've referenced 'CRDataSource 1.0 Type Library' but using 'Set crpApp = CreateObject("CRAXDRT.application")' I get the error 'ActiveX component can't create object'. Also should I use 'Implements CRDataSourceLib.CRDataSource' as decribed in the developer's help?
rhubarbtwoAuthor Commented:
So far I am using the following code:

     Private crpApp As CRAXDRT.Application
     Private crpReport As CRAXDRT.Report
     Private crpDatabase As CRAXDRT.Database
     Private crpTables As CRAXDRT.DatabaseTables
     Private crpTable As CRAXDRT.DatabaseTable
     Private crpField As CRAXDRT.FieldObject
     Private crpSection As CRAXDRT.Section
     Private crpReportObject As Object

'Sub code
     Set crpApp = New CRAXDRT.Application    
     Set crpReport = crpApp.OpenReport(sDocPath)

        Set crpDatabase = crpReport.Database
        Set crpTables = crpDatabase.Tables
        Set crpTable = crpTables.Item(1)
For Each crpSection In crpReport.Sections            
       For Each crpReportObject In crpSection.ReportObjects
            If crpReportObject.Kind = crFieldObject Then  
                Set crpField = crpReportObject
                sField = crpField.Name
            End If
       Next crpReportObject          
Next crpSection

But crpField.Name just returns 'Field1', 'Field2', etc
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

No, my code is pulling the information from the REPORT, not the recordset.  When I tested the code, I had NO recordset open and if you dig around in the local variables window, you will find that you can get the .ttx file name, sample data values assigned, etc.

The way I'd approach your merge problem is not by trying to change to report's field names, but rather by changing the ADO recordset field names to match those in the report.  You can dynamically construct your SQL select and include 'as' clauses to name the fields whatever you want.
rhubarbtwoAuthor Commented:
Oh yes, use aliases in the sql.

Or I could use textboxes/labels in the report as say [[FieldAliasName]], search for these, store position and dimension properties and replace with data fields....??

I'll try your code in your first comment on Monday, have a good weekend.

Any more thoughts oon my question at (the '= New' works but not the late binding)
Let me know - and I left a comment at your other question.
Any luck today with this code?

rhubarbtwoAuthor Commented:
I'm getting a 'file not found' error for either of the methods CRViewer1.ViewReport and crpReport.ReadRecords even though I set the corect path for the rpt in the OpenReport method.

Did you move the TTX file from its original location?  You cannot do that - one of the problems of using TTX files.
rhubarbtwoAuthor Commented:
That's useful to know. The cause was that I had manually edited the ttx file adding a field through Notepad.

I'm wanting to write a ttx file using code and then add it to reports manually through Crystal...should beno problem with that should there.
rhubarbtwoAuthor Commented:
Still got a problem: The report shows but it only shows the sample data in the ttx file, it doesn't show the data in the adoRS.
rhubarbtwoAuthor Commented:
I've made the following declarations:

Public adoCon As New ADODB.Connection
Public adoRS As New ADODB.Recordset
Public crpApp As CRAXDRT.Application
Public crpReport As CRAXDRT.Report

I've opened an ado con and an ado rs then....

    Set crpApp = New CRAXDRT.Application
    'Set crpReport = New CRAXDRT.Report
    Set crpReport = crpApp.OpenReport(sDocPath)
    crpReport.Database.Tables(1).SetDataSource adoRS, 3
    With Form1
        .CRViewer1.ReportSource = crpReport
    End With

But all I get in the report is the one line of sample data
rhubarbtwoAuthor Commented:

    Dim sDocPath As String
    sDocPath = App.Path & "\report1.rpt"
Thanks for the points.

You never get to see data in the rs during design time when using ttx files, only the sample data.  You also need to remove the comment from the set crpReport = new CRAXDRT.Report line and change that line to match my declaration above.
rhubarbtwoAuthor Commented:
If I remove the comment from set crpReport = new CRAXDRT then it causes an illegal op and crashes. If I don't set it to new the rpt opens but only shows the sample data....theis is at runtime.
rhubarbtwoAuthor Commented:
For more points would you have a simple sample app which works?
rhubarbtwoAuthor Commented:
A sample on Planet Source Code uses a Crystal Designer instead of a CRAXDRT.Report. For some reason I can get this sample to display in the viewer with my rpt which is linked to a ttx, but not using the above code.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.