crystal parameters

[thomas eicondev]

i have a crystal report that has 4 parameters on it (par 1 = customer, par 2 = division, par 3 = salesman, par 4 = company). if i fill the parameters with valid data i get the proper result set. my issue is if i leave a parameter blank i get no result set. is there a way i can run the report and skip 1 or more parameters. or can i set a parameter as optional on the report? i do not want to use a selection formula. the table i am using has close to 400 000 records and i know that a selection formula will load everything and then filter down. this process takes way to long. can someone please help.

Thank You,
THomas

[RobDog888]

You could use an ADO recordset with a query that takes 0 - 4 parameters.
Then set the rs as the .DataSource for the report. Allot faster and your only
using what you actually need.

In CR setup the report so it has all the controls on it that you need, bound to
the correct fields. Then when you create your rs if parameters are passed or
not, CR report will be isolated.

HTH

[thomas eicondev]

how do you do that? do you mean linking my report to a recordset instead of a data source (odbc).can you send me an example or a place to look.

Thank you

[Malim]

There is a good walkthrough titled "Reporting Off ADO Datasets" in VB.Net help. Type "crystal dataset" to search VB.Net help and it will come up in the results.

Basically, you have to create your dataset, then connect to the dataset object then set up the report pointing to the dataset instead of your database. This is just the structure of the dataset. It does not include data. You have to fill the dataset with data using the data adapter.

[thomas eicondev]

Thanks but I'm using VB6...

I finally got it working by linking the report to a TTX file... RobDog, is this what you were suggesting? Now i need to figure out how to deal with sub reports, do i have to link the sub report to it's own TTX file?

Also, is this best practice when working with VB and MSSQL SERVER taking into consideration that i needs flexible parameters that sometimes get used and other times don't?

[RobDog888]

A ttx file is also an option, but I havent used them before.

Here are a few links to some of my VB6 examples for you.
http://www.vbforums.com/showthread.php?t=316865
http://www.vbforums.com/showthread.php?t=316267
http://www.vbforums.com/showthread.php?t=303105

And this one is for .NET
http://www.vbforums.com/showthread.php?t=322513

HTH

[thomas eicondev]

thanks for the quick reply, you said you never used a ttx file. i though the only way to pass a rs to a rpt is if you rpt is created using a ttx file instead of a odbc datasource. i am using crystal 8.5 what version are you using? itred to run a report with the code from one of the links you gave me and i get the following message "server not yet open". what is this? and how do i get this report to preview to screen instead of going straight to the printer?

code start:----------------

Private Sub Command1_Click()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str1 As String
Dim moReport As CRAXDRT.Report

Set CrxApp = New CRAXDRT.Application
Set moReport = New CRAXDRT.Report

sServer = "gs3zt01"
sDatabase = "milehighdvd"
sUserName = "sa"
sUserPwd = "eicon_dev"

Set moReport = CrxApp.OpenReport("c:\program files\eicon\reports\Salesman_Commission_Summary new.rpt", 1)

moReport.Application.LogOnServer "P2SSQL.DLL", sServer, sDatabase, sUserName, sUserPwd
moReport.Database.Tables(1).SetLogOnInfo sServer, sDatabase, sUserName, sUserPwd

Call modDatabase.SetDatabases(cnn, 1)

str1 = "select * from Report_Qry_Salesman_Commission_Summary"
rs.CursorLocation = adUseServer
rs.Open str1, cnn, adOpenForwardOnly, adLockOptimistic, adCmdText

moReport.Database.SetDataSource rs, 3, 1
moReport.DisplayProgressDialog = True
moReport.PrintOut True, 1

Call modDatabase.CloseDatabases(cnn)

End Sub
code stop:----------------

[RobDog888]

What are you using for your database?

[thomas eicondev]

i'm using vb6, sql server 2000, and crystal reports 8.5

[RobDog888]

Ok, I have all of those and the same versions. So, does your report contin a subreport?

[thomas eicondev]

this report doesn't but i will have some reports that will have one or more subreports. is that an issue?

[RobDog888]

No, not really. It just means that you may have to log in to other tables.
If I get some tim eI will put together a small demo for you.

[thomas eicondev]

thanks that would be perfect, you da man robdog888

[RobDog888]

Did you want to use the CR Report Viewer Control for a print preview option?

[thomas eicondev]

the crviewer would be just fine.

[RobDog888]

If your report has ADO as a datasource then this should work with it.
I didnt have enough time to completely test it against my servers but I figure
there may be issues anyways. So give it a try. This assumes that you do not
have a subreport.

Option Explicit
'Add references to:
'Crystal Reports Viewer Control
'Crystal Reports 8.5 ActiveX Designer Run Time Library
'Crystal Reports 8.5 Library
'Microsoft ActiveX Data Objects 2.x Library
Private moApp As CRAXDRT.Application
Private moReport As CRAXDRT.Report
Private moCnn As ADODB.Connection
Private moRs As ADODB.Recordset
 
Private Sub Command1_Click()
    Set moRs = New ADODB.Recordset
    moRs.Open sSQL, moCnn, adOpenKeyset, adLockOptimistic, adCmdText
    If moRs.BOF = True And moRs.EOF = True Then
        MsgBox "No Record(s) returned!", vbOKOnly + vbExclamation
    Else
        Dim sUserName As String
        Dim sUserName As String
        Dim sUserPwd As String
        Dim sServer As String
        Dim sDatabase As String
        Dim sPara1 As String
        Dim sPara2 As String
        Set moReport = New CRAXDRT.Report
        Set moReport = moApp.OpenReport(App.Path & "\MyReport.rpt", 1)
        sUserName = "MyUserName"
        sUserPwd = "mypassword"
        sServer = "ServerName"
        sDatabase = "DatabaseName"
        sDriver = "P2SSQL.DLL"
        moApp.LogOnServer "P2SSQL.DLL", sServer, sDatabase, sUserName, sUserPwd
        moReport.Database.Tables(1).SetLogOnInfo sServer, sDatabase, sUserName, sUserPwd
        moReport.ParameterFields.Item(1).ClearCurrentValueAndRange
        moReport.ParameterFields.Item(1).AddCurrentValue sPara1
        moReport.ParameterFields.Item(2).ClearCurrentValueAndRange
        moReport.ParameterFields.Item(2).AddCurrentValue sPara2
        moReport.Database.SetDataSource moRs, 3, 1
        CRViewer1.ReportSource = moReport
        CRViewer1.ViewReport
        CRViewer1.ShowFirstPage
    End If
End Sub
 
Private Sub Form_Load()
    Dim sSQL As String
    Dim sDB As String
    Dim sServer As String
    sSQL = "SELECT * FROM Table1 WHERE Something = SomethingElse;"
    sDB = "MyDatabase"
    sServer = "MyServer"
    Set moCnn = New ADODB.Connection
    moCnn.ConnectionString = "provider=sqloledb;data source=" & sServer & ";initial catalog=" & sDB & ";integrated security=sspi;"
    moCnn.Open
End Sub

[thomas eicondev]

the code worked and it was extremely quick. the only thing is i will need to have reports that have one or more subreports. how do i go about that? what mods to the existing code do i need to make in order for this to work.

once again thanks a lot.

Thomas

[RobDog888]

I was hoping that that would bnot come up because I have not had too much success with them. Let me see
if I can get a good example going for you. Be back later.

[thomas eicondev]

i got the ado report setup and working fine. my report is linked to a table that has 200,000 records. if i run the report i get the following results:

1-report linked to an odbc connection takes about 6 sec
2-report linked to ttx file takes about 1.5 min
1-report linked to an ado rs gets about half way and errors out every single time and half way means 8 min.

i ran these reports from my vb app and directly from crystal and i always get the same results.

any ideas?

also do you know if ttx reports are supported in crystal 9?

Thomas

[RobDog888]

Probably, but I only have 8.5 Developer. What is the error when using ADO? Are all these the
subreports and their connection types or individule report testing by you?