[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

DataSource and Parameters

Posted on 2002-07-22
14
Medium Priority
?
727 Views
Last Modified: 2008-02-01
Hi,

Below is a simple example on showing a Crystal Report:

Option Explicit
Dim fixedHeight As Long

Private Sub Command1_Click()
    On Error GoTo EH
    Dim myApp As New CRAXDRT.Application
    Dim myRpt As New CRAXDRT.Report
    Dim tmp As String
   
    tmp = OpenDialog(Me, "*.rpt" + Chr$(0) + "*.rpt", "Select a Crystal Report", dlgOpen, App.Path)
   
    If tmp <> "" Then
        Set myRpt = myApp.OpenReport(tmp)
       
        CRViewer1.ReportSource = myRpt
       
        CRViewer1.DisplayGroupTree = False
        CRViewer1.DisplayTabs = False
        'CRViewer1.DisplayToolbar = False
        CRViewer1.DisplayBackgroundEdge = False
        CRViewer1.DisplayBorder = False
        CRViewer1.EnableGroupTree = False
       
        CRViewer1.ViewReport
       
    End If
   
    Set myRpt = Nothing
    Set myApp = Nothing
    Exit Sub
EH:
    ShowErrMsg
    Set myRpt = Nothing
    Set myApp = Nothing
End Sub

Private Sub Form_Load()
    Command1.Caption = "Open a report"
    fixedHeight = Me.Height - CRViewer1.Height
End Sub

Private Sub Form_Resize()
    CRViewer1.Width = Me.Width - 120
    CRViewer1.Height = Me.Height - fixedHeight
    Command1.Top = Me.Height - fixedHeight + 240
    Command2.Top = Me.Height - fixedHeight + 240
End Sub


My question is how to change the DataSource of the Crystal Report so that only specify records are shown in the report displayed. I also know that Crystal Report can create Parameters. I have created some of the parameter(s), so how can i use this parameter so that i can 'filter' the records displayed in the report.

Let say the SQL Query is:

SELECT DISTINCT
Institutions_Query.Ins_id, Institutions_Query.Name, Institutions_Query.Add1, Institutions_Query.Pos_code, Institutions_Query.Ins_type, Institutions_Query.Ins_Zone, Institutions_Query.Ctry_name, Institutions_Query.Remarks FROM Institutions Query ORDER BY Institutions_Query.Name ASC

So, the question is:

1. How to change the SQL Query of a Crystal Report? Or/And

2. How to implement Parameters in Crystal Report? (Let's say there is a Parameter named 'INS_CTRY' which filter the report by Institutions_Query.Ctry_name)

Note: I'm using Crystal Report 8.0


Thank you.
0
Comment
Question by:trowa
[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
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 7169304
There is no need for a parameter.  You can change the selection formula

CRViewer1.RecordSelectionFormula = "{Institutions_Query.Ctry_nameInstitutions_Query.Ctry_name} = 'Canada'"

You could do something like

CRViewer1.RecordSelectionFormula = "{Institutions_Query.Ctry_nameInstitutions_Query.Ctry_name} = " & Users_Choice

good luck
mlmcc
0
 

Author Comment

by:trowa
ID: 7179589
Hi mlmcc,

I add this line to make the code working:

myRpt.RecordSelectionFormula = "{Institutions_Query.Ctry_name} = '" & ctryValue & "'"

Thank You!

By the way, for research, can you tell me how can i:

1. How to change the SQL Query of a Crystal Report? And
2. How to implement Parameters in Crystal Report? (Let's say there is a Parameter named 'INS_CTRY' which filter the report by Institutions_Query.Ctry_name)

I'm willing to add some more points if you show me how to do these. Thank you again.
0
 

Author Comment

by:trowa
ID: 7192794
Any comments?
0
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

 

Author Comment

by:trowa
ID: 7192803
Any comments,mlmcc?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7195228
Sorry I have been out of town with no web access.  I'll try to catch up with this tomorrow since I don't have CR loaded and can't answer your other questions without referencing it.

mlmcc
0
 

Author Comment

by:trowa
ID: 7219130
Hi mlmcc,

Again, For research, can you tell me how can i:

1. How to change the SQL Query of a Crystal Report? And
2. How to implement Parameters in Crystal Report? (Let's say there is a Parameter named 'INS_CTRY' which filter the report by Institutions_Query.Ctry_name)

More pts will be awarded if i know the solution of above. TQs.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7223267
1. How to change the SQL Query of a Crystal Report?
Unfortunately you cannot change the SQL query from VB.  You can only change the WHERE and ORDER BY clauses.

2. How to implement Parameters in Crystal Report?
Here is how I do it

Search the list of parameters for the parameter name you want to set
Add the value

For i = 1 to myrpt.ParameterFields.count
  if myrpt.ParameterFields(i).name = "INS_CTRY" then
    myrpt.ParameterFields(i).AddCurrentValue "CityName"
Next i

Obivously if you have only 1 parameter you can use
    myrpt.ParameterFields(1).AddCurrentValue "CityName"

mlmcc
0
 

Author Comment

by:trowa
ID: 7224393
Thanks a lot, mlmcc. Will try it later this week.
0
 

Author Comment

by:trowa
ID: 7238052
Hi mlmcc,

This is want i tried so far..

Dim tmp As String
Dim myApp As New CRAXDRT.Application
Dim myRpt As New CRAXDRT.Report
   
Private Sub Command1_Click()
    On Error GoTo EH
    'Dim myApp As New CRAXDRT.Application
    'Dim myRpt As New CRAXDRT.Report
   
    tmp = OpenDialog(Me, "*.rpt" + Chr$(0) + "*.rpt", "Select a Crystal Report", dlgOpen, App.Path)
   
    If tmp <> "" Then
   
        Set myRpt = myApp.OpenReport(tmp)
        'myRpt.RecordSelectionFormula = "{Institutions_Query.Ctry_name} = 'Taiwan'"
       
        'For i = 1 To myRpt.ParameterFields.Count
        '    Debug.Print myRpt.ParameterFields(i).Name
        'Next i
       
        Debug.Print myRpt.SQLQueryString
       
        CRViewer1.ReportSource = myRpt
       
        CRViewer1.DisplayGroupTree = False
        CRViewer1.DisplayTabs = False
        'CRViewer1.DisplayToolbar = False
        CRViewer1.DisplayBackgroundEdge = False
        CRViewer1.DisplayBorder = False
        CRViewer1.EnableGroupTree = False
       
         ' CRViewer1. .SearchByFormula ("{Institutions_Query.Ctry_name} = 'Canada'")
       
        CRViewer1.ViewReport
       
    End If
   
    'Set myRpt = Nothing
    'Set myApp = Nothing
    Exit Sub
EH:
    ShowErrMsg
    'Set myRpt = Nothing
    'Set myApp = Nothing
End Sub

Private Sub Command3_Click()
    Set myRpt = myApp.OpenReport(tmp)
       
    myRpt.ParameterFields(1).AddCurrentValue "Taiwan"
   
    CRViewer1.ReportSource = myRpt
       
    CRViewer1.DisplayGroupTree = False
    CRViewer1.DisplayTabs = False
    'CRViewer1.DisplayToolbar = False
    CRViewer1.DisplayBackgroundEdge = False
    CRViewer1.DisplayBorder = False
    CRViewer1.EnableGroupTree = False
End Sub


The crystal report shows correctly went i clicked on Command1, however when i try click the Command3, the whole report become blank! Any reason why??

And FYI, myRpt.ParameterFields(1).Name equal to (?Ctry_name)

Thank you.

increase pts to 100.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7240179
You left this line out of command 3

       CRViewer1.ViewReport


mlmcc
0
 

Author Comment

by:trowa
ID: 7258337
I tried the method on other crystal report, different table this time.

This time, the crystal report got 2 parameter fields, that are:
{?ID}
{?Name}

When i tried to filter the records, no the expected records are shown, instead all records are shown.

Private Sub Command3_Click()
    Set myRpt = myApp.OpenReport(tmp)
       
    myRpt.ParameterFields(1).AddCurrentValue 1
   
    CRViewer1.ReportSource = myRpt
    CRViewer1.ViewReport
End Sub

The ID should be the value between 1 - 100, for example. But all records are shown, not the record  with ID = 1, why?
0
 
LVL 7

Expert Comment

by:mnye
ID: 9004527
This question has been classified abandoned. I will make a recommendation to the moderators on its resolution in a week or two. I appreciate any comments that would help me to make a recommendation.

Unless it is clear to me that the question has been answered I will recommend delete. It is possible that a Grade less than A will be given if no expert makes a case for an A grade. It is assumed that any participant not responding to this request is no longer interested in its final disposition.

If the user does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

mnye
EE Cleanup Volunteer
0
 
LVL 7

Expert Comment

by:mnye
ID: 9218485
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/Refund

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
mnye
EE Cleanup Volunteer
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 9270624
PAQed per request/recommendation & Points refunded

Lunchy
Friendly Neighbourhood Community Support Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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