i have this report that have a dynamic parameter , when i call from my application using vba it aske for server name , user id and password , even i have provided the connection string right , so if there is diffrent way to do it can you tell me , because this is killing me from over a month
i have attached the a screen shot and the code is following
thanks
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim userinfo As New RetrieveGlobals9.retrieveuserinfo
Dim strsql, sqlDataSource, pwd, userId1, constring, interCompanyID As String
Dim path, userf, userto As String
Public application As New CRAXDRT.application
Public report As New CRAXDRT.report
Private Sub CUPRPayrollReports_Initialize()
sqlDataSource = userinfo.sql_datasourcename
userId1 = userinfo.retrieve_user
pwd = "sql"
interCompanyID = "ASCTR"
End Sub
Private Sub DepartmentWisePrint_Changed()
CUPRPayrollReports_Initialize
path = "C:\Program Files\Microsoft Dynamics\GP\departmentWise.rpt"
Set cn = New ADODB.Connection
constring = "Provider=MSDASQL" & _
";Data Source=" & sqlDataSource & _
";User ID=" & userId1 & _
";Password=" & pwd & _
";Initial Catalog=" & interCompanyID
With cn
.ConnectionString = constring
.CursorLocation = adUseNone
.Open
End With
Set rst = New ADODB.Recordset
strsql = "SELECT * FROM DepWise"
rst.Open strsql, cn, adOpenDynamic, adLockOptimistic, adCmdText
Set report = application.OpenReport(path, 0)
report.Database.SetDataSource rst, 3, 1
report.EnableParameterPrompting = True
ReportViewer.CrystalActiveXReportViewer1.ReportSource = report
ReportViewer.Top = 0
ReportViewer.Left = 0
ReportViewer.Height = 500
ReportViewer.Width = 760
ReportViewer.ScrollBars = fmScrollBarsBoth
ReportViewer.CrystalActiveXReportViewer1.Top = 0
ReportViewer.CrystalActiveXReportViewer1.Left = 0
ReportViewer.CrystalActiveXReportViewer1.Height = 500
ReportViewer.CrystalActiveXReportViewer1.Width = 750
ReportViewer.CrystalActiveXReportViewer1.EnableGroupTree = False
ReportViewer.CrystalActiveXReportViewer1.EnableRefreshButton = True
ReportViewer.CrystalActiveXReportViewer1.EnableExportButton = True
ReportViewer.CrystalActiveXReportViewer1.EnablePrintButton = True
ReportViewer.CrystalActiveXReportViewer1.ViewReport
'If ReportViewer.Visible = False Then ReportViewer.Show
'If ReportViewer.Visible = False Then
ReportViewer.Show
Set rst = Nothing
Set cn = Nothing
End Sub