Crystal Asking A Parameter

[ARPRINCE]

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

[brucevde]

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.


'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)

'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.

[ARPRINCE]

Thanks.

[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

[brucevde]

Sure. For version 8.5 - I usually reference the Crystal Reports 8.5 ActiveX Designer Runtime Library.



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.

[brucevde]

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.


'*************************************************************************************************** ***************
'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

[techuser]

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.

[brucevde]

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.

'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)...

[techuser]

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



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.



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:
---------------------------------------------


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.


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.

[brucevde]

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?