HOW TO DEFINE DYNAMIC REPORT (COLUMNS)?

[sridhar412]

I am trying to use 'CREATE REPORT' inside a program to define dynamic columns in a report. (As the database structure of the report changes dynamically, i need to change).

I tried with 'Create report file ... fields <mvariable>
where mvariable is a string of field names with ',' as seperator.

I get an error mvariable not found. ie. i am not able to use
a variable in create report syntax.

any alternatives?

Thanks
sridhar

 

[ramani]

HI Sridhar,


mFields = &quot;myField1, myField2, myField3&quot;

CREATE REPORT myReportFile FROM myTableName FIELDS &mFields
REPORT FORM myReportFile PREVIEW IN SCREEN TO PRINTER PROMPT

This will work
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[sridhar412]

Thanks ramani,


I did try it. It does not work. gives error with variable name. I took the field names using afields(). The variable content is ok. when i give variable name (with or without macro &) it gives me error. (as mvariable not found)

sridhar

 

[wjs2]

Hello sridhar,

I tried ramani's code and it worked just fine (VFP 6.0). The only thing I had to add to what was posted was to make sure the table needed for the report was open. This is the exact code that ran:

use 'e:\Generic\Getap\exec\Checkviewer\vendname.dbf' in 1 alias inp share
mFields = &quot;vendno, vendor, netamt, disdol, ckno&quot;
CREATE REPORT myReportFile FROM inp FIELDS &mFields
REPORT FORM myReportFile PREVIEW IN SCREEN TO PRINTER PROMPT

return

Good Luck. Hope this helps.
WJS

 

[sridhar412]

Thanks guys, it works. i made a mistake of placing a COMMA
at the end of variable while building in the FOR loop.
The file need not be open. it opens automatically.

Additional Queries :

1. how to define header, footer, paper orientation (landscape etc) while using create report

2. Defining column heading dynamically.

Thanks in advance.

sridhar

 

[Mike Gagnon]

sridhar412

At this point you may want to consider create a whole report on-the-fly:
N.B. This is not my code

** This program demonstrates how to create FoxPro Reports on the fly.
** © by Markus Egger (EPS Software Corp.) 1997
** eMail: max@foxgang.net
** WWW: [URL unfurl="true"]Http://www.foxgang.net/eps[/URL]

** We set some variables to flter the data.
** The values are set to the date when this demo was created
** Obviously, this would change in a real world.
LOCAL ldStartDate
ldStartDate = DATE()

** We open the rooms table.
** Note: Feel free to modify this table to see some different results...
SELECT 0
USE Rooms

** First of all, we create an array that hold all the names/ numbers
** of our rooms. This array could be populated from a table...
DIME paRooms(14)
paRooms(1) = "101:"
paRooms(2) = "102:"
paRooms(3) = "103:"
paRooms(4) = "104:"
paRooms(5) = "105:"
paRooms(6) = "201:"
paRooms(7) = "202:"
paRooms(8) = "203:"
paRooms(9) = "204:"
paRooms(10) = "205:"
paRooms(11) = "206:"
paRooms(12) = "301:"
paRooms(13) = "302:"
paRooms(14) = ""

** We copy the original report to a temporary copy
** Note:	In a real world scenario, we would add some more checking,
**			but this would just complicate this demo.
IF File("PlanTemp.frx")
	ERASE PlanTemp.frx
ENDIF
COPY FILE Plan.frx TO PlanTemp.Frx
IF File("PlanTemp.frt")
	ERASE PlanTemp.frt
ENDIF
COPY FILE Plan.frt TO PlanTemp.Frt

** We open the report like a dbf file and add additional objects
SELECT 0
USE PlanTemp.frx EXCLUSIVE ALIAS Frx

** We locate for the first object that's not a visible object 
** and move the record pointer right before this record.
LOCATE FOR ObjType > 20
IF Found()
	SKIP -1
ELSE
	USE
	RETURN .F.
ENDIF

** We insert another record for every day we want to display.
LOCAL lnCounter
FOR lnCounter = 1 TO 27					&& The report shows 27 days...
	InsertFRXRecord()
	REPLACE Frx.ObjType	WITH 8		&& This is a field
	REPLACE Frx.ObjCode	WITH 0
	REPLACE Frx.Expr		WITH ["]+Alltrim(Str(Day(ldStartDate-1+lnCounter)))+["]		&& This is the expression
	REPLACE Frx.VPos		WITH 13000	&& This is the vertical position of the field
	REPLACE Frx.HPos		WITH 16000 + ( 3125 * (lnCounter-1) )		&& 16000 is the left position of the first field. 3125 is the space between the fields...
	REPLACE Frx.Height		WITH 3000		&& The height is not really important as long as it's high enough. (in other words: it doesn't matter if it's to high)
	REPLACE Frx.Width		WITH 2500		&& This is the field width
	REPLACE Offset			WITH 2			&& center allignment
ENDFOR

** We scan through the rooms table and check for reservations
SELECT Rooms
FOR lnCounter = 1 TO ALen(paRooms,1)		&& One iteration for each room...
	** The following might not be fast, but enough for this demo.
	** This demo is not supposed to show fast data access...
	LOCATE
	SCAN FOR Alltrim(Room) == Alltrim(paRooms(lnCounter))
		** We insert a rectangle...
		InsertFRXRecord()
		REPLACE Frx.ObjType		WITH 7		&& This is a rectangle
		REPLACE Frx.ObjCode		WITH 4
		REPLACE Frx.PenRed			WITH 255
		REPLACE Frx.PenGreen		WITH 0
		REPLACE Frx.PenBlue		WITH 0
		REPLACE Frx.FillRed			WITH 255
		REPLACE Frx.FillGreen		WITH 0
		REPLACE Frx.FillBlue		WITH 0
		REPLACE Frx.PenSize		WITH 0			&& Fill patterns and lines
		REPLACE Frx.PenPat			WITH 8
		REPLACE Frx.FillPat			WITH 1
		REPLACE Frx.Mode			WITH 0			&& Opaque
		REPLACE Frx.Offset			WITH 0			&& No curvature
		REPLACE Frx.ResetTotal		WITH 0
		REPLACE Frx.VPos			WITH 17000 + (3750*(lnCounter-1))			&& 17000 is the first line...
		REPLACE Frx.HPos			WITH 17125 + ( 3125 * (StartDate-ldStartDate) )			&& 17125 is the left position of the first field. 3125 is the space between the fields...
		REPLACE Frx.Height			WITH 2600									&& The height is not really important as long as it's high enough. (in other words: it doesn't matter if it's to high)
		REPLACE Frx.Width			WITH (3125*(EndDate-StartDate-1)) + 2500	&& This is the field width. 3125 is the space between the columns. We also add a little at the back and front.

		** We insert a field...
		InsertFRXRecord()
		REPLACE Frx.ObjType		WITH 8		&& This is a field
		REPLACE Frx.ObjCode		WITH 0
		REPLACE Frx.Expr			WITH ["]+Alltrim(Name)+["]		&& This is the expression
		REPLACE Frx.VPos			WITH 17200 + (3750*(lnCounter-1))			&& 17000 is the first line...
		REPLACE Frx.HPos			WITH 17125 + ( 3125 * (StartDate-ldStartDate) )			&& 17125 is the left position of the first field. 3125 is the space between the fields...
		REPLACE Frx.Height			WITH 2400									&& The height is not really important as long as it's high enough. (in other words: it doesn't matter if it's to high)
		REPLACE Frx.Width			WITH (3125*(EndDate-StartDate-1)) + 2500	&& This is the field width. 3125 is the space between the columns. We also add a little at the back and front.
		REPLACE Frx.Offset			WITH 2			&& center allignment
	ENDSCAN
ENDFOR

** We close the report
SELECT Frx
USE

** We also close the rooms table since we don't need it anymore.
SELECT Rooms
USE

** All the information we want to print will be generated on the fly.
** All the information will be a single object in the report,
** rather then data from a table. Since the report relies on having data
** coming from a table or cursor, we create a dummy cursor with
** one dummy record. This will cause the report to be printed once.
SELECT 0
CREATE CURSOR Dummy (x c(1))
APPEND BLANK

** We execute the report in preview mode...
REPORT FORM PlanTemp PREVIEW

** We close the dummy cursor...
USE

RETURN .T.




FUNCTION InsertFRXRecord
** This function inserts another record into the frx file and sets some default values

LOCAL lnOldSelect
lnOldSelect = Select()

SELECT Frx

INSERT BLANK
REPLACE Platform		WITH "WINDOWS"
REPLACE UniqueId		WITH Sys(2015)
REPLACE TimeStamp		WITH Val(Sys(3))
REPLACE FillChar		WITH "C"
REPLACE PenRed			WITH 0
REPLACE PenGreen		WITH 0
REPLACE PenBlue		WITH 0
REPLACE FillRed			WITH -1
REPLACE FillGreen		WITH -1
REPLACE FillBlue		WITH -1
REPLACE FontFace		WITH "Verdana"
REPLACE FontStyle		WITH 1
REPLACE FontSize		WITH 10
REPLACE Mode			WITH 1
REPLACE Top			WITH .T.
REPLACE NoRepeat		WITH .F.
REPLACE Spacing		WITH 2
REPLACE Offset			WITH 0
REPLACE Totaltype		WITH 0
REPLACE ResetTotal		WITH 1
REPLACE SupAlways		WITH .T.
REPLACE SupOvFlow		WITH .F.
REPLACE SuprpCol		WITH 3
REPLACE SupGroup		WITH 0
REPLACE SupValChng		WITH .F.


SELECT (lnOldSelect)
RETURN .T.

 

[sridhar412]

hi mgagnon,

Thanks. Let my try it. probably will get back with more!!

sridhar

 

[Mike Gagnon]

sridhar412

The only thing that is missing from the above code is the table &quot;Rooms&quot;, and here it is in code:

set date mdy
CREATE TABLE c:\ROOMS FREE (room c(10),startdate D,enddate D,;
name c(30))
INSERT INTO ROOMS (room,startdate,enddate,name) VALUES ("101:",CTOD("01/01/2002"),CTOD("01/10/2002"),"Guest #1")
INSERT INTO ROOMS (room,startdate,enddate,name) VALUES ("102:",CTOD("01/09/2002"),CTOD("01/10/2002"),"Guest #2")
INSERT INTO ROOMS (room,startdate,enddate,name) VALUES ("103:",CTOD("02/10/2002"),CTOD("02/15/2002"),"Guest #3")
GO top
etc....

And the original &quot;plan.frx&quot; file.
If you want to pursue this, give me your email address and I'll send you the whole zipped version.

 

[sridhar412]

mgagnon,

thanks. i wolld like to try this out.

please mail the zip file to

sridhar412@yahoo.com


sridhar

 

[Mike Gagnon]

sridhar412

It's on it's way.

 

[Mike Gagnon]

sridhar412

If you need to further explore the possibilities of modifying a report by code, this link might help:

http://www.foxtalknewsletter.com/FT/FTMag.nsf/0/F9FA874556FBE8768525692E007B8459

 

[PTCruiserII]

I have also written a report PRG that generates a useful report FRX for any selected cursor or DBF. It can also be used as a report wizard. If anyone wants it I can e-mail it. It is a simple PRG and can be called as a procedure call with parameters for Report Title, Page header, FRX name and orientation (landscape or portrait)

 

[sridhar412]

PTCruiserII (Programmer),

Would you please forward a copy to

sridhar412@yahoo.com

as i am looking to print a standard report from a cursor, where in the data structure is changed dynamically.

The earlier example and link are also of great help.

thanks, mike