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
Printable View
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.
Thanks.
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
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
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.
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.
In Crystal, use the Database -> Set Location menu. Tell me what is indicated in the location box near the bottom.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".
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)...
Sorry, it didn't say sp_getparts instead the stored procedure name is GetComputadoras... if this is a key to my problem.Quote:
Originally Posted by brucevde
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
Yes, that's my idea to use always this code, in case it finally works.Quote:
Originally Posted by brucevde
At Database / Set Database Location it displays a tree of nodes which has different levels:Quote:
Originally Posted by brucevde
---------------------------------------------
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:
---------------------------------------------
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
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.Quote:
Originally Posted by brucevde
Hope you and anyone who has an idea programming with this tool can help me,
Thanks again, Marcelo.
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?