Skip to Content

Software used:

Crystal Reports 2008
Visual Studio 2010

Assembly  Info: CrystalDecisions.CrystalReports.Engine.dll, CrystalDecisions.ReportSource.dll,
CrystalDecisions.Shared.dll, CrystalDecisions.Windows.Forms.dll, CrystalDecisions.ReportAppServer.ReportDefModel.dll, CrystalDecisions.ReportAppServer.DataDefModel.dll, CrystalDecisions.ReportAppServer.ClientDoc.dll,

 

Mr. Murphy was really rattled.  He had been asked to provide an estimate by higher management and he sure didn’t know how to go about it.   There were some database columns that were being used in various formulas in the Crystal Reports: the formula could be an independent formula field, or it could be hidden in some section suppression logic.  He needed to find the number of places where it was used and figure the effort required to change the logic at all places.

So he called Jason and explained to him the task at hand. Each formula in each Report needed to be checked.Suppression logic written for sections needed to be checked.  Suppression logic for each object in each section needed to be checked. It would be a mammoth, boring and “easy – to make a mistake” kind of task if done manually.

Mr. Murphy asked Jason whether he could build some kind of automation tool that would dump all such formulas in each Crystal Report into a xml/Excel so that one could easily search for certain phrases in the Excel and shortlist the reports.

It was better to create this tool, Mr. Murphy thought, who knows tomorrow they are going to change some other logic and again we will need to check all the Reports.

 

The logic uses the below steps:

1.  It is easy to get a dump of the formulas that are explicitly created in Crystal Report. This is done in the first For Loop using the properties of rdoc.DataDefinition.FormulaFields object

2. The second For loop in the sample code gets any suppression formula in a detail area. This example can be used for other Report areas like Report Header, Report Footer etc as required.

3.  The second For loop uses the object rasClientDoc.ReportDefController.ReportDefinition.DetailArea.Sections(i)
and gets the details of the formula from the Format.ConditionFormulas.Formula property.

4. The enum  CrystalDecisions.ReportAppServer.ReportDefModel. CrObjectFormatConditionFormulaTypeEnum.crObjectFormatConditionFormulaTypeEnableSuppress is used to get the suppression formula.

5. The third For loop is inside the previous For loop. It examines each object in a certain Detail Area using the syntax rasClientDoc.ReportDefController. ReportDefinition.DetailArea.Sections(i).ReportObjects(k)

6. As in the case of Detail area, the property Format.ConditionFormulas.Formula is used to determine the suppression formula.

7. You can use other enum values (refer point 4) to get other kinds of formulae.

8. This function can be called in a loop and names of all reports requiring analysis can be passed to it.

9.The ReportFormulaTable can be exported to get all the formula details in xml format.

 

Code Sample


Sub GetFormulaData(ByVal ReportName As String)

Dim rdoc As CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim rasClientDoc As CrystalDecisions.ReportAppServer.ClientDoc.ISCDClientDocument
Dim reportDataRow As DataRow
‘Dim allReportObjects As CrystalDecisions.ReportAppServer.ReportDefModel.ReportObjects
Dim thisObject As CrystalDecisions.ReportAppServer.ReportDefModel.ReportObject
rdoc = New CrystalDecisions.CrystalReports.Engine.ReportDocument
rdoc.Load(ReportName)
‘ A loop to get all formula explicitly created in the report
‘First For Loop

For j = 0 To rdoc.DataDefinition.FormulaFields.Count – 1
reportDataRow = ReportFormulaTable.NewRow()
reportDataRow(“Report Name”) = rdoc.FileName
reportDataRow(“Formula Name”) = rdoc.DataDefinition.FormulaFields.Item(j).FormulaName
reportDataRow(“Return Type”) = rdoc.DataDefinition.FormulaFields.Item(j).ValueType
reportDataRow(“Details”) = rdoc.DataDefinition.FormulaFields.Item(j).Text
reportDataRow(“Type”) = rdoc.DataDefinition.FormulaFields.Item(j).Kind
ReportFormulaTable.Rows.Add(reportDataRow)
Next j
‘use the ReportAppserver to search for other formulae
rasClientDoc = rdoc.ReportClientDocument

‘Second For Loop

For i = 0 To rasClientDoc.ReportDefController.ReportDefinition.DetailArea.Sections.Count() – 1
‘check if the ith section has any suppression logic
reportDataRow = ReportFormulaTable.NewRow()
reportDataRow(“Report Name”) = rdoc.FileName
‘Formula name shall be in the form Detail-1-Suppression_logic
reportDataRow(“Formula Name”) = “Detail-” & i.ToString() & “-Suppression_logic”
reportDataRow(“Return Type”) = “Boolean”
reportDataRow(“Details”) =      rasClientDoc.ReportDefController.ReportDefinition.DetailArea.Sections(i).Format.

ConditionFormulas.Formula(CrystalDecisions.ReportAppServer.ReportDefModel.

CrObjectFormatConditionFormulaTypeEnum.

crObjectFormatConditionFormulaTypeEnableSuppress).Text()
reportDataRow(“Type”) = “Section-Suppression Logic”
If Not IsDBNull(reportDataRow(“Details”)) Then
ReportFormulaTable.Rows.Add(reportDataRow)
End If

‘Third For Loop

For k = 0 To rasClientDoc.ReportDefController.ReportDefinition.DetailArea.Sections(i).

ReportObjects.Count() – 1
‘This loop will get suppression logic for each report object in the i-th section
thisObject =  rasClientDoc.ReportDefController.ReportDefinition.DetailArea.Sections(i).ReportObjects(k)
reportDataRow = ReportFormulaTable.NewRow()
reportDataRow(“Report Name”) = rdoc.FileName
‘Formula name shall be in the form Detail-1-Textbox23-Suppression_logic
reportDataRow(“Formula Name”) = “Detail-” & i.ToString() & “-” &
rasClientDoc.ReportDefController.ReportDefinition.DetailArea.Sections(i).ReportObjects(k).Name + “-Suppression_logic”
reportDataRow(“Return Type”) = “Boolean”
reportDataRow(“Details”) =   thisObject.Format.ConditionFormulas.Formula(CrystalDecisions.ReportAppServer.

ReportDefModel.CrObjectFormatConditionFormulaTypeEnum.

crObjectFormatConditionFormulaTypeEnableSuppress).Text()
reportDataRow(“Type”) = “Suppression Logic”
If Not IsDBNull(reportDataRow(“Details”)) Then
ReportFormulaTable.Rows.Add(reportDataRow)
End If
Next k
Next i

‘clean up
rdoc.Close()
rdoc.Dispose()

End Sub 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply