Crystal Report & Stored Procedure & VB

Start by going to File-Options, Database and check the box for Stored Procedures.

Your stored procedure should return a single recordset, most easily achieved by having the final statement in the stored procedure be a select statement of some kind.

I've found the best performance using the Crystal Reports "Microsoft SQL Server" data source, available under "More Data Sources" in the data explorer displayed when you create a new report.

Select your stored procedure from the list of stored procedures (if yours is not displayed, you have a permissions issue on the SQL server).

As far as VB goes, the only change you'll need to make is how your parameters are named.  If your stored procedure takes a parameter named @PARAM1, then Crystal Reports will name it {?@PARAM1} and that is how you need to reference it when you pass the parameters to CR from VB.

I'll dig up a code example in a minute.

You'll need to adapt this to your situation, but this is one way to pass parameters:

Private Sub Command1_Click()
  Dim crpParamDefs As CRAXDRT.ParameterFieldDefinitions
  Dim crpParamDef As CRAXDRT.ParameterFieldDefinition
  Dim crpSubreport As CRAXDRT.Report
  Set crpParamDefs = Report.ParameterFields
  For Each crpParamDef In crpParamDefs
    With crpParamDef
    Select Case .ParameterFieldName
      Case "@PARAM1"
        .SetCurrentValue "PARAM1 VALUE"
      Case "@PARAM2"
        .SetCurrentValue "PARAM2 VALUE"
    End Select
    End With
  Next
  Report.EnableParameterPrompting = False
  CRViewer1.ReportSource = Report
  CRViewer1.ViewReport
End Sub  
 

If you incorporate the reports into the application, then if you need to make minor changes to report layouts, you'll have to redistribute the entire application.  I prefer to keep the .RPT files seperate and in a shared location.  Keeps things a little cleaner in the IDE while developing too.

If you are passing parameters to Crystal Reports use datevalue(txtBeginDate) to pass a date parameter in.  If you were passing them straight into the SQL stored procedure, the text value alone would be fine.

txtBeginDate should be stored in one of the predefined system date formats - I always use mm/dd/yyyy or mm-dd-yyyy.

Your error message is probably caused by a missing SET on the crParamDefs.  See the example below:

References:

Crystal Reports Viewer Control
Crystal Reports ActiveX Designer Run Time Library

' Typical Variable Declarations
dim crApp as CRAXDRT.Application
dim crRept as CRAXDRT.Report
dim crParamDefs as CRAXDRT.ParameterFieldDefinitions
dim crParamDef as CRAXDRT.ParameterFieldDefiniton
dim crDBTab as CRAXDRT.DatabaseTable

' Open Report File
set crRept = crApp.OpenReport("WHATEVER.RPT")

' Logon to SQL server
crRept.Database.LogonServer "p2ssql.dll", "server name", "database name", "userid", "userpassword"

' Set table locations (because my reports run against multiple servers)
foreach crDBTab in crRep.Database.Tables
  crDBTab.SetLogonInfo "server name", "database name", "userid", "userpassword"
next

' Disable Parameter Prompting for the end user
crRep.EnableParameterPrompting = FALSE

' Gather the list of available parameters from the report
set crParamDefs = crRep.ParameterFields

' Loop through all parameters in the report by name, filling in the appropriate parameter with the right value
foreach crParamDef in crParamDefs
  select case crParamDef.ParameterFieldName
    case "SubTitle"
      crParamDef.SetCurrentValue "My Report Subtitle Goes Here"
    case "@BeginDate"
      crParamDef.SetCurrentValue datevalue(txtBeginDate)
    case "@EndDate"
      crParamDef.SetCurrentValue datevalue(txtEndDate)
    case "@IntegerParam"
      crParamDef.SetCurrentValue val(int(txtIntegerParam))
  end select
next

crViewer1.viewReport

In this example, your stored procedure takes three parameters:  @BeginDate, @EndDate and @IntegerParam.  You need do nothing in Crystal Reports to define these parameters.  The report itself is using an additional parameter, which you must create in Crystal Reports, named SubTitle.  I'll use something like this do put a summary of the parameters used at the top of the report.  Use the field placement tool to drop the parameter field on your report just like any other field.  If you want to further modify the parameters (to display a datetime as text, for example), you'll have to create a formula field which does the conversion and concatenation and then place the formula field on the report.

You can pass any value you want to CR, whether the user typed it or your program generated it in this way.

One of the few times you would want to pass parameters straight to the stored procedure is if you want to create a temporary table to base your report on instead of using a recordset returned by the stored procedure.  Doesn't come up often, but sometimes it's faster.

You really have no control over the connection use in CR version 8.5.  I haven't really gotten into version 9 yet, but I'll be curious to see if Crystal takes advantage of the connection pooling .Net offers.  I rather doubt it.

As to your second question, you can do pretty much anything you want to do.  For example, I have a single VB app which is really nothing more than a shell containing a menu, a couple of procedures to run and export reports based on parameter entries, etc stored in a database, and an MDI child form containing a viewer.  This allows my users to open as many reports as they have memory for at one time.  Technically, each report is getting a copy of the viewer, but they are created and destroyed on the fly as the user runs reports and closes the preview window.

Glad to help

This question is TWO YEARS OLD

Open a new question an cough up some points