SDK - find table and column names in report

[cmmrfrds]

I am looking for the object that holds the table and column information or alternately the SQL in a Command Object for a Crystal Report. I appreciate any pointers on what objects to look in for this information.

Thank you.

 

[Turkbear]

Hi,
That info is in the InfoStore object contained in the CMS database ( kinda', it is a structure built with info from the CMS's data)..Try using the Query Builder tool on the Admin page of the CMC to see what you can find, otherwise you will need to build custom code ( examples here:
thread768-1363760
)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[hilfy]

If you're using BusinessObjects, then you'll query the CMC like like TurkBear indicates and then cast the InfoObject as a "Report". If you're not using BusinessObjects, load the .rpt file into a ReportDocument object.

In the ReportDocument, you get the tables through the ReportDocument.Database.Tables property. To get the fields that are used in the report, you can use the Fields property of each Table in Tables - look at the UseCount property of each field to determine whether it's used in the report.

Getting the information from BusinessObjects is a little more problematic because you can't cast an InfoObject as a ReportDocument. If you need info on how to do that, let me know.

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[cmmrfrds]

Thank you both for the insight. I am writing the code in an Access program with VBA where I put the information in an Access table that makes it easy to report from. I will include a subroutine that shows how I am getting the information into the table. I would like to make another table where I would put the table and column names.

Here is the subroutine written in Access VBA. Please offer any comment on how to best incorporate the code for table/column information.

Public Function BuildRecordSelectionTable()
Dim objInfoStore As CrystalInfoStoreLib.InfoStore
Dim aSessionMgr As CrystalEnterpriseLib.SessionMgr
Dim aSession As CrystalEnterpriseLib.EnterpriseSession
Dim aResult As CrystalInfoStoreLib.InfoObjects
Dim aResult2 As CrystalInfoStoreLib.InfoObjects
Dim aResult3 As CrystalInfoStoreLib.InfoObjects
Dim infoStr As String, infoStr2 As String, infoStr3 As String

Set objInfoStore = New CrystalInfoStoreLib.InfoStore
Set aSessionMgr = New CrystalEnterpriseLib.SessionMgr
''- Use TOP nnnn to retrieve more than the 1000 limit.

Set aSession = aSessionMgr.Logon("commerfj", "mypassword", "Reno", "secWinAD")
Debug.Print "session APS = "; aSession.APSName
Debug.Print "session CMS = "; aSession.CMSName
Debug.Print "Start Time = "; Now()

Set objInfoStore = aSession.Service("", "InfoStore")

infoStr = "Select TOP 5000 SI_NAME, SI_DESCRIPTION, SI_PATH, SI_OWNER, SI_PARENT_FOLDER, SI_ID, "
infoStr = infoStr & "SI_PROCESSINFO, SI_PROCESSINFO.SI_RECORD_FORMULA, SI_FILES, SI_SCHEDULEINFO "
infoStr = infoStr & "From CI_INFOOBJECTS Where (SI_KIND IN ('CrystalReport', 'Pdf', 'Excel')) "
infoStr = infoStr & "and si_instance = 1 and si_recurring = 1 "

''infoStr = infoStr & "From CI_INFOOBJECTS Where (SI_PROGID='CrystalEnterprise.Report' or SI_PROGID='CrystalEnterprise.Pdf') "

Set aResult = objInfoStore.Query(infoStr)
Dim indx As Integer, indx3 As Integer

Debug.Print "info Count = "; aResult.Count
Debug.Print "SQL = "; infoStr

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
Dim folderID As Long, folderName As String, SI_ID As Long, SI_PROGID As Long
Set cn = CurrentProject.Connection

rs.Open "RecordSelectionTable", cn, adOpenDynamic, adLockOptimistic

For indx = 1 To aResult.Count
rs.AddNew
rs!reporttitle = aResult(indx).Title
rs!OwnerName = aResult(indx).Properties.Item("SI_OWNER")
folderID = aResult(indx).Properties.Item("SI_PARENT_FOLDER")
rs!folderID = aResult(indx).Properties.Item("SI_PARENT_FOLDER")

''''''''''''''''Debug.Print "Title ID = "; aResult(indx).Title
''-------- Get the Folder information
infoStr2 = "Select TOP 1 * From CI_INFOOBJECTS where SI_PROGID='CrystalEnterprise.Folder' and SI_ID = " & _
Chr(39) & folderID & Chr(39)
Set aResult2 = objInfoStore.Query(infoStr2)
If aResult2.Count > 0 Then
rs!CurrentFolder = aResult2(1).Properties.Item("SI_NAME")
End If
If aResult2.Count > 0 _
And aResult(indx).Properties.Item("SI_PARENT_FOLDER") > 0 Then
If aResult2(1).Properties.Item("SI_PATH").Properties.Item("SI_NUM_FOLDERS") > 0 Then
folderName = aResult2(1).Properties.Item("SI_PATH").Properties.Item("SI_FOLDER_NAME1")
Else
folderName = "UnKnown "
End If
Else
folderName = "Root Folder "
End If
rs!folderName = folderName

''-------- Get the Record Selection information
Dim CntValues As Integer, Rec_sel As Variant, indx4 As Integer
CntValues = aResult(indx).ProcessingInfo.Properties.Count
For indx4 = 1 To CntValues
''' - erred when going after 'Excel' or 'Pdf' types.
If aResult(indx).ProcessingInfo.Properties(indx4).Name = "SI_RECORD_FORMULA" Then
'' Rec_sel = aResult(indx).PluginInterface.RecordFormula
Rec_sel = aResult(indx).ProcessingInfo.Properties(indx4).Value
End If
Next
''Debug.Print "Record Formula = "; Rec_sel

rs!RecordSelect = Rec_sel

rs.Update

skipstuff:
Next


cn.Close
Set cn = Nothing
Set rs = Nothing

aSession.Logoff
Debug.Print "Finish Time = "; Now()
End Function

 

[cmmrfrds]

Dell, can you give me an idea on how to do this.

"Getting the information from BusinessObjects is a little more problematic because you can't cast an InfoObject as a ReportDocument. If you need info on how to do that, let me know."

Thank you.

 

[hilfy]

Basically, I think this is what you need to do:

1. Do your query to get the report - include the file info in the query.
2. Get the location of the file from the InfoObject. This will be in the format "input://<filename>". You'll have to know the path to the FileStore (and have access to it!) so that you can replace "input:" with it.
3. Load the file into a ReportDocument object and use the Database.Tables.Fields[x].UseCount property to get the fields that are used in the report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[cmmrfrds]

Thank you. I got the path from the administrator. It is
KAI\BUSINESSOBJECTSDATA\FILESTORE
Under
SI_FILES SI_PATH I find
frs://Input/a_100/032/000/8292/


I am not sure how to load into a
ReportDocument object. Is there a library other than CrystalInfoStoreLib.InfoObjects I will need for this? Can I access from code with BOE's special SQL, or do I need another way to do it?

 

[hilfy]

The .NET assembly is CrystalDecisions.ReportEngine. I'm not sure what you'd use for VBA.

You need to create a ReportDocument object and then use the .Load method to load the report into memory.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.