Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
REPORTING ON THE FLY

Wouldn't it be nice if we could give our users a way to create their own reports? Especially if the form is generic and can be executed with a simple command like

do PrintTable with TableName

This should bring up a form as shown below. The available columns will be filled automatically depending on the name of the table.



And finally should end up with a report like



For this we require two tables DataDictionary.dbf and PrintDef.dbf. The example of the tables can be found in the appendix.

The Form


Create the form as shown



Form Init Event
When the form is called, the DataDictionary is checked to see if there is a "Period" field in the table. Depending on that, dynamically hide or make visible the period objects as required and resize and relocate the other objects to fill the form. Create a cursor for List1:

CREATE CURSOR list1 (fieldname c(254), ccaption c(50), dd_ID n(5), startcol n(3)) 

DD_ID will have the ID of the DataDictionary table corresponding to the selected field. StartCol is a numeric field indicating the starting column positing of the column. This will be calculated at the time of creation of the FRX file.

Populate TreeView
Scan the DataDictionary for allt(TableName)==m.TableName and (PrintOK or not empty(LinkedTabl)
If PrintOk add a node to the TreeView.
If not empty(LinkedTabl)
Add node of DataDictionary to Tree View as Parent
Add all Columns of the Linked Table as child nodes

Arrow Click Events
Set up the click events of the two arrow buttons to select/deselect columns. Be sure not to allow a field to be selected twice, else 2 columns will have the same name in the cursor.

Creating the Cursor
In the Click Event of the Print Button, first create the cursor for the report. The required SQL Statement for the current example would be

SELECT 	transfer.transfer_n, transfer.trns_dt, lto.store_name as to_store_name, ;
	trfitem.descriptio as itm_descriptio, transfer.quantity, ;
	round(transfer.quantity*trfitem.rate,2) as value, transfer.from_store as group1, ;
	'From:'+Lfrom.store_name as grouphead1 ;
FROM transfer ;
	LEFT JOIN transfer lfrom ON transfer.from_store=lfrom.code ;
	LEFT JOIN transfer lto ON transfer.to_store=lto.code ;
	LEFT JOIN itemmast trfitem ON transfer.item=itemmast.code ;
WHERE BETWEEN(transfer.trns_dt,m.start,m.end) ;
ORDER BY group1, transfer.transfer_n

The SQL Statement can be broken into
1: Field List
2: Table from where to select
3: Joins
4: Where clause
5: Order clause

Field List

A scan of the list1 cursor will give you the Field List. Change the alias of each linked table to prevent duplicates. Also prefix column names of linked tables to prevent duplicates. Add to this the group fields from the Printdef table. Rename each group column as Group1, Group2 etc. Add to this the grouphead fields from the Printdef table. Rename each group column as GroupHead1, GroupHead2 etc.

Table from where to select
The command that called the form do printmodule with "TableName" gives the Table from which to select.

Joins
Scan the Data Dictionary for not empty(LinkedTabl) to get the join condition. Remember to change the alias of the linked table to prevent duplicate. The LinkedExpr has to be updated to reflect the changed table alias.

Where clause
Where clause is required for report pertaining to a period.

If required
Locate for alltrim(TableName)==m.tablename and period
m.start=thisform.start.value
m.end=thisform.end.value
m.where=’ between(‘+allt(FieldName)+’,m.start,m.end)’

Order clause
The order clause will be from the Printdef Table. Remember that each group expression has been renamed to group1, group2 etc. Assemble the SQL string and run it to get the cursor.

Creating the FRX
Scan the list1 cursor to:
1: Find out the total number of characters in the width of the report. The list1 has a column which links to the ID field of the DataDictionary and the DataDictionary has the width of the field. For memo fields, you have to update the width to whatever you want it to be. In case you have century on, then update the width of date field to 10 otherwise keep it at 8. For numeric fields that have to appear in the group footer and summary bands as totals, you can consider increasing their field width in the DataDictionary if required.
2: Also find out the maximum number of lines required for the column headers.
3: Update the StartCol field of the list1 cursor.

Use PRTINFO() function to find the orientation and then the width of the paper. Once you know the width of paper and number of characters required, you can find the font size that will fit the report. The width required is:

FONTMETRIC(6,Fontname,FontSize) * number of characters * 0.0155 (approx) inches.

Use this to find the font size required to cover all the columns. All dimensions in the FRX files are in 1/10000 of an inch. In case you want a field to be 0.5 inch in width, you have to put 5000 in the width column. Similarly for Height, both of bands and of the elements. The height of each line depends on the font and font size used. It will be:

FONTMETRIC(1,Font of Report, Font Size) * 0.0102 approx.

The height of a horizontal line is 100 approx. for 1 Point Pen. (0.01 inch). The report is a database file consisting of
1: The first record (Standard for all reports)
2: Page Header Band (1 Record. Basically contains height of the band)
3: Group Header Band (1 Record for each group. Contains height of the band and the group expression)
4: Detail Band (1 Record. Contains Height of the Band)
5: Group Footer Band (1 Record for each group. Contain Height of the band)
6: Page Footer Band (1 Record. Contains height of band)
7: Summary Band (1 Record. Contains height of band)
8: Report Elements ( 1 Record for each element – Expression, Label, Line)
9: Final Record (Standard for all reports)

Create a temporary Table with the structure of the FRX file and extension of FRX.

1: Add the first record
INSERT INTO report (PLATFORM, UNIQUEID, TIMESTAMP, OBJTYPE, OBJCODE, VPOS, WIDTH, FONTFACE, FONTSIZE) ;
VALUES ('WINDOWS', SYS(2015), 0, 1, 53, 1, -1, Font of Report, Font Size of Report)

2: Add the Page Header band
Find the height required of the Page Header band depending on number of horizontal lines and number of text lines required.
INSERT INTO report (PLATFORM, UNIQUEID, OBJTYPE, OBJCODE, HEIGHT) ;
VALUES ('WINDOWS', SYS(2015), 9, 1, Page Header Height)

3: Add a Group Header Band for each Group
- The group expression has been changed to Group1, Group2 … in the SQL Query
- Calculate the Height required for the Group Header Band
INSERT INTO report (PLATFORM, UNIQUEID, OBJTYPE, OBJCODE, HEIGHT, EXPR) ;
VALUES ('WINDOWS', SYS(2015), 9, 3, Band Height, 'Group'+ALLTRIM(STR(Group Number)) )

4: Add Detail Band
- Calculate the Height required for the Band
INSERT INTO report (PLATFORM, UNIQUEID, OBJTYPE, OBJCODE, HEIGHT) ;
VALUES ('WINDOWS', SYS(2015), 9, 4, Band Height)

5: Group Footer
- Calculate the Height required for each Band
- Add a record for each band
INSERT INTO report (PLATFORM, UNIQUEID, OBJTYPE, OBJCODE, HEIGHT) ;
VALUES ('WINDOWS', SYS(2015), 9, 5, Band Height)

6: Page Footer
INSERT INTO report (PLATFORM, UNIQUEID, OBJTYPE, OBJCODE, HEIGHT) ;
VALUES ('WINDOWS', SYS(2015), 9, 7, Page Footer Height)

7: Summary Band
INSERT INTO report (PLATFORM, UNIQUEID, OBJTYPE, OBJCODE, HEIGHT) ;
VALUES ('WINDOWS', SYS(2015), 9, 8, Band Height)

8: Report Elements
- Common columns for Labels, Expressions/Fields and Lines are:

Column Name Value Column NameValuePLATFORM WINDOWS UNIQUEID SYS(2015) TIMESTAMP 0 VPOS Vertical Position in 1/10000 of an inch. This includes the height of all the bands above it. 2083.333 has to be added to this for each band title.

In the above example, to calculate vpos of OurElement, add the height of Page Header, the 2 Group Headers and then 3 * 2083.333 for the 3 bands.HPOS Horizontal Position of element. Use StartCol in List1 cursor to calculate HEIGHT Height. 100 for horizontal line WIDTH Width. FONTMETRIC(6,Fontname,FontSize) * number of characters * 0.0155 PENRED -1 PENGREEN -1 PENBLUE -1 FILLRED -1 FILLGREEN -1 FILLBLUE -1 TOP .T. SUPALWAYS .T. SUPRPCOL 3

- Columns for Labels

Column Name Value OBJTYPE 5 EXPR Label enclosed in single Quote FONTFACE Font Face FONTSIZE Font Size MODE 1

- Columns for Expressions / Fields

Column Name Value OBJTYPE 8 EXPR Expression PICTURE Picture FILLCHAR C / N for Character / Numeric FONTFACE Font Face FONTSIZE Font Size MODE 1 TOTALTYPE 0 for all non numeric fields. 0 for all fields in the detail band DataDictionary.TotalType for numeric fields in the Group Footer and Summary Band. RESETTOTAL 5 + Group Number for Resetting on change of Group 1 for Resetting at end of report

- Columns for Horizontal Line

Column Name Value OBJTYPE 6 PENSIZE 1 PENPAT 8 OFFSET 1

9: Final Record for FRX
INSERT INTO report (PLATFORM, OBJTYPE, NAME, EXPR) ;
VALUES ('WINDOWS', 25, 'dataenvironment', 'Name = "Dataenvironment"')

- Close the FRX Table.
- Select the data cursor
- REPORT FORM temporary report file to get the printout

Acknowledgement
Ken Murphy for previewing the article and giving a lot of valuable pointers on how to present it.

APPENDIX
- DataDictionary.dbf:

id tablename fieldname fieldtype fieldlen fielddec ccaption Hdg linkedtabl linkedexpr linkedalia fld_prefix period printok totaltype expr 108 ITEMMAST CODE N 5 0 Item Code           .F. .F. 0   109 ITEMMAST DESCRIPTIO C 40 0 Item/Narration
Description Item Narration         .F. .T. 0   110 ITEMMAST UNIT C 5 0 Unit Unit         .F. .T. 0   122 STORES CODE N 2 0 Code           .F. .F. 0   123 STORES STORE_NAME C
25 0 Name of Location Name of Location         .F. .T. 0   124 TRANSFER PERIOD C 6 0 Period           .F. .F. 0   125 TRANSFER TRANSFER_N N
10 0 Transfer No. Transfer No.         .F. .T. 0   126 TRANSFER TRNS_DT D 8 0 Transfer Date Date         .T. .T. 0   127 TRANSFER FROM_STORE N 2 0 From   STORES STORES.CODE LFROM FM_ .F. .F. 0  
128 TRANSFER TO_STORE N 2 0 To   STORES STORES.CODE LTO TO_ .F. .F. 0   129 TRANSFER ITEM N 5
0 Item   ITEMMAST ITEMMAST.CODE TRFITEM ITM_ .F. .F. 0   130TRANSFER QUANTITY N 11 3 Quantity Quantity         .F. .T. 0   131 TRANSFER VALUE N 12 2 Value Value         .F. .T. 2 ROUND(TRANSFER.QUANTITY*TRFITEM.RATE,2)

Hdg is a memo field and each line in the field corresponds to one line in the heading.

- PrintDef.dbf

tablename Group order reportnarr grouphead TRANSFER TRANSFER.FROM_STORE TRANSFER.TRANSFER_N Transfer Register 'From:'+Lfrom.STORE_name

Group and GroupHead are memo fields and each line corresponds to one group

GENDBC OUTPUT

* *********************************************************
* *
* * 29/11/06 PRINTTABLE.DBC 18:16:28
* *
* *********************************************************
* *
* * Description:
* * This program was automatically generated by GENDBC
* * Version 2.26.67
* *
* *********************************************************

DisplayStatus([Creating database...])
CLOSE DATA ALL
CREATE DATABASE 'PRINTTABLE.DBC'
DisplayStatus([Creating table PRINTDEF...])
MakeTable_PRINTDEF()
DisplayStatus([Creating table DATADICTIONARY...])
MakeTable_DATADICTIONARY()
DisplayStatus([Finished.])

FUNCTION MakeTable_PRINTDEF
	***** Table setup for PRINTDEF *****
	CREATE TABLE 'PRINTDEF.DBF' NAME 'PRINTDEF' (TABLENAME C(20) NOT NULL, ;
		GROUP M NOT NULL, ;
		ORDER C(20) NOT NULL, ;
		REPORTNARR C(50) NOT NULL, ;
		GROUPHEAD M NOT NULL)

	***** Create each index for PRINTDEF *****
	SET COLLATE TO 'MACHINE'
	INDEX ON TABLENAME TAG TABLENAME

	***** Change properties for PRINTDEF *****
ENDFUNC

FUNCTION MakeTable_DATADICTIONARY
	***** Table setup for DATADICTIONARY *****
	CREATE TABLE 'DATADICTIONARY.DBF' NAME 'DATADICTIONARY' (ID N(5, 0) NOT NULL, ;
		TABLENAME C(20) NOT NULL, ;
		FIELDNAME C(20) NOT NULL, ;
		FIELDTYPE C(1) NOT NULL, ;
		FIELDLEN N(3, 0) NOT NULL, ;
		FIELDDEC N(3, 0) NOT NULL, ;
		CCAPTION C(40) NOT NULL, ;
		LINKEDTABL C(20) NOT NULL, ;
		LINKEDEXPR C(254) NOT NULL, ;
		LINKEDALIA C(20) NOT NULL, ;
		FLD_PREFIX C(10) NOT NULL, ;
		PERIOD L NOT NULL, ;
		PRINTOK L NOT NULL, ;
		HDG M NOT NULL, ;
		TOTALTYPE N(1, 0) NOT NULL, ;
		EXPR C(254) NOT NULL)

	***** Create each index for DATADICTIONARY *****
	SET COLLATE TO 'MACHINE'
	INDEX ON TABLENAME TAG TABLENAME
	INDEX ON ID TAG ID

	***** Change properties for DATADICTIONARY *****
ENDFUNC

FUNCTION DisplayStatus(lcMessage)
	WAIT WINDOW NOWAIT lcMessage
ENDFUNC

ABOUT THE AUTHOR: TUSHAR KANVINDE

tushar Kanvinde I have been working as a software developer since the late 1980s. Most of my experience is in developing software on dBase, Foxbase, Foxpro and now VFP. I also develop web based software in php. I am a partner in Softpro based in Goa, India.

FEEDBACK

nyron williams @ 12/27/2006 3:51:45 PM
Great I've always wanted to know how to do something like that.......Excellent Idea!!

Sheila Mae Sentin @ 12/28/2006 2:33:08 AM
Hi sir. Can you include in this article a sample code or zip file as demonstration?
Thanks a lot.

sheila

tushar @ 12/28/2006 6:42:39 AM
Sheila

Try to develop something on the lines of what I have given. In case you run into any problem, let me know through a post and I will do my best to help you out.

mike castillo @ 1/5/2007 5:26:10 AM
Here is some codes I would like to share, this code extracts and builds APPEND BLANK Codes from the FRX table then converts it to append blank so just incase you have already a template for your Report then this code is useful for you so for you can integrate easily to Tushars great article. Then the txt contents are located in c:\txt.txt file. Just take note of the second line code on opening / hacking the frx.


SELECT 0
USE c:\template2.frx AGAIN
nFld = AFIELDS(aFld)
vcCom = ""
vCRLF = CHR(13)+CHR(10)
SCAN
vcCom = vcCom + vCRLF + vCRLF + "SELECT REPORT" + vCRLF + "APPEND BLANK" + vCRLF + "REPLACE "
vcCom2 = ""
FOR n = 1 TO nFld
vcFld = aFld(n,1)
IF !EMPTY(&vcFld)
DO CASE
CASE TYPE("&vcFld")="C" OR TYPE("&vcFld")="M"
vcFldval = "'" + STRTRAN(ALLTRIM(&vcFld),vCRLF,"'+CHR(13) + CHR(10)+;" + vCRLF + "'") + "'"
CASE TYPE("&vcFld")="N"
vcFldval = ALLTRIM(STR(&vcFld))
CASE TYPE("&vcFld")="L"
vcFldval = IIF(&vcFld,".T.",".F.")
ENDCASE
vcCom2 = vcCom2 + IIF(!EMPTY(vcCom2),",;" + vCRLF,"") + vcFld + " WITH " + vcFldVal
ENDIF
ENDFOR
vcCom = vcCom + vcCom2
ENDSCAN
STRTOFILE(vcCom,"c:\txt.txt")

Elijah Kihuga @ 1/9/2007 8:20:32 AM
this looks yummy! There is a client who requires small adjustments to the reports, and sounds like I should give this a try!

Ronan Masangcay @ 2/15/2007 8:59:19 AM
I also have a client who requires this. Thanks a lot Tushar for sharing!!! Will try this.

Stefanus Eddy @ 2/16/2007 2:24:54 AM
great job Mr. Tushar,

but where we can find a great job source to tryout???.

or send to may email address:teknik.rajawali@gmail.com

thank you

tushar @ 2/16/2007 9:15:44 AM
There is no source for sharing. You have to create your own based on the article. In case you have problems in developing, post your query in the foxite forum. I will be there to help you out.

Regards
Tushar

dwitee panda @ 2/22/2007 9:09:38 AM
hi tushar i need some help in VFP reporting

i have got some field whcih are working fine when kept in group footer but they were not working in similar manner when kept in its group header..
when the fields are kep in group header then it is printed in in all the pages.
The fields are like no. of shipment of X type, Y type ,Z type
then theres a total field which counts total no. of shipments including all (x,y,z types).

what changes sud i do so that i can bring the field to group header.

Arif Khanbahadur @ 3/16/2007 1:27:06 PM
hi tushar,
i'm looking for this, it really help me.
thanks

mk sharma @ 9/1/2007 11:57:35 AM
what is used to create a treeview.

warm regards,
mk.

tushar @ 9/1/2007 12:57:36 PM
MK

I have used the Microsoft Treeview Control.

Regards
Tushar

Jeffrey Briones @ 2/28/2008 12:00:01 PM
Mr. Tushar,

Do you know how to link pictures in the report dialog option? (In having an error field must be a generic field!) Hope you can help me with the codes. Thanks.
email me at briones@jurong.com.

Regards,
Jeffrey (Abu Dhabi)

sanjay bhutani @ 6/24/2009 3:53:42 AM
Mr.Tushar

vvvv good article. its help lots of us.

If you any article on make a invoice for the help of grid.please send me

Regards
Sanjay Bhutani

tushar @ 6/24/2009 5:44:32 AM
Sanjay

Ask this question in the foxite forum and you should get an answer.

Markkas @ 1/23/2010 9:38:59 AM
Hi Tushar,
Do you know any links to ppt tutorials for Visual froxpro?

tushar @ 1/23/2010 9:49:21 AM
Hi Markkas

You will get a better response if you put this query to the foxite forum.

PEDRO J GONZALEZ @ 9/8/2017 2:53:21 AM
opoperation tech foxtice it mangers ceil phone hacker operation tech



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: