report from a view

[alank01]

i am a newbie with views and am probably going to ask some very basic questions here. i am more familiar with creating a sql select cursor before running a required report but i have a case were a view seems more appropriate because all the data setup is done in the dataenvironement of the report. the view is very straight forward, estentially ...WHERE cfield = ?mvar.

The view is placed in the data environment of the the report with appropriate relationships. what i would like advice on is how to correctly initialize the ?mvar. how do i pass a variable to a report dataenvironment? the variable mvar is a property of a the calling form. my first attempt is roughly the following.

in the beforeopentables of the report

public mvar
if type('form1') = 'O' AND !isnull('form1')
mvar = form1.mvar
else
mvar= space(20) && fake it out
endif

in the destroy event of the report from
release mvar

the trouble with this is i have to refer to the form name directly. i would prefer thisform.mvar or this.parent.mvar but this does not seem to work. also i'm not happy with the public declaration of mvar.

any suggestions on a better solution would be welcome. also is there a defensive technique for the case where ?mvar is of the wrong type. for instance if i run the view from the command window and get a prompt for the criteria if i put a logical for mvar i get data type mismatch. can the expression be ?(iif(type('mvar'),C,'') or something?

thanks X-)

 

[ramani]

HI
1. In the data environment of the relevant form, for the view you are adding.. put NODATAONLOAD = .T.
2. You dont have to do what you are thinking in BEFOREOPEN TABLES etc.

3. In the report form, you are accepting the Form1.mVar.
Then in the place where you are going to initiate the actual rporting...
i.e. REPORT FORM myReport ....... line of code..
in the previous lines.. you can add...
mVar=Form1.mVar
=REQUERY(myView)
then.. procedd to check if you want to print... if the view is empty.. etc.. and shoot the REPORT FORM codes...

This shall solve your problem.
ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[alank01]

thanks ramani for your response. these are two good tips that i was not really aware of that would be useful for views - NODATAONLOAD=.t. and REQUERY() on the view. basically thats the procedure i would use running a report via sql cursor i.e run query and check for result if _tally greater than 0 run report. what i really wanted to try this time though is move the actual view away from the form and into the dataenviroment of the report the reason being is that there will be potentially 7 different reports called by this one form. traditionally i would have 7 case statements run approriate sql and the appropriate report . what i would like is just have 7 different views but stored in each report so the report could actually be viewed away from the data enviroment of the calling form. maybe what i want to do is not a good idea but i thought i would give it a try. the only difficulty i see with the view in the report is getting that pesky mvar to be initialized or passed correctly. i can do it but i did'nt like my own code :-( if anyone else has done something similar please advise. thanks

 

[ramani]

HI

YOu can still do many reports from within one report form. It all depends on how you set it.
FOr example...
The report choices are set in the form...
From a/c to a/c
from date to date
from type to type
from salesman to salman etc etc.. parameters.
Select Detailed or summary
Select Sort Order (Combo Choice.. By salesman/item.. Item/Saleman etc etc.)

Then when ready.. Click on the command button 'DO REPORT' by user. or cancel Button..

Now the Click event of the 'DO REPORT' command button.. holds the trick what you want.
1. IF ThisForm.txtBox.. whatever are evaluated for filtering the view.
The resulting SQL cursor is made ready and left as the default table for report form. Thi SQL cursor can be a vie a well with proper REQUERY completed.
2. The combo Choice value obtained is used and a
DO CASE .. END CASE is done.
In ths.. cReport = myReport Form according to choice is set.
Fire the repot with macro.. or evaluation
REPORT FORM (myReport) PREVIEW IN SCREEN .. whatever

Hope you get your solution in thi ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com