asked on

How sort report based upon parm?

Basics: vb6 sp5, cr8.0, W/2000.

I have written a Visual Basic program that calls a Crystal Report and passes various parameters to it. One of these parms is the sort parm. In CR I want the report to sort in one of three ways based upon the passed parm from vb.

I thought that creating a formula that would interogate the parm and point to the field to be sorted would work. But it gives me errors.

For example, here is the code in a formula in CR that uses the Select Case method:

Select Case {?mSort_Opt_Parm}
Case "Invoice #"
formula = {Reporting.Inv_No}
Case "Invoice Date"
formula = {Reporting.Inv_Date}
Case "Delivery Date"
formula = {Reporting.Del_Date_Sort}
End Select

This code generates an error. I swapped out the Select Case with an If, ElseIf etc. code and it does not work either. Here is that code:

If {?mSort_Opt_Parm} = "Invoice #" Then {Reporting.Inv_No}
ElseIf {?mSort_Opt_Parm} = "Invoice Date" Then {Reporting.Inv_Date}
Else {Reporting.Del_Date_Sort}

I am trying to come up with a formula that points to the field I want the report to sort on, i.e. {Reporting.Inv_No}. With this type of field, I would only have to add it to the sort dialog box and the report would supposedly sort in the correct sequence based upon the passed parm. Should I be doing this differently?

Question: How do I sort a report based upon a passed parameter?

Thanks for the help.

Mike McCracken

How are you calling the report from VB. CR provides a sort field array to use.

Here is the code I use

Public Sub preview_Report_from_View_with_Sort(txt_Report_File_Name As String, _
txt_Record_Selection_Formula As String, _
txt_Sort_Fields() As String, _
int_Sort_Field_Count As Integer)
Dim i As Integer
Dim rpt_title As String
cr_Generic_Report.WindowTitle = rpt_title

cr_Generic_Report.ReportFileName = txt_Report_File_Name
cr_Generic_Report.ReplaceSelectionFormula txt_Record_Selection_Formula
For i = 0 To int_Sort_Field_Count - 1
cr_Generic_Report.SortFields(i) = txt_Sort_Fields(i)
Next i
cr_Generic_Report.WindowState = crptMaximized
cr_Generic_Report.Destination = crptToWindow
cr_Generic_Report.WindowShowCloseBtn = True
cr_Generic_Report.Action = 1
cr_Generic_Report.Reset

End Sub

The sort fields are strings like {view_rpt_basic.student_id}

If you need more information let me know.
good luck

mlmcc

EYoung

ASKER

mlmcc,

Here is the code I use in VB to call the report:

Private Sub Form_Activate()
Set Report = New dsrDetail_and_Summary

Report.EnableParameterPrompting = False

Report.ParameterFields(1).AddCurrentValue mTeam_No_Parm
Report.ParameterFields(2).AddCurrentValue mTeam_Name_Parm

If mTeam_No_Parm <> 0 Then
Report.RecordSelectionFormula = "{Reporting.mKey} = '" & mKey2 & "'"
End If

Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.DisplayTabs = False
CRViewer1.DisplayToolbar = True
CRViewer1.EnableGroupTree = False
CRViewer1.EnableExportButton = True
CRViewer1.ViewReport

Screen.MousePointer = vbDefault
End Sub

------------------------------------------

The report has two levels of grouping and then one of three fields make up the third sort field. For example, the sort fields could be Group1, Group2, Invoice_No. Or the sort fields could be Group1, Group2, Invoice_Date.

Would I add the .SortFields(i) as follows:

CRViewer1.SortFields(1) = Group1
CRViewer1.SortFields(2) = Group2
CRViewer1.SortFields(3) = Invoice_No

Thnaks for the help.

EYoung

ASKER

I tried doing the example above and it did not have any effect on the sort sequence of the report.

Would appreciate any help. Thanks.

EYoung

ASKER

Oops, I should have said I tried:

Report.ReportSortFields(1) = ...

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EYoung

ASKER

Thanks for the help.