Solved

Parameters to a Crystal Report

Posted on 2002-05-16
6
522 Views
Last Modified: 2010-05-19
I have an app written in VB6, and a Crystal Report in version 8.5 .  The report has some parameter fields, which when set up had some data attached.  This is so the report can pass those parameters to a SQL Server 2000 Stored Procedure in order to get the data back.  How do I pass new parameter values for those fields from my VB app?

I include References in my VB project to the Crystal Reports 8.5 Libary, and ActiveX Library. but unsure if I need any others, or if there is a better method.  If this is a better way of doing it, please let me know.  Also, please supply VB code.

I apologise if I've not explained this properly, so feel free to ask questions.  I may also up the points, depending on the complexity of the answer etc.

Thanks alot, Adi
0
Comment
Question by:adrianjohnson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:carpediem
ID: 7014148
I only pass selection queries and formulas to my crystal reports, but I assume it is very similar to passing formulas.

I'd try this:
IPRPT.ParameterFields(0) = "Parm1=2"

Where IPRPT is the name of the CR activeX control on the form and Parm1 is the name of the CR parmater.  If you need to set another parmater it would be the same excpt use ParameterFields(1) and keep incrementing the value of ParameterFields for each parameter.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7014234
Good guess but the format is a little different

IPRPT.ParameterFields(0) = "Parm1=2"


IPRPT.ParameterFields(0) = "ParmName;Value;True"

IPRPT.ParameterFields(0) = "Parm1;2;TRUE"

mlmcc
0
 
LVL 1

Accepted Solution

by:
Brighton earned 100 total points
ID: 7014238
Here is a little VB program that may answer your question

Dim crxApplication As New CRAXDRT.Application

Private Report As CRAXDRT.Report

Private Sub Form_Load()

    '
    ' This program expects at lest 3 parameters to be passed to it
    ' and the program first parses the command line into a ParameterArray
    '
    ' ParameterArray(1)   = Report to run
    ' ParameterArray(2)   = Output destination
    ' ParameterArray(3)   = What format is the output saved as
    ' ParameterArray(4+N) = Parameters that are passed to the crystal
    '                       report in the same order they are listed
    '                       in the report.
    '
    ' The program them sets any parameters the are passed in to it and places
    ' these values in the parameters that are in the report.
    '
   

    On Error GoTo ErrorHandle
    Dim linenbr As Integer
   
    Dim ParameterArray() As String
    Dim OutFile          As String      'ParameterArray(2)
    Dim OutputFormat     As Integer     '0 = No Format
                                        '1 = Crystal Report Format
                                        '8 = Text
                                        '31 = PDF
   
   
    Dim RC As Integer
    Dim Pcnt As Integer
    '
    ' Fill the array with the command line passed in
    ' using the comma as a deliminator between fields.
    '
    RC = FillArray(Command, ",", ParameterArray())
         
    Set crxApplication = New CRAXDRT.Application
   
    Dim crxParameterField As CRAXDRT.ParameterFieldDefinition
    Dim crxparameterFields As CRAXDRT.ParameterFieldDefinitions
   
    '
    ' The first parameter passed in is the name of the
    ' report to run
    '
   
    Set Report = crxApplication.OpenReport(Trim(ParameterArray(1)))
    Report.DiscardSavedData
   
    '
    ' The second parameter is the location where the
    ' the output file is to go
    '
    OutFile = Trim(ParameterArray(2))
   
    Pcnt = Report.ParameterFields.Count
    If Pcnt > 0 Then
        Set crxParameterField = Report.ParameterFields.Item(1)
    End If
   
 
    '
    ' Log on to the database
    '
    For i = 1 To Report.Database.Tables.Count
        Report.Database.Tables(i).SetLogOnInfo "", "", "<<Use Integrated Security>>", ""
    Next i
   
   
    If Pcnt > 0 Then
        crxParameterField.EnableMultipleValues = True
        Call crxParameterField.ClearCurrentValueAndRange
    End If

    '
    ' Read each parameter from the report and populate
    ' them in the same order they are read in from
    ' the command line.
    '
    'crxParameterField.EnableNullValue = True
    Dim crnull As Variant
    For Each crxParameterField In Report.ParameterFields
        crxParameterField.AddCurrentValue crnull
    Next
   
    Pcnt = Report.ParameterFields.Count
    If Pcnt > 0 Then
        For x = 1 To Pcnt
          If ParameterArray(x + 3) = "" Then
             Report.ParameterFields(x).AddCurrentValue crnull
          ElseIf Report.ParameterFields(x).ValueType = crNumberField Then
             Report.ParameterFields(x).AddCurrentValue CDbl(Trim(ParameterArray(x + 3)))
          Else
             Report.ParameterFields(x).AddCurrentValue CStr(Trim(ParameterArray(x + 3)))
          End If
        Next x
    End If
   
    Select Case Trim(CInt(ParameterArray(3)))
     Case Is = 0
      OutputFormat = 0  ' No Format
     
     Case Is = 1
      OutputFormat = 1  ' Crystal Report Format
     
     Case Is = 8
      OutputFormat = 8  ' Text
     
     Case Is = 31
      OutputFormat = 31 ' crEFTPortableDocFormat (PDF)
     
    End Select
   
    If OutputFormat = 0 Then
        RptName.Caption = Trim(ParameterArray(1))
        Form2.Show
    Else
   
        Report.DisplayProgressDialog = False
        Report.ExportOptions.DestinationType = crEDTDiskFile
        Report.ExportOptions.FormatType = OutputFormat
        Report.ExportOptions.DiskFileName = OutFile
        Report.Export False
    End If

    Err.Number = 0
    Unload ExecCR
    Exit Sub
   
   
ErrorHandle:
   
    Label1.Caption = linenbr
    Label2.Caption = Err.Number
    Label3.Caption = Err.Description
    Debug.Print Err.Number
   
    Err.Number = 1
    Unload ExecCR
   
   
End Sub


Function FillArray(CommandLineIn As String, Delimiter As String, aValues() As String) As Integer

     Dim ArrayCount As Integer
     Dim CurrentPosInArray As Integer
     Dim LenghtOfVariable As Integer
     Dim StringLength As Integer

     ArrayCount = 1
     CurrentPosInArray = 1
     LenghtOfVariable = 1

     StringLength = Len(CommandLineIn)
     Do
           ReDim Preserve aValues(1 To ArrayCount) As String

           StringLength = (InStr(CurrentPosInArray, CommandLineIn, Delimiter) - CurrentPosInArray)

           If StringLength < 0 Then

                 aValues(ArrayCount) = Right$(CommandLineIn, (Len(CommandLineIn) - (LenghtOfVariable - 1)))

                 Exit Do

           Else

                  aValues(ArrayCount) = Mid$(CommandLineIn, CurrentPosInArray, StringLength)

           End If

           LenghtOfVariable = LenghtOfVariable + (Len(aValues(ArrayCount)) + Len(Delimiter))
           CurrentPosInArray = LenghtOfVariable
           ArrayCount = ArrayCount + 1

     Loop
     If ArrayCount = 2 Then
        ArrayCount = 3
        ReDim Preserve aValues(1 To ArrayCount) As String
        aValues(3) = 8
        'aValues(4) = 1
     
     End If

     FillArray = UBound(aValues)

End Function

0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 101

Expert Comment

by:mlmcc
ID: 7014239
A little clarification

The generic version
IPRPT.ParameterFields(0) = "ParmName;Value;True"

Example based on the supplied code.
IPRPT.ParameterFields(0) = "Parm1;2;TRUE"

mlmcc
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7014652
The easiest way to run a crystal report from VB, and to set parameters is to use a Crystal Report Control.  So, under the Project|Components, you should pick the Crystal Report Control (viewer, whatever).

Then, in your code you'd have something like:

Report1.ReportFile = "C:\Code\MyRpt.rpt"
Report1.StoredProcParams(0) = 123
Report1.StoredProcParams(1) = chr$(34) & "Jones" & chr$(34)
Report1.Action = 1


The first question is about the nature of the parameters you mentioned.  In Crystal, there are two different kinds of parameters.  If you based your report on a stored proc, and the stored proc has parameters, then Crystal Creates StoredProcParams, which can be set using the code above.

If the developer went into Crystal and declared some Parameters there, then as mentioned above, I think this is referenced by Report1.ParametersFields(0)

My preference is not to work with Crystal's parameters, but only work with the ones generated for the Stored Procs Parameters.  When filling these parameters, make sure that they are formatted appropriately for the type of database you're accessing.  I wrapped the text parameter in double-quotes above, and the easiest way to do this is by appending the chr$(34).
0
 

Author Comment

by:adrianjohnson
ID: 7015799
Thanks for the code.  Got the report to work now, thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month4 days, 13 hours left to enroll

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question