Solved

DataSource and Parameters

Posted on 2002-07-22
14
717 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
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 100

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
 

Author Comment

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

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 100

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 100

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 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

16 Experts available now in Live!

Get 1:1 Help Now