Thank you.
The thing is that we are porting our application to work with both SQL Server and VFP back end, maybe later add MySQL. Originally all our reports were written using VFP report writer, but with multiple backend I thought that Crystal Report will work better and will be easier to maintain; create one report and change connections depends on the back end. Or so I thought.... Now, I can see that while SQL ODBC allows me to work with SQL Views and Stored Procedures seamlessly, this is not so easy for VFP. So I cannot create stored procedure in both to get the same result and build report based on it.
Oh well, will try a different approach or create a second set of reports in VFP
Thank you again
>>Thank you so much for your sample code. I am not very fluent with Crystal report yet, so please bear with me. When you created your vente_Resident.rpt inside the Crystal report have you added view using data export?
>
>
>
>
>
>Was the view parameterized in VFP ?
>
>if you want say "filter" , the answer is NOT.
>
>
>put a field ( noitelephoneIdReportActif ) in your table ( eg CUstomer.dbf )
>for the key .
>BEFORE running the CR , change this key with a value...
>
>
>in your view ( where clause )
> WHERE Resident.noitelephoneIdReportActif # 0
> ORDER BY VENTE.Nan_cloture , VENTE.Cnofact
>
>to filter , i use only the calculed field.
>
>
>
> .AddFieldsCalculed( "@nMois" , goapp.oReportSelection.spinnerMois.VALUE )
>
>
>
>simplest is to make an free table BEFORE the report and to build the report then
>
>
>
>>
>>Thank you again for your time.
>>
>>
>>>sorry for my poor english.
>>>
>>>here is a sample with a view.
>>>
>>>
>>>
>>>DEFINE CLASS vente_Resident AS CrystalReportEnvironment
>>> ReportFileName = "vente_Resident.rpt"
>>> aCursors[1] = "lv_cr_vente"
>>> *--------------------------------------------------
>>> FUNCTION BeforePrint()
>>> LOCAL lretval,;
>>> crxDbField
>>> WITH THIS
>>> .AddFieldsCalculed( "@nMois" , goapp.oReportSelection.spinnerMois.VALUE )
>>> .AddFieldsCalculed( "@nAn" , goapp.oReportSelection.spinnerAn.VALUE )
>>> .AddFieldsCalculed( "@lallow_imprime_detail_vente" , goapp.oReportSelection.chkdetail.VALUE )
>>> .Put_Compagny( COORD_SOC_ABREGE_840 , MRPA_LOC )
>>> *-- Changement de condition pour le groupe.
>>> IF goapp.oReportSelection.Cboordre_resident_chambre1.LISTINDEX = 2
>>> crxDbField = .crReport.DATABASE.TABLES.ITEM(1).FIELDS.ITEM(20)
>>> *-- GROUPE HEADER 2
>>> .crReport.areas.ITEM("GH1").groupConditionField = crxDbField
>>> ENDIF
>>> ENDWITH
>>> lretval = DODEFAULT()
>>> RETURN lretval
>>> ENDFUNC
>>> *---------------------------------------------------
>>>ENDDEFINE
>>>>>>
>>>you must create a dns
>>>
>>>
>>>
>>> *---------------------------------------------------------------------------------------------------
>>> *-- Change the data source of a report at runtime.
>>> PROCEDURE changedataSource
>>> *------------------------------------------------------------------------------------------------
>>> LPARAMETERS cTableName, cNewTableName , oreport
>>>
>>> *
>>> * cNewODBC ODBC DSN to use for this run
>>> * cTableName Name of the table this is to be applied to
>>> * cNewTableName New name of table if appropriate
>>> *
>>> LOCAL nItems, lSuccess , cNewODBC
>>> lSuccess = .F.
>>>
>>> cNewODBC = THIS.cDNS
>>>
>>>
>>> IF VARTYPE(cNewODBC) # 'C' OR VARTYPE(cTableName) # 'C'
>>> RETURN lSuccess
>>> ENDIF
>>>
>>> IF EMPTY(cNewTableName)
>>> cNewTableName = cTableName
>>> ENDIF
>>>
>>> *-- 09/2004
>>> IF VARTYPE( oreport ) <> 'O'
>>> oreport = THIS.crreport
>>> ENDIF
>>>
>>>
>>> *
>>> * Ensure '.dsn' is not included - also remember the ODBC name is CaSe SeNsItIvE
>>> *
>>> cNewODBC = STRTRAN( cNewODBC, '.dsn', '')
>>>
>>> * WITH THIS.crreport
>>> WITH oreport
>>>
>>> nItems = .DATABASE.TABLES.COUNT
>>>
>>> FOR nX = 1 TO nItems
>>> *
>>> IF UPPER( ALLTRIM( .DATABASE.TABLES.ITEM( nX ).Location ) ) = cTableName
>>> * WAIT WINDOW "Connection Serveur Ok " + .DATABASE.TABLES.ITEM( nX ).Location TIMEOUT 4
>>>
>>> .DATABASE.TABLES.ITEM( 1 ).SetLogOnInfo( cNewODBC , "", "" , "" )
>>> .DATABASE.TABLES.ITEM( 1 ).Location = cNewTableName
>>> lSuccess = .T.
>>>
>>>
>>> EXIT FOR
>>> ENDIF
>>> ENDFOR
>>>
>>> ENDWITH
>>>
>>> * THIS.crApplication.setmatchlogoninfo(.T.)
>>> *-- Warning !!! 06/2004 Dans le rapport sur le nouveau financement on a un sous rapport
>>> *-- composé d'une table Indépendante ccomp_fonc.DBF et ccompense.dbf.
>>> *-- La connection est ODBC FoxproTable.
>>>
>>> RETURN lSuccess
>>> ENDPROC
>>>
>>>
>>>
>>>
>>>
>>>>Thank you Bernhart.
>>>>Does this book has anything about using VFP Views?
>>>>
>>>>
>>>>>hi Yelena,
>>>>>
>>>>>I used the book of graig.i's a excellent document BUT , uses free table (that still functions with CR XI)!
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Thank you Naomi,
>>>>>>I read this article, but unfortunately it doesn't answer my question.
>>>>>>It is however has pretty good information on integrating Crystal Report Viewer into VFP application.
>>>>>>
>>>>>>If you know anyone who is "on first name bases" with crystal report for VFP maybe you can ask them for me. :)
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>Hi All,
>>>>>>>>I am new to Crystal report and having hard time with VFP parameterized views. Using VFP OLEDB drivers I am able to see all the views including parameterized ones, but I cannot add them to the report or create a command that will use them? Can anyone tell me if it is possible to use VFP parameterized Views in Crystal Report and if it is, please tell me how.
>>>>>>>>
>>>>>>>>Thank you in advance.
>>>>>>>>
>>>>>>>>P.S. I am using Crystal Report 2008
>>>>>>>
>>>>>>>I haven't tried integrating VFP with Crystal Report, but check
http://www.craigberntson.com/articles/integratingcr.pdf (and if you can find his book).
