[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBS - Run Crystal Report with Parameters

Posted on 2011-10-18
14
Medium Priority
?
3,179 Views
Last Modified: 2012-08-14
Via VB script I want to be able to run a crystal report (vers. XI), enter the report parameters, save the report to a server location and email it. This script (attached) does everything that I want except for setting the report parameters. Any ideas? Thanks!
Dim sReportName
Dim CRapp
Dim reportDb
Dim reportTables
Dim reportTable
Dim sReportOutput
Dim objOutl

'**********************************************************************************************
'Specify the report file name and location
'**********************************************************************************************
	
	sReportName = "C:\SampleCrystal\test.rpt" 'Crystal report location
	sReportOutput = "C:\SampleCrystal\SampleFinances_20111018.pdf"
	
'**********************************************************************************************
'Save report file in PDF format - specified location
'**********************************************************************************************

	Set objCRApp= WScript.CreateObject("CrystalRunTime.Application")
	Set objReport= objCRApp.OpenReport(sReportName)
	Set reportDb = objReport.Database
	Set reportTables = reportDb.Tables
	Set reportTable = reportTables.Item(1)

	Call reportTable.SetLogOnInfo("Opennet Custom Report") 'ODBC Connection to DBF file

	With objReport
		.EnableParameterPrompting=True
		.ExportOptions.FormatType = 31 'crEFTPortableDocFormat
		.ExportOptions.DestinationType = 1 'crEDTDiskFile
		.ExportOptions.DiskFileName = sReportOutput
		.Export(False)
	End With

'**********************************************************************************************
'Email attached report file
'**********************************************************************************************
	
	Set objOutl = CreateObject("Outlook.Application")
	Set objMailItem = objOutl.CreateItem(olMailItem)
	strEmailAddr  = "yourname@whatever.com"
	objMailItem.Subject = "Weekly Report File: " & Mid(sReportOutput, 18, 23) 
	objMailItem.Body = "Test: Please see the attached weekly report file."

	'objMailItem.Display
	objMailItem.Recipients.Add strEmailAddr
	objMailItem.Attachments.Add sReportOutput
	objMailItem.Send

'**********************************************************************************************
'Error handling and clean-up
'**********************************************************************************************

On Error Resume Next

Set CRapp = Nothing
Set report = Nothing
Set reportDb = Nothing
Set reportTables = Nothing
Set reportTable = Nothing
Set objMailItem = nothing
Set objOutl = nothing

Open in new window

0
Comment
Question by:GreenLeghorn
  • 7
  • 7
14 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36988825
Here is an example of passing parameters to the report

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_24572097.html

mlmcc
0
 

Author Comment

by:GreenLeghorn
ID: 36988970
I didn't find any code within that linked question/answer or the link ref'd in the question/answer that will work with my vb script. I need to know where to set the parameter in the script (in or outside of the With statement?) and the basic syntax that i need to use to make it work.

objReport.SetParameterValue("End Date", "1/1/2010")
- Error: Cannot use parenthesis when calling a Sub
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36989512
Try

objReport.SetParameterValue "End Date", "1/1/2010"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:GreenLeghorn
ID: 36992239
Error: Object doesn't support this property or method: 'objReport.SetParameterValue'
0
 

Author Comment

by:GreenLeghorn
ID: 36993016
There is 1 parameter for this report named "End Date" in Crystal.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36993020
Does VBS provide any options when you type in objReport. ?

I don't use VBS so I don't know how it is done.

Are you trying to use the RDC (CRAXDRT.DLL)?

mlmcc
0
 

Author Comment

by:GreenLeghorn
ID: 36993163
No, I am writing this script in notepad.
My code is posted above, I am not using any type of remote connection to call a DLL.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36993239
The RDC is not a remote connection.  It is the Report Design Component and the API for using Crystal.

My question is, what Crystal application object is being created here
Set objCRApp= WScript.CreateObject("CrystalRunTime.Application")

It seems to be the RDC application object.

Look for this file
crsdk_net_doc.chm

On my system it is here
C:\Program Files\Business Objects\Crystal Reports 11.5\Help\en\crsdk_net_doc\doc

You can then examine the parameter class and see how values are passed in VB.

mlmcc
0
 

Author Comment

by:GreenLeghorn
ID: 36993331
Oops, wrong interpretation.
I checked that help file and don't see any VBS code that can help me with my problem.

I see a lot of VB6 ref's but that isn't helping me to determine the correct VBS syntax to use.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36993360
There is very limited help for VBS with Crystal.  I don't believe they ever really intended it to be run that way.  You will be using the same dll with VBS as with VB6 or VB.Net.  The best you can probably get is to look at the references and see the methods used then try to use a similar construct.

I don't recall having ever seen a VBS example ont he Crystal site.

mlmcc
0
 

Author Comment

by:GreenLeghorn
ID: 36994503
I agree with you, but I have limited resources here for automating/scheduling Crystal reports and would like to just finish up this last piece of my code to include report parameters.

I have searched and searched the web and find many posts of where this same type of problem has come up, but I have yet to find a solution.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 36994575
Here is a question with code from Crystal

http://forums.sdn.sap.com/thread.jspa?threadID=1970049

Parameters passed as

Report.ParameterFields.GetItemByName("From").AddCurrentValue CDate("2011-06-06 00:00:00")


mlmcc
0
 

Author Comment

by:GreenLeghorn
ID: 36995235
That worked!! Thank you very much for that reference.
This VBS now runs the crystal report, includes parameters, saves to a disk file location and emails the report file.
Dim Appn
Dim Report
Dim FSO
Dim Path
Dim ReportName
Dim OutputFile
Dim DiskFilePath
Dim Param1
Dim Param1Name
Dim EmailAddr
Dim EmailAttach
Dim FullOutputPath
Dim EmailSubj
Dim EmailBody
 
On Error Resume Next

'**********************************************************************************************
'Define variables
'**********************************************************************************************
	ReportName = "C:\SampleCrystal\Test.rpt"
	DiskFilePath = "C:\SampleCrystal\"
	OutputFile = "SampleFinances"
	FileFormat = ".pdf"
	Param1Name = "End Date"
	Param1 = "2011-06-06 00:00:00"
	
	EmailAddr  = "yourname@whatever.com"
	EmailSubj = "Weekly Report File: "
	EmailBody = "Test: Please see the attached weekly report file."

'**********************************************************************************************
'Open report
'********************************************************************************************** 
	Set FSO = CreateObject("Scripting.FileSystemObject")
	Set Appn = CreateObject("CrystalRuntime.Application")
 	Set Report = Appn.OpenReport(ReportName)
 	
	Path = OutputFile & "_" & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now)
	FullOutputPath = DiskFilePath & Path & FileFormat
	
'**********************************************************************************************
'Report parameters
'********************************************************************************************** 
	Report.ParameterFields.GetItemByName(Param1Name).AddCurrentValue CDate(Param1)
	Report.EnableParameterPrompting = False

'**********************************************************************************************
'Report format
'********************************************************************************************** 
	Report.ExportOptions.FormatType = 31 ' crEFTPortableDocFormat
	Report.ExportOptions.DestinationType = 1 ' crEDTDiskFile
	Report.ExportOptions.PDFExportAllPages = True
	Report.ExportOptions.DiskFileName = FullOutputPath 
	Report.Export (False)

'**********************************************************************************************
'Email report
'********************************************************************************************** 	
	Set objOutl = CreateObject("Outlook.Application")
	Set objMailItem = objOutl.CreateItem(olMailItem)
	objMailItem.Subject = EmailSubj & Mid(FullOutputPath, 18, 29) 
	objMailItem.Body = EmailBody
	'objMailItem.Display
	objMailItem.Recipients.Add EmailAddr
	objMailItem.Attachments.Add FullOutputPath
	objMailItem.Send

'**********************************************************************************************
'Object clean-up
'********************************************************************************************** 	
	Set FSO = Nothing
	Set Appn = Nothing
	Set Report = Nothing
	Set objOutl = Nothing
	Set objMailItem = Nothing

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36997075
Great.  I'll save this as a reference for Crystal/VBS questions in the future

mlmcc
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

873 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