Solved

DataSource and Parameters

Posted on 2002-07-22
14
718 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# Crystal Reports Formula 6 79
Cross-tab sort rows by field not displayed 6 50
cross tab report accumulative amount 9 45
business objects to connect to MSSQL 6 61
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 …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 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

19 Experts available now in Live!

Get 1:1 Help Now