Crystal SQLQueryString

I am writing some code that analyses the 700+ Crystal reports we have within our business. I am aiming to have this code evalaute the data access code for each report. I have managed to determine whether the report is using a Command, Stored Procedure or Table access to obtain the data. However, when I am retrieving the SQLQueryString property of the Report I seem to be having an adverse affect on the msdb database on our SQL Server. It appears to be causing some locking issues. Is there any other method of obtaining the SQL Command code from the Crystal Objects?In your Front End application, check if there is any property to get query
Something like

CR.GetQuery

crystal reports_oracle

Can we execute/Run crystal reports with PL/SQL code in Oracle?yes u can use oracle stored procedures ,using ref cursors to report.|||shareem Thnks for reply,
but my question is can we run crystal reports in pl/sql code. instead of schedule to run crystal reports|||Why do you want to run CR from ORACLE?sql

crystal reports XI and VBA

Hi folks..
I am using crystal reports created in version XI with Excel(VBA).
i have the following code to display the report..
********************
Private Sub CommandButton2_Click()
Dim rep As New CrystalReport
rep.ReportFileName = "c:\apcg\test\report1.rpt"

CRViewer1.ReportSource = rep
CRViewer1.ViewReport
End Sub

************
But i am facing an error
Runtime error:430 - Class does not support Automation or does not support expected interface..

Could you any one solve this problem..

i knew that the problem is with CRViewer.dll
when i tried to register that file, it failed..
also i am getting an error "Error accessing Registry" when i click references in VB project menu...

Thanks in advance
salaiTry to reinstall CR X1 and see what happens

Crystal Reports print 3 copies

I am looking for a way to add a code to my Crystal report that will tell the report to print three copies of each work order and send an end of document so the printer will staple the 3 copies together. Then do the same for each work order. The report generates multiple work orders. Is there a VBA script I could add?CRAXDRT.report contains a method named PrintOut which takes number of copies as parameter.

Crystal Reports from VC++

Hi All,

I am creating crystal reports(version 8.5) using VC++ code.
Its working fine on the the machines which have crystal reports installed .
however same is not the case with non crystal machines even after registring the Crystl32.OCX used by the Crystal Reports Object.

I am getting error " Unable to logon to server" while trying to run the report.
DSNs are same on both type of machines.
(one with crystal reports and one without it )

I have already distributed crystal dlls with the application.
(Crystl32.OCX,Craxdrt.dll, CRViewer.dll,CRDesignerCtrl.dll,Craxddrt.dll)

Do I need to do some thing in addition to this ?

kindly help.

thanks
SanjayCoule you please provide me with your code, it helps me build my own app...

Rob

crystal reports 10 incompatibility with single quotes in SP

My SP has this code but apparently Crystal Reports doesn't know how to
interpret the quotes in @.query:
fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
while @.@.FETCH_STATUS = 0
begin
select @.query = 'insert into #results_table ' +
'select ''' + @.person_ID + ''',''' + @.full_name + ''',''' +
cast(@.charge as varchar(10)) + ''', sum(paidAmt),sum(adjustedAmt), 0, 0' +
' from PAYMENTLINEITEMS where encounterID in ' + @.encounters_id
exec (@.query)
fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
end
For some reason, I can't see the fields on the crystal report if I use this
code (excluding the Fetch in the loop). When I comment this, then I can see
them. The reason I'm using it is because I can't use @.encounters_id (
consists of '(12,14,15)' ) in the regular Insert. So I need to do it like
this.
Any help is appreciated.This code is part of a stored procedure in SQL Server, right?
I know Crystal does some strange (and annoying) things with how it views
database objects, but I can't see how it would have any knowledge of this
piece of code. Crystal, as far as I know, only sees the data structure(s)
that your query returns and should be unaffected by this.
Have you confirmed that the stored procedure itself is able to execute by
running it in query analyzer?
"VMI" <vonchi_m AT yahoo DOT com> wrote in message
news:%23l6htoQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
> My SP has this code but apparently Crystal Reports doesn't know how to
> interpret the quotes in @.query:
> fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
> while @.@.FETCH_STATUS = 0
> begin
> select @.query = 'insert into #results_table ' +
> 'select ''' + @.person_ID + ''',''' + @.full_name + ''',''' +
> cast(@.charge as varchar(10)) + ''', sum(paidAmt),sum(adjustedAmt), 0, 0' +
> ' from PAYMENTLINEITEMS where encounterID in ' + @.encounters_id
> exec (@.query)
> fetch next from c2 into @.person_Id, @.full_name, @.charge,
@.encounters_id
> end
> For some reason, I can't see the fields on the crystal report if I use
this
> code (excluding the Fetch in the loop). When I comment this, then I can
see
> them. The reason I'm using it is because I can't use @.encounters_id (
> consists of '(12,14,15)' ) in the regular Insert. So I need to do it like
> this.
> Any help is appreciated.
>|||Yes, the code is part of a stored procedure.
I do get a resultset when I run it, but I did notice now that the "Query
batch completed with errors." I had seen it before and I went through all
the messages and never saw any red text. I assumed that if I saw the result
set of the last Select statement, everything was fine. The problem was that
set nocount was off. I switched it to On and now I can see clearly that the
procedure found errors with the data being processed.
thanks again.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23R8aftQVGHA.4952@.TK2MSFTNGP09.phx.gbl...
> This code is part of a stored procedure in SQL Server, right?
> I know Crystal does some strange (and annoying) things with how it views
> database objects, but I can't see how it would have any knowledge of this
> piece of code. Crystal, as far as I know, only sees the data structure(s)
> that your query returns and should be unaffected by this.
> Have you confirmed that the stored procedure itself is able to execute by
> running it in query analyzer?
> "VMI" <vonchi_m AT yahoo DOT com> wrote in message
> news:%23l6htoQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
> @.encounters_id
> this
> see
>|||The problem was the last names with quotes (O'neill, O'Sullivan, etc...)
Thanks for the help.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23R8aftQVGHA.4952@.TK2MSFTNGP09.phx.gbl...
> This code is part of a stored procedure in SQL Server, right?
> I know Crystal does some strange (and annoying) things with how it views
> database objects, but I can't see how it would have any knowledge of this
> piece of code. Crystal, as far as I know, only sees the data structure(s)
> that your query returns and should be unaffected by this.
> Have you confirmed that the stored procedure itself is able to execute by
> running it in query analyzer?
> "VMI" <vonchi_m AT yahoo DOT com> wrote in message
> news:%23l6htoQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
> @.encounters_id
> this
> see
>|||VMI (vonchi_m AT yahoo DOT com) writes:
> My SP has this code but apparently Crystal Reports doesn't know how to
> interpret the quotes in @.query:
> fetch next from c2 into @.person_Id, @.full_name, @.charge, @.encounters_id
> while @.@.FETCH_STATUS = 0
> begin
> select @.query = 'insert into #results_table ' +
> 'select ''' + @.person_ID + ''',''' + @.full_name + ''',''' +
> cast(@.charge as varchar(10)) + ''', sum(paidAmt),sum(adjustedAmt), 0, 0' +
> ' from PAYMENTLINEITEMS where encounterID in ' + @.encounters_id
> exec (@.query)
> fetch next from c2 into @.person_Id, @.full_name, @.charge, >
@.encounters_id
> end
> For some reason, I can't see the fields on the crystal report if I use
> this code (excluding the Fetch in the loop). When I comment this, then I
> can see them. The reason I'm using it is because I can't use
> @.encounters_id ( consists of '(12,14,15)' ) in the regular Insert. So I
> need to do it like this.
No you don't! There is no need to use EXEC to handle comma-separated
list. Use a table-valued function that unpacks the list to a table.
The you don't need the dynamic SQL, and don't have to worry about
O'Neill.
See http://www.sommarskog.se/arrays-in-...ist-of-integers
for an example.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

crystal reports (the coder way) ?

Hi

I have a .NET application that needs to obtain the details of the parameters for a crystal report.

I have written some code, that opens a report into the crystal viewer and the gets the parameter info and displays it in a msgbox.

However, the viewer prompts for the parameters, before i get a chance to get the information and display it.:(

My question is, does anyone know of a way that i can get the parameter information from a report, without the user being prompted for the parameters ?

Thanks in anticipation[Moved thread]|||YOu need to populate the parameters to the report before viewing it the viewer or printing it to the printer.

here is a sample from VB on how to populate parameters and formulas.

Dim crFormula As CRAXDRT.FormulaFieldDefinition
Dim crParam As CRAXDRT.ParameterFieldDefinition

crReport.DiscardSavedData

For Each crParam In crReport.ParameterFields
Select Case crParam.Name
Case "{?SortType}"
crParam.AddCurrentValue m_strSortType

End Select
Next crParam


For Each crFormula In crReport.FormulaFields
Select Case crFormula.Name
Case "{@.StartDate}"
crFormula.Text = "DATE( 2001, 01, 01)"
Case "{@.EndDate}"
crFormula.Text = "DATE( 2002, 03, 30)"
crFormula.Text = """All Offices"""
Case "{@.DetailOrSummaryFlag}"
crFormula.Text = """" & m_strSummaryDetailFlag & """"
Case "{@.Seperate}"
crFormula.Text = """FALSE"""
Case "{@.SetShowWarning}"
crFormula.Text = """FALSE"""
Case Else
'do nothing
End Select
Next crFormula|||Hi

I have a .NET application that needs to obtain the details of the parameters for a crystal report.

I have written some code, that opens a report into the crystal viewer and the gets the parameter info and displays it in a msgbox.

However, the viewer prompts for the parameters, before i get a chance to get the information and display it.:(

My question is, does anyone know of a way that i can get the parameter information from a report, without the user being prompted for the parameters ?

Thanks in anticipation

Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report
REPORT = APP.OpenReport("C:\SHIPMENT_SCHEDULE\REPORTS\XL.rpt")

REPORT.EnableParameterPrompting = False
(this is the one you should include...disabling the parameter prompting)

Crystal Reports - Newbie:subreports on two stored proc

Hi i would like to develop a report which displays unrelated data from two stored procedures.
I want to know how to code the ASP webform so that it passes different parameters to differnt stored procedures and displays the report with data from both stored procedure. pls help.Make two procedures to one and display the records

Select columns from table where col=@.col

crystal reports

Dear Friends,

Please help me urgent.

I have tried to create the code in vb.net 2003 in crystal reports 10. I had my code as follows:-

Con.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Program Files\inventory.MDB;" & _
"Persist Security Info=False;" & _
"Jet OLEDB:Database Password = tz_2006"
Con.Open()

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

RS = New ADODB.Recordset
Dim strReportName As String

CHECK_STRING = "SELECT * FROM CITY_CITIES"
RS.Open(CHECK_STRING, Con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

strReportName = "CITIES"
Dim strReportPath As String = "C:\Program Files\REPORTS" & "\" & strReportName & ".rpt"

'Check file exists
If Not IO.File.Exists(strReportPath) Then
Throw (New Exception("Unable to locate report file:" & vbCrLf & strReportPath))
End If

Dim rptDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

rptDocument.Load(strReportPath)
rptDocument.SetDataSource(RS)
AxCRViewer1.EnableGroupTree = False
AxCRViewer1.ReportSource = rptDocument
AxCRViewer1.ViewReport()
End Sub

The Error I am getting is the 3rd line from below (i.e. AxCRViewer1.ReportSource = rptDocument) which the Error states that "an unhanded exception of type 'system.invalidcastexception' occured in axinterop.crviewerlib.dll" and "Additional information: No such interface supported"

Thanks.Open the report and Do verify Database

Crystal Reports

Hello out there,

I have a problem with crystal reports. I have added the report from VB6. This is the code in the Form Section

Visual Basic 6 Code:
------------
Dim Report As New CrystalReport1

Private Sub Form_Load()
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

strSelectString = "{M_Receipt.PayMtd} = 'Credit Card' "

CrystalReport1.RecordSelectionFormula = strSelectString

End Sub
------------
end of Visual Basic 6 Code

This code still gives me all the records from the database. I connected to the DSN in the Crytal report itself using Database-->Set Location, then pointing to my DSN.(Connecting to an Access Database)

How can i resolve this so that only the records i want are displayed.

ThanksMillions in advance[ Moved to Crytal Report forum]|||Add parameters in the said crystal report, use these parameters in the Report Selection option.

Pass values to the crystal report from the VB script.

Sample way of passing parameters is as below :

For eg., the parameter name is vPayMtd.

CrystalReport1.Parameterfields(0) = 'vPayMtd;' & 'Credit Card' & ';true'

Here if want to pass a value in the condition of comparision
of vPayMtd = 'Credit Card', above notation can be used.

Try with this.|||Hi Friends,

m new to crystal reports and facing some problems regarding the same

i made one rpt in crystal rpts.
and now want to call it in vb 6

the coding which i have used is as follows.

frmemp.crystalreport1.connect = "dsn,uid,pwd"
frmemp.crystalreport1.reportfilename = "c:\programe files\..\empdtl.rpt"
frmemp.crystalreport1.action = 1

m i missing out something,

please guide me for this query.

regards,
kalpana

crystal reports

how can i design the crystal reports in windows application using C#.
what code i should write to get all the fields in a report format which should be generated into a crystal report.Visit www.VBCity.com and search in Crystal Reports section|||As you specified on that topic, Doevents would do that. You need to write it twice or thrice

Doevents
Doevents
Doevents

Crystal Report XI with Excel(VBA).

Hi folks..
I am using crystal reports created in version XI with Excel(VBA).
i have the following code to display the report..
********************
Private Sub CommandButton2_Click()
Dim rep As New CrystalReport
rep.ReportFileName = "c:\apcg\test\report1.rpt"

CRViewer1.ReportSource = rep
CRViewer1.ViewReport
End Sub

************
But i am facing an error
Runtime error:430 - Class does not support Automation or does not support expected interface..

Could you any one solve this problem..

i knew that the problem is with CRViewer.dll
when i tried to register that file, it failed..
also i am getting an error "Error accessing Registry" when i click references in VB project menu...

Thanks in advance
salaiTry to reinstall CR and see what happens

Crystal Report Query Engine error

Hi,
My project is being done in Vb.Net 2003 and SQL Server2000. When I try to access the Crystal Report thru code only I get the query engine error. the Sql Query however works perfectly in Query Analyser.
The Query uses left outer join operation from 4 tables. Is there any way I can overcome my error?

Thanks in advance
JohnsyIt could be useful to know exactly what error message says, to figure out if we're dealing with connection problem, or there is an error in query.
Also, you could try running Profiler to see what happens on SQL Server when you run your report.|||hi johnsy,

i too got the same problem . please tell me the solution if had solved tat problem.
how to create dataset.xsd for 2 tables

Crystal Report Factors

hi guys!
i just want to ask my problem about my exporting file under vb code applications...
when i click the export button it doesnt work... nothing happen...
i wonder if you can give an idea how to fix this problems...
ASAP!
thank you very much!
benjzMake sure you have the correct dlls installed on the machine you want to Export from. In Crystal Reports 8.5, there's a file called runtime.hlp that tells you what files are required and whee to put them.|||do i need to install those dll in every client that are using the application? i thought its automatically deploy by setup app...

is it ok if you can give me the list of this dll's?

thankz a lot!

benjz|||Depending on what app you're using to create your setup file, you may have to specify that you want Export capabilities included.

See my post here (http://www.dev-archive.com/forum/showthread.php?t=318504) for a list of the Export dlls required (for CR 8.5).

crystal report comparison

hi
I have to compare two crystal report text. Is there any tool which does this?
or Any Code which does this?
It has to be done on rpt only.No conversion of report to any other form.

With regards
RashmiIs is at runtime u want to compare 2 reports?

Crystal Error

HI ,

I'm trying to print a rpt. file ( C.R. 8.5) from a VB.net program
code :

Dim Reporte As New crAlmacen
Dim x As String = Reporte.FilePath()
Dim x1 As String = Reporte.ResourceName
Reporte.SetDatabaseLogon("termi", "termi", "SERVIDOR1", "mestres")
Reporte.Refresh()
' Reporte.PrintOptions.PrinterName = "Lexmark Optra S 2455"

Reporte.PrintToPrinter(1, False, 1, 1)

An unhandled exception of type 'CrystalDecisions.CrystalReports.Engine.InvalidArgumentException' occurred in crystaldecisions.crystalreports.engine.dll

Additional information: Error in File C:\DOCUME~1\ADMINI~1\CONFIG~1\Temp\temp_50488d2a-21be-4a5a-a4df-ad83112d6274.rpt:
Invalid printer specified.

thanks a lotHI ,

I HAVE SOLVED THE PROBLEM

Dim Reporte As New crAlmacen
Dim x As String = Reporte.FilePath()
Dim x1 As String = Reporte.ResourceName
Reporte.SetDatabaseLogon("termi", "termi", "SERVIDOR1", "mestres")
Reporte.Refresh()

NEW CODE LINE
---------------------
Reporte.PrintOptions.PaperSource = CrystalDecisions.[Shared].PaperSource.Auto
---------------------
' Reporte.PrintOptions.PrinterName = "Lexmark Optra S 2455"
Reporte.PrintToPrinter(1, False, 1, 1)

AND NOW GO RIGHT

BEST REGARDS|||Glad that's sorted :D

Crystal 8.5 Cant Change Database Path in Code

Hi everyone,

I have used Crystal for about 7 years, and have never had this problem before. I am using Crystal 8.5 Developer, on WinXP Pro, with Delphi4 and using an Advatage database, I am trying to change the data location in code
eg: frmMain.crpe.Tables[inTbNo].Path := stDBPath; // this has worked for years.

I have also tried hard coding just to test it:
frmMain.crpe.Tables[0].Path := '\\Server\Server\Gigasoft\dbStockOrderBeta';
frmMain.crpe.Tables.Propagate := True;
// this still does not work

I have formatted the machine and reloaded only the bare minimum of software just incase there was some conflict, still no luck.

I have permanent tables in 1 folder and temp tables in another, the reports only use 1 or the other not both.

The reports that use the permanent tables are the ones causing the problem.

The reports using the temp tables seem to work OK, as they are constantly changing location to the temp locations depending on the user who is logged in.

I hope someome has had this problem and solved it before.

Thanks in Advance for any help
Daryl...What is the error you get?
Check the path is shared and accessible|||There is no error msg, it just doesn't change the database path at all. The path is accessible by crystal because data for the temp table reports are in a folder 1 below the main folder and most of the time they seem to work.

Thanks
Daryl...

Crystal 4.6 Question

this is my code, and when It comes to show the report on the screen nothing happens:

CrystalReport1.Connect = "Provider = Microsoft.Jet.OLEDB.4.0;" &
App.Path & "\config.mdb;UID=sa;PWD=12345;"
Me.CrystalReport1.DataFiles(0) = App.Path + "\db.mdb"

CrystalReport1.ReportFileName = App.Path + "\boent_rep.rpt"
CrystalReport1.Destination = crptToWindow
CrystalReport1.DiscardSavedData = True

CrystalReport1.ReportFileName = "c:\lm_cliente.rpt"
CrystalReport1.SelectionFormula = formula 'whatever formula is
CrystalReport1.Action = 1

After the last line I have checked the .status property being the result -1. I know that after a successful display the result in .status is 3.

What is the .status property means, what are the possible results. What does it means if a -1 result is what I get? Please help :D*bump*|||Did you know the answer?

Cryatal Viewer

I need to test if the user had clicked the print button on the crystal viewer in my design (not in run time) this button is disable how can I by code know if the user had clicked this button on any else button of the crystal reportLet me get this straight...

You want to determine if the Crystal Viewer was clicked in design time

Do you want to add custome events to any of those controls
I can't see why you want to do this, they already have functionality, they already function as they should :confused:

Or..

You can in any case loop through the Form's controls, determine if the current control is a
CrystalDecisions.Windows.Forms.PageView then loop further through that container to (for example) change the caption of the Tabpage.

Can you explain your problem a bit more please!|||I need to know when he clicked the print button I need to know if he choosed to print as Landscape or portrait

CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005

I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.

Hi,

For a free one, look at mygenerationsoftware.com.

For a cheap-ish one (I use it all the time), try codesmithtools.com (trial version is free).

Hope this helps,

Jules

|||

Thanks for solution.

Regards

|||Hope you get on OK with the utilities.