Solved

Crystal Report & Stored Procedure & VB

Posted on 2003-10-27
16
84,065 Views
Last Modified: 2012-06-21
Hello,

I need advice on how to implement Crystal Reports within VB application using SQL Server 2000 as back-end.

I have stored procedure that I intend using with my Crystal Report. What I want to do is to use VB forms to pass parameters to my Report, what is easiest way to do this?

Thank you for all your help!


PS
I'm using VB6 and Crystal 8.5
0
Comment
Question by:tina69
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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  
 
0
 

Author Comment

by:tina69
Comment Utility
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



0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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.
0
 

Author Comment

by:tina69
Comment Utility
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


0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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.
0
 

Author Comment

by:tina69
Comment Utility
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



0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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.

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 500 total points
Comment Utility
Anything else you need?
0
 

Author Comment

by:tina69
Comment Utility
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!


0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
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
0
 

Expert Comment

by:alien_alan
Comment Utility
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.
0
 

Expert Comment

by:alien_alan
Comment Utility
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
0
 

Expert Comment

by:APHComputersLtd
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
This question is TWO YEARS OLD

Open a new question an cough up some points
0
 

Expert Comment

by:APHComputersLtd
Comment Utility

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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now