Solved

Crystal Report & Stored Procedure & VB

Posted on 2003-10-27
16
84,233 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
[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
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 9630382
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
ID: 9630446
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
ID: 9637758
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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
LVL 12

Expert Comment

by:DRRYAN3
ID: 9637952
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
ID: 9638010
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
ID: 9639527
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
ID: 9642677
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
ID: 9643309
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
 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 500 total points
ID: 9654557
Anything else you need?
0
 

Author Comment

by:tina69
ID: 9681672
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
ID: 9681888
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
ID: 11092211
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
ID: 11092247
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
ID: 14993745
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
ID: 14994416
This question is TWO YEARS OLD

Open a new question an cough up some points
0
 

Expert Comment

by:APHComputersLtd
ID: 14998345

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

729 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