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  
 

Thank You DRRYAN3 fo quick response,

Question:

For report source
CRViewer1.ReportSource = Report

What is a best way to implement Crystal Reports...
Have Reports on a network and specify network location for reports or
bring reports into VB front-end and have it as part of the application?

I'll have multiple user accessing application and reports

Thanks

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.

I'm Sorry, forgot to clarify one more thing

Before running reports user will be prompted to enter begindate and enddate(Format on a back end as date and time).  What exactly will be in
.SetCurrentValue "PARAM1 VALUE"? txtBeginDate and txtEndDate for "PARAM1 VALUE"?

Thank You

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.

DRRYAN3,

What are you using for References and Components cause I've tried to use your sample code and it's giving me a "User defined type not defined" on line:
'
Dim crpParamDefs As CRAXDRT.ParameterFieldDefinitions
'

Also, can I pass another parameter not from Data entry form but internaly. Basically I will have 3 parameters: 2 parameters(begindate & enddate) will be entered by user, third parameter will
be ID (global variable m_ID will hold that value). How can I pass that ID?  

Can you please also explain a little more what is the difference between when passing parameters to stored procedure and when passing parameters to a report.

For example, I want to display my parameters values on a report what syntax do I need to use?

Thank You

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.

Thank you DRRYAN3 for all your help, you've been very helpful!

I've been wondering for a while now, it looks like anytime you create CR report that  using Stored Procedure, this report creates a DB connection. So if you have 10 reports that you need to run you'll have 10 connections? Is that true? If it is, is it possible to create one connection(from VB code for example) for all reports?
Also can I use one VB form to pass parameters to all my reports and to use one CRViewer for all reports. Or for each report I wpuld need to use different CRViewer?

Thanks again!

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

I can't use the Keyword "SET" in the folling statement

set crRept = crApp.OpenReport("WHATEVER.RPT")

WHenever I type it VB will autodelete it.

I can't use the Keyword "SET" in the following statement

set crRept = crApp.OpenReport("WHATEVER.RPT")

WHenever I type it VB will autodelete it.

So I'm getting the same error that tina69 was getting on 10/29/03

Dear All,
I am using the above method to pass the variables to the report. (.SetCurrentvalue). After running the report I receive nothing except the column headers in the report.
Checked the parameters report on the report object and the currentvalue property shows the value I am passing but the value property is empty, not sure whether that needs to be set as well although in the above code example the value property hasn't been set.
Please help as I am quite behind the time scheduling of the project.
Regards,

D.

This question is TWO YEARS OLD

Open a new question an cough up some points

DRRYAN3,
Thanks for paying attention to this question.
I opened a new question with the following title "Question Title: Empty screen when running a Crystal Report viewer in Visual basic by passing parameters settings", but i thought could be helpful if i add some notes to the end of this old question as well.

Regards,
D.