Crystal Asking A Parameter

Printable View

May 1st, 2003, 03:34 PM
ARPRINCE

Crystal Asking A Parameter

When I run my program report, CR always asks for a parameter. I want to handle this paramater prompt in the code. I mean, instead of prompting the user, I want to give the report the value of the parameter during runtime.

Any ideas?

Thanks

The exact code depends on which version of Crystal and how you are executing the report within VB.

I use this code with the Crystal Reports Engine 8 Object Library (CRPEAuto), to set parameters.
VB Code:
'objReport  is declared as a CRPEAuto.Report 
With objReport
    .ParameterPromptingEnabled = False
            
    .ParameterFields(1).SetCurrentValue Trim$(objWard.Ward)
    .ParameterFields(2).SetCurrentValue dteFrom
    .ParameterFields(3).SetCurrentValue dteTo
End With
and this code is used with the Crystal Reports Designer 8.5 ActiveX Runtime Library (CRAXDRT)
VB Code:
'objReport is declare as CRAXDRT.Report
With objReport
    .EnableParameterPrompting = False
 
    .ParameterFields(2).AddCurrentValue Trim$(objWard.Ward)
    .ParameterFields(2).AddCurrentValue dteFrom
    .ParameterFields(3).AddCurrentValue dteTo
End With
Note that if a parameter value has not been set Crystal will still display its dialog box, even if the ParameterPrompting property is False.

May 2nd, 2003, 07:37 AM
ARPRINCE

Thanks.
May 2nd, 2003, 11:42 AM
jhermiz

Cool bruce would it be possible to show us code to set the object report to the actual report..and any other necessary code needed. I could really use what you had earlier posted to Prince regarding parameters..but ive never opened a report in vb.

Please let me know...

Jon
May 2nd, 2003, 01:20 PM
brucevde
Sure. For version 8.5 - I usually reference the Crystal Reports 8.5 ActiveX Designer Runtime Library.
VB Code:
```
Dim objCrystalApp As CRAXDRT.Application
Dim objReport As CRAXDRT.Report
 
Set objCrystalApp = New CRAXDRT.Application
 
Set objReport = objCrystalApp.OpenReport(ReportPath, 0)
 
'To Show the report.
'frmReport is a VB Form that contains the Crystal Viewer Control
'in this case called Viewer 
frmReport.Viewer..ReportSource = objReport
frmReport.Viewer.ViewReport
```
These are some of the things to consider but would depend on your situation. Database/Table datasources, Table Locations and SubReports.

The Report object has Database and Table collections. For each Table used in your report (this includes subreports) you may need to call the SetLogonInfo method.

I use the direct or oledb methods, no odbc dsn. This means if I develop a report using our development sql server I must change the login information to point to the production server and use the Users login id.

You also may need to set the Table.Location property.

I decided to post this procedure that we use to set the login information and location for each report. Just remember, this has never been used with MS Access only Oracle and SQL Server - stored procedures or linked tables. I will leave you to figure out what is going on, hopefully the procedure comments will help.

VB Code:

'******************************************************************************************************************
'Procedure: SetReportDataSources
 
'Description:  '      Crystal saves the Login Information and Location as part of the report.  This procedure
'      overrides the login information and location for all the report's DatabaseTables including any
'      Subreports.  It assumes all tables use the same login criteria (ie all data is from the same database).
'      It also assumes that all reports are created from stored procedures(System standard).
'      In order to override the Location we need to parse the DatabaseTable.Location property.
'      This allows us to easily support several "environments" but we need to tell the Crystal engine
'      which Login information to use and which stored procedure to use.
 
'      For example - An application has three environments.  Production, QA and Dev.  The QA and Dev
'      databases are called Pubs_Dev and Pubs_QA on server Handel.  A developer creates a report
'      using the Pubs_Dev database and a stored procedure called selAuthorsbyState.  Crystal will save the
'      Location as pubs_dev.dbo.Proc(selAuthorsByState).  Note that the location written does depend on the 
'      author's options/preferences. We are not going to rely on a everyone following a "Standard Practice".
 
'      When a user named TestUser runs the application, selects the QA environment at login,
'      and prints the report - the data is still retrieved from the Pubs_Dev database(even if we tell Crystal 
'      to login to the QA database).
 
'      We must change the Location to pubs_QA.dbo.Proc(selAuthorsByState) or simply selAuthorsByState 
'      this ensures the data is retrieved from the QA database tables.
'******************************************************************************************************************
Private Sub SetReportDataSources(CrystalReport As CRAXDRT.Report, ServerName As String, DatabaseName As String, UserName As String, Password As String)
    On Error GoTo ErrorHandler
    
    Dim lngStartPos As Long
    Dim lngEndPos As Long
    
    Dim objSubReport As CRAXDRT.Report
    
    Dim objDB  As CRAXDRT.Database
    Dim objTables    As CRAXDRT.DatabaseTables
    Dim objTable     As CRAXDRT.DatabaseTable
    
    Dim objSections  As CRAXDRT.Sections
    Dim objSection As CRAXDRT.Section
    
    Dim objReportObjects As CRAXDRT.ReportObjects
    Dim objSubReports As CRAXDRT.SubreportObject
    Dim lngIdx As Long
    
    
    Set objDB = CrystalReport.Database
    Set objTables = objDB.Tables
        
    'set the login information and location for each table in the report
    For Each objTable In objTables
    
        lngStartPos = InStr(1, objTable.Location, "(", vbTextCompare) + 1
        lngEndPos = InStr(lngStartPos, objTable.Location, ")", vbTextCompare)
        
        If lngEndPos = 0 Then
            lngEndPos = Len(objTable.Location) + 1
        End If
        
        lngEndPos = lngEndPos - lngStartPos
        
        objTable.Location = Mid$(objTable.Location, lngStartPos, lngEndPos)
        objTable.SetLogOnInfo ServerName, DatabaseName, UserName, Password
    Next
    
    'set the login information and location for each table in the all subreports
    'Access to the sub reports is through the Sections collection
    Set objSections = CrystalReport.Sections
 
    For Each objSection In objSections
        
        Set objReportObjects = objSection.ReportObjects
        
        If objReportObjects.Count > 0 Then
 
            For lngIdx = 1 To objReportObjects.Count
                'make sure the report object is a subreport.
                If objReportObjects(lngIdx).Kind = crSubreportObject Then
 
                    'open the subreport and set the login information
                    Set objSubReports = objReportObjects(lngIdx)
                    Set objSubReport = CrystalReport.OpenSubreport(objSubReports.SubreportName)
 
                    Set objDB = objSubReport.Database
                    Set objTables = objDB.Tables
 
                    For Each objTable In objTables
                        
                        lngStartPos = InStr(1, objTable.Location, "(", vbTextCompare) + 1
                        lngEndPos = InStr(lngStartPos, objTable.Location, ")", vbTextCompare)
                        
                        If lngEndPos = 0 Then
                            lngEndPos = Len(objTable.Location) + 1
                        End If
                        
                        lngEndPos = lngEndPos - lngStartPos
                        
                        objTable.Location = Mid$(objTable.Location, lngStartPos, lngEndPos)
                        objTable.SetLogOnInfo ServerName, DatabaseName, UserName, Password
                    Next
                
                End If
            
            Next
 
        End If
 
    Next
 
Exit Sub
 
ErrorHandler:
    Err.Raise Err.Number, "SetReportDataSources", Err.Source, Err.Description
 
End Sub

Feb 10th, 2006, 07:18 AM
techuser

Re: Crystal Asking A Parameter

Hi Bruce, I'm trying your code and it throws an error at the following line:

objTable.Location = Mid$(objTable.Location, lngStartPos, lngEndPos)

The error is: Run-time error '-2147189176 (80047e48)'
Crystal Reports ActiveX Designer

The parameters at that moment of my code are like this:
objTable.Location=sp_getparts;1
lngStartPos=1
lngEndPos=17

Why can this be happening?

sp_getparts is my stored procedure design-timed written.

Hope you can help me, I really cannot understand why is that difficult to throw a report with Crystal Reports with a stored procedure. It looks like it works great if no parameters are needed and if data is fixed. Not my case regretfully and I think it's not any other programmers case either.

Thanks in advance, Marcelo.
Feb 10th, 2006, 11:01 AM
brucevde

Re: Crystal Asking A Parameter

I have no idea why lngEndPos is equal to 17. From what you posted it should equal 12. Also this code is required whether the report has parameters or not.

Take a look at this from my procedure comments.

Quote:

'Crystal will save the Location as pubs_dev.dbo.Proc(selAuthorsByState). Note that the location written does depend on the
'author's options/preferences. We are not going to rely on a everyone following a "Standard Practice".

In Crystal, use the Database -> Set Location menu. Tell me what is indicated in the location box near the bottom.

Again, all our reports are created using the native Microsoft SQL Server Driver, not OLEDB or ODBC DSN - that probably makes a difference.

This code was created 3 years ago, is used by 5 different applications with 100's of users running reports all day long. It has yet to fail me... (if I may - toot toot)...
Feb 10th, 2006, 12:04 PM
techuser

Re: Crystal Asking A Parameter

Quote:

Originally Posted by brucevde

I have no idea why lngEndPos is equal to 17. From what you posted it should equal 12.

Sorry, it didn't say sp_getparts instead the stored procedure name is GetComputadoras... if this is a key to my problem.
I mean you say, it should say 12 instead of 17 at lngEndPos, why is that?

Code
objTable.Location=GetComputadoras;1
lngStartPos=1
lngEndPos=17

Quote:

Originally Posted by brucevde

Also this code is required whether the report has parameters or not.

Yes, that's my idea to use always this code, in case it finally works.

Quote:

Originally Posted by brucevde

Take a look at this from my procedure comments.

In Crystal, use the Database -> Set Location menu. Tell me what is indicated in the location box near the bottom.

At Database / Set Database Location it displays a tree of nodes which has different levels:

---------------------------------------------
first level
report

second level
MADSRV006 (which is my SQL Database)

3rd level (it has 2 nodes):
1.Properties
2.GetComputadoras;1 (this is the stored proc)

The info at:

1.Properties
Database Type: OLE DB (ADO)
Provider: SQLOLEDB
DataSource: SRVMAD006
Initial Catalog: MyCatalog
User ID: sa
Integrated Security: False
Locale Identifier: 11274
Connect Timeout: 15
General Timeout: 0
OLE DB Services: -5
Current Language:
Initial Filename:
Use Encryption for Data: 0
Replication server name connect option:
Tag with column collation when possible: 0

2.GetComputadoras
Properties
Table Name: GetComputadoras;1
Table Type: Stored Procedures
Overriden Qualified Table Name:
---------------------------------------------

Quote:

Originally Posted by brucevde

Again, all our reports are created using the native Microsoft SQL Server Driver, not OLEDB or ODBC DSN - that probably makes a difference.

Isn't it better to use OLEDB? I have understood it's better cause it skips a step on the connection cycle and so it's faster.

Quote:

Originally Posted by brucevde

This code was created 3 years ago, is used by 5 different applications with 100's of users running reports all day long. It has yet to fail me... (if I may - toot toot)...

I understand this worked with different connections types like ODBC or other kind but I also understand that was 3 years ago... so... what do programmers use today to make reports? I really need to make a lots of reports and believe me I don't want to waste any more time if Crystal Reports 9.2 isn't worth.

Hope you and anyone who has an idea programming with this tool can help me,

Thanks again, Marcelo.
Feb 10th, 2006, 12:23 PM
brucevde

Re: Crystal Asking A Parameter

The code I wrote was for version 8.5. The only thing I can suggest is to debug and step through the code line by line. What is the value of objTable.Location at run-time?