How to use Crystal Report 7 in Visual Foxpro Environment

[Triplex]

Good Day Folks, I'm a new member of Tek-Tips Forum. And I have question regarding Crystal Report 7. Before, Im using VB as my front end and Crystal Report as my report writer. Im new in our company and we're using Visual Foxpro 6 as our Front End. I want to know if you have tried using Crystal Report as the Report Writer same as with VB? Through Active x control? If yes, How? I hope you can help me with my problem.

Thanks and God Bless...

Triplex

 

[skuhlman]

I haven't done it through VB, but I belive it's pretty similar. Here is a site with a couple of articles you may find helpful. I used them when I did my first VFP / Crystal connection and was able to figure things out.

http://www.craigberntson.com/articles.htm

 

[Triplex]

Hi, Thanks for the reply I'll visit the site then give you feedback afterwards. Thanks. God Bless..

 

[VCat88]

Preview a report
----------------
Firstly, u need to run the crystal report and create the report. Make sure U can view the report at the same time.

Second, for an example, add a command button in your form and put in the line below

oCRApp = createobject("crystal.crpe.application&quot
oRep = oCRApp.OpenReport("C:\MYREPORT.RPT&quot
oRep.Preview

wait &&To pause the crystal report.


Then AFTER that, run and test the program.

 

[Triplex]

VCat88,

Good Day Thanks for the reply. I tried your code and I come up with an error 'OLE IDispatch exception code 20599 from cpecut32: Cannot Open SQL Server." Are you using SQL as Back End Database?

Skuhlman,

I already checked the site and I read an Article about "Integrating Crystal Reports with Visual Foxpro" and "Using Crystal Reports With VFP". There was an instructions like this.

Before using the CRAXDRT.DLL, you should register the component with Visual FoxPro’s Intellisense Manager. This will allow all the properties, methods, and events, to be selected using Intellsense. The following steps guide you through this registration process:

1.From the Visual FoxPro menu, select Tools | Intellisense Manager.
2.Select the “Types” tab.
3.Click “Type Libraries” to display the Type Library References dialog.
4.Select “Crystal Reports 8.5 ActiveX Designer Runtime Library” from the list. Make sure the check box next to the item is checked.
5.Click “Done” to return to the IntelliSense Manager
6.Click OK.
7.Test the IntelliSense settings. In the Command Window, type “MODIFY COMMAND Test” then press Enter
8.Type the following code into the editor window
LOCAL oCR AS CrystalRuntime.Application
oCR.
9.Notice that as soon as you press the . that IntelliSense will kick in and give you a list of PEMs from the Crystal Reports Application object.
10.Press Escape and answer Yes to the Discard Changes dialog.

Right now, im using Visual Foxpro 6 and Crystal Report 7 and SQLServer 2000 as may Back End Dbase. I can't find the IntelliSense Manager in Foxpro Menu. Do I need to upgrade the Visual Foxpro Version?

Thanks Again

Triplex

 

[VCat88]

Try this first...
When the moment u save the crystal report, u need to save the crystal report with data together.
(Demo on Crystal Report can able to show on screen using Visual foxpro)

Only the problem is that the data is not updated.
So, U need to open the crystal report again and update the data, then save it again with data so that when the moment preview, it able to preview the latest data (the moment u save) when u run this simple program.

We need to find a solution for the error :-
"OLE IDispatch exception code 20599 from cpecut32: Cannot Open SQL Server."
So far, i still not discover the solution for this problem.
If this problem solve, i guess we could preview the report with updated data. So, we no need to refresh the data everytime we change the data.

 

[Triplex]

VCat88,

Hi, Have you tried using Active x Control? When I'm using VB5 before I make use of this control to integrate crystal into VB. I tried the other day this kind of procedure and It works. First you go Tolls in Foxpro menu thn select Options go to Controls "Tab" then select ActiveX Controls then Select Crystal Report Control then click OK. What you are going do is to add this control in your form then try to add this code in your command button

ThisForm.Olecontrol1.ReportSource = 0
ThisForm.Olecontrol1.PrintFileType = 7
ThisForm.Olecontrol1.Destination = 0
ThisForm.Olecontrol1.WindowTitle = "Sample"
ThisForm.Olecontrol1.ReportFileName = "C:\reports\sample2.rpt"
ThisForm.Olecontrol1.PrintReport

Make it sure that you created an ODBC connection.

 

[VCat88]

Then how to create an ODBC connection in Visual Foxpro?

 

[VCat88]

I got error OLE error code 0x800a018a. Unknown OLE status code.

 

[Triplex]

Creating an ODBC connection is easy.

1. Go to Start. Select Setting
2. Select control Panel
3. Select administrative Tools
4. Select Data Sources (ODBC)
5. Click ADD
6. Select Microsoft Visual Foxpro Driver(*.dbf) if your using plain Microsoft Visual Foxpro Database or SQL Server if your using SQL as Back end Database.
7. Type your prefered Data Source Name (Ex. Inventory). Select Free Table Directory if your using DBF or .DBC if you create a container of all your tables.
8. Type in the path of your Dbase (Ex. c:\Inventory)
9. Then Click OK.

now you already your ODBC connection.

Use this ODBC connection in your Crystal Report.

Now open your Crystal Report. click Custom in your Report Gallery. Slect SQL/ODBC. In the Log On Server Select the ODBC that you crated.

 

[VCat88]

Solution solved !

 

[Triplex]

I have a new problem. What if I want to filter my report according Dept? But instead of using the crystal report parameter, I want to make use of textbox control of Visual Foxpro as the source of the Department. Have tried this Tricks?

Hope you can help me.

 

[VCat88]

All right, try this concept.
When the moment at the text box. Press tab key. There will be a list of selection department. From the list. Select and press enter. then the textbox will capture this value.
Beside this, you can create a open document icon. When the moment you press the tab key, it will same like click on open document icon for select.

 

[VCat88]

Coding:
modi proj proj1.(Click Classes).Click New.
Classname:FindDept
Base on FormSet
Store in Find

Ex:Create 1 cmdOK
ctrl-W (SAVE)

MODI CLASS fIND
SELECT FIND.VCX,SELECT CLASSNAME FINDDEPT
CLICK OPEN

THEN THE FORM THAT U CREATE JUST NOW APPEAR (YOU MAY MODIFY HERE)
u need to redesign the popup menu. Include lblCode, lblname,lstfind,label(Press Esc to exit), lbl (Order By),optgrporder(Code & name, cmdOK & cmdCancel.


CmdOK.click
if thisform.lstfind.listcount > 0 then
select dept
thisform.retcode = dept_code
thisform.retname = dept_name
else
thisform.retcode = ""
thisform.retname = ""
endif
thisform.hide

CmdCancel.click
thisform.retcode = ""
thisform.hide

LstFind.doubleclick
if thisform.lstfind.listcount > 0 then
select dept
thisform.retcode = dept_code
thisform.retname = dept_name
else
thisform.retcode = ""
thisform.retname = ""
endif
thisform.hide

LstFind.click
LPARAMETERS nKeyCode, nShiftAltCtrl
if nkeycode = 32 then && Spacebar key
do case
case thisform.optgrporder.value = 1
thisform.optgrporder.value = 2
thisform.optgrporder.optname.click()
this.refresh
case thisform.optgrporder.value = 2
thisform.optgrporder.value = 1
thisform.optgrporder.optcode.click()
this.refresh
endcase
endif
Finddept.Activate
thisform.lstfind.requery
if thisform.lstfind.listcount > 0 then
thisform.lstfind.listindex = 1
thisform.lstfind.setfocus
else
thisform.cmdcancel.setfocus
endif

Finddept.Keypress
LPARAMETERS nKeyCode, nShiftAltCtrl
if nkeycode = 27 then
thisform.retcode = ""
thisform.hide
endif

Ctrl -w to save

 

[VCat88]

modi form test1
U need to include New Method "FindDept"

put in thisform.finddept.show()

Take an Example of apply find fiscal:-
Coding for cmdfscl.click
this.enabled = .F.
thisform.findfscl.show()
if thisform.findfscl.retfscl <> 0 then
thisform.txtfscl.value = thisform.findfscl.retfscl
startdate = &quot;1/&quot; + alltrim(str(month(f_date))) + &quot;/&quot; + alltrim(str(Year(f_date)))
thisform.lblfscl.caption = &quot;From : &quot; + dtoc(ctod(startdate)) + &quot; To &quot; + dtoc(F_date)
endif
this.enabled = .T.